应用事件探查器优化SQL Server系统

核心提示：使用存储过程分析Normalize后的数据有了标准化后的语句就可以使用存储过程进行统计分析了，分析的基本思想是把所有模式一样的语句的Reads，应用事件探查器优化SQL Server系统(3)，CPU和Duration做group by统计，得出访问模式信息：1.某类语句的总共执行次数，举例说：select top 1

使用存储过程分析Normalize后的数据

有了标准化后的语句就可以使用存储过程进行统计分析了。分析的基本思想是把所有模式一样的语句的Reads，CPU和Duration做group by统计，得出访问模式信息：

1.某类语句的总共执行次数，平均读页面数（reads）/平均CPU时间/平均执行时间等。

2.该类语句在所有语句的比例，如执行次数比例，reads比例，CPU比例等。

存储过程的定义以及说明如下：Create procedure usp_GetAccessPattern 8000
@duration_filter int=-1 --传入的参数，可以按照语句执行的时间过滤统计
as begin
/*首先得到全部语句的性能数据的总和*/
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, --这是所有语句耗费的CPU时间
@sum_reads=sum(reads)*0.01, --这是所有语句耗费的Reads数目，8K为单位。
@sum_writes=sum(writes)*0.01,--这是所有语句耗费的Writes数目，8K为单位。
@sum_duration=sum(duration)*0.01--这是所有语句的执行时间总和。
from tblBatches --这是Read80Trace产生的表，包括了Trace文件中所有的语句。
where duration>=@duration_filter --是否按照执行时间过滤
/*然后进行Group by，得到某类语句占用的比例*/
Select 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(writes) ))+' : '+ltrim(str(avg(writes) ))
--writes_stats,''+str(sum(writes)/@sum_writes,4,1) +'%)',
ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration)))
duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' DurRatio ,
textdata,count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/
@sum_reads rp,sum(duration)/@sum_duration dp
into #queries_staticstics from
/* tblUniqueBatches表中存放了所有标准化的语句。*/
(select reads,cpu,duration,writes,convert(varchar(2000),NormText)
textdata from tblBatches
inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.
hashid where duration>@duration_filter
) B group by textdata --这个group by很重要，它对语句进行归类统计。
print 'Top 10 order by cpu+reads+duration'
select top 10 * from #queries_staticstics order by cp+rp+dp desc
print 'Top 10 order by cpu'
select top 10 * from #queries_staticstics order by cp desc
print 'Top 10 order by reads'
select top 10 * from #queries_staticstics order by rp desc
print 'Top 10 order by duration'
select top 10 * from #queries_staticstics order by dp desc
print 'Top 10 order by batches'
select top 10 * from #queries_staticstics order by tp desc
End

考虑到输出结果横向较长，存储过程中把writes去掉了。这是因为大部分的数据库系统都是Reads为主的。你可以轻易的修改存储过程把write也包括进去。

存储过程并不复杂，很容易理解。可以看到统计的结果放在queries_staticstics表中，然后按照不同的条件排序后输出。举例说：select top 10 * from #queries_staticstics order by cp desc

上面的语句将把queries_staticstics表中的记录按照某类语句占用总CPU量的比例cp(即sum(cpu)/@sum_cpu)进行排序输出。这让你在分析服务器CPU性能问题的时候快速定位哪一类语句最耗CPU资源，从而对症下药。