--------------------------------------------------------------------------------
--  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)
--                  SQL Server 2012 (11.0)
--
--  Version     :   3.0009.0100.0000 (2012-05-10)
--------------------------------------------------------------------------------

USE [tempdb];

GO

BEGIN TRY
    SELECT (
            Case XE_Data.Event.value(N'(data/type/@name)[1]', N'NVarChar(64)')
                WHEN N'unicode_string' THEN Cast (
                        XE_Data.Event.value(
                            N'(data/value)[1]',
                            N'NVarChar(MAX)'
                        )
                        AS XML
                    )
                WHEN N'xml' THEN XE_Data.Event.query(N'(data/value/deadlock)[1]')
                ELSE Cast(N'<unrecognizedXMLDeadlockReport />' AS XML)
            END
        ) 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 XE_Data(Event)
    WHERE (XE_Data.Event.value(N'@name', N'NVarChar(4000)') = N'xml_deadlock_report')
    ORDER BY XE_Data.Event.value(N'(@timestamp)[1]', N'DateTime') ASC;
END TRY
BEGIN CATCH
    IF (Error_Number() = 9436)
    BEGIN
        SELECT Cast (
                Replace (
                    Replace (
                        XE_Data.Event.value(
                            N'(data/value)[1]',
                            N'NVarChar(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 XE_Data(Event)
        WHERE (XE_Data.Event.value(N'@name', N'NVarChar(4000)') = N'xml_deadlock_report')
        ORDER BY XE_Data.Event.value(N'(@timestamp)[1]', N'DateTime') ASC;
    END;
END CATCH;

GO
