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

2021-12-26 23:38:19

通常接手一台数据库服务器后，我们会开启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批完成事件
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'

跟踪窗口如图所示：

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

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

  * 功能说明：查询跟踪目录视图得到运行中的服务器端跟踪定义

  * 注意事项：如果要运行提取出的跟踪定义，请注意调整筛选条件部分参数赋值的位置

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

 -- Create a Queue

 DECLARE  @rc             int

         ,@TraceID        int

         ,@MaxFileSize    bigint=50

         ,@DateTime       datetime=null

 EXEC @rc = sp_trace_create @TraceID output ,0,N'InsertFileNameHere', @MaxFileSize, @DateTime

 if (@rc != 0) goto error

 -- Set the events

 DECLARE @on bit = 1

 EXEC @rc = sp_trace_setevent @TraceID,10,1, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,2, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,3, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,8, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,9, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,10, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,11, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,12, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,13, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,14, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,15, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,16, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,17, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,18, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,31, @on

 EXEC @rc = sp_trace_setevent @TraceID,10,35, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,1, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,3, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,8, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,9, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,10, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,11, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,12, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,13, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,14, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,15, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,16, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,17, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,18, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,31, @on

 EXEC @rc = sp_trace_setevent @TraceID,12,35, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,1, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,3, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,8, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,9, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,10, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,11, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,12, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,14, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,31, @on

 EXEC @rc = sp_trace_setevent @TraceID,33,35, @on

 -- Set the Filters

 DECLARE  @Intfilter      int

         ,@BigIntfilter   bigint

 -- 请将参数的赋值放置到对应筛选表达式的前面(如果有的话)

 SET @BigIntFilter = 1000--13,0,4

 SET @BigIntFilter = 10000000--13,0,5

 SET @IntFilter = 0--18,0,4

 EXEC @rc = sp_trace_setfilter @TraceID,10,0,7,N'SQL Server Profiler - ca0047c1-71f7-4e31-b634-f9bb3fbce69a'

 EXEC @rc = sp_trace_setfilter @TraceID,13,0,4,@BigIntFilter

 EXEC @rc = sp_trace_setfilter @TraceID,13,0,5,@BigIntFilter

 EXEC @rc = sp_trace_setfilter @TraceID,13,0,1,null

 EXEC @rc = sp_trace_setfilter @TraceID,18,0,4,@IntFilter

 EXEC @rc = sp_trace_setfilter @TraceID,18,0,1,null

 -- Set the trace status to start

 exec sp_trace_setstatus @TraceID, 1

 -- Display trace id for future references

 select TraceID=@TraceID

 goto finish

 error:

 select ErrorCode=@rc

 finish:

 go

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

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

码农公寓

相关文章