SQL Server 監控實戰：精準診斷阻塞與等待統計 (第三篇)

核心指標：阻塞偵測與會話追蹤

在 SQL Server 運行維護中，阻塞是導致響應變慢、甚至應用程式超時的常見元兇。透過動態管理檢視 (DMV) sys.dm_os_waiting_tasks，我們能快速鎖定正在等待資源的會話，並找出阻塞源頭。

執行以下查詢即可列出所有被阻塞的任務，包含阻塞者的 Session ID、等待時長與受害者 Session ID：

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 後，利用 DBCC INPUTBUFFER 即可檢視該會話正在執行的 T-SQL 批次。例如若阻塞者為 SPID 87：

DBCC INPUTBUFFER(87);

全景視野：等待統計分析 Top 10

要從宏觀角度掌握實例級的性能瓶頸，sys.dm_os_wait_stats 是不可或缺的工具。以下查詢按累積等待時間降序，列出前 10 大等待類型，幫助您快速識別資源爭用熱點（如鎖、鎖存器、I/O、CPU 排程等）：

SELECT TOP 10 *
FROM sys.dm_os_wait_stats
-- 可選：過濾掉常良性等待類型
-- WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
ORDER BY wait_time_ms DESC;

進階診斷：自訂存儲過程鎖定鏈分析

為了更直觀地呈現「誰阻塞了誰」以及涉及的具體鎖資源，可在資料庫中部署下列存儲過程 dbo.sp_block。它結合 sys.dm_tran_locks 與 sys.dm_os_waiting_tasks，輸出鎖模式、資源類型、資料庫名稱、關聯實體 ID 以及阻塞鏈關係。參數 @spid 為可選，若提供則僅過濾該會話相關的阻塞資訊。

CREATE PROC dbo.sp_block (@spid BIGINT = NULL)
AS
SELECT
    t1.resource_type,
    'database' = DB_NAME(t1.resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks AS t1
JOIN sys.dm_os_waiting_tasks AS t2
    ON t1.lock_owner_address = t2.resource_address
WHERE t1.request_session_id = ISNULL(@spid, t1.request_session_id);

使用範例：

-- 檢視所有阻塞
EXEC sp_block;

-- 僅檢視 SPID 7 相關的阻塞
EXEC sp_block @spid = 7;

善用這組查詢與工具，配合定期基線收集，即可在阻塞演變為嚴重事故前，完成精準定位與處置。