SQL Server 阻塞监控自动化解决方案

发表于 分类于 ，
通过自动化监控机制实现 SQL Server 阻塞问题的事后分析和追溯，解决 BI 系统响应缓慢和超时问题

问题现象

问题现象

BI 系统在获取数据时频繁出现以下问题：

  • 数据加载缓慢，转圈等待时间过长
  • 查询操作超时，无法正常返回结果
  • 使用传统方法 sp_who2 排查时，问题已消失

问题原因

传统的被动排查方法存在以下局限性：

时效性差：等待运维人员登录服务器时，阻塞可能已经结束，无法捕获现场。

缺乏历史数据：无法追溯问题发生的频率、规律和趋势。

无法量化影响：不清楚阻塞的持续时间、影响范围和业务影响程度。

简单来说，需要一个”监控摄像头”来持续记录数据库会话的阻塞状态，以便事后回放和分析。

解决方案

建立自动化监控机制：

  • 每分钟采集一次数据库会话阻塞状态
  • 保留最近 7 天的历史记录
  • 实现事后分析和问题追溯
  • 提供量化的阻塞统计数据

实施步骤

步骤 1：创建监控数据库

为避免与业务数据混合，创建独立的工具数据库：

1
CREATE DATABASE DBAUtility;

步骤 2：创建监控数据表

创建表 BlockingSnapshots，用于存储阻塞快照数据：

1
2
3
4
5
6
7
8
9
CREATE TABLE DBAUtility.dbo.BlockingSnapshots (
		SnapshotTime DATETIME NOT NULL,        -- 记录时间
		Spid INT NOT NULL,                     -- 被阻塞的会话 ID
		BlockingSpid INT NOT NULL,             -- 阻塞源会话 ID
		SqlText NVARCHAR(MAX),                 -- 被阻塞会话执行的 SQL
		BlockerSql NVARCHAR(MAX),              -- 阻塞源执行的 SQL
		WaitTimeMs BIGINT,                     -- 等待时长（毫秒）
		HasBlocking BIT DEFAULT 1              -- 是否存在阻塞
);

注意：实际使用时需根据需求添加索引以提升查询性能。

步骤 3：创建监控存储过程

步骤 3：创建监控存储过程

创建存储过程 CaptureBlockingSnapshot，实现以下功能：

  1. 查询系统 DMV 视图（sys.dm_exec_requests、sys.dm_exec_sessions 等），识别当前阻塞关系
  2. 追溯阻塞链，定位根源阻塞会话（Head Blocker）
  3. 获取阻塞源和被阻塞会话的 SQL 文本
  4. 将阻塞信息插入 BlockingSnapshots 表
  5. 若当前无阻塞，记录正常状态快照（便于对比分析）
  6. 自动清理 7 天前的历史数据

提示：完整的存储过程代码请参见文末的相关文件。

步骤 4：配置自动执行作业

步骤 4：配置自动执行作业

使用 SQL Server Agent 创建定时作业，每分钟执行一次 CaptureBlockingSnapshot 存储过程。

遇到的问题

作业创建后执行失败，错误信息如下：

1
无法找到登录名 'CLOUD-MC\Administrator'. [SQLSTATE 42000] (错误 18456)

原因分析：

根据测试发现，使用域账号作为作业所有者时，SQL Server Agent 在验证域账号时可能因为域控制器连接不稳定而失败。
解决方案：

将作业所有者更改为 sa 账户或其他本地 SQL 登录账户：

1
2
3
4
5
6
7
USE msdb;
GO

EXEC dbo.sp_update_job
		@job_name = N'Capture Blocking Snapshot',
		@owner_login_name = N'sa';
GO

步骤 5：验证监控运行状态

步骤 5：验证监控运行状态

等待几分钟后查询监控表，确认数据采集正常：

1
2
3
4
5
6
7
8
SELECT TOP 20
		SnapshotTime,
		HasBlocking,
		Spid,
		BlockingSpid,
		WaitTimeMs
FROM DBAUtility.dbo.BlockingSnapshots
ORDER BY SnapshotTime DESC;

预期结果：

  • 每分钟生成一条新记录
  • 无阻塞时 HasBlocking = 0
  • 有阻塞时 HasBlocking = 1 且包含详细的阻塞信息

使用方法

查询 1：最近阻塞时间点分布

查看最近 2 小时内发生阻塞的时间分布：

1
2
3
4
5
6
SELECT SnapshotTime, COUNT(*) AS 阻塞会话数
FROM DBAUtility.dbo.BlockingSnapshots
WHERE HasBlocking = 1
	AND SnapshotTime >= DATEADD(HOUR, -2, GETDATE())
GROUP BY SnapshotTime
ORDER BY SnapshotTime DESC;

查询 2：阻塞详情分析

查看最近 30 分钟内的阻塞详情，按等待时长排序：

1
2
3
4
5
6
7
8
9
10
SELECT
		SnapshotTime AS 记录时间,
		Spid AS 被阻塞会话,
		BlockingSpid AS 阻塞源会话,
		WaitTimeMs AS 等待时长_毫秒,
		BlockerSql AS 阻塞源SQL
FROM DBAUtility.dbo.BlockingSnapshots
WHERE HasBlocking = 1
	AND SnapshotTime >= DATEADD(MINUTE, -30, GETDATE())
ORDER BY WaitTimeMs DESC;

查询 3：阻塞频率统计

计算最近 1 小时内阻塞发生的频率：

1
2
3
4
5
6
7
SELECT
		COUNT(DISTINCT SnapshotTime) AS 总采样次数,
		COUNT(DISTINCT CASE WHEN HasBlocking = 1 THEN SnapshotTime END) AS 阻塞次数,
		CAST(COUNT(DISTINCT CASE WHEN HasBlocking = 1 THEN SnapshotTime END) * 100.0
				 / COUNT(DISTINCT SnapshotTime) AS DECIMAL(5,2)) AS 阻塞率_百分比
FROM DBAUtility.dbo.BlockingSnapshots
WHERE SnapshotTime >= DATEADD(HOUR, -1, GETDATE());

实际案例

问题场景

2025 年 11 月 17 日 1:30 ，用户反馈 BI 系统响应缓慢，报表无法正常加载。

排查过程

使用监控数据进行回溯分析：

1
2
3
4
5
6
SELECT SnapshotTime, COUNT(*) AS 阻塞会话数
FROM DBAUtility.dbo.BlockingSnapshots
WHERE HasBlocking = 1
	AND SnapshotTime BETWEEN '2025-11-17 01:20' AND '2025-11-17 01:40'
GROUP BY SnapshotTime
ORDER BY SnapshotTime;

方案优势

  • 被动变主动：无需等待问题复现，可随时回溯历史数据进行分析
  • 可量化分析：精确统计阻塞频率、持续时间和影响范围，提供数据支持
  • 精准定位：明确识别阻塞源 SQL 和阻塞链路，快速定位问题根因

注意事项

权限要求

执行账户需要具备以下权限：

1
GRANT VIEW SERVER STATE TO [YourMonitoringAccount];

作业所有者配置

建议使用 sa 或本地 SQL 账户作为作业所有者，避免域账号验证问题：

1
2
3
4
5
6
-- 查看当前作业所有者
SELECT name, owner_sid, SUSER_SNAME(owner_sid) AS owner_name
FROM msdb.dbo.sysjobs
WHERE name = 'Capture Blocking Snapshot';

-- 如需修改，使用上述 sp_update_job 语句

存储空间管理

监控数据会持续增长，建议：

  • 定期检查 DBAUtility 数据库大小
  • 根据实际情况调整保留天数（默认 7 天）
  • 如需长期保留，可归档至历史表或数据仓库

扩展优化

1. 阈值告警

当阻塞等待时间超过阈值时，通过 Database Mail 发送邮件通知：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 在存储过程中添加告警逻辑
IF EXISTS (
		SELECT 1 FROM DBAUtility.dbo.BlockingSnapshots
		WHERE SnapshotTime >= DATEADD(MINUTE, -5, GETDATE())
			AND WaitTimeMs > 60000  -- 超过 60 秒
)
BEGIN
		EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'DBA Alert',
				@recipients = 'dba@company.com',
				@subject = 'SQL Server Blocking Alert',
				@body = 'Detected long-running blocking...';
END

2. 热点对象分析

统计哪些数据库对象经常被阻塞，指导索引和查询优化：

1
2
3
4
5
6
7
8
9
SELECT
		OBJECT_NAME(resource_associated_entity_id, DB_ID()) AS 对象名称,
		COUNT(*) AS 阻塞次数
FROM DBAUtility.dbo.BlockingSnapshots bs
INNER JOIN sys.dm_tran_locks l ON bs.BlockingSpid = l.request_session_id
WHERE bs.SnapshotTime >= DATEADD(DAY, -7, GETDATE())
	AND l.resource_type = 'OBJECT'
GROUP BY resource_associated_entity_id
ORDER BY COUNT(*) DESC;

3. 可视化监控

可将监控数据接入 Grafana、Power BI 等平台，实现图表化展示：

  • 阻塞趋势图（时间序列）
  • 阻塞热力图（时段分布）
  • Top 阻塞源 SQL 排行

相关资源

完整的实现脚本和详细文档：

  • blocking_capture.sql - 包含建库、建表、存储过程的完整脚本
  • create_job.sql - SQL Server Agent 作业创建脚本
  • README.md - 详细的部署和使用说明文档