Monday, 11 March 2013

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:
  <victimProcess id="processNumber"/>
<deadlock victim="processNumber"/>
Basically we want the file content to have this structure:
  <deadlock victim="...">
  . . .
  . . .
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

--  Create a service broker service to receive the events and route to the queue
CREATE SERVICE DeadlockService
ON QUEUE DeadlockQueue ([])

-- Create the event notification for capturing deadlock graphs and send to the service
TO SERVICE 'DeadlockService', 'current database' ;

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 ;

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
FROM DeadlockQueue ;

SELECT  @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)[1]') AS XML
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


No comments: