本文檔介紹了兩項關(guān)鍵的 T-SQL 查詢，用于排查 Microsoft SQL Server 中的死鎖問題。

1. 查詢 system_health 會話中的歷史死鎖信息

system_health 是 SQL Server 默認啟用的擴展事件 (Extended Events) 會話。它會自動捕獲包括死鎖 (xml_deadlock_report) 在內(nèi)的多種系統(tǒng)事件，是排查近期發(fā)生的死鎖的首選之地。

查詢腳本

SELECT
    XEventData.XEvent.value('(@timestamp)[1]', 'datetime') AS DeadlockDateTime,
    XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadlockGraph
FROM
    (SELECT CAST(target_data AS XML) AS TargetData
     FROM sys.dm_xe_session_targets st
              JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
     WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer') AS Data
        CROSS APPLY
    TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
ORDER BY
    DeadlockDateTime DESC;

結(jié)果集說明

如何分析死鎖圖 (DeadlockGraph)

1. 在 SSMS (SQL Server Management Studio) 中執(zhí)行上述查詢。
2. 點擊結(jié)果集中 DeadlockGraph 列的 XML 鏈接。
3. SSMS 會在一個新的窗口中以圖形化的方式展示死鎖關(guān)系圖，非常直觀。
   • 橢圓：表示參與死鎖的進程（SPID）。
   • 矩形：表示被爭搶的資源（如鍵、頁、表、行）。
   • 箭頭：表示進程對資源的請求和等待關(guān)系。
4. 在圖形化界面中，可以清楚地看到：
   • 哪些兩個（或多個）會話被卷入死鎖。
   • 它們各自持有(owner)什么資源，又在等待(waiter)什么資源。
   • 它們當時正在執(zhí)行的 T-SQL 語句（inputbuf）。

2. 查詢特定會話 (SPID) 的詳細信息

當從死鎖圖或其它途徑（如監(jiān)控、錯誤日志）獲知一個具體的會話 ID (SPID) 后，可以使用以下查詢來獲取該會話的詳細實時狀態(tài)和資源使用情況。

查詢腳本

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status AS session_status,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.reads,
    s.writes,
    s.logical_reads,
    s.cpu_time,
    s.memory_usage,
    DB_NAME(s.database_id) AS database_name,
    r.status AS request_status,
    r.command,
    r.start_time,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id, -- **關(guān)鍵：阻塞此會話的SPID**
    r.cpu_time AS request_cpu_time,
    r.total_elapsed_time,
    r.reads AS request_reads,
    r.writes AS request_writes,
    r.logical_reads AS request_logical_reads
FROM sys.dm_exec_sessions s
         LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id = 219; -- **替換為你需要關(guān)注的 SPID**

關(guān)鍵字段說明 (用于死鎖/阻塞分析)

總結(jié)與排查步驟建議

1. 發(fā)現(xiàn)死鎖：通過應(yīng)用程序錯誤日志、SQL Server 錯誤日志或監(jiān)控工具發(fā)現(xiàn)死鎖錯誤（錯誤號 1205）。
2. 獲取死鎖圖：使用第一個查詢從 system_health 會話中提取最近的死鎖圖。
3. 分析死鎖圖：在 SSMS 中圖形化查看死鎖圖，確定涉及的主要會話 (SPID) 和爭搶的資源。
4. 調(diào)查會話詳情：將圖形中發(fā)現(xiàn)的 SPID 代入第二個查詢，了解這些會話的詳細信息（誰發(fā)起的、從哪里來、在做什么操作），特別是 blocking_session_id 字段可以幫助確認阻塞關(guān)系。
5. 解決問題：根據(jù)分析結(jié)果，通常的解決方案包括：
   • 優(yōu)化查詢/索引：確保事務(wù)盡可能短小，為經(jīng)常查詢的字段添加索引，避免表掃描。
   • 調(diào)整事務(wù)隔離級別：在必要時使用更低的隔離級別（如 READ COMMITTED）。
   • 調(diào)整訪問順序：在應(yīng)用層代碼中，確保對不同資源的訪問順序在所有事務(wù)中都保持一致。
   • 使用提示：在極少數(shù)情況下，考慮使用鎖提示（如 NOLOCK, UPDLOCK, ROWLOCK），但需謹慎評估其副作用。

到此這篇關(guān)于SQL Server死鎖排查的實戰(zhàn)指南的文章就介紹到這了,更多相關(guān)SQL Server死鎖排查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！