SQL Server 阻塞監控腳本：捕獲阻塞鏈、SQL 文本、等待時長、客戶端信息的完整方案

阻塞本質與監控目標

阻塞 = 鎖持有時間過長/過多/粒度過大。DBA 需知：誰阻塞誰、雙方執行什麼 SQL、阻塞多久、來自哪個客戶端/應用、發生頻率。

核心監控查詢

SELECT 
    wt.blocking_session_id AS BlockingSessionId,
    sp.program_name AS ProgramName,
    COALESCE(sp.loginame, sp.nt_username) AS LoginName,
    ec1.client_net_address AS ClientIpAddress,
    db.name AS DatabaseName,
    wt.wait_type AS WaitType,
    ec1.connect_time AS BlockingStartTime,
    wt.wait_duration_ms / 1000.0 AS WaitDurationSec,
    ec1.session_id AS BlockedSessionId,
    h1.text AS BlockedSQLText,
    h2.text AS BlockingSQLText
FROM sys.dm_os_waiting_tasks AS wt
INNER JOIN sys.dm_exec_connections AS ec1 ON wt.waiting_task_address = ec1.most_recent_sql_handle
INNER JOIN sys.dm_exec_sessions AS sp ON ec1.session_id = sp.session_id
LEFT JOIN sys.databases AS db ON sp.database_id = db.database_id
OUTER APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT most_recent_sql_handle 
     FROM sys.dm_exec_connections 
     WHERE session_id = wt.blocking_session_id)
) AS h2
WHERE wt.blocking_session_id > 0;

輸出欄位業務含義

部署建議

• 封裝為 存儲過程 + SQL Agent 任務 定時寫入歷史表
• 閾值告警：WaitDurationSec > 30 觸發郵件通知開發/DBA
• 配合 sp_who2 active、sp_lock、活動監視器、標準報表「所有阻塞事務」多維排查

常見 DMV 對比