SQLServer 利用profiler生成脚本在后台跟踪堵塞语句或慢查询语句

分享个SQLServer profiler 的一个技巧吧。很早用过，忘记总结了，现在再用时记录下来。

当启用 SQLServer profiler 跟踪sql语句的时候，是非常方便的，同时也可以按照各个维度筛选跟踪。但是对于长时间跟踪，一直打开着profiler界面不是很好。有一个技巧是可以把profiler 的跟踪设置导出成sql 脚本，脚本可以在后台执行。以跟踪慢查询为例。

【堵塞跟踪】

1. 首先打开profiler 跟踪堵塞语句，事件选择 Blocked process report ，再把其他的事件都去掉。

2. 分别执行以下语句，模拟堵塞情况。

[sql]  view plain  copy

 print ?

1. --[查询窗口1],更新一行记录,等待20秒钟  
2. BEGIN TRAN  
3.     UPDATE dbo.AAA SET Title='KK'  
4.     WAITFOR DELAY '00:00:20'  
5. COMMIT TRAN  
6.   
7. --同时再打开[查询窗口2],更新同一行记录(将被堵塞)  
8. UPDATE dbo.AAA SET Title='KK'  

3. 几秒钟后，profiler 捕获到了堵塞信息。

4. 查看XML格式的堵塞信息。<blocked-process> 块是被堵塞的信息，<blocking-process> 为堵塞其他进程的信息。

[plain]  view plain  copy

 print ?

1. <blocked-process-report>  
2.  <blocked-process>  
3.   <process id="process271931c8" taskpriority="0" logused="0" waitresource="KEY: 13:72057595528151040 (f2008a1296d8)"   
4.     waittime="9313" ownerId="923979" transactionname="UPDATE" lasttranstarted="2015-10-19T16:54:36.753"   
5.     XDES="0x53bec08" lockMode="U" schedulerid="1" kpid="7712" status="suspended" spid="55" sbid="0" ecid="0"   
6.     priority="0" trancount="2" lastbatchstarted="2015-10-19T16:54:36.753" lastbatchcompleted="2015-10-19T16:33:12.240"   
7.     clientapp="Microsoft SQL Server Management Studio - 查询" hostname="kk" hostpid="4204" loginname="kk\Administrator"   
8.     isolationlevel="read committed (2)" xactid="923979" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
9.      
10.    <executionStack>  
11.     <frame line="1" stmtstart="36" sqlhandle="0x020000005733991f90e1d1e1f620bc49ef0224e73cc0dc81"/>  
12.     <frame line="1" sqlhandle="0x020000008d15b82d9936449bac7b1e417abd9ca11e87c9b3"/>  
13.    </executionStack>  
14.    <inputbuf>  
15. UPDATE dbo.AAA SET Title='KK'   </inputbuf>  
16.   </process>  
17.  </blocked-process>  
18.  <blocking-process>  
19.   <process status="suspended" waittime="10501" spid="69" sbid="0" ecid="0" priority="0" trancount="1"   
20.   lastbatchstarted="2015-10-19T16:54:35.563" lastbatchcompleted="2015-10-19T16:43:28.033" lastattention="2015-10-19T15:37:32.157"   
21.   clientapp="Microsoft SQL Server Management Studio - 查询" hostname="kk" hostpid="4204" loginname="kk\Administrator"   
22.   isolationlevel="read committed (2)" xactid="923974" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">  
23.      
24.    <executionStack>  
25.     <frame line="3" stmtstart="102" stmtend="152" sqlhandle="0x020000006a0d703b95612db79d614f608d92db0ec58bcfb3"/>  
26.    </executionStack>  
27.    <inputbuf>  
28. BEGIN TRAN  
29.     UPDATE dbo.AAA SET Title='KK'  
30.     WAITFOR DELAY '00:00:20'  
31. COMMIT TRAN   </inputbuf>  
32.   </process>  
33.  </blocking-process>  
34. </blocked-process-report>  

但是这样跟踪的确不方便，可以试试以下这种方法。

1. 在当前 profiler 设置中导出SQL 脚本

2. 更改了创建跟踪（sp_trace_create）一些跟踪信息。最终脚本如下

[sql]  view plain  copy

 print ?

1. -- Create a Queue  
2. declare @rc int  
3. declare @traceid int          --跟踪分配的编号  
4. declare @options int          --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8  
5. declare @tracefile nvarchar(500)--跟踪文件的存储路径  
6. declare @maxfilesize bigint   --跟踪文件的大小，单位是mb，默认5mb  
7. declare @endtime datetime     --停止跟踪的日期和时间,为NULL则表示一直跟踪  
8. declare @filecount int        --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效  
9. set @options = 2  
10. set @tracefile = N'E:\BlockedTraceResult\BlockedTraceName'  
11. set @maxfilesize = 20  
12. set @endtime = DATEADD(D,1,GETDATE())  
13. set @filecount = 5  
14.   
15. --  exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL   
16. exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@endtime,@filecount  
17. if (@rc != 0) goto error  
18.   
19. --  Client side File and Table cannot be scripted  
20.   
21. --  Set the events  
22. declare @on bit  
23. set @on = 1  
24. exec sp_trace_setevent @TraceID, 137, 15, @on  
25. exec sp_trace_setevent @TraceID, 137, 12, @on  
26. exec sp_trace_setevent @TraceID, 137, 1, @on  
27. exec sp_trace_setevent @TraceID, 137, 13, @on  
28. exec sp_trace_setevent @TraceID, 137, 14, @on  
29.   
30.   
31. --  Set the Filters  
32. declare @intfilter int  
33. declare @bigintfilter bigint  
34.   
35. --  Set the trace status to start  
36. exec sp_trace_setstatus @TraceID, 1  
37.   
38. --  display trace id for future references  
39. select TraceID=@TraceID  
40. goto finish  
41.   
42. error:   
43. select ErrorCode=@rc  
44.   
45. finish:   
46. go  

3. 执行上面的脚本，跟踪即可在后台运行，跟踪记录记录在文件中。

4. 要查看跟踪内容，可以双击跟踪文件以 profiler 打开查看。或者可以用sql 语句查看其跟踪设置和跟踪记录内容。

[sql]  view plain  copy

 print ?

1. select * from sys.traces  
2. select * from ::fn_trace_getinfo(0)  
3.   
4. select * from ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTraceName.trc',default)  
5.   
6. select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData  
7. from ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTraceName.trc',default)  

5. 停止、启用和删除跟踪

[sql]  view plain  copy

 print ?

1. select id,status from sys.traces  
2.   
3. --  exec sp_trace_setstatus id,num  
4.   
5. exec sp_trace_setstatus 3,0 --停止跟踪  
6. exec sp_trace_setstatus 3,1 --启动跟踪  
7. exec sp_trace_setstatus 3,2 --删除跟踪  

6. 堵塞时间设置（多少秒才算是堵塞）

[sql]  view plain  copy

 print ?

1. --  查看堵塞时间(单位:秒)  
2. exec sp_configure N'blocked process threshold (s)'  
3.   
4. --  设置堵塞时间  
5. exec sp_configure N'blocked process threshold (s)',5   
6. reconfigure  

注意：

这里跟踪的只是堵塞，如果一个语句执行30秒，虽然执行时间较长，但是没有堵塞其他语句，也是跟踪不到的。所以有的慢查询是会漏掉的。

如果设置5秒捕获堵塞，同一个查询如果堵塞了10秒，将会跟踪到2次相同的信息，也就是每5秒捕获一次。

【慢查询、堵塞跟踪】

同样步骤，跟踪慢查询只要 跟踪存储过程或者TSQL的完成情况，如下图所示，跟踪 RPC:Complited 和 SQL:BacthComplited ，只有跟踪 Complited 才有时间字段，starting 是没有时间的，所以starting 没必要跟踪了。

筛选跟踪时间，这里设置只跟踪记录大于等于3000毫秒的语句。

导出脚本，语句如下：

[sql]  view plain  copy

 print ?

1. declare @rc int  
2. declare @traceid int          --跟踪分配的编号  
3. declare @options int          --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8  
4. declare @tracefile nvarchar(500)--跟踪文件的存储路径  
5. declare @maxfilesize bigint   --跟踪文件的大小，单位是mb，默认5mb  
6. declare @endtime datetime     --停止跟踪的日期和时间,为NULL则表示一直跟踪  
7. declare @filecount int        --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效  
8. set @options = 2  
9. set @tracefile = N'E:\SlowQueryTraceResult\SlowTraceName'  
10. set @maxfilesize = 20  
11. set @endtime = NULL --无结束时间  
12. set @filecount = 5  
13.   
14. exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@endtime,@filecount  
15. if (@rc != 0) goto error  
16.   
17. -- Client side File and Table cannot be scripted  
18.   
19. -- Set the events  
20. declare @on bit  
21. set @on = 1  
22. exec sp_trace_setevent @TraceID, 10, 15, @on  
23. exec sp_trace_setevent @TraceID, 10, 16, @on  
24. exec sp_trace_setevent @TraceID, 10, 1, @on  
25. exec sp_trace_setevent @TraceID, 10, 17, @on  
26. exec sp_trace_setevent @TraceID, 10, 18, @on  
27. exec sp_trace_setevent @TraceID, 10, 12, @on  
28. exec sp_trace_setevent @TraceID, 10, 13, @on  
29. exec sp_trace_setevent @TraceID, 10, 6, @on  
30. exec sp_trace_setevent @TraceID, 10, 14, @on  
31. exec sp_trace_setevent @TraceID, 12, 15, @on  
32. exec sp_trace_setevent @TraceID, 12, 16, @on  
33. exec sp_trace_setevent @TraceID, 12, 1, @on  
34. exec sp_trace_setevent @TraceID, 12, 17, @on  
35. exec sp_trace_setevent @TraceID, 12, 6, @on  
36. exec sp_trace_setevent @TraceID, 12, 14, @on  
37. exec sp_trace_setevent @TraceID, 12, 18, @on  
38. exec sp_trace_setevent @TraceID, 12, 12, @on  
39. exec sp_trace_setevent @TraceID, 12, 13, @on  
40.   
41.   
42. -- Set the Filters  
43. declare @intfilter int  
44. declare @bigintfilter bigint  
45.   
46. set @bigintfilter = 3000000  
47. exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter  
48.   
49. -- Set the trace status to start  
50. exec sp_trace_setstatus @TraceID, 1  
51.   
52. -- display trace id for future references  
53. select TraceID=@TraceID  
54. goto finish  
55.   
56. error:   
57. select ErrorCode=@rc  
58.   
59. finish:   
60. go  

测试跟踪，设置事务执行大于3秒钟：

[sql]  view plain  copy

 print ?

1. BEGIN TRAN  
2.     SELECT * FROM dbo.TestTab  
3.     WAITFOR DELAY '00:00:03'  
4. ROLLBACK TRAN  

跟踪结果：

[sql]  view plain  copy

 print ?

1. select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData  
2. from ::fn_trace_gettable('E:\SlowQueryTraceResult\SlowTraceName.trc',default)  

注意：

与堵塞一样，如果一条语句可能执行不到1秒钟，但是资源被其他进程占用了，导致这条语句等待了10秒，这条语句也同样被输出。

跟踪的语句只有执行完成才能跟踪到，正在执行的语句是无法跟踪到的。

对于当前正在执行的，可以用系统 DMV 视图查看，如 master.dbo.sysprocesses 或者 sys.dm_exec_requests 等。

转载：http://blog.csdn.net/kk185800961/article/details/49252037