加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

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

发布时间：2020-12-12 13:27:14 所属栏目：MsSql教程 来源：网络整理
导读：当启用?SQLServer profiler 跟踪sql语句的时候，是非常方便的，同时也可以按照各个维度筛选跟踪。但是对于长时间跟踪，一直打开着profiler界面不是很好。有一个技巧是可以把profiler 的跟踪设置导出成sql 脚本，脚本可以在后台执行。以跟踪慢查询为例。 【堵

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


【堵塞跟踪】

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



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

[sql]? view plain ?copy
  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. SET?Title='KK'??

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



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

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



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


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



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

copy
    --?Create?a?Queue??
  1. declare?@rc?int??
  2. declare?@traceid?int??????????--跟踪分配的编号??
  3. declare?@options?--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:BlockedTraceResultBlockedTraceName'??
  10. set?@maxfilesize?=?20??
  11. set?@endtime?=?DATEADD(D,1,GETDATE())??
  12. set?@filecount?=?5??
  13. --??exec?@rc?=?sp_trace_create?@TraceID?output,?0,?N'InsertFileNameHere',?@maxfilesize,?NULL???
  14. exec?@rc?=?sp_trace_Create?@TraceID?output,@options,@tracefile,@maxfilesize,@endtime,@filecount??
  15. if?(@rc?!=?0)?goto?error??
  16. --??Client?side?File?and?Table?cannot?be?scripted??
  17. --??Set?the?events??
  18. declare?@on?bit??
  19. set?@on?=?1??
  20. exec?sp_trace_setevent?@TraceID,?137,?15,?@on??
  21. on??
  22. ??
  23. --??Set?the?Filters??
  24. declare?@intfilter?declare?@bigintfilter?bigint??
  25. --??Set?the?trace?status?to?start??
  26. exec?sp_trace_setstatus?@TraceID,?1??
  27. --??display?trace?id?for?future?references??
  28. select?TraceID=@TraceID??
  29. goto?finish??
  30. error:???
  31. select?ErrorCode=@rc??
  32. finish:???
  33. go??

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



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

copy
    select?*?from?sys.traces??
  1. from?::fn_trace_getinfo(0)??
  2. from?::fn_trace_gettable('E:BlockedTraceResultBlockedTraceName.trc',default)??
  3. select?ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData)?XMLTextData??
  4. default)??

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

copy
    select?id,status?--??exec?sp_trace_setstatus?id,num??
  1. exec?sp_trace_setstatus?3,0?--停止跟踪??
  2. --启动跟踪??
  3. --删除跟踪??

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

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


注意：

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


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



【慢查询、堵塞跟踪】


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



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



导出脚本，语句如下：

copy
    int??
  1. --跟踪分配的编号??
  2. --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8??
  3. --跟踪文件的存储路径??
  4. --跟踪文件的大小，单位是mb，默认5mb??
  5. ??
  6. ??
  7. set?@options?=?2??
  8. set?@tracefile?=?N'E:SlowQueryTraceResultSlowTraceName'??
  9. set?@maxfilesize?=?20??
  10. set?@endtime?=?NULL?--无结束时间??
  11. set?@filecount?=?5??
  12. --?Client?side?File?and?Table?cannot?be?scripted??
  13. --?Set?the?events??
  14. --?Set?the?Filters??
  15. bigint??
  16. set?@bigintfilter?=?3000000??
  17. exec?sp_trace_setfilter?@TraceID,?4,?@bigintfilter??
  18. --?Set?the?trace?status?to?start??
  19. --?display?trace?id?for?future?references??
  20. go??

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

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

跟踪结果：

copy
    from?::fn_trace_gettable('E:SlowQueryTraceResultSlowTraceName.trc',153); font-weight:bold; background-color:inherit">default)??



注意：

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

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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同