该阻塞排查Query是否适用？有无更优方案？（SPID动态变化场景）

一、原查询的适用性分析 #

原查询可用于基础阻塞排查，但存在明显局限：

• 依赖已过时的sys.sysprocesses视图（SQL Server 2005起已被动态管理视图替代，后续版本兼容性存疑）
• 仅抓取查询执行瞬间的SPID快照，在SPID持续变化的场景下，无法追踪完整阻塞链的生命周期，容易遗漏关键信息
• 临时表#T的快照机制，无法应对快速变化的进程状态，可能出现阻塞链断裂或信息不全的情况

二、针对SPID持续变化场景的优化方案 #

1. 使用现代动态管理视图（DMV）替代过时视图 #

改用sys.dm_exec_requests、sys.dm_exec_sessions和sys.dm_exec_sql_text组合，这些视图能实时反映当前请求状态，更适配动态场景：

SET NOCOUNT ON;

WITH BlockingChain AS (
    -- 定位阻塞源头（未被其他进程阻塞的进程）
    SELECT 
        r.session_id AS spid,
        r.blocking_session_id AS blocked,
        CAST(REPLICATE('0', 4 - LEN(CAST(r.session_id AS VARCHAR))) + CAST(r.session_id AS VARCHAR) AS VARCHAR(1000)) AS level,
        REPLACE(REPLACE(COALESCE(t.text, 'No SQL Text'), CHAR(10), ' '), CHAR(13), ' ') AS batch
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE r.blocking_session_id = 0
        AND EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WHERE r2.blocking_session_id = r.session_id)
    
    UNION ALL
    
    -- 递归获取被阻塞的后续进程
    SELECT 
        r.session_id AS spid,
        r.blocking_session_id AS blocked,
        CAST(bc.level + RIGHT(CAST((1000 + r.session_id) AS VARCHAR(100)), 4) AS VARCHAR(1000)) AS level,
        REPLACE(REPLACE(COALESCE(t.text, 'No SQL Text'), CHAR(10), ' '), CHAR(13), ' ') AS batch
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.spid
    WHERE r.blocking_session_id <> 0
)
SELECT 
    N'    ' + REPLICATE(N'|         ', LEN(level)/4 - 1) 
    + CASE WHEN (LEN(level)/4 - 1) = 0 THEN 'HEAD -  ' ELSE '|------  ' END
    + CAST(spid AS NVARCHAR(10)) + N' ' + batch AS blocking_tree
FROM BlockingChain 
ORDER BY level ASC;

2. 增加周期性采样捕捉动态变化 #

如果需要追踪SPID快速变化的阻塞链，可结合临时表进行周期性采样，留存历史状态：

SET NOCOUNT ON;

-- 创建临时表存储历史阻塞数据
CREATE TABLE #BlockingHistory (
    capture_time DATETIME DEFAULT GETDATE(),
    spid INT,
    blocked_spid INT,
    batch NVARCHAR(MAX),
    wait_type NVARCHAR(128),
    wait_time_ms INT
);

-- 周期性采样（示例：每2秒采样一次，共采样5次）
DECLARE @loop INT = 0;
WHILE @loop < 5
BEGIN
    INSERT INTO #BlockingHistory (spid, blocked_spid, batch, wait_type, wait_time_ms)
    SELECT 
        r.session_id,
        r.blocking_session_id,
        REPLACE(REPLACE(COALESCE(t.text, 'No SQL Text'), CHAR(10), ' '), CHAR(13), ' '),
        r.wait_type,
        r.wait_time
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE r.blocking_session_id <> 0 OR EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WHERE r2.blocking_session_id = r.session_id);
    
    WAITFOR DELAY '00:00:02';
    SET @loop = @loop + 1;
END

-- 查询历史阻塞链，按时间和阻塞关系排序
SELECT 
    capture_time,
    spid,
    blocked_spid,
    batch,
    wait_type,
    wait_time_ms
FROM #BlockingHistory
ORDER BY capture_time DESC, blocked_spid;

DROP TABLE #BlockingHistory;

3. 补充关键诊断字段 #

在查询中加入wait_type、wait_time_ms、transaction_isolation_level等字段，能更精准定位阻塞原因（如锁等待、IO等待或资源竞争）。

三、总结 #

原查询能完成基础阻塞链可视化，但不适用于SPID持续变化的动态场景。改用现代DMV并结合周期性采样的方案，能更好地追踪动态变化的阻塞状态，获取更完整的诊断信息。

内容的提问来源于stack exchange，提问作者Akshay P