April 7, 2016 at 6:51 am
I'm trying to schedule a query to alert us when a deadlock occurs. My research has led me to extended events and the below query. I cant get the below to return any deadlock xml. i see the deadlocks in the log and sys health event file. Just not in the ring buffer. I recycled the sql logs. Would that have something to do with it? I have both 1204 and 1222 enabled
SELECT
xed.value('@timestamp', 'datetime') as Creation_Date,
xed.query('.') AS Extend_Event
FROM
(
SELECT CAST([target_data] AS XML) AS Target_Data
FROM sys.dm_xe_session_targets AS xt
INNER JOIN sys.dm_xe_sessions AS xs
ON xs.address = xt.event_session_address
WHERE xs.name = N'system_health'
AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC
April 7, 2016 at 6:55 am
im reading that 1204 needs to be activeated for all sessions in order to get the XML graph. At the moment i've got it set to sessions = 0. If i enable this for all sessions will there be a larger performance hit on the server?
April 7, 2016 at 7:07 am
You don't need any traceflags for deadlocks to show up in system health.
1204 was the old SQL 2000 traceflag for deadlock graphs, it shouldn't be used on any version since then. 1222 completely replaced it on SQL 2005 and above.
Ring buffer is short-lived. The deadlocks could be getting thrown out by other events before your query runs. You can always create a deadlock XE session yourself and have no other events in it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2016 at 10:12 am
Not sure where you're getting that traceflags have to be set to work with extended events. As Gail says, that isn't true.
If I were going to use extended events to generate an alert, I'd strongly suggest setting up a session all on its own, as Gail says. You want to be able to exercise control over how it works, and you just won't be able to using system_health.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply