SQL Server 死鎖快速排查神器：sp_who_lock 存儲過程完整版

SQL Server 死鎖與阻塞一鍵診斷方案

在高併發的 SQL Server 環境中，死鎖與阻塞是資料庫管理員（DBA）與開發人員的常態痛點。傳統做法需手動執行 sp_who、sp_who2、DBCC INPUTBUFFER 等多條指令，耗時且易漏診。本文提供一個生產級存儲過程 sp_who_lock，可自動識別死鎖鏈路、阻塞源頭，並直接輸出問題 SQL 文本，大幅縮短故障定位時間。

一、核心功能

• 自動偵測死鎖環：遞歸分析 sysprocesses 中的 blocked 鏈路，精準找出「持有鎖且被阻塞」的進程（死鎖發起者）。
• 區分死鎖與單向阻塞：若無環狀等待，則列出所有阻塞鏈路的 SPID 及其正在執行的 SQL。
• 即時輸出問題語句：自動調用 DBCC INPUTBUFFER 顯示阻塞雙方的最後一條 SQL 批次。
• 零依賴部署：僅依賴系統視圖 master..sysprocesses，SQL Server 2005 至 2022 皆可直接執行。

二、存儲過程完整代碼

USE master;
GO

IF OBJECT_ID('dbo.sp_who_lock', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_who_lock;
GO

CREATE PROCEDURE dbo.sp_who_lock
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @spid INT, @blk INT, @count INT, @index INT, @lock TINYINT;
    SET @lock = 0;

    -- 暫存表：收集所有被阻塞的 SPID 及其阻塞者
    CREATE TABLE #temp_who_lock (
        id INT IDENTITY(1,1) PRIMARY KEY,
        spid INT NOT NULL,
        blk INT NOT NULL
    );

    -- 1. 找出「被阻塞但不再阻塞他人」的進程（鏈路尾端）
    INSERT INTO #temp_who_lock (spid, blk)
    SELECT 0, blocked
    FROM (
        SELECT * FROM master..sysprocesses WHERE blocked > 0
    ) a
    WHERE NOT EXISTS (
        SELECT 1 FROM master..sysprocesses 
        WHERE a.blocked = spid AND blocked > 0
    );

    -- 2. 收集所有被阻塞進程
    INSERT INTO #temp_who_lock (spid, blk)
    SELECT spid, blocked
    FROM master..sysprocesses
    WHERE blocked > 0;

    SELECT @count = COUNT(*) FROM #temp_who_lock;
    IF @count = 0
    BEGIN
        PRINT '無阻塞與死鎖資訊';
        DROP TABLE #temp_who_lock;
        RETURN 0;
    END

    SET @index = 1;
    -- 3. 檢測是否存在環狀等待（死鎖）
    WHILE @index <= @count
    BEGIN
        IF EXISTS (
            SELECT 1 FROM #temp_who_lock a
            WHERE a.id > @index
              AND EXISTS (
                  SELECT 1 FROM #temp_who_lock 
                  WHERE id <= @index AND a.blk = spid
              )
        )
        BEGIN
            SET @lock = 1;
            SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index;
            PRINT '引起資料庫死鎖的是: ' + CAST(@spid AS VARCHAR(10)) + ' 進程號，其執行的 SQL 語法如下';
            SELECT @spid AS DeadlockSPID, @blk AS BlockedBySPID;
            DBCC INPUTBUFFER(@spid);
            DBCC INPUTBUFFER(@blk);
        END
        SET @index = @index + 1;
    END

    -- 4. 若無死鎖，輸出所有阻塞鏈路詳情
    IF @lock = 0
    BEGIN
        SET @index = 1;
        WHILE @index <= @count
        BEGIN
            SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index;
            IF @spid = 0
                PRINT '引起阻塞的是: ' + CAST(@blk AS VARCHAR(10)) + ' 進程號，其執行的 SQL 語法如下';
            ELSE
                PRINT '進程號 SPID: ' + CAST(@spid AS VARCHAR(10)) + 
                      ' 被進程號 SPID: ' + CAST(@blk AS VARCHAR(10)) + 
                      ' 阻塞，其當前執行的 SQL 語法如下';
            DBCC INPUTBUFFER(@spid);
            DBCC INPUTBUFFER(@blk);
            SET @index = @index + 1;
        END
    END

    DROP TABLE #temp_who_lock;
    RETURN 0;
END;
GO

三、使用方法

1. 在 SSMS 或 sqlcmd 中執行上述腳本建立存儲過程（建議放在 master 資料庫）。
2. 發現應用響應變慢、事務超時時，直接執行：

EXEC sp_who_lock;

3. 觀察輸出結果：

• 若出現 「引起資料庫死鎖的是: SPID xx」** → 立即分析該 SPID 的 SQL，優化索引、調整事務隔離級別或拆分大事務。
• 若出現 「進程號 SPID: xx 被進程號 SPID: yy 阻塞」** → 檢查阻塞源（yy）的 SQL，評估是否需 KILL yy 或優化查詢計畫。

CREATE TABLE dbo.BlockLog (
    LogTime DATETIME DEFAULT GETDATE(),
    SPID INT, BlkSPID INT, IsDeadlock BIT,
    SqlText NVARCHAR(MAX)
);
-- 在 sp_who_lock 內改寫 PRINT 為 INSERT dbo.BlockLog ...

GRANT EXECUTE ON dbo.sp_who_lock TO [DBA_Role];
-- 僅授予 DBA/開發組執行權限，避免濫用