SQL Server 2005 死鎖與阻塞監控存儲過程 sp_who_lock

效能測試或生產環境排查時，快速定位阻塞鏈與死鎖發起者至關重要。本文提供存儲過程 sp_who_lock，能遞迴分析 sysprocesses 系統檢視，輸出完整的阻塞鏈與當前執行的 SQL 陳述式。

核心邏輯

1. 從 sysprocesses 找出所有 blocked > 0 的 SPID（被阻塞者）
2. 識別「阻塞源頭」：blocked 值不在被阻塞 SPID 清單中的進程（即沒被其他人阻塞的阻塞者）
3. 建立暫存表 #tmp_lock_who 記錄 spid（被阻塞者）與 bl（阻塞者）
4. 迴圈逐筆呼叫 DBCC INPUTBUFFER(@bl) 取得阻塞者正在執行的 SQL 文字

完整建立腳本

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
  DECLARE @spid INT, @bl INT, @intTransactionCountOnEntry INT,
          @intRowcount INT, @intCountProperties INT, @intCounter INT;

  CREATE TABLE #tmp_lock_who (id INT IDENTITY(1,1), spid SMALLINT, bl SMALLINT);
  IF @@ERROR <> 0 RETURN @@ERROR;

  -- 找出阻塞鏈：源頭阻塞者 + 所有被阻塞者
  INSERT INTO #tmp_lock_who (spid, bl)
  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;
  IF @@ERROR <> 0 RETURN @@ERROR;

  SELECT @intCountProperties = COUNT(*), @intCounter = 1 FROM #tmp_lock_who;
  IF @@ERROR <> 0 RETURN @@ERROR;

  IF @intCountProperties = 0
    SELECT '目前無阻塞與死鎖資訊' AS message;

  WHILE @intCounter <= @intCountProperties
  BEGIN
    SELECT @spid = spid, @bl = bl FROM #tmp_lock_who WHERE id = @intCounter;

    BEGIN
      IF @spid = 0
        SELECT '引發資料庫死鎖的是：' + CAST(@bl AS VARCHAR(10)) + ' 號進程，其執行的 SQL 如下：';
      ELSE
        SELECT '進程 SPID：' + CAST(@spid AS VARCHAR(10)) + 
               ' 被進程 SPID：' + CAST(@bl AS VARCHAR(10)) + 
               ' 阻塞，其當前執行的 SQL 如下：';

      DBCC INPUTBUFFER(@bl);
    END

    SET @intCounter = @intCounter + 1;
  END

  DROP TABLE #tmp_lock_who;
  RETURN 0;
END
GO

使用方式

切換至任意使用者資料庫（或 master），直接執行：

EXEC sp_who_lock

輸出範例解讀

• spid = 0：表示該行是「阻塞源頭」（Head Blocker），其 bl 欄位即為根因進程 ID。
• spid > 0：表示被阻塞的受害者進程，bl 為阻塞它的進程 ID。
• DBCC INPUTBUFFER 輸出的 EventInfo 欄位即為該進程正在執行的批次文字（前 255 字元）。

進階替代方案（SQL Server 2008+）

新版本建議使用擴充事件 或 sys.dm_exec_requests / sys.dm_os_waiting_tasks DMV 組合查詢，可獲得更完整的等待類型、等待時間、資源描述等資訊，且不依賴已棄用的 sysprocesses。

注意事項

• 需具備 VIEW SERVER STATE 權限或 sysadmin 固定伺服器角色。
• 高併發環境下，阻塞鏈可能在毫秒級變化，輸出僅代表執行當下的快照。
• 死鎖會由 SQL Server 自動偵測並選擇犧牲者回滾，sp_who_lock 更適合偵測「長時間阻塞」而非瞬間死鎖。