Query for deadlocks using extended events in sql 2008 sp2

Question

I found this query on the web but would like to include the creation time and order the results by the creation time.  Not quite sure how to do this.  Please advise.

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'

Answer 1

Here is a query:

SELECT event.c.value('@timestamp', 'datetime2(3)'),
       datavalue.c.value('(./text())[1]', 'nvarchar(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 event(c)
CROSS APPLY event.c.nodes ('data/value') AS datavalue(c)
WHERE event.c.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

I've rearranged the query for better performance. The // is a wild-card, which forces SQL Server to read all nodes. Not efficient.

When you need to access data on different levels in an XML document, my experience is that the best is to run nodes() for each level you need to access.

The /text() thing looks clumsy, but it can improve performance drastically as it eliminates some weird corner cases for SQL Server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thanks Erland, that did the trick!

Answer 3

FYI, here is another version I found on the web.  It appears to run much faster than the previous revision.  Note, it appears that the XML is not compatible with SSMS so you must look at in an external XML viewer.

SELECT
	xed.value('@timestamp', 'datetime2(3)') as CreationDate,
	xed.query('.') AS XEvent
FROM
(
	SELECT CAST([target_data] AS XML) AS TargetData
	FROM sys.dm_xe_session_targets AS st
		INNER JOIN sys.dm_xe_sessions AS s
			ON s.address = st.event_session_address
		WHERE s.name = N'system_health'
			AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

Answer 4

FYI, here is another version I found on the web.  It appears to run much faster than the previous revision.  

XML performance is always a challenge, because the optimizer works in a dark room, and there are certainly imperfections in Microsoft's implementation.

Since I'm at home and only have my playground server, I cannot really measure performance for these particular queries. However, I recently worked with reading the data from a 30 MB XEvent file. The query I got originally used filtering of the kind you used. I rewrote the query in the style that I posted, and it improved performance considerably. But which is the best strategy depends on the nature of the data and what you want to get out of it. The system health sessions has other events as well, so maybe this filtering is better.

Note, it appears that the XML is not compatible with SSMS so you must look at in an external XML viewer.

This is because the Deadlock report is an XML document nested into another XML document. Replace

   xed.query('.') AS XEvent

in your query with:

  cast(xed.value('(data/value/text())[1]', 'nvarchar(MAX)') AS xml) AS XEvent

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

I had the occassion to test the queries on a production server, and the version you posted returned data instantly. Mine run for several minutes without producing a result, so it's quite obvious which one to use.

---

Erland Sommarskog, SQL Server MVP, [e-mail]