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] 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. :