Deadlock info from XEvents XML

Here's a handy script I developed to take advantage of the built-in XEvents System Health session in SQL 2008.

XEvents is the new infrastructure for diagnostics data collection, set to usurp SQL Traces in utility for this purpose.  XEvents are already up and running in SQL 2k8 and 2k8R2 but without an official GUI.  The Denali RC's currently sport an XEvents interface in Management Studio, which will officially get the ball rolling.

Right now though, you can still use XEvents, and the pre-packaged System Health trace session that is running by default.

Here's a great way to get some fairly-easy to read (for XML) diagnostic info on deadlocks that happened on your server... without enabling a Trace flag or a SQL Trace.

declare @xml xml

select @xml = CAST(t.target_data as xml)

from sys.dm_xe_session_targets t

inner join sys.dm_xe_sessions s

     on s.address = t.event_session_address

inner join sys.server_event_sessions es

     on s.name = es.name

     where s.name = 'system_health'

     and t.target_name = 'ring_buffer'

select @xml.query('/RingBufferTarget/event [@name="xml_deadlock_report"]')

You can clearly see the victim and its blocking, the InputBuffer strings for each (which won't see through a stored procedure unfortunately) and the same stuff you'd be used to seeing in a Trace - spid, hostname, clientapp, date/time.  Use any XML viewer to view the output, including SSMS's native ability to view XML.