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

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

　　
select top 10 * from #queries_staticstics order by cp desc

　　Use mydb
　　Exec usp_GetAccessPattern 
　　/*你可以输入一个执行时间作为过滤参数，毫秒为单位。如usp_GetAccessPattern 1000*/