SQLServer 利用profiler生成脚本在后台跟踪堵塞语句或慢查询语句
<div id="blogColumnPayAdvert">
<div class="column-group">
<div class="column-group-item column-group0 column-group-item-one">
<div class="item-l">
<a class="item-target" href="https://blog.csdn.net/saga_gallon/category_6310868.html" target="_blank" title="Sql server 应用" data-report-click="{"spm":"1001.2101.3001.6332"}">
<img class="item-target" src="https://img-blog.csdnimg.cn/20201014180756923.png?x-oss-process=image/resize,m_fixed,h_64,w_64" alt="">
<span class="title item-target">
<span>
<span class="tit">Sql server 应用</span>
<span class="dec">专栏收录该内容</span>
</span>
</span>
</a>
</div>
<div class="item-m">
<span>22 篇文章</span>
<span>0 订阅</span>
</div>
<div class="item-r">
<a class="item-target article-column-bt articleColumnFreeBt" data-id="6310868">订阅专栏</a>
</div>
</div>
</div>
</div>
<article class="baidu_pl">
<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-6e43165c0a.css">
<div id="content_views" class="htmledit_views">
<p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; font-family:Arial; font-size:14px; line-height:26px"> 当启用 SQLServer profiler 跟踪<a href="https://so.csdn.net/so/search?q=sql%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=sql%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"sql语句\"}"}" data-tit="sql语句" data-pretit="sql语句">sql语句</a>的时候,是非常方便的,同时也可以按照各个维度筛选跟踪。但是对于长时间跟踪,一直打开着profiler界面不是很好。有一个技巧是可以把profiler 的跟踪设置导出成sql 脚本,脚本可以在后台执行。以跟踪慢查询为例。</p>
【堵塞跟踪】
1. 首先打开profiler 跟踪堵塞语句,事件选择 Blocked process report ,再把其他的事件都去掉。
2. 分别执行以下语句,模拟堵塞情况。
- --[查询窗口1],更新一行记录,等待20秒钟
- BEGIN TRAN
- UPDATE dbo.AAA SET Title='KK'
- WAITFOR DELAY '00:00:20'
- COMMIT TRAN
- --同时再打开[查询窗口2],更新同一行记录(将被堵塞)
- UPDATE dbo.AAA SET Title='KK'
3. 几秒钟后,profiler 捕获到了堵塞信息。
4. 查看XML格式的堵塞信息。<blocked-process> 块是被堵塞的信息,<blocking-process> 为堵塞其他进程的信息。
- <blocked-process-report>
- <blocked-process>
- <process id="process271931c8" taskpriority="0" logused="0" waitresource="KEY: 13:72057595528151040 (f2008a1296d8)"
- waittime="9313" ownerId="923979" transactionname="UPDATE" lasttranstarted="2015-10-19T16:54:36.753"
- XDES="0x53bec08" lockMode="U" schedulerid="1" kpid="7712" status="suspended" spid="55" sbid="0" ecid="0"
- priority="0" trancount="2" lastbatchstarted="2015-10-19T16:54:36.753" lastbatchcompleted="2015-10-19T16:33:12.240"
- clientapp="Microsoft SQL Server Management Studio - 查询" hostname="kk" hostpid="4204" loginname="kk\Administrator"
- isolationlevel="read committed (2)" xactid="923979" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
- <executionStack>
- <frame line="1" stmtstart="36" sqlhandle="0x020000005733991f90e1d1e1f620bc49ef0224e73cc0dc81"/>
- <frame line="1" sqlhandle="0x020000008d15b82d9936449bac7b1e417abd9ca11e87c9b3"/>
- </executionStack>
- <inputbuf>
- UPDATE dbo.AAA SET Title='KK' </inputbuf>
- </process>
- </blocked-process>
- <blocking-process>
- <process status="suspended" waittime="10501" spid="69" sbid="0" ecid="0" priority="0" trancount="1"
- lastbatchstarted="2015-10-19T16:54:35.563" lastbatchcompleted="2015-10-19T16:43:28.033" lastattention="2015-10-19T15:37:32.157"
- clientapp="Microsoft SQL Server Management Studio - 查询" hostname="kk" hostpid="4204" loginname="kk\Administrator"
- isolationlevel="read committed (2)" xactid="923974" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
- <executionStack>
- <frame line="3" stmtstart="102" stmtend="152" sqlhandle="0x020000006a0d703b95612db79d614f608d92db0ec58bcfb3"/>
- </executionStack>
- <inputbuf>
- BEGIN TRAN
- UPDATE dbo.AAA SET Title='KK'
- WAITFOR DELAY '00:00:20'
- COMMIT TRAN </inputbuf>
- </process>
- </blocking-process>
- </blocked-process-report>
但是这样跟踪的确不方便,可以试试以下这种方法。
1. 在当前 profiler 设置中导出SQL 脚本
2. 更改了创建跟踪(sp_trace_create)一些跟踪信息。最终脚本如下
- -- Create a Queue
- declare @rc int
- declare @traceid int --跟踪分配的编号
- declare @options int --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8
- declare @tracefile nvarchar(500)--跟踪文件的存储路径
- declare @maxfilesize bigint --跟踪文件的大小,单位是mb,默认5mb
- declare @endtime datetime --停止跟踪的日期和时间,为NULL则表示一直跟踪
- declare @filecount int --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效
- set @options = 2
- set @tracefile = N'E:\BlockedTraceResult\BlockedTraceName'
- set @maxfilesize = 20
- set @endtime = DATEADD(D,1,GETDATE())
- set @filecount = 5
- -- exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
- exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@endtime,@filecount
- if (@rc != 0) goto error
- -- Client side File and Table cannot be scripted
- -- Set the events
- declare @on bit
- set @on = 1
- exec sp_trace_setevent @TraceID, 137, 15, @on
- exec sp_trace_setevent @TraceID, 137, 12, @on
- exec sp_trace_setevent @TraceID, 137, 1, @on
- exec sp_trace_setevent @TraceID, 137, 13, @on
- exec sp_trace_setevent @TraceID, 137, 14, @on
- -- Set the Filters
- declare @intfilter int
- declare @bigintfilter bigint
- -- 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
3. 执行上面的脚本,跟踪即可在后台运行,跟踪记录记录在文件中。
4. 要查看跟踪内容,可以双击跟踪文件以 profiler 打开查看。或者可以用sql 语句查看其跟踪设置和跟踪记录内容。
- select * from sys.traces
- select * from ::fn_trace_getinfo(0)
- select * from ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTraceName.trc',default)
- select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData
- from ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTraceName.trc',default)
5. 停止、启用和删除跟踪
- select id,status from sys.traces
- -- exec sp_trace_setstatus id,num
- exec sp_trace_setstatus 3,0 --停止跟踪
- exec sp_trace_setstatus 3,1 --启动跟踪
- exec sp_trace_setstatus 3,2 --删除跟踪
6. 堵塞时间设置(多少秒才算是堵塞)
- -- 查看堵塞时间(单位:秒)
- exec sp_configure N'blocked process threshold (s)'
- -- 设置堵塞时间
- exec sp_configure N'blocked process threshold (s)',5
- reconfigure
注意:
这里跟踪的只是堵塞,如果一个语句执行30秒,虽然执行时间较长,但是没有堵塞其他语句,也是跟踪不到的。所以有的慢查询是会漏掉的。
如果设置5秒捕获堵塞,同一个查询如果堵塞了10秒,将会跟踪到2次相同的信息,也就是每5秒捕获一次。
【慢查询、堵塞跟踪】
同样步骤,跟踪慢查询只要 跟踪存储过程或者TSQL的完成情况,如下图所示,跟踪 RPC:Complited 和 SQL:BacthComplited ,只有跟踪 Complited 才有时间字段,starting 是没有时间的,所以starting 没必要跟踪了。
筛选跟踪时间,这里设置只跟踪记录大于等于3000毫秒的语句。
导出脚本,语句如下:
- declare @rc int
- declare @traceid int --跟踪分配的编号
- declare @options int --TRACE_FILE_ROLLOVER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBOX=8
- declare @tracefile nvarchar(500)--跟踪文件的存储路径
- declare @maxfilesize bigint --跟踪文件的大小,单位是mb,默认5mb
- declare @endtime datetime --停止跟踪的日期和时间,为NULL则表示一直跟踪
- declare @filecount int --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效
- set @options = 2
- set @tracefile = N'E:\SlowQueryTraceResult\SlowTraceName'
- set @maxfilesize = 20
- set @endtime = NULL --无结束时间
- set @filecount = 5
- exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@endtime,@filecount
- if (@rc != 0) goto error
- -- Client side File and Table cannot be scripted
- -- Set the events
- declare @on bit
- set @on = 1
- exec sp_trace_setevent @TraceID, 10, 15, @on
- exec sp_trace_setevent @TraceID, 10, 16, @on
- exec sp_trace_setevent @TraceID, 10, 1, @on
- exec sp_trace_setevent @TraceID, 10, 17, @on
- exec sp_trace_setevent @TraceID, 10, 18, @on
- exec sp_trace_setevent @TraceID, 10, 12, @on
- exec sp_trace_setevent @TraceID, 10, 13, @on
- exec sp_trace_setevent @TraceID, 10, 6, @on
- exec sp_trace_setevent @TraceID, 10, 14, @on
- exec sp_trace_setevent @TraceID, 12, 15, @on
- exec sp_trace_setevent @TraceID, 12, 16, @on
- exec sp_trace_setevent @TraceID, 12, 1, @on
- exec sp_trace_setevent @TraceID, 12, 17, @on
- exec sp_trace_setevent @TraceID, 12, 6, @on
- exec sp_trace_setevent @TraceID, 12, 14, @on
- exec sp_trace_setevent @TraceID, 12, 18, @on
- exec sp_trace_setevent @TraceID, 12, 12, @on
- exec sp_trace_setevent @TraceID, 12, 13, @on
- -- Set the Filters
- declare @intfilter int
- declare @bigintfilter bigint
- set @bigintfilter = 3000000
- exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
- -- 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
测试跟踪,设置事务执行大于3秒钟:
- BEGIN TRAN
- SELECT * FROM dbo.TestTab
- WAITFOR DELAY '00:00:03'
- ROLLBACK TRAN
跟踪结果:
- select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData
- from ::fn_trace_gettable('E:\SlowQueryTraceResult\SlowTraceName.trc',default)
![](https://i-blog.csdnimg.cn/blog_migrate/ade6de5c9a5a7282537db0a04a326653.jpeg)
注意:
与堵塞一样,如果一条语句可能执行不到1秒钟,但是资源被其他进程占用了,导致这条语句等待了10秒,这条语句也同样被输出。
跟踪的语句只有执行完成才能跟踪到,正在执行的语句是无法跟踪到的。
对于当前正在执行的,可以用系统 DMV 视图查看,如 master.dbo.sysprocesses 或者 sys.dm_exec_requests 等。
转载于http://blog.csdn.net/kk185800961/article/details/49252037
</article>