--------------------------------------------------------------------------------
--  File        :   Demo 08_Display deadlock information from the XE ring buffer,2008+.sql
--
--  Purpose     :   This script retrieves deadlock information from the 
--                  built-in "system_health" Extended Events session, new to 
--                  SQL Server 2008.
--
--                  NOTE: This code is based on a wonderful article by 
--                  Jonathan Kehayias:
--                      "Retrieving Deadlock Graphs with SQL Server 2008 Extended Events" <http://www.sqlservercentral.com/articles/deadlocks/65658/>
--
--  SQL Server  :   SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   3.0006.0100.0053 (2010-11-17)
--------------------------------------------------------------------------------

USE [tempdb];

GO

SELECT Cast
    (
        Replace(
            Replace(
                [XEventData].[XEvent].value
                (
                    N'(data/value)[1]',
                    N'VarChar(MAX)'
                ),
                N'<victim-list>',
                N'<deadlock><victim-list>'
            ),
            N'<process-list>',
            N'</victim-list><process-list>'
        )
        AS XML
    ) AS [DeadlockGraph]
FROM
(
    SELECT Cast([_XESessionTargets].[target_data] AS XML) AS [TargetData]
    FROM [sys].[dm_xe_session_targets] AS [_XESessionTargets]
    INNER JOIN [sys].[dm_xe_sessions] AS [_XESessions]
    ON ([_XESessions].[address] = [_XESessionTargets].[event_session_address])
    WHERE ([_XESessions].[name] = N'system_health')
) AS [_Data]
CROSS APPLY [TargetData].nodes(N'//RingBufferTarget/event') AS [XEventData]([XEvent])
WHERE ([XEventData].[XEvent].value(N'@name', N'NVarChar(4000)') = N'xml_deadlock_report');

GO
