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

最新推荐文章于 2023-04-25 10:14:45 发布
最新推荐文章于 2023-04-25 10:14:45 发布
阅读量9.1k 收藏 6
点赞数 4
分类专栏： SQLServer 常用工具 SQLServer SQLServer DBA
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kk185800961/article/details/49252037
版权

分享个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 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)


注意：

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


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

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



薛定谔的DBA
关注
  • 4
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
sql性能优化：MS-SQL（SQL Server）跟踪日志信息查看-阻塞跟踪日志和慢SQL跟踪日志-综合精简版
橙-极纪元-cplvfx-JJY.Cheng
03-24 680
sql性能优化：MS-SQL（SQL Server）跟踪日志信息查看-阻塞跟踪日志和慢SQL跟踪日志
EFProf_SQLserver执行脚本跟踪_sqlserver监控_EFProf_
09-28
一款跟踪SQLserver执行脚本的软件，只需在.net项目中引用该工具的动态库就可以完美监控SQLserver执行脚本的多项指标（执行语句，耗时等），比SQLserver自带的SQLserver profiler工具使用起来要简单。
使用SQLProfiler进行死锁追踪
dingxiting的博客
04-25 672
死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
sqlserver监控阻塞（死锁）具体情况
weixin_30702413的博客
09-01 288
sqlserver监控阻塞（死锁）具体情况 　　公司sqlserver的监控系统主要是采用zabbix监控，但是zabbix的监控只能通过性能计数器给出报警，而无法给出具体的阻塞情况，比如阻塞会话、语句、时间等，所以需要配合sqlserver的一些特性来进行监控，这里给出一个方案： 　　1.创建阻塞日志表，用于记录阻塞情况 　　2.新建作业...
SQL Server之捕获阻塞事件
10-30 290
在SQL Server 2005之前的版本，分析哪些进程产生阻塞以及哪些进程被阻塞，都需要使用脚本手工执行。现在使用SQL Profiler可以直接监控阻塞的情况，使用SQL Profiler监控哪些进程被阻塞，首先需要确定SQL Trace的事件类，监控阻塞的事件类为Errors and Warnings-Blocked process report，如图所示。 Blocked process...
SQL SERVER 慢查询检查语句
weixin_30537451的博客
05-23 643
我建议您先优化慢查询，然后根据IOPS、QPS、CPU等指标决定是否升级实例规格。排查历史慢查询：SELECT TOP 20 [Total IO] = (qs.total_logical_reads + qs.total_logical_writes) , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /...
sqlserve如何查找慢SQL
weixin_42594427的博客
01-11 1733
可以使用 SQL Server 自带的性能监控工具来查找慢 SQL。 使用 SQL Server Management Studio 的查询窗口打开查询计划，可以看到执行时间和其他性能指标。 使用 SQL Server 性能监控器来监控执行计划、等待时间和资源使用情况。 使用 SQL Server Profiler 来跟踪每条 SQL 语句的执行情况，并分析性能瓶颈。 使用数据库内置的 D...
SQLSERVER排查CPU占用高的情况
onway_goahead的博客
12-04 930
参考：https://www.cnblogs.com/lyhabc/archive/2013/06/12/3133273.html 排查方向 一般排查都是用下面的脚本，一般会用到三个视图sys.sysprocesses,dm_exec_sessions,dm_exec_requests USE master GO --如果要指定数据库就把注释去掉 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid]...
sqlserver 查看脚本执行时间
weixin_41048746的博客
07-06 2485
方法一： --开启统计信息 SET STATISTICS TIME ON SET STATISTICS IO ON GO select * from XXX --这里写执行的脚本，没有分号 GO 执行后在输出的消息体里查看： 占用时间：当前这一步的总时间。如果占用时间比CPU时间大很多，那开销主要用于IO上。 逻辑读：从数据缓存读取的页数。数字越小，性能越好。在性能调优中这个数字...
利用sqlprofile优化goldengate中慢的sql语句
莫等闲
11-05 1053
HP unix中监控到一个进程占用cpu较高，后来发现是gg中的语句执行计划有问题，导致cpu偏高。由于gg中的语句不能更改，怎样才能改变不能更改的语句的执行计划呢，这里可以采用sqlprofile来优化。 [szggs1@szodsd01] $ top -h System: szodsd01 Wed Nov 5 10:1
Arthas-trace命令查看方法性能开销耗时、追踪方法调用路径
小蜗牛的博客
12-11 7765
Arthas-trace命令查看方法性能开销耗时、追踪方法调用路径
SQL执行耗时跟踪_跟踪语句.sql
06-01
由SQL Server Profiler 模板导出。
经典SQL语句大全
12-20
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段，不能和普通的字段放在一起； 13、对数据库进行操作： 分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后...
sql经典语句一部分
07-31
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段，不能和普通的字段放在一起； 13、对数据库进行操作： 分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后...
SQL Server数据库技术大全 电子书
04-10
1.5.2 使用SQL Server Profiler跟踪数据库/22 1.5.3 使用SQL Server 2008联机丛书/23 1.6 SQL Server 2008系统数据库简介/24 1.6.1 系统数据库master——系统表的管理/24 1.6.2 系统数据库model——数据库的模板/25 ...
第十六章——处理锁、阻塞和死锁（3）——使用SQLServer Profiler侦测死锁
数据库
03-20 241
前言： 作为DBA，可能经常会遇到有同事或者客户反映经常发生死锁，影响了系统的使用。此时，你需要尽快侦测和处理这类问题。 死锁是当两个或者以上的事务互相阻塞引起的。在这种情况下两个事务会无限期地等待对方释放资源以便操作。下面是死锁的示意图： 本文将使用SQLServer Profiler来跟踪死锁。 准备工作： 为了侦测死锁，我们需要先模拟死锁。本例将使用两个不同的会话创...
SQL Server死锁跟踪常用方法
Hehuyi_In的博客
09-20 7744
一、利用windows计数器 下面的查询提供了自从上次重启以来在本服务器上发生的所有死锁： SELECT cntr_value AS NumOfDeadLocks FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadl...
用 SqlProfiler 跟踪超时的语句
我想我是海 冬天的大海 心情随风轻摆
02-20 3320
超时的语句， 直接用脚本无法查询其执行情况， 只能用 sqlprofiler 来跟踪了。 测试程序： using System; using System.Data.SqlClient; namespace ConsoleApplication1 { class Program { static void Main(string[] args)
可以统计一下SQL server的查询语句吗？
最新发布
06-11
5. 分析查询语句：当跟踪结束后，可以在 SQL Server Profiler 工具中查看捕获到的查询语句，包括查询的执行时间、执行计划、IO 操作等信息，以便进行统计和分析。 需要注意的是，SQL Server Profiler 工具会对 SQL ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值