Deadlock Graph

Find a Deadlock Graph:

SELECT CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph

FROM (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets st
 INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
 WHERE NAME = 'system_health'
 ) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Parse Deadlock graph:

DECLARE @deadlock TABLE (
 DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED
 ,DeadlockGraph XML
 );

INSERT INTO @deadlock
VALUES ('');

-- insert the deadlock XML in the above line! Duplicate as necessary for additional graphs.
WITH CTE
AS (
 SELECT DeadlockID
  ,DeadlockGraph
 FROM @deadlock
 )
 ,Process
AS (
 -- get the data from the process node
 SELECT CTE.DeadlockID
  ,
  --[DeadlockTime]=
  CTE.[DeadlockGraph]
  ,[Victim] = CASE 
   WHEN Deadlock.Process.value('@id', 'varchar(50)') = Deadlock.Process.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)')
    THEN 1
   ELSE 0
   END
  ,[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)')
  ,[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)')
  ,[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)')
  ,[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)')
  ,[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime')
  ,[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime')
  ,[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime')
  ,[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
  ,[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)')
  ,[SPID] = Deadlock.Process.value('@spid', 'int')
  ,-- server process id
  [SBID] = Deadlock.Process.value('@sbid', 'int')
  ,-- server batch id
  [ECID] = Deadlock.Process.value('@ecid', 'int')
  ,-- thread id of given SPID (0 is always the parent)
  [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)')
  ,[WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)')
  ,[LogUsed] = Deadlock.Process.value('@logused', 'int')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)
 )
 ,ExecutionStack
AS (
 -- get the data from the executionStack node
 SELECT CTE.DeadlockID
  ,ProcessID = Execution.Stack.value('../../@id', 'varchar(50)')
  ,Code = Execution.Stack.value('.', 'varchar(1000)')
  ,ProcName = Execution.Stack.value('@procname', 'sysname')
  ,Line = Execution.Stack.value('@line', 'int')
  ,RN = row_number() OVER (
   PARTITION BY CTE.DeadlockID
   ,Execution.Stack.value('../../@id', 'varchar(50)') ORDER BY (
     SELECT 1
     )
   )
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process/executionStack/frame') AS Execution(Stack)
 )
 --SELECT * FROM ExecutionStack
 ,PageLock
AS (
 -- get the data from the pagelock node
 SELECT DeadlockID
  ,ObjectName = PageLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = PageLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Page'
  ,LockMode = PageLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/pagelock/owner-list/owner') AS PageLock(Process)
 )
 ,KeyLock
AS (
 -- get the data from the keylock node
 SELECT DeadlockID
  ,ObjectName = KeyLock.Process.value('../../@objectname', 'varchar(200)') + '.' + KeyLock.Process.value('../../@indexname', 'varchar(200)')
  ,-- get the index name also
  ProcessID = KeyLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Key'
  ,LockMode = KeyLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/keylock/owner-list/owner') AS KeyLock(Process)
 )
 ,RidLock
AS (
 -- get the data from the ridlock node
 SELECT DeadlockID
  ,ObjectName = RIDLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = RIDLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'RID'
  ,LockMode = RIDLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/ridlock/owner-list/owner') AS RIDLock(Process)
 )
 ,ObjectLock
AS (
 -- get the data from the objectlock node
 SELECT DeadlockID
  ,ObjectName = ObjectLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = ObjectLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Object'
  ,LockMode = ObjectLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/objectlock/owner-list/owner') AS ObjectLock(Process)
 )
-- combine all the data together, and display in
SELECT Process.[DeadlockID]
 ,Process.[SPID]
 ,Process.[SBID]
 ,Process.[ECID]
 ,Process.[DeadlockGraph]
 ,-- include the graph - can click and open in separate window
 Process.[ProcessID]
 ,Process.[Victim]
 ,[LockedType] = coalesce(PageLock.LockType, KeyLock.LockType, RIDLock.LockType, ObjectLock.LockType)
 ,[LockMode] = coalesce(PageLock.LockMode, KeyLock.LockMode, RIDLock.LockMode, ObjectLock.LockMode)
 ,[LockedObject] = coalesce(PageLock.ObjectName, KeyLock.ObjectName, RIDLock.ObjectName, ObjectLock.ObjectName)
 ,[Procedure #] = es.RN
 ,es.[ProcName]
 ,es.[Line]
 ,es.[Code]
 ,Process.[ClientApp]
 ,Process.[HostName]
 ,Process.[LoginName]
 ,Process.[TransactionTime]
 ,Process.BatchStarted
 ,Process.BatchCompleted
 ,Process.[InputBuffer]
 ,Process.[IsolationLevel]
 ,Process.WaitResource
 ,Process.LogUsed
FROM Process
INNER JOIN ExecutionStack es ON es.ProcessID = Process.ProcessID
 AND es.DeadlockID = Process.DeadlockID
LEFT JOIN PageLock ON PageLock.ProcessID = Process.ProcessID
 AND PageLock.DeadlockID = Process.DeadlockID
LEFT JOIN KeyLock ON KeyLock.ProcessID = Process.ProcessID
 AND KeyLock.DeadlockID = Process.DeadlockID
LEFT JOIN RIDLock ON RIDLock.ProcessID = Process.ProcessID
 AND RIDLock.DeadlockID = Process.DeadlockID
LEFT JOIN ObjectLock ON ObjectLock.ProcessID = Process.ProcessID
 AND ObjectLock.DeadlockID = Process.DeadlockID
ORDER BY Process.DeadlockID
 ,Process.victim DESC
 ,-- show the victim first
 Process.ProcessID
 ,Process.ECID
 ,-- show in thread order by SPID
 es.RN;-- execution stack order