--
查询死锁和阻塞的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 ' ;