Do trace flags 1204 and 1222 give any more information than available via the xml_deadlock_report event in Extended Events?

Question

I am exploring deadlock detection approaches.

1. Trace flags 1204 and 1222
2. Extended events (xml_deadlock_report)

Do the trace flags contain any more data than what is available via the Extended Events xml_deadlock_report event?

Answer 1

Both of these traceflags will just dump the deadlock graph into the SQL Server errorlog and contain no extra information and are just harder to read.

The System Health event trace is already grabbing all deadlock graphs for you and there is a little reason to log them any more, except to keep a history. If you do, use an extended event trace and capture the xml_deadlock_report event and direct to a file target.

This query from Dan Guzman will show you the last 10 deadlocks from the system health trace.

WITH
  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%'
)
, AllSystemHealthFiles AS (
    SELECT 
        REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
    FROM CurrentSystemHealthTraceFile
    )
, DeadLockReports AS (
    SELECT CAST(event_data AS xml) AS event_data
    FROM AllSystemHealthFiles
    CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
    WHERE xed.object_name like 'xml_deadlock_report'
)
SELECT TOP 10 *
, DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
, event_data AS DeadlockReport
FROM DeadLockReports;
GO