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  阅读(930)  评论(0)  编辑  收藏  举报