Wednesday, June 19, 2013

Query to list Deadlocks thru Extended Event

I have been used to turning on the Trace flags 1222 or 1204 to get information on the deadlocks.Depending upon the type of environment I would either have them turned on at start-up or on demand  in response to errors that were logged in previous night's batch log file and look for this behavior to recur.
On top of this, if I need to dig in a bit further, I would turn on the Profiler trace.

         SQL Server 2008 onwards,there are extended events that runs a default lightweight session that captures deadlock info (and a bunch of others see Jonathan Kehayias article) in a ring buffer and stores it in memory.We could query the Extended Event DMVs to fetch deadlock info from the memory.This is especially awesome since we do not have to turn on the trace flag or profiler.
        There is a very informative article by Jonathan Kehayias on Extended Event for deadlocks.My query has been built on Jonathan's query.

      There are a few changes I had made.I have listed the deadlock timestamp alongside the XML.
Also, I faced an issue with the query running forever in certain 2008\ R2 instances whereas in others it just worked fine.I am not sure if this is a bug. However,I was able to workaround this by moving the inline table's output to a variable (or a temporary table) .

Since I have to be able to capture this info in environments with varying versions and service packs I have added a TRY CATCH block to make sure that the query worked irrespective of whether the issue with the missing tag has been fixed or not.

DECLARE @deadlock_info XML, 
        @last_n_days   INT = 10; 

SELECT @deadlock_info = Cast(target_data AS XML) 
FROM   sys.dm_xe_session_targets st 
       JOIN sys.dm_xe_sessions s 
         ON s.address = st.event_session_address 
WHERE  name = 'system_health' 

BEGIN try 
    SELECT Cast(xeventdata.xevent.value('(data/value)[1]', 
                       'varchar(max)') 
                       AS XML) 
           AS 
           DeadlockGraph, 
           xeventdata.xevent.value('@timestamp', 'datetime2' 
           ) AS 
           Error_Timestamp 
    FROM   (SELECT @deadlock_info AS TargetData) AS 
           deadlock_info 
           CROSS apply targetdata.nodes ( 
                       '//RingBufferTarget/event') AS 
                       XEventData 
                                    ( 
           xevent) 
    WHERE  xeventdata.xevent.value('@name', 'varchar(4000)') 
           = 
                  'xml_deadlock_report' 
           AND xeventdata.xevent.value('@timestamp', 
               'datetime2' 
               ) > ( 
               Getutcdate() - @last_n_days ) 
END try 

BEGIN catch 
    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, 
           xeventdata.xevent.value('@timestamp', 'datetime2' 
           ) 
           AS 
           Error_Timestamp 
    FROM   (SELECT @deadlock_info AS TargetData) AS 
           deadlock_info 
           CROSS apply targetdata.nodes ( 
                       '//RingBufferTarget/event') AS 
                       XEventData 
                                    ( 
           xevent) 
    WHERE  xeventdata.xevent.value('@name', 'varchar(4000)') 
           = 
                  'xml_deadlock_report' 
           AND xeventdata.xevent.value('@timestamp', 
               'datetime2' 
               ) > ( 
               Getutcdate() - @last_n_days ) 
END catch 

References
Jonathan Kehayias Article
Bart Duncan's SQL Weblog 

1 comment:

  1. Aravind, its really very nice script and your first extended events script.

    I am searching for the script like this whenever there is a deadlock in my application.

    Please continue your good work and persons like me will benefit for sure.

    Thanks
    Senthil

    ReplyDelete