0

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?

2

1 Answer 1

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
1
  • The deadlock graph does capture the query that holds the lock. It just captures the process that holds the lock and the query shown is the current query from input buffer.
    – variable
    Sep 9, 2021 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.