概述
当你的SQL Server数据库系统运行缓慢的时候你或许多多少少知道可以使用SQL Server Profiler（中文叫SQL事件探查器）工具来进行跟蹤和分析是的Profiler可以用来捕获发送到SQL Server的所有语句以及语句的执行性能相关数据（如语句的read/writes页面数目CPU的使用量以及语句的duration等）以供以后分析但本文并不介绍如何使用Profiler 工具而是将介绍如何使用readtrace（有关该工具见后面介绍）工具结合自定义的存储过程来提纲挈领地分析Profiler捕获的Trace文件最终得出令人兴奋的数据分析报表从而使你可以高屋建瓴地优化SQL Server数据库系统
本文对那些需要分析SQL Server大型数据库系统性能的读者如DBA等特别有用在规模较大应用逻辑复杂的数据库系统中Profiler产生的文件往往非常巨大比如说在Profiler中仅仅配置捕获基本的语句事件运行二小时后捕获的Trace文件就可能有GB级的大小应用本文介绍的方法不但可以大大节省分析Trace的时间和金钱把你从Trace文件的海量数据中解放出来更是让你对数据库系统的访问模式了如指掌从而知道哪一类语句对性能影响最大哪类语句需要优化等等
Profiler trace文件性能分析的传统方法以及局限
先说一下什么是数据库系统的访问模式除了可以使用Trace文件解决如死锁阻塞超时等问题外最常用也是最主要的功能是可以从Trace文件中得到如下三个非常重要的信息
运行最频繁的语句
最影响系统性能的关键语句
各类语句群占用的比例以及相关性能统计信息
本文提到的访问模式就是上面三个信息我们知道数据库系统的模块是基本固定的每个模块访问SQL Server的方式也是差不多固定的具体到某个菜单某个按钮都是基本不变的所以在足够长的时间内访问SQL Server的各类语句及其占用的比例也基本上是固定的换句话说只要Profiler采样的时间足够长（我一般运行小时以上）那么从Trace文件中就肯定可以统计出数据库系统的访问模式每一个数据库系统都有它自己独一无二的访问模式分析Profiler Trace文件的一个重要目标就是找出数据库系统的访问模式一旦得到访问模式你就可以在优化系统的时候做到胸有成竹心中了然可惜直到目前为止还没有任何工具可以方便地得到这些信息
传统的Trace分析方法有两种一种是使用Profiler工具本身比如说可以使用Profiler的Filter功能过滤出那些运行时间超过秒以上的语句或按照CPU排序找出最耗费CPU的语句等另一种是把Trace文件导入到数据库中然后使用TSQL语句来进行统计分析这两种方法对较小的Trace文件是有效的但是如果Trace文件数目比较多比较大（如个MB以上的trace文件）那么这两种方法就有很大的局限性其局限性之一是因为文件巨大的原因分析和统计都非常不易常常使你无法从全局的高度提纲挈领地掌握所有语句的执行性能你很容易被一些语句迷惑而把精力耗费在上面而实际上它却不是真正需要关注的关键语句局限性之二是你发现尽管很多语句模式都非常类似（仅仅是执行时参数不同）却没有一个简单的方法把他们归类到一起进行统计简而言之你无法轻而易举地得到数据库系统的访问模式无法在优化的时候做到高屋建瓴纲举目张这就是传统分析方法的局限性使用下面介绍的Readtrace工具以及自定义的存储过程可以克服这样的局限性
Readtrace工具介绍以及它的Normalization 功能
ReadTrace工具是一个命令行工具使用ReadTrace工具可以大大节省分析Trace文件的时间有事半功倍的效果ReadTrace的主要工作原理是读取Trace文件然后对语句进行Normalize (标准化)导入到数据库生成性能统计分析的HTML页面另外Readtrace可以生成RML文件然后OSTRESS工具使用RML文件多线程地重放Trace文件中的所有事件这对于那些想把Profiler捕获的语句在另外一台服务器上重放成为可能本文不详细介绍Readtrace或OStress工具有兴趣的读者请自行参阅相关资料相关软件可以从微软网站下载（注软件名称为RML）
我要利用的是ReadTrace的标准化功能什么是标准化？就是把那些语句模式类似但参数不一样的语句全部归类到一起举例说Trace中有几条语句如下
select * from authors where au_lname = white
select * from authors where au_lname = green
select * from authors where au_lname = carson
经过标准化后上面的语句就变成如下的样子
select * from authors where au_lname = {str}
select * from authors where au_lname = {str}
select * from authors where au_lname = {str}
有了标准化后的语句统计出数据库系统的访问模式就不再是难事运行Readtrace 的时候我一般使用如下的命令行
Readtrace –f –dmydb –Imytracetrc
其中－f开关是不生成RML文件因为我不需要重放的功能生成的RML文件比较大建议读者如果不需要重放的话也使用－f开关
－d开关告诉readtrace把trace文件的处理结果存到mydb数据库中我们后面创建的存储过程正是访问readtrace在mydb中生成的表来进行统计的－I开关是指定要分析的的trace文件名Readtrace工具很聪明如果该目录下有Profiler产生的一系列Trace文件如mytracetrcmytracetrcmytracetrc等那么它会一一顺序读取进行处理
除了上面介绍的外Readtrace还有很多其它有趣的开关比如说使用－i开关使得Readtrace可以从zip或CAB文件中读取trace文件不用自己解压所有开关在Readtracechm中有详细介绍我最欣赏的地方是readtrace的性能分析几个GB大小的trace文件不足一小时就搞定了我的计算机是一台内存仅为MB的老机器有这样的性能我很满意
你也许会使用readtrace分析压力测试产生的trace文件我建议还是分析从生产环境中捕获的Trace文件为好因为很多压力测试工具都不能够真正模拟现实的环境其得到的trace文件也就不能真实反映实际的情况甚至有些压力测试工具是循环执行自己写的语句更不能反映准确的访问模式建议仅仅把压力测试产生的trace作为参考使用
使用存储过程分析Normalize后的数据
有了标准化后的语句就可以使用存储过程进行统计分析了分析的基本思想是把所有模式一样的语句的ReadsCPU和Duration做group by统计得出访问模式信息
某类语句的总共执行次数平均读页面数（reads）/平均CPU时间/平均执行时间等
该类语句在所有语句的比例如执行次数比例reads比例CPU比例等
存储过程的定义以及说明如下
Create procedure usp_GetAccessPattern
@duration_filter int= 传入的参数可以按照语句执行的时间过滤统计
as begin
/*首先得到全部语句的性能数据的总和*/
declare @sum_total float@sum_cpu float@sum_reads float@sum_duration float@sum_writes float
select @sum_total=count(*)*这是所有语句的总数
@sum_cpu=sum(cpu)* 这是所有语句耗费的CPU时间
@sum_reads=sum(reads)* 这是所有语句耗费的Reads数目K为单位
@sum_writes=sum(writes)*这是所有语句耗费的Writes数目K为单位
@sum_duration=sum(duration)*这是所有语句的执行时间总和
from tblBatches 这是ReadTrace产生的表包括了Trace文件中所有的语句
where duration>=@duration_filter 是否按照执行时间过滤
/*然后进行Group by得到某类语句占用的比例*/
Select ltrim(str(count(*))) exec_stats+ str(count(*)/@sum_total)+% ExecRatio
ltrim(str(sum(cpu)))+ : ++ltrim(str(avg(cpu))) cpu_stats+str(sum(cpu)/@sum_cpu)+% CpuRatio
ltrim(str(sum(reads) ))+ : +ltrim(str(avg(reads) )) reads_stats+str(sum(reads)/@sum_reads) +% ReadsRatio
ltrim(str(sum(writes) ))+ : +ltrim(str(avg(writes) )) writes_stats+str(sum(writes)/@sum_writes) +%)
ltrim(str(sum(duration) ))+ : +ltrim(str(avg(duration))) duration_stats+str(sum(duration)/@sum_duration)+% DurRatio
textdatacount(*)/@sum_total tpsum(cpu)/@sum_cpu cpsum(reads)/@sum_reads rpsum(duration)/@sum_duration dp
into #queries_staticstics from
/* tblUniqueBatches表中存放了所有标准化的语句*/
(select readscpudurationwritesconvert(varchar()NormText)textdata from tblBatches
inner join tblUniqueBatches on tblBatchesHashId=tblUniqueBatcheshashid where duration>@duration_filter
) B group by textdata 这个group by很重要它对语句进行归类统计
print Top order by cpu+reads+duration
select top * from #queries_staticstics order by cp+rp+dp desc
print Top order by cpu
select top * from #queries_staticstics order by cp desc
print Top order by reads
select top * from #queries_staticstics order by rp desc
print Top order by
