详解SQL Server 2008工具SQL Server Profiler

一、sql profiler工具简介

sql profiler是一个图形界面和一组系统存储过程，其作用如下：

1.图形化监视sql server查询；

2.在后台收集查询信息；

3.分析性能；

4.诊断像死锁之类的问题；

5.调试t-sql语句；

6.模拟重放sql server活动；

也可以使用sql profiler捕捉在sql server实例上执行的活动。这样的活动被称为profiler跟踪。

　　1、profiler跟踪

　　从开始=》所有程序=》microsoft sql server 2008=》性能工具打开profiler工具，也可以打开sql server management studio=》工具=》sql server profiler。

　　然后选择文件=》新建=》跟踪打开一个连接窗口，选择将要跟踪的服务器实例然后连接。打开如下“跟踪属性”对话框。

　　

　　如果有许多跟踪，可以提供一个跟踪名称来帮助在以后进行分类。不同的跟踪模板可帮助建立用于不同目的的跟踪。

　　打开跟踪属性窗口后，单击“事件选择”选项卡，为跟踪提供更详细的定义。

　　

　　2、事件

　　一个事件表现sql server中执行的各种活动。这些活动可以简单地分类为事件类，游标事件，锁事件，存储过程事件和t-sql事件是常见的事件类。

　　对于性能分析，主要对sql server上执行的各种活动的资源压力水平的事件感兴趣。资源压力主要包含如下内容：

sql活动涉及哪一类的cpu使用？使用了多少内存？涉及多少i/0操作?sql活动执行了多长时间？特定的查询执行的频率有多高？查询面对哪类错误和警告？

　　下面给出跟踪查询结束的事件：

事件类	事件	说明
stored procedures	rpc:completed	rpc完成事件
	sp:completed	存储过程完成事件
	sp:stmtcompleted	在存储过程中一条sql语句完成事件
t-sql	sql:batchcompleted	t-sql批完成事件
	sql:stmtcompleted	一条t-sql语句完成事件

　　rpc事件表示存储过程使用远程过程调用(rpc)机制通过oledb命令执行。如果一个数据库应用程序使用t-sql execute语句执行一个存储过程，那么存储过程将被转化为一个sql批而不是一个rpc。rpc请求通常比execute请求快，因为它绕过了sql server中的许多语句解析和参数处理。

　　t-sql由一条或多条t-sql语句组成。语句或t-sql语句在存储过程中也是单独和离散的。用sp:stmtcompleted或sql:stmtcompleted事件捕捉单独的语句可能是代价很高的操作，这取决于单独语句的数量。假设系统中的每个存储过程包含且只有一条t-sql语句。在这种情况下，完成的语句集合相当小。现在假定过程中有多条语句，而且这些过程中有些使用其他语句调用其他过程。收集所有这些额外的数据现在变成系统上非常厉害的负载。在生产机上一定要慎用。

　　现在回到那个事件选择面板，只有已经被选择的事件才会被显示。如果想显示所有可供选择的事件，则只需选中“显示所有事件”单选框，要添加一个跟踪事件，在event列中查找一个事件类下的事件，并单击其左边的检查框；要删除不需要的事件，取消选中的事件选择框。

　　光分类就有好多的说：

　　

　　下面给出其他一些与性能诊断有关的事件:

事件类	事件	说明
security audit(安全审计)	audit login(登录审计)	记录用户连接到sql server或断开连接时数据库的连接
	audit logout(注销审计)
sessions(会话)	existingconnection(现有连接)	表示所有在跟踪开始之间连接到sql server的用户
cursors(游标)	cursorimplicitconversion(游标隐含转换)	表明创建的游标类型与所请求的类型个不同
errors and warnings(错误和警告)	attention(注意)	表示由于客户端撤销查询或者数据库连接破坏引起请求中断
	exception(异常)	表明sql server发生了异常
	execution warning(执行警告)	表明在查询或存储过程执行期间出现了警告
	hash warning(哈希警告)	表明hash操作发生了错误
	missing column statistics(列统计丢失)	表明优化器要求的确定处理策略用的类统计丢失
	missing join predicate(连接断言丢失)	表明查询在两个表没有连接断言情况下执行
	sort warning(排序警告)	表明像select这样的查询中执行排序操作没有合适的内存
locks(锁)	lock:deadlock(死锁)	标志着死锁的出现
	lock:deadlock chain(死锁链)	显示产生死锁的查询链条
	lock:timeout(锁超时)	表示锁已经超过其超时参数，该参数由setlock_timeout timeout_perious(ms)命令设置
stored procedures(存储过程)	sp:recompile(重编译)	表明用于一个存储过程的执行计划必须重编译，原因是执行计划不存在，强制的重编译，或者现有的执行计划不能重用
	sp:starting(开始)  sp:stmtstarting(语句开始)	分别表示一个sp:stmtstarting存储过程和存储过程中的一条sql语句的开始。他们对于识别开始单因为一个操作导致attention事件未能结束的查询很有用
transactions(事物)	sqltransaction(sql事务)	提供数据库事务的信息，包括事务开始/结束的时间、事务持续事件等信息

　　3、事件列

 　　事件以不同的特性(被称为数据列)来表现。数据列表现一个事件的不通特性，如事件的类、用于该事件的sql语句、事件的资源开销以及事件来源。

数据列	说明
eventclass(事件类)	事件类型，如sql:statementcompleted
textdata	事件所用的sql语句，如select * from person
cpu	事件的cpu开销（以ms表示），如对一个select语句，cpu=100表示该语句执行100ms
reads	为一个事件所执行的逻辑读操作数量。例如对一个select语句，reads=800表示该语句需要800次逻辑读操作
writes	为一个事件所执行的逻辑写操作数量
duration	事件的执行时间(ms)
spid	用于该事件的sql server进程标识符
starttime	事件开始的时间

 　　以上是常用的数据列，另外还有一些不太常用的数据列：

　　binarydata(二进制数据)　　integerdata(整数数据)　　eventsubclass(事件子类)　　databaseid(数据库标识符)　　objectid(对象标识符)　　indexid(索引标识符)　　transactionid(事务标识符)　　error(错误)　　endtime(结束时间)

　　列数据可以重新安排以符合你自己所喜欢的风格，要控制列数据的安放，单击组织列按钮，将打开如下对话框。可以单击up和down按钮修改列的位置，将列移入groups意味着它将成为一个合计列。

 　　

　　4、列筛选器

　　除了为一个profiler跟踪定义事件和数据列之外，还可以定义各种过滤条件。这些条件帮助缩小跟踪的输出，这往往是一个好主意。下面给出常用过滤条件列表。

事件	过滤条件实例	用处
applicationname(应用程序名称)	not like:sql profiler	过滤profiler生成的事件。这是默认的行为
databaseid(数据库标识符)	equals:<id of the database to monitor>	过滤特定数据库生成的事件。数据库id:select db_ic('northwind')
duration(持续时间)	greater than or equal:2	对于性能分析，经常会为一个大的工作负载捕捉跟踪，在大的跟踪中，许多事件日志具有比所感兴趣更小的持续周期(duration)。过滤这个事件日志，因为几乎没有可用于优化这些sql活动的余地
reads(读操作数)	greater than or equal"2	过滤读操作较小的事件
spid	equals:<database users to monitor>	定位由特定的数据库用户发送的查询

 　　下面给出设置过滤列的方式：

　　

 　　5、跟踪模板

　　sql server profiler可以用自定义事件、数据列和过滤器创建一个跟踪模板，然后定义一个新的跟踪，然后重用跟踪个模板来捕捉一个跟踪。定义新跟踪模板的过程类似于定义新跟踪，步骤如下：

创建一个新的跟踪。和前面一样定义事件，数据列和过滤器。从文件=》另存为菜单将跟踪定义保存为跟踪模板。

　　sql server profiler将自动将新的模板加入到其模板列表中。

　　新建模板：

　　

　　保存模板:

　　

　　查看：

　　

　　6、跟踪数据

　　定义了跟踪以后，单击运行按钮将开始捕捉事件并将其显示在屏幕上，可以看到一系列滚动事件，可以在我们称之为sql tv的屏幕上看到系统的运行，可以像dvd播放机一样或多或少地控制跟踪，可以使用工具栏上的按钮暂停、开始和停止跟踪，甚至可以在工作室暂停跟踪并修改它。

　　一旦完成了sql server活动的捕捉，就可以将跟踪输出保存为一个跟踪文件或一个跟踪表。保存到跟踪文件的跟踪输出是一个原生的格式，可以由profiler打开以分析sql查询。将跟踪的输出保存为一个表，也可以使profiler在跟踪表上用select语句来分析其中的sql查询。

　　具体的操作为 文件 =》 另存为 =》 跟踪表。选择你希望存入的的数据库和表，然后你就可以像普通表一样执行各种sql查询。

二、跟踪的自动化

　　profiler gui简化了profiler跟踪的收集。不幸的是，这种简易性有其代价。profiler工具捕捉的事件进入内存中的缓冲以便通过网络反馈给gui。gui依赖网络，网络流量可能降低系统的速度并导致缓冲被填满。这将在较小的程度上影响服务器的性能。进一步地，当缓冲被填满，服务器将开始丢弃事件以避免严重地影响服务器性能。

　　1、使用gui捕捉跟踪

　　可以以两种方法两创建一个脚本化跟踪-手工或者使用gui。在轻松地满足脚本的所有要求之间，最简易的方法就是使用profiler工具的gui,需要如下步骤：

定义一个跟踪；单击文件=》导出=》脚本跟踪定义；必须选择目标服务器类型， sql server2005/2008；未文件命名，并保存它；

　　这些不走将生成所有步骤跟踪并将其输出到一个文件所需的所有脚本命令。

　　使用management studio手工启动新的跟踪:

打开文件；使用系统的相关名称和路径替换insertfilenamehere；执行脚本，它将返回带有traceid的单列结果集；

　　可以通过sql agent自动化这个脚本的执行，甚至可以使用sqlcmd.exe使用程序从命令行运行这个脚本。不管使用哪种方法，这个脚本将启动跟踪。如果没有定义跟踪停止时间，就必须使用traceid手工停止跟踪。

　　2、使用存储过程捕捉跟踪

　　查看上一节中定义的脚本，会看到以特定顺序条用的一系列命令：

sp_trace_create:创建一个跟踪定义；sp_trace_setevent:添加事件和事件列到跟踪中；sp_trace_setfilter:将过滤器应用到跟踪；

　　一旦定义了sql跟踪持续到跟踪被停止。因为sql跟踪作为一个后端进程持续运行，managerment studio会话不需要保持打开。可以使用sql server内建函数fn_trace_getinfo确定正在运行的跟踪，查询如下：

select * from ::fn_trace_getinfo(default);

　　输出图：

　　

　　fn_trace_getinfo函数的输出中，不同的traceid的数量表示sql server上活动跟踪的数量。

第三列(value)表示跟踪是否正在运行(value=1)或者停止(value=0)。可以通过执行存储过程sp_trace_setstatus停止特定的跟踪，如traceid=1，如下所示：

exec sp_trace_setstatus 1,0;

在跟踪停止之后，它的定义必须执行sp_trace_setstatus关闭并且从服务器中删除，如下所示:

exec sp_trace_setstatus 1,2;

　　为了验证跟踪成功地停止，重新执行fn_trace_getinfo函数，并确定该函数的输出不包含该traceid。

　　这种技术所创建的跟踪文件的格式与profiler创建的跟踪文件相同。因此，这种跟踪文件可以与profiler创建的文件以相同的方式进行分析。

　　使用前一小节所概述的存储过程捕捉sql跟踪，避免了与profiler gui相关的开销。而且还比profiler工具提供了管理sql跟踪计划的更大灵活性。

三、结合跟踪和性能监视器输出

　　如果自动化了性能监视器捕捉到文件，又自动化了profiler数据捕捉到一个文件。它们覆盖相同的时间段，那么就可以在sql profiler gui中一起使用它们。确定跟踪有starttime和endtime数据字段，按照以下步骤进行：

打开跟踪文件(当然前提是你曾经 另存为=》跟踪文件)；单击 文件=》 导入性能数据；选择导入的性能监视器文件；

　　执行上面的操作将打开如下所示对话框，这里允许选择包含性能监视器计数器。

 

　　选择了想要包含的计数器之后，单击ok按钮将一起打开profiler和性能监视器数据。现在，可以开始一起使用跟踪数据和性能监视器数据。如果在顶部窗口选择一个时间，它将在性能 监视器中放置一条红线，显示数据中事件发生的时间。相反，可以单击性能监视器数据，表示那段 时间的事件将被选中。这些性能工作得很好，将可以在调整过程中定时使用它们以确认瓶颈和压力 点，并确定导致这些压力的特定查询。

四、sql profiler使用要点

　　sql profiler使用建议如下：

限制事件和数据列的数量；抛弃用于性能分析的启动事件；限制跟踪的输出大小；避免联机数据列排序；远程运行proflier；

　　1、限制事件和数据列

　　在跟踪sql查询时，可以通过过滤事件和数据列来决定哪些sql活动应该被捕捉。选择更多的事件造成了大量的跟踪开销。数据列不会增加太多的开销，因为它们只是一个事件类的特性。因此，知道每个所希望跟踪事件的原因，并根据必要性来选择事件是很重要的。

　　最小化捕捉的事件数量避免sql server浪费宝贵的资源带宽去生成所有的事件。捕捉像锁和执行计划这样的事件时应该小心进行，因为这些事件会使跟踪输出变得非常大并降低sql server的性能。

　　过滤分两个阶段：预过滤由sql server执行，后过滤由用户执行。预过滤是捕捉sql server活动的联机阶段，预过滤提供多种溢出：

降低了sql server的性能影响，因为生成有限数量的时间；降低跟踪输出大小；简化后过滤操作，首先因为要捕捉的事件更少了；

　　预过滤的唯一缺点是，可能丢失一些彻底分析中需要的重要信息。

　　2、丢弃性能分析所用的启动事件

　　所用于性能分析的信息围绕一个查询的资源开销。想sp:stmtstarting这样的启动事件不提供这种信息，因为只有在事件完成之后，才能计算i/o量、cpu负载和查询的持续时间。所以，在跟踪运行缓慢的查询以进行性能分析时，不需要捕捉启动事件。这种信息由对应的完成事件来提供。

　　什么情况下适合捕捉启动事件呢？应该在预期某些sql查询因为错误而不能结束执行，或者频繁发现attention事件的时候捕捉启动事件。attention事件一般表示用户中途撤销了查询或者查询超时，可能因为查询运行了太长时间。

　　3、限制跟踪输出大小

　　除了预过滤事件和数据列，其他过滤条件也会限制跟踪输出的大小。同样，限制大小可能丢失所关注的总体系统状态中感兴趣的事件。但是，如果关注于开销较大的查询，过滤器是有帮助的。

　　通过过滤器，能够筛选执行事件》=2或逻辑读数量》=100的查询，因为消耗太低的查询基本上不需要优化。

　　4、避免在线数据列排序

　　在性能分析期间，一般在不同的数据列(如duration、cpu、reads)上排序以确定相应数字最大的查询。如果脱机排序，就能降低在与sql server交互时必须进行的profiler活动。排序捕捉到的sql跟踪输出的方法如下：

捕捉跟踪，不做任何排序或分组；另存为跟踪输出到一个跟踪文件；打开跟踪文件并按照需要在特定的数据列上排序或分组跟踪文件输出；

　　5、远程运行profiler

　　直接在生产服务器上运行测试工具一般不是一个好办法。profiler有一个大型的用户界面，因此，在其他机器上运行它更好。与系统监视器相似，profiler不应该通过终端服务会话来运行，因为这样工具的主要部分仍然在服务器上运行。在直接将跟踪输出收集到一个文件时，保存在profiler运行的本地文件上。这仍然是比通过系统存储过程将profiler作为服务器端跟踪来运行更加资源密集的操作。使用系统存储过程仍然是最好的选择。

　　6、限制使用某些事件

　　某些事件的开销比其他的事件大。由于生成的查询的特性，语句完成事件的开销可能非常大。需要谨慎地使用，特别是在已经遇到压力的系统上，必须谨慎使用的事件有：showplan xml事件，performance:showplan xml、performance:showplan xml for query compile和performance:showplan xml statistics prifile。虽然这些事件可能有用，但是不要在生产机器上使用它们。

五、没有profiler的情况下查询性能度量

　　建立一个跟踪能收集许多数据供以后使用，但是这种收集可能代价很大，必须等待结果。

　　如果要立即捕捉系统的性能度量，特别是关于查询性能的度量，那么动态管理视图sys.dm_exec_query_stats正式所需要的。如果还需要查询运行及其单独开销的历史记录，那么跟踪仍然是更好的工具。但是，如果只需要知道这时候运行时间最长的查询或者最多的物理读操作，则可以从sys.dm_exec_query_stats得到这些信息。

　　因为sys.dm_exec_query_stats只是一个视图，可以简单地对其进行查询并获得服务器上查询计划统计的信息。

列	描述
plan_handle	引用执行计划的指针
creation_time	计划创建的时间
last_execution time	查询最后一次使用的计划时间
execution_count	计划已经使用的次数
total_worker_time	从创建起计划使用的cpu时间
total_logical_reads	从创建器计划使用的读操作数量
total_logical_writes	从创建器计划使用的写操作数量
query_hash	可用于识别有相似逻辑的查询的一个二进制hash
query_plan_hash	可用于识别有相似逻辑的计划的一个二jinzhihash

　　为了过滤从sys.dm_exec_query_stats返回的信息，需要将其连接到其他动态管理函数上，如sys.dm_exec_sql_text可以显示与计划相关的查询文本，sys.dm_query_plan显示用于查询的执行计划。一旦连接到其他dmf，可以限制希望过滤得数据库或过程。