Prior to SQL Server 2008, finding deadlock information was a challenging task. It required running a SQL Trace or enabling specific trace flags for the instance. However, with the introduction of Extended Events in SQL Server 2008, troubleshooting deadlocks has become much easier.
Extended Events is an advanced troubleshooting feature that allows us to capture and analyze various events in SQL Server. One of the default sessions in Extended Events is the system_health session, which collects important information for troubleshooting common problems in SQL Server.
The system_health session captures various events, including:
- Sessions encountering errors with severity >= 20
- Sessions encountering memory-related errors
- Non-yielding problems
- Detected deadlocks
- Sessions waiting on latches or locks for an extended period of time
- Sessions waiting for external or pre-emptive waits
Unlike previous versions of SQL Server, we can now obtain deadlock information after the fact without enabling additional tracing. The system_health session stores the collected information in a ring_buffer target as an XML document in the sys.dm_xe_session_targets DMV.
To extract the deadlock graphs from the XML, we can use XPath and a derived table with a CROSS APPLY operation. Here’s an example query:
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'
However, it’s important to note that there is a bug in the current deadlock graph output from Extended Events, which results in an end tag mismatch for the <victim-list> XML node. This bug can be easily worked around by using the REPLACE function to switch the incorrect end tag </deadlock> with the correct one </victim-list>.
Additionally, there is another bug that has already been fixed in Cumulative Update #1 for SQL Server 2008. This bug can cause the deadlock graph data to be empty. If you encounter blank rows when running the query, applying the cumulative update is necessary.
Once you have obtained the deadlock graph as an XML document, you can view it in Management Studio. However, unlike the deadlock graph generated by SQL Trace, you cannot save the Extended Events deadlock graph as a .XDL file and open it graphically in Management Studio. The Extended Events deadlock graph has a new XML schema and format, which includes multiple victims.
If you need assistance in understanding the information contained in the deadlock graph, you can refer to Bart Duncan’s blog posts on the subject or seek help from the community in the Administering Forum.
For further information on Extended Events and the system_health session, you can visit the CSS Team blog.
With the advancements in Extended Events, troubleshooting deadlocks in SQL Server has become more efficient and accessible. The ability to capture and analyze deadlock information after the fact without additional tracing has greatly simplified the process.