Robp avatar image
Robp asked

Finding where a deadlock query is coming from

In a deadlock graph we're given information that shows the deadlock victim and the victor, which is great if you've memorized where ever single query comes from. However, I'm like most people and have no idea where the origins of those queries are located so I can try to fix it if it's a scheduling conflict or a bad query design or whatever. It may be in, for example, a job, some other process from another server, some third party application's process, wherever... I dont know if a trace will solve finding the orgin of either the deadlock victim or victor, but are there any tools or tricks of finding where those queries originate from?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Tom Staab avatar image
Tom Staab answered
Good timing for this question. Just yesterday, I was running some scripts looking at deadlock extended events in our dev environment. There are some good articles online by Paul Randal, Brent Ozar and others. This was the first one that I found, and I modified it for my situation: [][1] Since I didn't already have an extended event session dedicated to deadlocks, I used the default system health session. SELECT CreationDate = xed.value('@timestamp', 'datetime') , CreationDateLocal = DATEADD(minute, DATEDIFF(minute, SYSUTCDATETIME(), SYSDATETIME()), xed.value('@timestamp', 'datetime')) , ExtendedEvent = xed.query('.') FROM ( SELECT TargetData = CONVERT(xml, target_data) FROM sys.dm_xe_sessions xs INNER JOIN sys.dm_xe_session_targets xt ON xt.event_session_address = xs.address WHERE = N'system_health' AND xt.target_name = N'ring_buffer' ) XMLData CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') XEventData(xed) ORDER BY CreationDate DESC ; The XML data will include the SQL statement, hostname, login, application, etc. [1]:
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Robp avatar image Robp commented ·
This really helps! Can you explain what the CreationDate and CreateionDateLocal mean? I was thinking that CreationDateLocal could mean the time the record is created after I ran the query, but those times seem to be off by a few minutes and I get my return results back in less than a second.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
The timestamp of the deadlock event is stored as a UTC time. The local time just converts that to the time zone of the server.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.