如何从SQL Server死锁报告中识别被终止的查询（Victim）

原脚本核心问题 #

原脚本在TD CTE中同时对受害者列表和进程列表执行CROSS APPLY，会生成受害者与所有进程的笛卡尔积，导致后续关联时出现重复数据，无法精准匹配到对应的牺牲品查询。

修改后的完整脚本 #

DECLARE @DATA_LOCK TABLE(TARGET_DATA XML);

INSERT INTO @DATA_LOCK 
SELECT CAST(target_data AS XML) AS TARGET_DATA
FROM   sys.dm_xe_session_targets st
       INNER JOIN sys.dm_xe_sessions s 
          ON s.address = st.event_session_address
WHERE  s.name = 'system_health'
       AND st.target_name = 'ring_buffer';

WITH 
DATA_SRC AS
(
SELECT X_EVENT.query('.') AS X_EVENT_LOCK
FROM   @DATA_LOCK
       CROSS APPLY 
          TARGET_DATA.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
          AS X_EVENT_DATA(X_EVENT)
),
DATA_LOCKS AS
(
SELECT ROW_NUMBER() 
          OVER(ORDER BY X_EVENT_LOCK.value('(event/@timestamp)[1]', 
                                           'datetimeoffset')) 
          AS DEADLOCK_NUM,
       X_EVENT_LOCK.value('(event/@timestamp)[1]', 
                          'datetimeoffset') AS DEADLOCK_DATETIME, 
       X_EVENT_LOCK.query('(event/data/value/deadlock)[1]') 
          AS DEADLOCK_GRAPH_XML
FROM   DATA_SRC
),
-- 单独提取每个死锁的牺牲品进程ID
VICTIMS AS
(
SELECT DEADLOCK_NUM,
       DEADLOCK_DATETIME,
       victim_node.value('@id', 'NVARCHAR(128)') AS VICTIM_ID
FROM DATA_LOCKS
     CROSS APPLY DEADLOCK_GRAPH_XML.nodes('/deadlock/victim-list/victimProcess') AS Victims(victim_node)
),
-- 单独提取每个死锁的所有进程及完整执行信息
PROCESSES AS
(
SELECT DEADLOCK_NUM,
       DEADLOCK_DATETIME,
       process_node.value('@id', 'NVARCHAR(128)') AS PROCESS_ID,
       process_node.value('@currentdbname', 'NVARCHAR(128)') AS DATABASE_NAME,
       -- 提取最顶层调用的存储过程（若存在）
       process_node.value('(executionStack/frame/@procname)[last()]', 'NVARCHAR(128)') AS TOP_LEVEL_PROC,
       -- 提取实际执行的SQL语句（执行栈最后一行）
       process_node.value('(executionStack/frame)[last()]', 'VARCHAR(MAX)') AS EXECUTED_SQL,
       process_node.value('@clientapp', 'NVARCHAR(256)') AS CLIENT_APP,
       process_node.value('@loginname', 'NVARCHAR(128)') AS LOGIN_NAME
FROM DATA_LOCKS
     CROSS APPLY DEADLOCK_GRAPH_XML.nodes('/deadlock/process-list/process') AS Processes(process_node)
)

-- 关联进程与牺牲品信息，精准标记被终止的查询
SELECT 
    CASE WHEN V.VICTIM_ID IS NOT NULL THEN 'VICTIM !' ELSE 'Alive...' END AS FINAL_STATE,
    P.*
FROM PROCESSES P
LEFT JOIN VICTIMS V 
    ON P.DEADLOCK_NUM = V.DEADLOCK_NUM 
    AND P.PROCESS_ID = V.VICTIM_ID
ORDER BY P.DEADLOCK_NUM, P.PROCESS_ID;

关键优化点 #

• 拆分牺牲品提取与进程信息提取为独立CTE，彻底避免笛卡尔积导致的数据重复问题。
• 优化SQL语句提取逻辑：用[last()]获取执行栈最底层的实际执行语句，以及最顶层的存储过程调用，更贴合死锁排查需求。
• 新增客户端应用、登录名字段，辅助定位问题来源。
• 简化关联逻辑，通过DEADLOCK_NUM和PROCESS_ID精准匹配，确保牺牲品标记结果准确。

内容的提问来源于stack exchange，提问作者SQLpro