Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

For the first time in SQL Server, we can now get deadlock information after the fact without having to enable any additional tracing for troubleshooting. The system_health session uses a ring_buffer target which stores the information collected by events firing in memory as an XML document in the sys.dm_xe_session_targets DMV. This can be joined to the sys.dm_xe_sessions DMV to get the session information along with the data stored in the ring_buffer target:


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'

 Using XPath we can then get just the deadlock graphs from the XML by using a derived table and a CROSS APPLY to get the individual Event Nodes as follows: 

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') 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(4000)') = 'xml_deadlock_report'

 
To get around the invalid XML problem, perform an inline replace before casting to XML as follows:

 select CAST(
            REPLACE(
                REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
                '', ''),
            '','
')
        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(4000)') = 'xml_deadlock_report'

 

 

credits : http://www.sqlservercentral.com/articles/deadlock/65658/

 

No comments:

Post a Comment