SqlServer性能优化和工具Profiler

合理的优化和熟练的运用Profiler会让你更好的掌握系统的sql语句和存储过程的效率

目录

第1章 如何打开SQL Server Profile. 3

第2章 SQL Server Profile. 3

2.1 了解SQL Server Profiler. 3

2.2 创建跟踪... 3

2.3 查看、分析跟踪... 6

第3章 SQL Server Profiler模板... 7

第4章 Msdn的相关介绍... 10

4.1 概述... 10

4.2 Profiler trace文件性能分析的传统方法以及局限... 10

4.3 Read80trace工具介绍以及它的Normalization 功能... 11

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

4.5 使用usp_GetAccessPattern的一些技巧... 18

4.6 哪个是HOT 数据库？... 19

第1章 如何打开SQL Server Profile

在企业管理器界面（已连接到相关数据库）依次打开：tool —> SQLServer Profiler —> New Trace(新建跟踪器)—> 链接到相应数据库服务器（connect）—> 运行跟踪(run)—> Clear Trace Window（）清除跟踪窗口—>。。。 此时网页同时打开，做相关操作，跟踪窗口即相继显示您在网页操作过程中所执行过的所有T-SQL语句，Pause或Start，点击各个执行过的T-SQL语句，逐个排查。

第2章 SQL Server Profile

2.1 了解SQL Server Profiler

SQL Server Profiler 是图形化实时监视工具，能帮助系统管理员监视数据库和服务器的行为, 比如死锁的数量，致命的错误，跟踪Transact-SQL 语句和存储过程。可以把这些监视数据存入表或文件中，并在以后某一时间重新显示这些事件来一步一步地进行分析。

通常我们使用SQL Server Profiler 仅监视某些插入事件，这些事件主要有： 登录连接的失败、成功或断开连接； DELETE、 INSERT、 UPDATE 命令； 远程存储过程调用（RPC） 的状态； 存储过程的开始或结束，以及存储过程中的每一条语句； 写入SQL Server 错误日志的错误； 打开的游标； 向数据库对象添加锁或释放锁。 我们之所以不监视过多的事件，原因在于对事件进行监视往往增加系统的负担，并且使跟踪文件很快增长成大容量文件，从而引起不必要的麻烦。

2.2 创建跟踪

在SQL Server 中可以使用SQL Server Profiler 创建跟踪，也可以使用跟踪创建向导或是扩展存储过程。在这里我们将介绍如何使用SQL Server Profiler 来创建跟踪。

（1） 启动SQL Server Profiler （与启动SQL Server EntERPrise Manager 一样），从File 的下拉菜单中选择New， 再选择Trace 选项，此时打开Trace Properties 对话框，如图19-2 所示。

在该对话框中定义跟踪的名称、类型、运行跟踪的SQL Server 以及跟踪输出数据的存储方式。其中各选项的含义为： Shared：表示所有被允许登录到运行SQL Server Profiler 服务器的用户都可以使用该跟踪； Private：表示只有当前创建者可以使用该跟踪； Capture to file： 表示将跟踪结果存储到文件中，可减少进行跟踪时的内存开销； Capture to table： 表示将跟踪结果存储到表中，这极易引起较大的额外系统开销。

（2） 选中Events 标签页，如图19-3 所示该对话框用来确定将跟踪哪些事件。在 Available Events 下的窗口中选择要跟踪的事件，底部的方框内便显示出该事件的含义，单击Add ，增加到Selected Events 事件中；

3） 选中Data Columns 标签页，如图19-4 所示。在该对话框可以选择跟踪事件哪些数据列，通常选择那些我们比较关心的列。

（4） 选中Filters 标签页，如图19-5 所示。

（5） 单击“确定”按钮完成跟踪创建。

2.3 查看、分析跟踪

使用SQL Server Profiler 可以查看跟踪中的事件数据，在跟踪中的每一行代表一个事件，这些事件数据是由跟踪的属性决定的。可以把SQL Server 数据拷贝到其它的应用程序中，如SQL Server Query Analyzer 或Index Tuning Wizard 然后利用它们进行数据分析，但通常我们使用SQL Server Profiler 来进行跟踪分析。

利用SQL Server Profiler 既可以打开扩展名为.trc 的跟踪文件，也可以打开扩展名为.log 的日志文件，以及一般的SQL 脚本文件

因为跟踪信息通常保存在文件或表中，所以通过打开表或文件就可以查看、分析跟踪。

打开跟踪的步骤为：

从File 菜单中选择Open， 再选择Trace Files， 弹出如图19-6 所示的对话框。

第3章 SQL Server Profiler模板

可以使用 SQL Server 事件探查器 来创建模板，定义要包含在跟踪中的事件类和数据列。定义并保存模板后，可以运行跟踪来记录每个选定事件类的数据。您可以将一个模板用于多个跟踪；模板本身并不会执行。

SQL Server 事件探查器 提供了预定义的跟踪模板，使您可以轻松配置特定跟踪可能最需要的事件类。例如，Standard 模板可帮助您创建通用跟踪，用于记录登录、注销、已完成的批处理和连接信息。您可以使用此模板来运行跟踪而无需修改，也可以基于该模板创建具有不同事件配置的其他模板。

注意：

除了通过预定义模板进行跟踪以外，SQL Server 事件探查器 还允许您从空模板（默认情况下不包含任何事件类）创建跟踪。当计划的跟踪与任何预定义模板的配置都不相符时，使用空跟踪模板会十分有用。

SQL Server 事件探查器 可以跟踪各种服务器类型。例如，可以跟踪 SQL Server 2000、Analysis Services 和 SQL Server 2005。但每种服务器可以包含的事件类会有所不同。因此，SQL Server 事件探查器 为不同服务器保留不同的模板，并提供与所选服务器类型匹配的特定模板。

预定义模板

除了 Standard（默认）模板以外，SQL Server 事件探查器 还包含几个可监视特定类型的事件的预定义模板。下表列出了预定义模板、其用途以及其捕获何种事件类的信息。

默认模板

SQL Server 事件探查器 自动指定 Standard 模板作为应用于任何新跟踪的默认模板。但是，您可以将默认模板更改为其他任何预定义模板或用户定义模板。若要更改默认模板，请在使用“跟踪模板属性”对话框的“常规”选项卡创建或编辑模板时，选中“用作所选服务器类型的默认模板”复选框。

若要导航到“跟踪模板属性”对话框，请在 SQL Server 事件探查器 的“文件”菜单中，选择“模板”，然后单击“新建模板”或“编辑模板”。

第4章 Msdn的相关介绍

4.1 概述

当你的SQL Server数据库系统运行缓慢的时候，你或许多多少少知道可以使用SQL Server Profiler（中文叫SQL事件探查器）工具来进行跟踪和分析。是的，Profiler可以用来捕获发送到SQL Server的所有语句以及语句的执行性能相关数据（如语句的read/writes页面数目，CPU的使用量，以及语句的duration等）以供以后分析。但本文并不介绍如何使用Profiler 工具，而是将介绍如何使用read80trace（有关该工具见后面介绍）工具结合自定义的存储过程来提纲挈领地分析Profiler捕获的Trace文件，最终得出令人兴奋的数据分析报表，从而使你可以高屋建瓴地优化SQL Server数据库系统。

本文对那些需要分析SQL Server大型数据库系统性能的读者如DBA等特别有用。在规模较大、应用逻辑复杂的数据库系统中Profiler产生的文件往往非常巨大，比如说在Profiler中仅仅配置捕获基本的语句事件，运行二小时后捕获的Trace文件就可能有GB级的大小。应用本文介绍的方法不但可以大大节省分析Trace的时间和金钱，把你从Trace文件的海量数据中解放出来，更是让你对数据库系统的访问模式了如指掌，从而知道哪一类语句对性能影响最大，哪类语句需要优化等等。

4.2 Profiler trace文件性能分析的传统方法以及局限

先说一下什么是数据库系统的访问模式。除了可以使用Trace文件解决如死锁，阻塞，超时等问题外，最常用也是最主要的功能是可以从Trace文件中得到如下三个非常重要的信息：

本文提到的访问模式就是上面三个信息。我们知道，数据库系统的模块是基本固定的，每个模块访问SQL Server的方式也是差不多固定的，具体到某个菜单，某个按钮，都是基本不变的，所以，在足够长的时间内，访问SQL Server的各类语句及其占用的比例也基本上是固定的。换句话说，只要Profiler采样的时间足够长（我一般运行2小时以上），那么从Trace文件中就肯定可以统计出数据库系统的访问模式。每一个数据库系统都有它自己独一无二的访问模式。分析Profiler Trace文件的一个重要目标就是找出数据库系统的访问模式。一旦得到访问模式，你就可以在优化系统的时候做到胸有成竹，心中了然。可惜直到目前为止还没有任何工具可以方便地得到这些信息。

传统的Trace分析方法有两种。一种是使用Profiler工具本身。比如说可以使用Profiler的Filter功能过滤出那些运行时间超过10秒以上的语句，或按照CPU排序找出最耗费CPU的语句等。另一种是把Trace文件导入到数据库中，然后使用T-SQL语句来进行统计分析。这两种方法对较小的Trace文件是有效的。但是，如果Trace文件数目比较多比较大（如4个500MB以上的trace文件），那么这两种方法就有很大的局限性。其局限性之一是因为文件巨大的原因，分析和统计都非常不易，常常使你无法从全局的高度提纲挈领地掌握所有语句的执行性能。你很容易被一些语句迷惑而把精力耗费在上面，而实际上它却不是真正需要关注的关键语句。局限性之二是你发现尽管很多语句模式都非常类似（仅仅是执行时参数不同），却没有一个简单的方法把他们归类到一起进行统计。简而言之，你无法轻而易举地得到数据库系统的访问模式，无法在优化的时候做到高屋建瓴，纲举目张。这就是传统分析方法的局限性。使用下面介绍的Read80trace工具以及自定义的存储过程可以克服这样的局限性。

4.3 Read80trace工具介绍以及它的Normalization 功能

Read80Trace工具是一个命令行工具。使用Read80Trace工具可以大大节省分析Trace文件的时间，有事半功倍的效果。Read80Trace的主要工作原理是读取Trace文件，然后对语句进行Normalize (标准化)，导入到数据库，生成性能统计分析的HTML页面。另外，Read80trace可以生成RML文件，然后OSTRESS工具使用RML文件多线程地重放Trace文件中的所有事件。这对于那些想把Profiler捕获的语句在另外一台服务器上重放成为可能。本文不详细介绍Read80trace或OStress工具，有兴趣的读者请自行参阅相关资料，相关软件可以从微软网站下载（注：软件名称为RML，http://www.microsoft.com/downloads/）。

我要利用的是Read80Trace的标准化功能。什么是标准化？就是把那些语句模式类似，但参数不一样的语句全部归类到一起。举例说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}

有了标准化后的语句，统计出数据库系统的访问模式就不再是难事。运行Read80trace 的时候我一般使用如下的命令行：

Read80trace –f –dmydb –Imytrace.trc

其中－f开关是不生成RML文件，因为我不需要重放的功能。生成的RML文件比较大，建议读者如果不需要重放的话，也使用－f开关。

－d开关告诉read80trace把trace文件的处理结果存到mydb数据库中。我们后面创建的存储过程正是访问read80trace在mydb中生成的表来进行统计的。－I开关是指定要分析的的trace文件名。Read80trace工具很聪明，如果该目录下有Profiler产生的一系列Trace文件，如mytrace.trc,mytrace1.trc,mytrace2.trc等，那么它会一一顺序读取进行处理。

除了上面介绍的外，Read80trace还有很多其它有趣的开关。比如说使用－i开关使得Read80trace可以从zip或CAB文件中读取trace文件，不用自己解压。所有开关在Read80trace.chm中有详细介绍。我最欣赏的地方是read80trace的性能。分析几个GB大小的trace文件不足一小时就搞定了。我的计算机是一台内存仅为512MB的老机器，有这样的性能我很满意。

你也许会使用read80trace分析压力测试产生的trace文件。我建议还是分析从生产环境中捕获的Trace文件为好。因为很多压力测试工具都不能够真正模拟现实的环境，其得到的trace文件也就不能真实反映实际的情况。甚至有些压力测试工具是循环执行自己写的语句，更不能反映准确的访问模式。建议仅仅把压力测试产生的trace作为参考使用。

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

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

存储过程的定义以及说明如下：

/*************************************************************/

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资源，从而对症下药。

现在让我们看一个实例的输出：

/********************/

Use mydb

Exec usp_GetAccessPattern 

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

/********************/

输出结果如图 1所示（是部分结果，另外，因为原输出结果横向很长，为方便阅读，把结果从中截断为两部分）：

图 1：输出结果采样一

上面的例子采样于一家大型公司的业务系统。该系统的问题是应用程序运行缓慢，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，说明该存储过程一直在连续不断地运行。

那么该系统运行最频繁的语句是什么呢？我从结果中摘取另外一部分如下（图 2）：

图 2：输出结果采样二

从上表可以看出，最频繁运行的语句是

USE xb SET QUOTED_IDENTIFIER,ANSI_NULL_DFLT_ON…

显然这是一条执行环境配置语句，没有参考价值。倒是另外两条占用语句总数8.2%的语句值得关注：

SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS……

SELECT COUNT(*) FROM x_PROCESS_STATS WHERE PROCESS……

在这个例子中，因为关键语句DBO.x_DEDUP_PROC非常突出，甚至上面的两条语句都可以忽略了。

让我们再多看一个例子（图 3）：

图 3：输出结果采样三

从上面的例子中, 可以得出关键的语句是:

SELECT COUNT(*) FROM GTBL7MS

SELECT CaseNO FROM PATIENTDATA_sum WHERE MRN = @P1

后续的检查发现相关的表没有有效的索引，加上索引后性能立即整体地提高了不少.。解决了这两个语句，需要使用同样的手段继续分析和优化，直到系统的性能能够接受为止.。注意性能调优是一个长期的过程，你不太可能一两天就可以把所有的问题都解决。也许一开始可以解决80%的问题，但是后面20%的问题却需要另外80%的时间。

4.5 使用usp_GetAccessPattern的一些技巧

usp_GetAccessPattern的输出报表包含了非常丰富的信息。分析报表的时候需要有大局观。你也可以有目的性地选择你需要的信息。如果是CPU性能瓶颈的系统，那么你需要关注CPU占用比例高的那类语句。如果是磁盘IO出现性能瓶颈那么你需要找到那些Reads占用比例大而且平均reads也很高的语句。需要注意的是有时候运行频繁的语句未必就是你需要关注的关键语句。一个最理想的情况是关键语句正好就是最频繁的语句。有时候即使最频繁语句占用的资源比例不高，但如果还可以优化，那么因为放大效应，微小的优化也会给系统带来可观的好处。

在使用usp_GetAccessPattern的时候多结合@duration_filter参数使用。因为参数以毫秒为单位，建议参数不要小于1000，而应该是1000的倍数 如3000，5000等。该参数常常会给出非常有意思的输出。该输出和不带参数运行的结果会有某些重叠。重叠出现的语句通常正是需要关注的语句。要注意运行最多最密的语句未必有超过1000毫秒的执行时间，所有带参数运行的结果有可能不包括最频繁语句。我常常同时交叉分析四个结果，一个是不带参数运行得到的，另三个分别是使用1000，3000和5000毫秒为参数运行的结果。比较分析这四个结果往往使我对数据库系统的访问模式有非常清晰透彻的理解。

运行存储过程时你也许会碰到int整数溢出的错误。这是因为表tblBatches 中的reads,cpu 和writes字段是int而不是bigint。可以运行如下语句进行修正：

alter table tblBatches  alter column  reads bigint 

alter table tblBatches  alter column  cpu bigint 

alter table tblBatches  alter column  writes bigint

修正后溢出问题就会解决。

4.6 哪个是HOT 数据库？

本文到这里就基本上结束了。你已经知道如何使用Read80Trace和usp_GetAccessPattern得到数据库系统的访问模式，以及如何从全局的高度去分析访问模式报表，从而在优化系统的时候做到提纲挈领，胸有成竹。

除此之外，你还可以应用类似的分析思想得到每个数据库的占用资源比例。这对于SQL Server有多个数据库的情况非常有用。从报表中你可以立即知道哪个数据库是最HOT最消耗系统资源的数据库。语句如下：

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

下面是一个上面语句结果的一个例子：

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%

上面的结果明确地告诉我们ID为37，33和36的数据库是最活跃的数据库。一个有趣的事实是数据库20发出的语句总数比例是53.9%，但是其占用的系统资源比例却不高。