写在前面的话：

该文章内容是参考  

https://www.cnblogs.com/gered/p/10998090.html

作者只是在该文章的基础上增加了，在实践的过程中，遇到的一些细节；

在此特别感谢作者郭大侠1的无私分享

前言 

如果缺省跟踪信息打开，它将在SQL Server启动时自动记录SQL Server启动信息。该功能犹如飞机的黑匣子，会记录一个SQL Server实例配置信息的所有改动。查看该信息，可以看出故障时数据库进行了什么样的改动。缺省跟踪信息和ERRORLOG存储在相同的日志文件夹中，其名称如“log_xxx.trc”，此处的xxx是一组序列号。你可以使用Profiler打开这些文件来查看事件记录，也可以使用函数 "fn_trace_gettable"来处理这些文件。

我们的SQL Server数据库中“跟踪”也是无处不在的，如果我们利用好了跟踪技巧，就可以针对某些特定的场景做定向分析，找出充足的证据来破案。

简单的举几个应用场景：

在线生产库为何突然宕机？

数百张数据表为何不翼而飞？

刚打好补丁的系统为何屡遭黑手？

新添加的信息表为何频频丢失？

某张表字段的突然更改，究竟为何人所为？

这些个匿名的访问背后，究竟是人是鬼？

突然增加的增量数据，究竟是对是错？

数百兆的日志爆炸式的增长背后又隐藏着什么？

这一且的背后，是应用程序的BUG还是用户品质的缺失？

我相信如用过SQL Server数据库的人，都会或多或少的利用过SQL Profiler工具。这个玩意就是利用SQL Trace形成的一个图形化操作工具。
 

一、阻塞blocked process threshold 介绍

blocked process threshold 服务器配置选项 - SQL Server 2014 | Microsoft Learn

1.1、介绍

blocked process threshold 选项用于指定阈值（以秒为单位），超过该阈值将生成阻塞的进程报告。 该阈值可介于 0 到 86,400 之间。 默认情况下，不生成阻塞的进程报告。 对于系统任务或正在等待未生成可检测死锁的资源的任务，不生成该事件。

可以定义一个生成该事件时执行的 警报 。 例如，可以选择通知管理员采取相应的操作来处理阻塞情况。

阻塞的进程阈值使用死锁监视器后台线程监视等待时间大于（或数倍于）配置的阈值的任务列表。 每个报告间隔中，为每个阻塞的任务生成一次事件。

已通过最大努力完成了阻塞的进程报告。 不保证报表的数据始终为实时数据，也不保证报表数据接近实时。

该设置立即生效，无需停止并重新启动服务器。

1.2、画重点

默认情况下，不生成阻塞的进程报告。

默认情况下，不生成阻塞的进程报告。

默认情况下，不生成阻塞的进程报告。

该设置立即生效，无需停止并重新启动服务器。

该设置立即生效，无需停止并重新启动服务器。

该设置立即生效，无需停止并重新启动服务器。

二、查看阻塞是否开启

EXEC sp_configure

运行sql 后找到name为【blocked process threshold (s)】的行，

如果你没有找到，则运行下面语句，来打开高级选型

EXEC sp_configure 'show advanced options',1 --显示高级选型
RECONFIGURE WITH OVERRIDE;
GO

下图为没有开启，config_value的值为0；run_value的值为0；

下图为已开启，config_value的值为1；run_value的值为1；

值为1的意思 阻塞的阈值为1秒，只要阻塞1秒以上就会被记录

三、开启阻塞跟踪，并设置为大于等于2秒时跟踪

3.1、显示高级选项

EXEC sp_configure 'show advanced options',1 --显示高级选型
RECONFIGURE WITH OVERRIDE;
GO

3.2、设置阈值为2秒

exec sp_configure  'blocked process threshold (s)',2  --设置阈值为2秒
RECONFIGURE WITH OVERRIDE;
GO

3.3、关闭高级选项

EXEC sp_configure 'show advanced options',0 --关闭高级选型
RECONFIGURE WITH OVERRIDE;
GO

3.4、三个步骤可以连在一起运行

 --//【设置阻塞阈值-start】///--EXEC sp_configure 'show advanced options',1 --显示高级选型
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure  'blocked process threshold (s)',2  --设置阈值为2秒
RECONFIGURE WITH OVERRIDE;
GOEXEC sp_configure 'show advanced options',0 --关闭高级选型
RECONFIGURE WITH OVERRIDE;
GO--//【设置阻塞阈值-end】///--

四、模拟阻塞

4.1、首先打开profiler 

 4.2、连接数据库

 4.3、新建跟踪

 我这里名称写的是【模拟阻塞2023-3-22】

 使用模版：选择空白

然后点击左上角的【事件选择】

勾选【显示所有事件】和【显示所有列】

找到【Errors and Warnings事件】

Errors and Warnings事件介绍：

 包括返回 SQL Server 错误或警告时生成的事件类；例如，在编译存储过程时出错，或者 SQL Server 中出现异常。

 点击名称左边+号打开组

找到【Blocked process report】勾选

Blocked process report介绍：

指示某个任务已被阻塞，导致超过指定的时间。此事件类不包括系统任务和正在等待未发现死锁的资源的任务。若要配置报告的频率，请使用 sp_configure 命令配置阻塞的进程阈值选项。

 然后点击【运行】

4.4、运行阻塞代码

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

--//【阻塞测试-start】///--
--[查询窗口1],更新一行记录,等待20秒钟
use DBTase
BEGIN TRANUPDATE dbo.user2 SET name='堵塞跟踪1'WAITFOR DELAY '00:00:20'
COMMIT TRAN--同时再打开[查询窗口2],更新同一行记录(将被堵塞)
UPDATE dbo.user2 SET name='堵塞跟踪2'
--//【阻塞测试-end】///--

1.需要分成2个窗体运行

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

4.4.2、跟踪阻塞结果如下

 4.4.3、查看XML格式的堵塞信息。

 块是被堵塞的信息，

 为堵塞其他进程的信息。

use DBTase
UPDATE dbo.user2 SET name='堵塞跟踪2'   
use DBTase
BEGIN TRANUPDATE dbo.user2 SET name='堵塞跟踪1'WAITFOR DELAY '00:00:20'
COMMIT TRAN   

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

五、使用t-sql构建阻塞跟踪

5.1、在当前 profiler 设置中导出SQL 脚本

文件》导出》编写跟踪定义的脚本》用于SQL Server 2005-2008 R2

 5.2、保存文件后，再打开文件，内容如下

/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 2023/03/22  19:59:42         */
/****************************************************/-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 -- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network shareexec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
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, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 26, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @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 finisherror: 
select ErrorCode=@rcfinish: 
go

5.3、我们需要对生成的代码做一些修改

/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 2023/03/22  19:59:42         */
/****************************************************/-- Create a Queue
--declare @rc int
--declare @TraceID int
--declare @maxfilesize bigint
--set @maxfilesize = 5 
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 @stoptime datetime    --停止跟踪的日期和时间,为NULL则表示一直跟踪
declare @filecount int    --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效set @options = 2
set @tracefile = N'D:\MSsqlServerTraceLog\BlockedLog'
set @maxfilesize = 50
set @stoptime = DATEADD(D,10,GETDATE()) --跟踪10天
set @filecount = 5-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share--exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@stoptime,@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, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 26, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @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 finisherror: 
select ErrorCode=@rcfinish: 
go

注意：文件路径

D:\MSsqlServerTraceLog\BlockedLog

1.在D盘你必须保证，D盘下有【MSsqlServerTraceLog】目录

2.而【BlockedLog】则是生成的文件名

 options： 

tracefile：

跟踪将写入的位置和文件名

maxfilesize：

指定跟踪文件可以增长到的最大文件大小 (MB)

stoptime：

指定停止跟踪的日期和时间，默认值为 NULL。如果为 NULL，该跟踪将一直运行，直到它被手动停止或服务器关闭。

5.4、运行代码生成文件

运行上面的代码后，在D盘的【MSsqlServerTraceLog】文件夹下会生成【BlockedLog.trc】文件

 5.5、查看生成的文件有2种方法

新生成的文件是没有记录的，你需要运行下《4.4、运行阻塞代码》的代码

5.5.1、双击打开

双击后，文件会在profiler里打开

5.5.2、sql命令查看

【第一步】查看日志路径

--【第一步】查看日志配置
--*注意：为了保证查看日志路径有效，先执行下面的SQL，查看日志路径
select * from sys.traces

 【第二步】查看日志信息

SELECT  *
FROM fn_trace_gettable('D:\MSsqlServerTraceLog\BlockedLog.trc', DEFAULT)
order by StartTime desc

运行结果如图 

 5.5.3、sql命令的【XMLTextData】查看

select ServerName,EventClass,StartTime,TextData,CONVERT(XML,TextData) XMLTextData
from ::fn_trace_gettable('D:\5TraceLog\BlockedLog.trc',default)

* 注意文件路径

 XMLTextData 的字段是可以直接点击打开的

5.6、结果字段（列）说明

六、停止、启用和删除跟踪

sp_trace_setstatus (Transact-SQL)

6.1、语法

sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status  

6.2、参数

[ @traceid = ] trace_id 要修改的跟踪的 ID。 trace_id 为 int，无默认值。 用户使用此 trace_id 值来标识、修改和控制跟踪。 有关检索 trace_id的信息，请参阅 sys.fn_trace_getinfo (Transact-SQL) 。

[ @status = ] status 指定要在跟踪上实现的操作。 status 为 int，无默认值。

下表列出了可以指定的状态。

 备注

在关闭跟踪前首先必须先停止它。 在查看跟踪前首先必须先停止并关闭它。

6.3、查看当前跟踪任务ID

--查看全部跟踪任务select * from ::fn_trace_getinfo(0)

 如上图，我在结果里看到了我的阻塞文件名字【BlockedLog.trc】，所以，我的阻塞根据ID 是2

即traceid=2

6.4、完整的代码

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    --删除跟踪

七、注意事项

这里跟踪的只是堵塞，如果一个语句执行30秒，虽然执行时间较长，但是没有堵塞其他语句，也是跟踪不到的。所以有的慢查询是会漏掉的。如果设置5秒捕获堵塞，同一个查询如果堵塞了10秒，将会跟踪到2次相同的信息，也就是每5秒捕获一次。

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

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

八、如何重启实例后自动启动？

这是一个很严重的问题

注意，跟踪重启实例后会关闭，

简单理解，重启电脑（服务器）后关闭，跟踪任务

8.1、解决方案

注意，跟踪重启实例后会关闭，可以把上面的代码运行后再生成一个存储过程，然后运行开机自启 

第一步：在文件顶部增加创建存储过程的代码

CREATE PROCEDURE CPLVFX_BlockedLogTrace
AS

我的存储过程的名字叫 【CPLVFX_BlockedLogTrace】

 第二步：在文件底部增加开机自启代码

use master
EXEC sp_procoption'CPLVFX_BlockedLogTrace','STARTUP','ON'
print 'ok'
GO

8.2、开机自启sp_procoption介绍

8.2.1、介绍

设置或清除自动执行的存储过程。 每次启动 SQL Server 实例时，设置为自动执行的存储过程都会运行。

8.2.2、语法

sp_procoption [ @ProcName = ] 'procedure'   , [ @OptionName = ] 'option'   , [ @OptionValue = ] 'value'

8.2.3、参数

[ @ProcName = ] 'procedure' 要为其设置选项的过程的名称。 过程 为 nvarchar (776) ，没有默认值。

[ @OptionName = ] 'option' 要设置的选项的名称。 选项的唯一值是“启动”。

[ @OptionValue = ] 'value' 是将选项设置为 (true ，还是在) 或 关闭 (false 或 关闭) 。 值为varchar (12) ，没有默认值。

8.2.4、返回代码值

0（成功）或错误号（失败）

8.2.5、备注

启动过程必须位于 master 数据库中，不能包含 INPUT 或 OUTPUT 参数。 所有数据库恢复后将开始执行存储过程，并在开始时记录“恢复已完成”消息。

8.2.6、权限

要求具有 sysadmin 固定服务器角色的成员身份。

执行权限默认授予 sysadmin 固定服务器角色的成员。启动过程必须属于 master 数据库的所有者。

8.3、完整示例代码

/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 2023/03/21  18:18:18         */
/****************************************************/--use master--创建存储过程
CREATE PROCEDURE CPLVFX_BlockedLogTrace
AS-- 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 @stoptime datetime    --停止跟踪的日期和时间,为NULL则表示一直跟踪
declare @filecount int    --跟踪文件的数量,其值大于1,TRACE_FILE_ROLLOVER=2 时有效set @options = 2
set @tracefile = N'D:\MSsqlServerTraceLog\BlockedLog'
set @maxfilesize = 50
set @stoptime = DATEADD(D,10,GETDATE()) --跟踪10天
set @filecount = 5-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share--exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
exec @rc = sp_trace_Create @TraceID output,@options,@tracefile,@maxfilesize,@stoptime,@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, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 26, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @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 finisherror: 
select ErrorCode=@rcfinish: 
go--开机启动
use master
EXEC sp_procoption'CPLVFX_BlockedLogTrace','STARTUP','ON'
print 'ok'
GO

九、延伸阅读

sql性能优化：MS-SQL跟踪信息查看select * from ::fn_trace_gettable

sql性能优化：MS-SQL跟踪信息查看select * from ::fn_trace_gettable_橙-极纪元的博客-CSDN博客

SQL Server跟踪（Trace)--系统跟踪日志；从小白到大神，文章细节满满，细致到令人发指。

SQL Server跟踪（Trace)--系统跟踪日志；从小白到大神，文章细节满满，细致到令人发指。_橙-极纪元的博客-CSDN博客