Uncategorized

Deadlock Chart from XE Ring Buffer

Simple script to obtain deadlock graph from default XE session system_health. Contains only deadlocks capture for certain period of time back (hours). Transact-SQL SELECT [xe].[query]('(event/data/value/deadlock)[1]') [DeadlockGraph] FROM ( SELECT [xe].[query]('.') [xe] FROM ( SELECT CAST([target_data] AS XML) [td] FROM [sys].[dm_xe_session_targets] [st] INNER JOIN [sys].[dm_xe_sessions] [s] ON [s].[address] = [st].[event_session_address] WHERE [s].[NAME] = 'system_health' AND [st].[target_name] = 'ring_buffer' ) [Data] CROSS APPLY [td].[nodes]('RingBufferTarget/event[@name="xml_deadlock_report"]') AS [XEventData]([xe]) ) [a] GO 1234567891011 SELECT [xe].[query]('(event/data/value/deadlock)[1]') [DeadlockGraph]FROM ( SELECT [xe].[query]('.') [xe]    FROM ( SELECT CAST([target_data] AS XML) [td]   FROM [sys].[dm_xe_session_targets] [st] INNER JOIN [sys].[dm_xe_sessions] [s] ON [s].[address] = [st].[event_session_address]   WHERE [s].[NAME] = 'system_health' AND [st].[target_name] = 'ring_buffer'        ) [Data] CROSS APPLY [td].[nodes]('RingBufferTarget/event[@name="xml_deadlock_report"]') AS [XEventData]([xe])) [a]GO