应用Profiler优化SQL Server数据库系统

核心提示： 使用存储过程分析Normalize后的数据有了标准化后的语句就可以使用存储过程进行统计分析了，分析的基本思想是把所有模式一样的语句的Reads，应用Profiler优化SQL Server数据库系统(4)，CPU和Duration做group by统计，得出访问模式信息：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也包括进去。