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

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

概述分享个SQLServer profiler 的一个技巧吧。很早用过,忘记总结了,现在再用时记录下来。 当启用 SQLServer profiler 跟踪sql语句的时候,是非常方便的,同时也可以按照各个维度筛选跟踪。但是对于长时间跟踪,一直打开着profiler界面不是很好。有一个技巧是可以把profiler 的跟踪设置导出成sql 脚本,脚本可以在后台执行。以跟踪慢查询为例。 【堵塞跟踪】 1.

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

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


【堵塞跟踪】

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 Queuedeclare @rc intdeclare @traceID int		  --跟踪分配的编号declare @options int		  --TRACE_file_RollovER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBox=8declare @tracefile nvarchar(500)--跟踪文件的存储路径declare @maxfilesize bigint	  --跟踪文件的大小,单位是mb,默认5mbdeclare @endtime datetime	  --停止跟踪的日期和时间,为NulL则表示一直跟踪declare @filecount int		  --跟踪文件的数量,其值大于1,TRACE_file_RollovER=2 时有效set @options = 2set @tracefile = N'E:\BlockedTraceResult\BlockedTracename'set @maxfilesize = 20set @endtime = DATEADD(D,1,GETDATE())set @filecount = 5--  exec @rc = sp_trace_create @TraceID output,N'InsertfilenameHere',@maxfilesize,NulL exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@endtime,@filecountif (@rc != 0) goto error--  ClIEnt sIDe file and table cannot be scripted--  Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID,137,15,@onexec sp_trace_setevent @TraceID,12,13,14,@on--  Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigint--  Set the trace status to startexec sp_trace_setstatus @TraceID,1--  display trace ID for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: go

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



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

select * from sys.tracesselect * from ::fn_trace_getinfo(0)select * from ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTracename.trc',1)select Servername,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextDatafrom ::fn_trace_gettable('E:\BlockedTraceResult\BlockedTracename.trc',1)

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

select ID,status from sys.traces--  exec sp_trace_setstatus ID,numexec 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 intdeclare @traceID int		  --跟踪分配的编号declare @options int		  --TRACE_file_RollovER=2/SHUTDOWN_ON_ERROR=4/TRACE_PRODUCE_BLACKBox=8declare @tracefile nvarchar(500)--跟踪文件的存储路径declare @maxfilesize bigint	  --跟踪文件的大小,单位是mb,默认5mbdeclare @endtime datetime	  --停止跟踪的日期和时间,TRACE_file_RollovER=2 时有效set @options = 2set @tracefile = N'E:\SlowqueryTraceResult\SlowTracename'set @maxfilesize = 20set @endtime = NulL --无结束时间set @filecount = 5exec @rc = sp_trace_Create @TraceID output,@filecountif (@rc != 0) goto error-- ClIEnt sIDe file and table cannot be scripted-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID,10,16,17,18,6,@on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintset @bigintfilter = 3000000exec sp_trace_setfilter @TraceID,4,@bigintfilter-- Set the trace status to startexec sp_trace_setstatus @TraceID,1-- display trace ID for future referencesselect TraceID=@TraceIDgoto finisherror: select ErrorCode=@rcfinish: go

测试跟踪,设置事务执行大于3秒钟:

BEGIN TRAN    SELECT * FROM dbo.TestTab    WAITFOR DELAY '00:00:03'RolLBACK TRAN

跟踪结果:

select Servername,TextData) XMLTextDatafrom ::fn_trace_gettable('E:\SlowqueryTraceResult\SlowTracename.trc',1)



注意:

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


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

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

总结

以上是内存溢出为你收集整理的SQLServer 利用profiler生成脚本在后台跟踪堵塞语句或慢查询语句全部内容,希望文章能够帮你解决SQLServer 利用profiler生成脚本在后台跟踪堵塞语句或慢查询语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1168684.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-01
下一篇 2022-06-01

发表评论

登录后才能评论

评论列表(0条)

保存