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?
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: [
http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx][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 xs.name = 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]:
http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx
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.
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.
18 People are following this question.
