35276.htm

一本很基础的SQL讲解

<TD class=code bgColor=#e6e6e6><PRE>　　
select top 10 * from #queries_staticstics order by cp desc</PRE></TD></TR></TBODY></TABLE>
<P>上面的语句将把queries_staticstics表中的记录按照某类语句占用总CPU量的比例cp(即sum(cpu)/@sum_cpu)进行排序输出。这让你在分析服务器CPU性能问题的时候快速定位哪一类语句最耗CPU资源，从而对症下药。 
<P>现在让我们看一个实例的输出： 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>　　Use mydb
　　Exec usp_GetAccessPattern 
　　/*你可以输入一个执行时间作为过滤参数，毫秒为单位。如usp_GetAccessPattern 1000*/</PRE></TD></TR></TBODY></TABLE>
<P>输出结果如图 1所示（是部分结果，另外，因为原输出结果横向很长，为方便阅读，把结果从中截断为两部分）： 
<CENTER><IMG src="/files/uploadimg/20061127/1122070.jpg"></CENTER>
<CENTER>图 1：输出结果采样一</CENTER>
<P>上面的例子采样于一家大型公司的业务系统。该系统的问题是应用程序运行缓慢，SQL Server 服务器的CPU高居不下（8个CPU都在90%～100%间波动）。我使用PSSDIAG工具采样2小时左右的数据，然后运行read80trace和usp_GetAccessPattern得出上面的结果。报表一目了然。存储过程DBO.x_DEDUP_PROC在两小时内共运行75次，却占用了90.8％的CPU资源，94.6%的Reads，从访问模式的角度，该存储过程正是导致CPU高和系统性能慢的关键语句。一旦优化了该存储过程，系统的性能问题将迎刃而解。你也许有疑问，两小时内共运行75次,不是很频繁啊。其实你看看这条存储过程的平均CPU时间是681961毫秒，大概11分钟左右。也就是说一个CPU两小时内最多可以执行（60*2）/11＝10条左右，该系统总共有8个CPU，即使全部CPU都用来运行该语句，那么最多也就是10*8＝80条左右。上面执行总数是75，说明该存储过程一直在连续不断地运行。 
<P>那么该系统运行最频繁的语句是什么呢？我从结果中摘取另外一部分如下（图 2）： 
<CENTER><IMG src="/files/uploadimg/20061127/1122071.jpg"></CENTER>
<P>
<CENTER>图 2：输出结果采样二</CENTER>
<P>从上表可以看出，最频繁运行的语句是 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>USE xb SET QUOTED_IDENTIFIER,ANSI_NULL_DFLT_ON…</PRE></TD></TR></TBODY></TABLE>
<P>显然这是一条执行环境配置语句，没有参考价值。倒是另外两条占用语句总数8.2%的语句值得关注： 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>　　SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS……
　　SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS……</PRE></TD></TR></TBODY></TABLE>
<P>在这个例子中，因为关键语句DBO.x_DEDUP_PROC非常突出，甚至上面的两条语句都可以忽略了。 
<P>让我们再多看一个例子（图 3）： 
<CENTER><IMG src="/files/uploadimg/20061127/1122072.jpg"></CENTER>
<CENTER>图 3：输出结果采样三</CENTER>
<P>从上面的例子中, 可以得出关键的语句是: 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>SELECT COUNT(*) FROM GTBL7MS
　　SELECT CaseNO FROM PATIENTDATA_sum WHERE MRN = @P1</PRE></TD></TR></TBODY></TABLE>
<P>后续的检查发现相关的表没有有效的索引，加上索引后性能立即整体地提高了不少.。解决了这两个语句，需要使用同样的手段继续分析和优化，直到系统的性能能够接受为止.。注意性能调优是一个长期的过程，你不太可能一两天就可以把所有的问题都解决。也许一开始可以解决80%的问题，但是后面20%的问题却需要另外80%的时间。</P>
<P><B>使用usp_GetAccessPattern的一些技巧</B> 
<P>usp_GetAccessPattern的输出报表包含了非常丰富的信息。分析报表的时候需要有大局观。你也可以有目的性地选择你需要的信息。如果是CPU性能瓶颈的系统，那么你需要关注CPU占用比例高的那类语句。如果是磁盘IO出现性能瓶颈那么你需要找到那些Reads占用比例大而且平均reads也很高的语句。需要注意的是有时候运行频繁的语句未必就是你需要关注的关键语句。一个最理想的情况是关键语句正好就是最频繁的语句。有时候即使最频繁语句占用的资源比例不高，但如果还可以优化，那么因为放大效应，微小的优化也会给系统带来可观的好处。 
<P>在使用usp_GetAccessPattern的时候多结合@duration_filter参数使用。因为参数以毫秒为单位，建议参数不要小于1000，而应该是1000的倍数 如3000，5000等。该参数常常会给出非常有意思的输出。该输出和不带参数运行的结果会有某些重叠。重叠出现的语句通常正是需要关注的语句。要注意运行最多最密的语句未必有超过1000毫秒的执行时间，所有带参数运行的结果有可能不包括最频繁语句。我常常同时交叉分析四个结果，一个是不带参数运行得到的，另三个分别是使用1000，3000和5000毫秒为参数运行的结果。比较分析这四个结果往往使我对数据库系统的访问模式有非常清晰透彻的理解。 
<P>运行存储过程时你也许会碰到int整数溢出的错误。这是因为表tblBatches 中的reads,cpu 和writes字段是int而不是bigint。可以运行如下语句进行修正： 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>　　alter table tblBatches alter column reads bigint 
　　alter table tblBatches alter column cpu bigint 
　　alter table tblBatches alter column writes bigint</PRE></TD></TR></TBODY></TABLE>
<P>修正后溢出问题就会解决。 
<P><B>蛇足：哪个是HOT 数据库？</B> 
<P>本文到这里就基本上结束了。你已经知道如何使用Read80Trace和usp_GetAccessPattern得到数据库系统的访问模式，以及如何从全局的高度去分析访问模式报表，从而在优化系统的时候做到提纲挈领，胸有成竹。 
<P>除此之外，你还可以应用类似的分析思想得到每个数据库的占用资源比例。这对于SQL Server有多个数据库的情况非常有用。从报表中你可以立即知道哪个数据库是最HOT最消耗系统资源的数据库。语句如下： 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>print 'group by dbid'
declare @sum_total float,@sum_cpu float,@sum_reads float,
@sum_duration float,@sum_writes float
select @sum_total=count(*)*0.01,@sum_cpu=sum(cpu)*0.01,
@sum_reads=sum(reads)*0.01,@sum_writes=sum(writes)*0.01,
@sum_duration=sum(duration)*0.01 from tblBatches

select dbid,
ltrim(str(count(*))) exec_stats,''+ str(count(*)/
@sum_total,4,1)+'%' ExecRatio,
ltrim(str(sum(cpu)))+' : '++ltrim(str(avg(cpu))) cpu_stats,
''+str(sum(cpu)/@sum_cpu,4,1)+'%' CpuRatio ,
ltrim(str(sum(reads) ))+' : '+ltrim(str(avg(reads) ))
 reads_stats,''+str(sum(reads)/@sum_reads,4,1) +'%' ReadsRatio ,
ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration))) 
duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' DurRatio ,
count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/
@sum_reads rp,sum(duration)/@sum_duration dp
into #queries_staticstics_groupbydb from

(select reads,cpu,duration,writes,convert(varchar(2000),
NormText)textdata,dbid from tblBatches 
inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.
hashid
) b group by dbid order by sum(reads) desc

select dbid,ExecRatio batches,CPURatio CPU,ReadsRatio Reads,
DurRatio Duration
from #queries_staticstics_groupbydb</PRE></TD></TR></TBODY></TABLE>
<P>下面是一个上面语句结果的一个例子： 
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>　　dbid 　batches 　CPU 　Reads　 Duration 
　　------ 　-------　 ----- 　-------　 -------- 
　　37 　　21.1% 　18.7% 　29.1%　 27.1% 
　　33 　　12.7% 　32.4% 　19.5% 　24.8% 
　　36 　　5.6% 　28.3% 　15.6% 　26.1% 
　　20 　　53.9% 　2.9% 　14.2% 　2.1% 
　　22 　　0.8% 　　7.2% 　13.2% 　6.6% 
　　25 　　1.0%　　 3.6% 　5.4% 　3.5% 
　　16 　　0.0% 　　1.5% 　1.9% 　0.7% 
　　35 　　2.0% 　　2.7% 　1.8% 　5.7% 
　　7 0.　　1% 　　0.1% 　1.1% 　　0.3%</PRE></TD></TR></TBODY></TABLE>
<P>上面的结果明确地告诉我们ID为37，33和36的数据库是最活跃的数据库。一个有趣的事实是数据库20发出的语句总数比例是53.9%，但是其占用的系统资源比例却不高。</P>
<P>(责任编辑：铭铭 <A href="mailto: [e-mail]">[e-mail]</A> TEL：[phone])</P></td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>