SQL Server 2008 阻塞分析與解決：動態管理視圖實戰指南

什麼是阻塞？

當一個會話持有資源鎖，另一個會話請求相衝突的鎖而被迫等待，即發生阻塞。短暫阻塞屬正常，長時間阻塞會嚴重影響並發效能。

常見長時間阻塞成因

1. 無索引表上的大量行鎖升級為表鎖。
2. 應用程式在事務中等待使用者互動（如 GUI 輸入），長時間持有鎖。
3. 事務啟動前查詢的資料在事務中被修改（幻讀/不可重複讀）。
4. 不當使用鎖定提示（如只讀幾行卻加 TABLOCKX）。
5. 單一大事務更新大量資料行/表，建議拆分為多個小事務。

一、用 DMV 找出並解決阻塞進程

1. 找出阻塞鏈

SELECT blocking_session_id, wait_duration_ms, session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;

結果範例：blocking_session_id=52 阻塞 session_id=54。

2. 查看阻塞者正在執行的 SQL

SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE c.session_id = 52;  -- 阻塞者的 session_id

回傳的是參數化後的 SQL（自動參數化計畫），非原始批次文本。

3. 強制終止阻塞會話

KILL 52;

注意：KILL 會回滾該會話的未提交事務，可能導致資料不一致，請謹慎使用。若阻塞成串（A 阻塞 B，B 阻塞 C），需遞迴查找最頂層阻塞者（blocking_session_id 為 NULL 或不被其他阻塞的 session）。

KILL 命令參數詳解

• session_id：一般會話 ID，終止一般/分散式事務。
• UOW：分散式事務的工作單元 GUID（從 sys.dm_tran_locks.request_owner_guid 取得），終止孤立分散式事務（session_id = -2）。
• WITH STATUSONLY：僅顯示現有回滾進度，不終止會話。

二、設定鎖等待超時 (SET LOCK_TIMEOUT)

語法：SET LOCK_TIMEOUT timeout_ms（毫秒）。超時後回傳錯誤 1222，陳述式終止，但不影響持有鎖的會話。

範例

-- 會話 1：持有鎖
BEGIN TRAN
UPDATE Production.ProductInventory SET Quantity = 400 WHERE ProductID = 1 AND LocationID = 1;

-- 會話 2：設 1 秒超時
SET LOCK_TIMEOUT 1000;
UPDATE Production.ProductInventory SET Quantity = 406 WHERE ProductID = 1 AND LocationID = 1;
-- 1 秒後報錯：Msg 1222, Lock request time out period exceeded.

此設定僅影響「等待鎖釋放」的時間，不影響資源被佔用的時間。適合用於不想無限期等待的應用層邏輯。

進階排查技巧

• 結合 sys.dm_tran_locks 查看鎖資源類型（OBJECT, PAGE, KEY, RID...）、模式（S, X, U...）、狀態。
• 使用 sys.dm_exec_requests 觀察 wait_type（如 LCK_M_S, LCK_M_X）與 wait_time。
• 長期阻塞往往源於索引缺失、統計過期導致錯誤計畫，建議配合 sys.dm_db_missing_index_details 分析。