Capturing Deadlock Info in SQL Server with System Health and Event Notification

System Health
SQL Server 2008 introduced a new way to capture deadlock information, which is by using system_health event session in Extended Events. This is run by default in the server. To see recent deadlocks that have occurred, run this query:

WITH SystemHealth
 AS (
 SELECT CAST(target_data as xml) AS TargetData
 FROM sys.dm_xe_session_targets st
 JOIN sys.dm_xe_sessions s
 ON s.address = st.event_session_address
 WHERE name = 'system_health'
 AND st.target_name = 'ring_buffer')
 
 SELECT XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS DeadLockGraph
 FROM SystemHealth
 CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
 WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'

Copy the content of each row and save into an .XDL file then open the file on SQL Management Studio. It happened to me that I received this message "The 'victim-list' start tag on line 1 does not match the end tag of 'deadlock' ..." when SSMS is trying to open the file. When I checked the file, there are two nodes immediately after the <deadlock-list> node that do not match. Not sure why they are generated incorrectly. Then I changed those nodes:

<victim-list>
  <victimProcess id="processNumber"/>

to

<deadlock victim="processNumber"/>

Basically we want the file content to have this structure:

<deadlock-list>
  <deadlock victim="...">
    <process-list>
  . . .
 </process-list>
    <resource-list>
  . . .
 </resource-list>
  </deadlock>
</deadlock-list>

Now I could see a deadlock graph is displayed graphically.



Service Broker Event Notification
In SQL Server 2005 or above, we can also use Event Notification to capture deadlock information. We can send the event to a service and then to a queue. Below are the scripts to setup the event notification, service and queue:

-- Need to use a broker enabled database
USE msdb;

--  Create a service broker queue to hold the events
CREATE QUEUE DeadlockQueue
GO

--  Create a service broker service to receive the events and route to the queue
CREATE SERVICE DeadlockService
ON QUEUE DeadlockQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Create the event notification for capturing deadlock graphs and send to the service
CREATE EVENT NOTIFICATION CaptureDeadlocks
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE 'DeadlockService', 'current database' ;
GO 

Each time a deadlock happen, the DeadlockQueue will be added.

USE msdb ;

-- try to see DeadlockQueue data
select * from DeadlockQueue

Then to see the contending queries of the first deadlock before one of them is chosen as a deadlock victim:

USE msdb;
-- Cast message_body to XML and query deadlock graph from TextData
SELECT  message_body.value('(/EVENT_INSTANCE/TextData/
                                  deadlock-list)[1]', 'varchar(max)')
                                  AS DeadlockGraph
FROM    ( SELECT    CAST(message_body AS XML) AS message_body
          FROM      DeadlockQueue
        ) AS sub ;
GO

To get the xml data of the deadlocks one by one:

DECLARE @message_body XML ;

RECEIVE TOP(1) -- just handle one message at a time
@message_body=message_body
FROM DeadlockQueue ;

SELECT  @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)[1]') AS XML
GO

Then we can save it as an .XDL file and open in SSMS.

After we have done, drop the instances

drop event notification CaptureDeadlocks on server

drop service DeadlockService

drop queue DeadlockQueue

Example scripts to create a deadlock
Below is an example of how to create a deadlock. We just need to pick two tables and open two SSMS query windows to try these.

-- open window 1 and run this
begin tran
select top(1) Name, 'first window' from TableOne with (xlock);
-- then wait for a while

-- open window 2 and run this
begin tran
SELECT top(1) Name, 'second window' from TableTwo with (xlock);
SELECT top(1) Name, 'second window' from TableOne with (xlock);
-- then wait for a while

-- back to window 1 and run this
select top(1) Name, 'first window' from TableTwo with (xlock);
-- wait for a while, a deadlock should happen

References:
https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx