sql server查询死锁的sql语句
--查询死锁和阻塞的sql语句
SELECT CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]',
'varchar(max)'),
'<victim-list>', '<deadlock><victim-list>'),
'<process-list>', '</victim-list><process-list>') AS XML) 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 [name] = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
WHERE XEventData.XEvent.value('@name', 'varchar(8000)') = 'xml_deadlock_report' ;
'varchar(max)'),
'<victim-list>', '<deadlock><victim-list>'),
'<process-list>', '</victim-list><process-list>') AS XML) 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 [name] = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
WHERE XEventData.XEvent.value('@name', 'varchar(8000)') = 'xml_deadlock_report' ;