Trace-跟踪高消耗的语句需添加哪些事件

通常接手一台数据库服务器后,我们会开启Profiler跟踪来了解SQL Server的繁忙情况。我们首先会想到的是监控CPU或Duration超过某一阈值的语句/过程。那么所创建的Trace添加哪些事件和列比较合适?
新建跟踪,默认模板会选择Audit Login、Audit Logout、ExistingConnection、RPC:Completed、SQL:BatchCompleted、SQL:BatchStarting这些事件。但我们稍作修改,下表中给出跟踪查询结束的事件:

事件类 事件 说明 备注
Stored Procedures RPC:Completed RPC完成事件  
SP:Completed 存储过程完成事件 所有执行的存储过程(过程中调用其他过程,也能跟踪到)
SP:StmtCompleted 在存储过程中一条SQL语句完成事件 捕捉单条语句代价过高
T-SQL SQL:BatchCompleted T-SQL批完成事件  
SQL:StmtCompleted 一条T-SQL语句完成事件 捕捉单条语句代价过高

选择表中的事件,加上Exception异常事件,选择列在默认的基础上增加DatabaseID、DatabaseName、Error、HostName,设置完成后的跟踪属性如下:

点击运行,在查询窗口选择对应数据库执行下面语句:

DBA_Pro_GetJobSchedules '20151215 07:00:00.000','20151215 08:00:00.000'
WAITFOR DELAY '00:00:03'
SELECT GETDATE()
GO

跟踪结果如图所示:

我们来分析语句是如何执行的:整段语句是一个批处理语句,因此在跟踪的最后面有一个SQL:BatchCompleted事件(红框),对应的TextData是三行批处理语句。三行语句对应三个SQL:StmtCompleted事件(橙框)。测试中故意在DBA_Pro_GetJobSchedules过程的结尾加上一句exec uest.uest_test,因此我们看到两个SP:Completed事件(紫框)。DBA_Pro_GetJobSchedules过程是用来查看指定区间内作业执行情况,过程由很多条SQL语句组成;exec uest.uest_test过程只是从sys.objects获取10条类型为表的记录,exec uest.uest_test对于DBA_Pro_GetJobSchedules来说是过程中的一条SQL语句,因此我们可以看到很多SP:StmtCompleted事件(蓝框+蓝下划线)。
仔细查看发现很多"重复"代码,对于本例中其实我们只要知道SQL:BatchCompleted事件,就可以通过查看过程涉及代码继续分析,而不用每次都把过程中涉及的语句全部跟踪出来。SP:StmtCompleted或SQL:StmtCompleted事件捕捉单独的语句可能是代价很高的操作,这取决于单独语句的数量。如果过程中有多条语句,而且这些过程中有些使用其他语句调用其他过程。收集所有这些额外的数据对生产环境的影响可能会非常大。
我们再看SP:Completed事件,查询窗口执行的语句只有一个过程,只是过程中又调用了另一过程。SP:Completed事件把两个过程都跟踪出来了。这个事件对于临时跟踪某个过程执行情况很有用。之前有遇到过,在没有选择此事件的情况下,去跟踪某个过程的执行情况,Profiler下怎么也看不到,但在sys.dm_exec_procedure_stats视图下却发现过程一直被调用。后面只能通过查询它的依赖关系,将调用它的所有过程找出来,再针对那些过程去跟踪!还是来看前面的例子,是否有必要在第一次的时候将此事件添加进去?不添加,有些过程在我们的跟踪文件中永远都看不到它们的身影,可实际上它们可能被频繁调用。添加,SP:Completed事件和SQL:BatchCompleted事件中的TextData内容会有重复,如果我们不加筛选去分析某个过程的执行次数、消耗可能就会出错。个人觉得,首次跟踪不必添加,我们先把消耗大的过程/语句找出来,然后会对那些过程去分析,自然会去查看过程涉及的代码,到时自然能知道是哪段代码消耗过高,再去细化便可。
于是去掉SP:Completed事件、SP:StmtCompleted和SQL:StmtCompleted事件:

编辑列筛选器,CPU>=0 ms,1 ms<=Duration<=10000 ms,运行跟踪,查询窗口执行下面语句:

DBA_Pro_GetJobSchedules '20151215 07:00:00.000','20151215 08:00:00.000'

跟踪窗口如图所示:

利用导出已有的服务器端跟踪中的脚本提取上面跟踪的定义:

 1 /************************************************************
 2  * 功能说明:查询跟踪目录视图得到运行中的服务器端跟踪定义
 3  * 注意事项:如果要运行提取出的跟踪定义,请注意调整筛选条件部分参数赋值的位置
 4  ************************************************************/
 5 -- Create a Queue
 6 DECLARE  @rc             int
 7         ,@TraceID        int
 8         ,@MaxFileSize    bigint=50
 9         ,@DateTime       datetime=null
10 
11 EXEC @rc = sp_trace_create @TraceID output ,0,N'InsertFileNameHere', @MaxFileSize, @DateTime
12 if (@rc != 0) goto error
13 
14 -- Set the events
15 DECLARE @on bit = 1
16 EXEC @rc = sp_trace_setevent @TraceID,10,1, @on
17 EXEC @rc = sp_trace_setevent @TraceID,10,2, @on
18 EXEC @rc = sp_trace_setevent @TraceID,10,3, @on
19 EXEC @rc = sp_trace_setevent @TraceID,10,8, @on
20 EXEC @rc = sp_trace_setevent @TraceID,10,9, @on
21 EXEC @rc = sp_trace_setevent @TraceID,10,10, @on
22 EXEC @rc = sp_trace_setevent @TraceID,10,11, @on
23 EXEC @rc = sp_trace_setevent @TraceID,10,12, @on
24 EXEC @rc = sp_trace_setevent @TraceID,10,13, @on
25 EXEC @rc = sp_trace_setevent @TraceID,10,14, @on
26 EXEC @rc = sp_trace_setevent @TraceID,10,15, @on
27 EXEC @rc = sp_trace_setevent @TraceID,10,16, @on
28 EXEC @rc = sp_trace_setevent @TraceID,10,17, @on
29 EXEC @rc = sp_trace_setevent @TraceID,10,18, @on
30 EXEC @rc = sp_trace_setevent @TraceID,10,31, @on
31 EXEC @rc = sp_trace_setevent @TraceID,10,35, @on
32 EXEC @rc = sp_trace_setevent @TraceID,12,1, @on
33 EXEC @rc = sp_trace_setevent @TraceID,12,3, @on
34 EXEC @rc = sp_trace_setevent @TraceID,12,8, @on
35 EXEC @rc = sp_trace_setevent @TraceID,12,9, @on
36 EXEC @rc = sp_trace_setevent @TraceID,12,10, @on
37 EXEC @rc = sp_trace_setevent @TraceID,12,11, @on
38 EXEC @rc = sp_trace_setevent @TraceID,12,12, @on
39 EXEC @rc = sp_trace_setevent @TraceID,12,13, @on
40 EXEC @rc = sp_trace_setevent @TraceID,12,14, @on
41 EXEC @rc = sp_trace_setevent @TraceID,12,15, @on
42 EXEC @rc = sp_trace_setevent @TraceID,12,16, @on
43 EXEC @rc = sp_trace_setevent @TraceID,12,17, @on
44 EXEC @rc = sp_trace_setevent @TraceID,12,18, @on
45 EXEC @rc = sp_trace_setevent @TraceID,12,31, @on
46 EXEC @rc = sp_trace_setevent @TraceID,12,35, @on
47 EXEC @rc = sp_trace_setevent @TraceID,33,1, @on
48 EXEC @rc = sp_trace_setevent @TraceID,33,3, @on
49 EXEC @rc = sp_trace_setevent @TraceID,33,8, @on
50 EXEC @rc = sp_trace_setevent @TraceID,33,9, @on
51 EXEC @rc = sp_trace_setevent @TraceID,33,10, @on
52 EXEC @rc = sp_trace_setevent @TraceID,33,11, @on
53 EXEC @rc = sp_trace_setevent @TraceID,33,12, @on
54 EXEC @rc = sp_trace_setevent @TraceID,33,14, @on
55 EXEC @rc = sp_trace_setevent @TraceID,33,31, @on
56 EXEC @rc = sp_trace_setevent @TraceID,33,35, @on
57 
58 -- Set the Filters
59 DECLARE  @Intfilter      int
60         ,@BigIntfilter   bigint
61 -- 请将参数的赋值放置到对应筛选表达式的前面(如果有的话)
62 SET @BigIntFilter = 1000--13,0,4
63 SET @BigIntFilter = 10000000--13,0,5
64 SET @IntFilter = 0--18,0,4
65 EXEC @rc = sp_trace_setfilter @TraceID,10,0,7,N'SQL Server Profiler - ca0047c1-71f7-4e31-b634-f9bb3fbce69a'
66 EXEC @rc = sp_trace_setfilter @TraceID,13,0,4,@BigIntFilter
67 EXEC @rc = sp_trace_setfilter @TraceID,13,0,5,@BigIntFilter
68 EXEC @rc = sp_trace_setfilter @TraceID,13,0,1,null
69 EXEC @rc = sp_trace_setfilter @TraceID,18,0,4,@IntFilter
70 EXEC @rc = sp_trace_setfilter @TraceID,18,0,1,null
71 
72 -- Set the trace status to start
73 exec sp_trace_setstatus @TraceID, 1
74 
75 -- Display trace id for future references
76 select TraceID=@TraceID
77 goto finish
78 
79 error: 
80 select ErrorCode=@rc
81 
82 finish:
83 go
View Code

修改跟踪文件存放位置到F:\TroubleShooting\Trace\sample,并将参数的赋值放置到对应筛选表达式的前面。停止原跟踪,执行代码创建并启动新跟踪,再在查询窗口多次执行之前的语句,之后停止并移除跟踪,用Profiler打开新跟踪文件:

上面的跟踪定义基本可以作为首次跟踪的模板,只需调整文件存储位置,及过滤筛选值即可。同时再次验证导出已有的服务器端跟踪脚本的正确性。

posted @ 2015-12-20 20:10  Uest  阅读(794)  评论(0编辑  收藏  举报