SQL Server 死鎖/封鎖快速診斷腳本：找出阻塞鏈與執行語句

症狀

系統出現死鎖或嚴重封鎖，單純 KILL 進程治標不治本，需找出根因 SQL 陳述式。

診斷腳本 (在 master 執行)

USE master
GO

DECLARE @spid INT, @bl INT

DECLARE s_cur CURSOR FOR
    SELECT 0, blocked
    FROM (SELECT * FROM sysprocesses WHERE blocked > 0) a
    WHERE NOT EXISTS (
        SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) b
        WHERE a.blocked = b.spid
    )
    UNION
    SELECT spid, blocked FROM sysprocesses WHERE blocked > 0

OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid, @bl

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @spid = 0
        PRINT '引發封鎖鏈首的 SPID: ' + CAST(@bl AS VARCHAR(10)) + '，其執行 SQL 如下：'
    ELSE
        PRINT 'SPID ' + CAST(@spid AS VARCHAR(10)) + ' 被 SPID ' + CAST(@bl AS VARCHAR(10)) + ' 封鎖，當前 SQL 如下：'

    DBCC INPUTBUFFER(@bl)  -- 顯示該 SPID 最後送入的批次
    FETCH NEXT FROM s_cur INTO @spid, @bl
END

CLOSE s_cur
DEALLOCATE s_cur

EXEC sp_who2  -- 輔助檢視所有進程狀態

輸出範例解讀

引發封鎖鏈首的 SPID: 71，其執行 SQL 如下：
EventType       Parameters EventInfo
Language Event  0          BEGIN TRAN
                            SELECT * FROM test WITH (HOLDLOCK)
                            WAITFOR TIME '12:00'
                            SELECT * FROM test
                            COMMIT

SPID 64 被 SPID 71 封鎖，當前 SQL 如下：
Language Event  0          SELECT * FROM test
                            INSERT test VALUES (1,2)

SPID 65 被 SPID 64 封鎖...

可見 SPID 71 開啟交易並加 HOLDLOCK 長時間持有鎖，導致後續連鎖封鎖。

後續分析步驟

1. 以 SPID 找出對應應用程式/模組（可結合 sp_who2 的 ProgramName, HostName, LoginName）
2. 檢視該程式碼：是否必要使用 HOLDLOCK？交易是否過長？索引是否缺失導致掃描鎖升級？
3. 優化：縮短交易範圍、移除不必要的 HOLDLOCK、補上適當索引、調整隔離等級