Query for deadlocks from system health empty results

Question

Using SQL 2017 Developer edition
Im trying to get a query working to return any deadlock events from the system health extended events. I ran a test scenario on my local instance to force deadlock conditions and I can see the events if I filter for them under the system_health > package0.event_file, but the following query isn't returning any of those results

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT XEvent.query('.') AS XEvent
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        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'
        ) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

Answer 1

The ring buffer target is a circular memory structure of limited size so the deadlock info may have rolled off. You can also read the target file in T-SQL using sys.fn_xe_file_target_read_file. Below is an example that will read from all available target files to retrieve recent deadlocks.

--get xml_deadlock_report from system_health session file target
WITH
      --get full path to current system_health trace file
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
      --get trace folder name and add base name of system_health trace file with wildcard
    , BaseSystemHealthFileName AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
        FROM CurrentSystemHealthTraceFile
        )
      --get xml_deadlock_report events from all system_health trace files
    , DeadLockReports AS (
        SELECT CAST(event_data AS xml) AS event_data
        FROM BaseSystemHealthFileName
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
        WHERE xed.object_name like 'xml_deadlock_report'
    )
--display 10 most recent deadlocks
SELECT TOP 10
      DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
    , event_data AS DeadlockReport
FROM DeadLockReports
ORDER BY LocalTime ASC;

Answer 2

The query is correct. But the ring buffer target för the system health session is only 4 MB (with max 5000 events). I.e., your deadlock event isn't there anymore, it has been aged out.

I suggest you keep your own trace running, and use the file target instead of ring buffer. Here's an example:

CREATE EVENT SESSION [Deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'R:\Deadlocks',max_file_size=(1024),max_rollover_files=(3))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

--Autostart if you want
ALTER EVENT SESSION [Deadlocks] ON SERVER 
 WITH (STARTUP_STATE=ON)

 --Start
ALTER EVENT SESSION Deadlocks 
ON SERVER
STATE = START

Answer 3

For deadlock notifications, you have two options that don't rely on tracing. The issue with tracing is that you need a polling process, keep track of what events you have handled (sent emails for) already etc.

One is to re-configure error 1205 (the deadlock error) so it goes to the eventlog (using sp_altermessage), and then have an SQL Server Agent alert for that error message. Agent polls the eventlog every 20 seconds. This is super-easy to do, but you won't get the deadlock graph.

The other is to use the Service Broker infrastructure (instead of Extended Events or old-style tracing). Here's a solution based on that: https://itsalljustelectrons.blogspot.com/2017/06/Handling-SQL-Server-Deadlocks-With-Event-Notifications.html