如何监控和解决SQL Server的阻塞(2)（事件通知）

最新推荐文章于 2025-06-18 14:20:40 发布

原创

最新推荐文章于 2025-06-18 14:20:40 发布 · 2.9k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #数据 #server #数据库 #database

本文详述了如何监控SQL Server的阻塞问题，重点在于设定阻塞的厥值，并通过服务器配置选项blocked process threshold进行设置。当阻塞超过预设阈值时，通过事件通知发送包含阻塞信息的邮件给DBA。步骤包括创建事件通知、阻塞表、存储过程以及处理阻塞的队列。

如何监控和解决SQL Server的阻塞(1) : 点击打开链接
如何监控和解决SQL Server的阻塞(3)（扩展事件）点击打开链接

上篇介绍了什么是阻赛,以及如何察看"当前"的阻塞现象. 这篇我们详细谈一下如何去监控阻塞.

1. 什么是阻塞的厥值

随着系统用户数的增加,数据库访问连接也不断增加,数据库上可能的阻塞可能性也随之增加。　对于DBA来说，我们更加关注正在发生的阻塞是什么类型的，这个阻塞在什么情况下是不可以被企业用户所接受的。例如一个在线交易系统，用户在付款的时候被阻塞了５秒钟，那么５秒钟是不能被用户所接受的。而对于一个报表系统，５秒钟不算什么，可能阻塞事件超过３分钟，那么用户才会开始抱怨。这个用户忍耐的时间，我们称为厥值(Threshold).

2. 监控阻塞的厥值

2.1 监控的目的

当某个阻塞超过假定的厥值后，DBA收到通知。

2.2 如何设定阻塞的厥值

SQL 提供了一个服务器配置选项　blocked process threshold ，该选项用于指定阈值（以秒为单位），超过该阈值

最低0.47元/天解锁文章

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

HeavenInShanghai

关注关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

SQL Server跟踪（Trace)--阻塞跟踪日志

橙-极纪元-JJYCheng

03-22

1173

SQL Server跟踪（Trace)--阻塞跟踪日志

SQLServer 的事件通知

Lion_Long的博客

04-08

1846

应为将消息发送到远程服务器上的服务代理的事件通知配置服务代理对话框安全性。必须根据完整安全模型手动配置对话框安全性。服务代理路由确定接收通知消息的服务。如果事件通知以远程服务器上的服务为目标，则源服务器和目标服务器都必须在其上定义路由，以确保发生双向通信。

参与评论您还未登录，请先登录后发表或查看评论

SQLServer进程阻塞的检查和解决办法

10-23

SQLServer进程阻塞的检查和解决办法，有具体的代码，使用方便~

SQL Server2008计划任务及邮件通知

10-22

以图片形式，说明如何在SQLServer2008下创建计划任务，并且配置计划任务，使之在执行成功后，自动发送邮件给指定的管理员。

SqlServer2008实例20阻塞之找到并解决阻塞进程

复行数十步

07-12

821

当一个数据库会话中的事务正在锁定一个或多个其他会话事务想要读取或者修改的资源时，会产生阻塞。通常短时间的阻塞没有问题，且是较忙的应用程序所需要的。然而，设计得比较糟糕的应用程序会导致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话读取和更新它们。在SQL Server中，一个阻塞的进程会无限期地保持阻塞，或者直到它超时(根据SET LOCK TIMEOUT)、服务器关闭、进程被杀死、连接完成了更新或者其他发生在原始事务上的操作导致它释放了资源上的锁。 ...

SQL Server 事件通知（Event notifications）

weixin_34127717的博客

12-13

372

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）基础知识（Rudimentary Knowledge）事件通知监控DDL（NotifyQueue_DDL）事件通知监控SQL跟踪事件（NotifyQueue_Trace）注意事项（Attention）疑问（Questions）参考文献（References） ...

微软工程师讲解SQL server阻塞

微软新技术

01-17

679

微软工程师讲解SQL server阻塞阻塞定义　　当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时，将发生阻塞。其结果是强制第二个连接等待，而在第一个连接上阻塞。不管是来自同一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另一个连接。　　说明：一些需要锁保护的操作可能不明显，例如系统目录表和索引上的锁。大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的

13、SQL Server 跟踪与事件通知：从 SQL Trace 到扩展事件

bert9linguist的博客

06-18

本文详细介绍了 SQL Server 中的跟踪、事件通知和扩展事件的相关知识。从 SQL Trace 的基本操作和扩展存储过程，到事件通知的原理和应用，再到扩展事件的架构和对象分析，逐步了解了 SQL Server 中不同的监控和调试手段。同时通过实际案例展示了如何利用扩展事件和事件通知实现高效的死锁监控，为数据库性能优化和故障排查提供了全面的解决方案。

sqlserver进程死锁关闭的方法

09-10

- 使用死锁探测和监控：SQL Server提供死锁图形日志（Deadlock Graphs）和死锁监控器（sys.dm_os_wait_stats），通过定期分析这些信息，可以发现可能导致死锁的模式并进行调整。 - 使用死锁超时设置：通过设置` ...

数据库阻塞监控工具（SQLSERVER和ORACLE）

07-28

MSSBlockingMonitor是一款专为SQL Server设计的阻塞监控实用程序，它可以持续监控数据库阻塞情况，提供详细的阻塞链路报告，帮助定位并解决阻塞问题。使用MSSBlockingMonitor，你可以获取关于阻塞的详细信息，如...

2012 使用XEvent sqlserver.blocked_process_report检测阻塞

weixin_33812433的博客

02-25

193

网上看到的一篇文章来追踪阻塞的，非常简便而且性能很好，跟大家分享。原文地址：AnXEventaDay(21of31)–TheFuture–TrackingBlockinginDenali在2005新增的BlockedProcessReporttrace事件是我最喜欢的功能之一，当进程被阻塞超过用户允许的阻塞时间后会自动产生XML的报表。我曾经2年...

Blocked Process Report

weixin_34403693的博客

06-23

347

当同个对象上有互斥的锁存在时，查询需要等待很长时间，我们是否可以收到来自SQL Server提醒？答案是可以的，做法非常简单，因为SQL Server为你提供了称为Blocked Process Report 的功能。很可惜这个功能默认是未启用的。因此这篇文章我想给你讲解下Blocked Process Report 的大致用法，当SQL Server出现锁时，你可以用它来做的进一步的故障排除。 ...

SQL Server 查看进程阻塞及处理

feritylamb的博客

02-05

3827

SELECT '进程号SPID：[' + CAST(@spid AS VARCHAR(10)) + ']被' + ' 进程号SPID：[' + CAST(@bl AS VARCHAR(10)) + ']阻塞,其当前进程执行的SQL语法如下';SELECT '进程号SPID:[' + CAST(@bl AS VARCHAR(10)) + ']引起死锁,其执行的SQL语法如下';--首先，查看进程，查看否存在阻塞进程，blocked>0都是当前被阻塞的进程。--查看此进程执行的SQL 语句。

SQL Server进程阻塞的检查和解决办法

lhsxsh的专栏

03-19

1253

create procedure sp_who_lockasbegindeclare @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter intcreate table #tmp_lock_who (id int identity(1,1),sp

SQL Server阻塞的检查

weixin_30267691的博客

05-08

261

1. 阻塞除了内存、CPU、I/O这些系统资源以外，阻塞和死锁是影响数据库应用性能的另一大因素。所谓的「阻塞」，是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待，直到原来的事务释放相关的锁，或直到它超时、服务器关闭、进程被杀死。一般的系统中，偶尔有短时间的阻...

SQLServer2005所提供的主动通知

ciwan8376的博客

09-06

293

在数据库内存放的数据的使用模式有许多是变动少而查询多的。据统计，数据库中一般有 20% 以上的数据属于此类型，例如公司的产品、人事数据，以及用作参照的数据表（lookup table），例如提供地址输入的可选择的国家、城市，人事...

python 接收sqlserver 数据表变更通知

zengliguang的专栏

05-01

650

确保你的消息队列服务已经正确配置并运行，且SQL Server与Python应用之间的网络连接畅通无阻。这样，每当SQL Server中的数据表发生变更时，Python应用就能即时收到通知并作出响应。为了实时地从Python应用接收通知，可以配置触发器将变更事件发送到一个消息队列服务，如RabbitMQ、Kafka或Azure Service Bus等。），这些触发器在数据发生变化时会被激活。触发器可以执行一些操作，比如将变更信息写入另一个日志表，或者通过服务 broker 发送消息到外部系统。

sqlserver 通过作业警报监控堵塞

羽之大公公的博客

07-10

862

1.创建阻塞日志表，用于记录阻塞情况 2.新建作业，用于将阻塞情况记录到阻塞日志表中，并发送邮件（如果没有配置邮件，或者不需要发送邮件，可以忽略此步骤） 3.创建警报，当阻塞大于阈值时，触发上面作业查询堵塞sql: WITH sess AS ( SELECT DATEDIFF(S,er.start_time,GETDATE()) as duration, ...

如何在sqlserver 中自动监控死锁进程和sql

最新发布

12-05

<think> 我们被要求提供自动监控SQL Server中死锁进程和SQL的方法。根据之前的引用和回答，我们可以总结出几种自动监控死锁的方法，包括使用扩展事件、SQL Server代理作业、系统健康会话以及自定义监控存储过程等。 ### 方法1：使用扩展事件（Extended Events）自动捕获死锁扩展事件是SQL Server中用于性能监控的轻量级系统。我们可以创建一个扩展事件会话来自动捕获死锁信息，并将结果保存到文件中，然后定期分析这些文件。 #### 步骤： 1. **创建扩展事件会话**：使用以下脚本创建一个扩展事件会话，用于捕获死锁图（deadlock graph）： ```sql CREATE EVENT SESSION [DeadlockMonitor] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename = N'C:\Monitor\DeadlockMonitor.xel') -- 指定文件路径 WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON); GO ``` 注意：需要确保指定的文件路径存在，并且SQL Server服务账户有写入权限。 2. **启动会话**： ```sql ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START; GO ``` 3. **配置SQL Server代理作业定期分析扩展事件文件**：可以创建一个SQL Server代理作业，定期执行一个存储过程来读取扩展事件文件，解析死锁信息并存储到表中。 - 首先，创建一个表来存储死锁信息： ```sql CREATE TABLE [dbo].[DeadlockEvents]( [DeadlockID] [int] IDENTITY(1,1) NOT NULL, [EventTime] [datetime] NOT NULL, [DeadlockGraph] [xml] NOT NULL, CONSTRAINT [PK_DeadlockEvents] PRIMARY KEY CLUSTERED ([DeadlockID] ASC) ); ``` - 然后，创建存储过程来读取扩展事件文件并插入到表中： ```sql CREATE PROCEDURE [dbo].[usp_CollectDeadlockEvents] AS BEGIN INSERT INTO [dbo].[DeadlockEvents] ([EventTime], [DeadlockGraph]) SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), [event_data].[EventTime]) AS [EventTime], [event_data].[DeadlockGraph] FROM (SELECT n.value('(@timestamp)[1]', 'datetime') AS [EventTime], n.query('.') AS [DeadlockGraph] FROM (SELECT CAST(event_data AS XML) AS [EventData] FROM sys.fn_xe_file_target_read_file('C:\Monitor\DeadlockMonitor*.xel', NULL, NULL, NULL)) AS [EventTable] CROSS APPLY [EventData].[nodes]('//event') AS [q](n) WHERE n.value('(@name)[1]', 'varchar(50)') = 'xml_deadlock_report') AS [event_data]; END ``` - 创建SQL Server代理作业，每天执行一次或多次此存储过程。 ### 方法2：使用系统健康会话（system_health） SQL Server 2008及更高版本内置了一个扩展事件会话`system_health`，它默认会捕获死锁信息。我们可以定期从该系统会话中提取死锁信息。 #### 步骤： 1. **查询system_health会话中的死锁事件**：以下查询可以提取死锁图： ```sql SELECT [DeadlockGraph] = XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') FROM (SELECT CAST([target_data] AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address] WHERE s.[name] = N'system_health' AND st.[target_name] = N'ring_buffer') AS Data CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent); ``` 2. **将查询结果保存到表**：同样可以创建代理作业，定期运行上述查询（或修改为插入表）来收集死锁信息。 ### 方法3：使用SQL Server代理警报可以配置SQL Server代理警报来响应死锁事件。 #### 步骤： 1. **启用死锁跟踪标志**： ```sql DBCC TRACEON(1222, -1); -- 将死锁信息写入错误日志 ``` 2. **配置SQL Server代理警报**： - 在SQL Server代理中创建新的警报。 - 设置警报类型为“SQL Server性能条件警报”，计数器为`Number of Deadlocks/sec`，当值大于0时触发。 - 或者在“错误号”选项中指定错误号1205（死锁牺牲品的错误号）。 3. **为警报添加操作**：可以配置警报触发时执行的操作，例如发送电子邮件通知，或者执行一个作业来收集当前死锁信息。 ### 方法4：使用自定义存储过程监控参考引用[4]，创建一个存储过程来自动收集死锁信息，并定期运行。 ### 总结以上方法都可以实现自动监控死锁。推荐使用扩展事件（方法1）或系统健康会话（方法2）来捕获死锁信息，并结合SQL Server代理作业定期收集数据。这样，我们可以将死锁信息持久化到表中，便于后续分析。 ### 注意事项 - 自动监控死锁可能会产生大量数据，需要定期清理。 - 在生产环境中部署前，请先测试，避免影响性能。 ### 相关问题 1. 如何解析死锁图（Deadlock Graph）中的信息？ 2. 如何设置死锁的自动通知（例如邮件通知）？ 3. 如何减少SQL Server中的死锁发生？ 4. 如何根据收集的死锁信息进行性能优化？ [^1]: 使用扩展事件监控死锁。 [^2]: 利用系统健康会话收集死锁信息。 [^3]: 配置SQL Server代理警报响应死锁。 [^4]: 通过自定义存储过程自动收集死锁信息。 </think>在 SQL Server 中实现自动监控死锁进程和 SQL 语句，推荐以下高效可靠的解决方案： --- ### 一、使用扩展事件 (Extended Events) 自动捕获死锁 **原理**：轻量级监控，捕获死锁图形和完整 SQL 语句 **步骤**： 1. **创建扩展事件会话** ```sql CREATE EVENT SESSION [DeadlockMonitor] ON SERVER ADD EVENT sqlserver.xml_deadlock_report -- 捕获死锁图 ADD TARGET package0.event_file( SET filename = N'C:\Monitor\DeadlockMonitor.xel', -- 保存路径 max_file_size = 10 -- 文件大小(MB) ) WITH (STARTUP_STATE = ON); -- 服务启动时自动运行 GO ``` **关键参数**： - `xml_deadlock_report`：捕获死锁的 XML 格式报告 - `event_file`：保存到文件便于后续分析 2. **启动会话** ```sql ALTER EVENT SESSION [DeadlockMonitor] ON SERVER STATE = START; ``` 3. **定期解析死锁信息** 创建存储过程解析 .xel 文件： ```sql CREATE PROCEDURE usp_AnalyzeDeadlocks AS BEGIN SELECT event_data.value('(event/@timestamp)[1]','datetime') AS [Time], event_data.value('(event/data/value/deadlock/victim-process/@id)[1]','varchar(10)') AS VictimPID, event_data.query('(event/data/value/deadlock/process-list/process)') AS ProcessDetails -- 包含SQL语句 INTO DeadlockHistory FROM (SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file('C:\Monitor\DeadlockMonitor*.xel', NULL, NULL, NULL) ) AS EventTable; END ``` **输出字段**： - `ProcessDetails`：包含死锁进程的完整 SQL 语句 - `VictimPID`：被牺牲的进程 ID 4. **配置 SQL Server 代理作业** - 定时执行存储过程（如每 15 分钟） - 添加邮件通知：死锁发生时自动发送警报[^1] --- ### 二、利用系统健康会话 (system_health) 自动监控 **原理**：使用内置会话捕获死锁（SQL Server 2008+ 默认启用） **查询死锁记录**： ```sql SELECT xed.value('@timestamp', 'datetime') AS [Time], xed.query('.') AS DeadlockGraph -- 完整死锁图 FROM ( SELECT CAST([target_data] AS XML) AS TargetData FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'system_health' ) AS Data CROSS APPLY TargetData.nodes('//event[@name="xml_deadlock_report"]') AS XEventData (xed) ORDER BY [Time] DESC; ``` **优势**： - 零配置，开箱即用 - 自动保留最近死锁记录（默认 5 个） --- ### 三、自定义死锁监控存储过程 **参考引用[4]创建自动化脚本**： ```sql CREATE PROCEDURE usp_AutoDeadlockMonitor @MinutesBack INT = 60 -- 监控最近N分钟 AS BEGIN SELECT dl.deadlock_id, dl.event_date, victim_process = dl.victim_process_id, blocking_process = dl.blocking_process_id, sql_text = t.text FROM ( -- 死锁基础信息 SELECT deadlock_id = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY event_time)), event_time, victim_process_id = victim_xml.value('@id', 'VARCHAR(10)'), blocking_process_id = blocking_xml.value('@id', 'VARCHAR(10)') FROM sys.dm_xe_session_targets st CROSS APPLY (SELECT CAST(st.target_data AS XML) ) AS x CROSS APPLY x.target_data.nodes('//event') AS e(event_xml) CROSS APPLY event_xml.nodes('//victim-process') AS v(victim_xml) CROSS APPLY event_xml.nodes('//blocking-process') AS b(blocking_xml) WHERE st.target_name = 'event_file' AND event_xml.value('@name','VARCHAR(50)') = 'xml_deadlock_report' AND event_time > DATEADD(MINUTE, -@MinutesBack, GETDATE()) ) dl CROSS APPLY sys.dm_exec_sql_text( CONVERT(VARBINARY(64), '0x' + blocking_xml.value('(./@sqlhandle)[1]','VARCHAR(200)'), 1) ) t; END ``` **执行**： ```sql EXEC usp_AutoDeadlockMonitor @MinutesBack = 30; -- 检查最近30分钟 ``` --- ### 四、死锁自动处理策略 1. **自动终止低优先级进程** ```sql ALTER DATABASE [YourDB] SET DEADLOCK_PRIORITY LOW; -- 指定库级别优先级 ``` 2. **代理作业自动终止阻塞源** ```sql CREATE PROCEDURE usp_KillDeadlockSource AS BEGIN DECLARE @BlockingSPID INT; SELECT TOP 1 @BlockingSPID = blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; IF @BlockingSPID IS NOT NULL EXEC('KILL ' + @BlockingSPID); END ``` --- ### 五、最佳实践建议 1. **监控部署流程**： ```mermaid graph TD A[创建扩展事件会话] --> B[配置SQL代理定时作业] B --> C[解析死锁XML存入历史表] C --> D[设置邮件警报] ``` 2. **关键指标**： - 死锁频率（次/小时） - 平均死锁持续时间（ms） - 高频死锁对象（表/索引） 3. **优化方向**： - 为高频死锁查询添加 `NOLOCK` 提示（需评估一致性风险） - 优化索引减少锁竞争 - 使用 `READ COMMITTED SNAPSHOT` 隔离级别[^3] --- ### 总结通过扩展事件 + SQL Server 代理 + 自定义存储过程的组合方案，可实现： ✅ 自动捕获死锁图形 ✅ 记录完整 SQL 语句 ✅ 历史数据分析 ✅ 实时邮件警报 ✅ 自动终止进程（可选） > ⚠️ 生产环境建议先测试监控脚本的性能影响[^2] --- ### 相关问题 1. 如何解析死锁图（Deadlock Graph）中的关键信息？ 2. 如何配置死锁发生时的自动邮件通知？ 3. 哪些索引优化策略可减少死锁？ 4. 如何区分死锁与普通阻塞？ [^1]: 使用扩展事件实现自动化死锁监控。 [^2]: 通过SQL Server代理定期执行监控脚本。 [^3]: 修改隔离级别降低死锁概率。