Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Introduction
Deadlock data can be extracted in many ways, including the deadlock graph event in Profiler and the 1204 and 1222 trace flags. This article demonstrates how to obtain deadlock information from the SYSTEM_HEALTH extended events trace and files.
Methods of event retrieval
You can view deadlocks within management studio. Deadlock data can also be retrieved by querying the SYSTEM_HEALTH extended events trace directly using the ring buffer to identify deadlocks that have occurred. Finally, deadlock information can be queried directly from the SYSTEM_HEALTH trace .XEL files, using the sys.fn_xe_file_target_read_file function.
From Management Studio:
Expand the Management subtree in Object Explorer for the server connection you wish to view.
Expand the system_health session.
Right click the event_file and select View Target Data…
Apply a filter for xml_deadlock_report.
<Finally, if a deadlock event is shown, select it to see its detail.
From a query:
First, query the extended events dynamic management views to identify if the SYSTEM_HEALTH trace is on the server and that the xml_deadlock_report is included in the trace.
SELECT s.name, se.event_name
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_events se ON (s.address = se.event_session_address) and (event_name = 'xml_deadlock_report')
WHERE name = 'system_health'
Once the deadlock event has been confirmed to be in available, you can query the data from the ring buffer or from the .XEL files.
Ring buffer method:
DECLARE @version int
SET @version = (@@microsoftversion / 0x1000000) & 0xff;
IF (@version = 10)
BEGIN
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'
END
IF (@version > 10)
BEGIN
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.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
END
Note that the ring buffer is a moving window and deadlocks may not immediately be available when using this method. For more immediate results use the UI tools built into management studio or query the .XEL files.
Files method:
SELECT CONVERT(xml, event_data).query('/event/data/value/child::*'),
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'
Conclusion
The system_health extended events session can be a gold mine for researching deadlocks and many issues. Using the management studio UI tools allows the data to be viewed in real-time and is very simple and easy to use; however, querying the data directly is much more flexible and is much easier to store data and collect information across multiple servers.