善用 sp_lock 診斷 SQL Server 阻塞與效能瓶頸

鎖定觀念釐清

鎖定並非壞事，它是資料庫為確保 ACID 一致性而實作的必要機制。問題在於過度鎖定、長時間持有、死結導致並發度下降。系統變慢時，首要任務是「看見」正在發生什麼鎖定。

sp_lock 回傳欄位解讀

實戰診斷步驟

1. 執行 sp_lock 觀察全景

EXEC sp_lock;

若回傳數百列，尋找 status = 'WAIT' 或同一 spid 擁有大量鎖定的列。

2. 找出持有大量鎖定的 SPID

SELECT spid, COUNT(*) AS lock_count
FROM master..syslockinfo
GROUP BY spid
ORDER BY lock_count DESC;

3. 追蹤該 SPID 正在執行的陳述式

DBCC INPUTBUFFER();
-- 或使用更詳細的
SELECT text FROM sys.dm_exec_sql_text(
  (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = )
);

4. 判斷阻塞鏈

-- 找出阻塞者與被阻塞者
SELECT
  blocking.session_id AS blocker_spid,
  blocked.session_id  AS victim_spid,
  blocked.wait_type,
  blocked.wait_time,
  blocked.wait_resource,
  sqltext.text AS blocker_sql
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) sqltext
WHERE blocked.blocking_session_id > 0;

常見鎖定模式與對策

• X (Exclusive) 大量持有：長跑 UPDATE/DELETE/INSERT，考慮分批次、加索引減少掃描範圍。
• Sch-M (Schema Modification)：DDL 操作（ALTER TABLE, TRUNCATE）會阻塞所有存取，安排維護時段執行。
• KEY / PAG / ROW WAIT：索引碎片、熱點頁、缺少索引導致掃描過多資料列，優化查詢計畫。
• 死結：錯誤訊息 1205，需檢查應用程式存取資源順序是否一致，或啟用 `READ_COMMITTED_SNAPSHOT` 減少讀寫衝突。

自動化監控建議

• 建立 Agent 作業，定期執行 sp_lock 並記錄至歷史表，趨勢分析鎖定熱點。
• 設定警示：當 `sys.dm_os_wait_stats` 中 `LCK_M_*` 等待時間超過閾值時發送通知。
• 使用 Extended Events (XEvent) 捕捉 `lock_deadlock`、`blocked_process_report` 事件，取代舊式 Trace。

結語

掌握 sp_lock、DBCC INPUTBUFFER 與 DMV 聯合查詢，能快速定位阻塞源頭、識別長跑交易、預防死結。鎖定可見，效能可控。