How to view the XML deadlock graph

An easy way to view deadlock information is to find the deadlock report in the Extended Event system_health session.  Under the system_health session, double-click on package0.event_file.  In the window that opens, create a filter so that you only see the xml_deadlock_report.

Depending on the number of rows that you have recorded this may take awhile.  Once you have your final list, you can double-click on the report that you want to examine.  The report will come up in XML and have tons of good information for you to examine.  

However you sometimes want to view the deadlock graph itself.  To do this save the xml but change the file extension to ".xdl".  Now if you drag that file to SQL Server Management Studio, the graph will be displayed.

Similarly you can query the ring buffer with:

WITH SystemHealth

AS ( 

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'

       AND st.target_name = 'ring_buffer')

SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph

FROM SystemHealth

       CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

 

Or the extended event file with:

Start by finding the path to the files:

SELECT target_data

FROM sys.dm_xe_session_targets st

INNER JOIN sys.dm_xe_sessions s 

ON s.address = st.event_session_address

WHERE name = 'system_health'

AND target_name = 'event_file'

then query the data

Select Cast(event_data AS xml) AS TargetData

FROM sys.fn_xe_file_target_read_file('C:\events\system_health*.xel',NULL,NULL,NULL)

WHERE object_name like 'xml_deadlock_report';

references: https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx