sqlskills-logo-2015-white.png

Getting historical deadlock info using extended events

In the TechNet Magazine article I wrote on Advanced Troubleshooting with Extended Events, I mentioned the always-on event session called system_health. Jonathan Kehayias, a fellow MVP and blogging mad-man, posted a great article with SQL Server Central today about how to get historical deadlock graph info from it. His article explains some of the pre-reqs for doing this (like installing 2008 RTM CU1 – see KB 956717 – to get it working and a bug with the XML output) and gives some code.

After wrestling with the download of CU1 (it took 5 tries to get it to download!), I got it installed in one of my VPCs and gave Jon's code a whirl. I forced a deadlock by creating two tables, locking them from separate transactions and then trying to select from the other table. See the code below:

— Connection 1:
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);
GO

BEGIN TRAN
UPDATE t1 SET c1 = 2;
GO

— Connection 2:
CREATE TABLE t2 (c1 INT);
INSERT INTO t2 VALUES (1);
GO

BEGIN TRAN
UPDATE t2 SET c1 = 2;
GO

— Connection 1:
SELECT * FROM t2;
GO

— Connection 2:
SELECT * FROM t1;
GO

And one of them will be killed by the deadlock monitor.

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now running Jon's (slightly reformatted) code:

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
    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';

Gives the XML deadlock graph (which unfortunately can't be displayed graphically because the format differs from the Profiler/trace-flag output)

<deadlock-list>
  <deadlock>
    <victim-list>
      <victimProcess id="process53d9000" />
    </victim-list>
    <process-list>
      <process id="process53d9000" taskpriority="0" logused="256" waitresource="RID: 1:1:304:0" waittime="331" ownerId="1868" transactionname="user_transaction" lasttranstarted="2009-02-23T13:32:05.100" XDES="0x4eb8c10" lockMode="S" schedulerid="1" kpid="2216" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2009-02-23T13:32:33.093" lastbatchcompleted="2009-02-23T13:32:05.100" clientapp="Microsoft SQL Server Management Studio – Query" hostname="CHICAGO" hostpid="2552" loginname="CHICAGO\Administrator" isolationlevel="read committed (2)" xactid="1868" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="" line="1" sqlhandle="0x02000000c70b7b1daba27f2ddf3e772600949464d524b6f2" />
        </executionStack>
        <inputbuf>
SELECT * FROM t1;
    </inputbuf>
      </process>
      <process id="process4985558" taskpriority="0" logused="256" waitresource="RID: 1:1:294:0" waittime="10181" ownerId="1877" transactionname="user_transaction" lasttranstarted="2009-02-23T13:32:08.067" XDES="0x4eb9be0" lockMode="S" schedulerid="1" kpid="2020" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2009-02-23T13:32:23.240" lastbatchcompleted="2009-02-23T13:32:08.067" clientapp="Microsoft SQL Server Management Studio – Query" hostname="CHICAGO" hostpid="2552" loginname="CHICAGO\Administrator" isolationlevel="read committed (2)" xactid="1877" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="" line="1" sqlhandle="0x02000000f4d34532698b7ad324df813feb2ba5024730cb79" />
        </executionStack>
        <inputbuf>
SELECT * FROM t2;
    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <ridlock fileid="1" pageid="304" dbid="1" objectname="" id="lock47aea80" mode="X" associatedObjectId="72057594039042048">
        <owner-list>
          <owner id="process4985558" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process53d9000" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="294" dbid="1" objectname="" id="lock47af200" mode="X" associatedObjectId="72057594038976512">
        <owner-list>
          <owner id="process53d9000" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process4985558" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>
  </deadlock>
</deadlock-list>

Cool!

14 thoughts on “Getting historical deadlock info using extended events”

  1. When I try ” Jon’s (slightly reformatted) ” against a SQL Server 2008 R2 db (Microsoft SQL Server Management Studio 10.50.4000.0) I get the following error.

    Msg 9436, Level 16, State 1, Line 1
    XML parsing: line 5, character 15, end tag does not match start tag

    Reply

  2. Hi Paul,

    While tried above script it’s not showing the Deadlock information.
    The fields are empty. Please help me to find Dead lock history.
    I made one Deadlock and ran above script and it’s not showing any information.
    My SQL server Version is 2008R2

    Reply

  3. XML parsing: line 5, character 15, end tag does not match start tag

    Can you pls help with this error?

    original SQL
    select CAST(
    REPLACE(
    REPLACE(XEventData.XEvent.value(‘(data/value)[1]’, ‘varchar(max)’),
    ”, ”),”,”)

    as xml) as DeadlockGraph
    FROM
    (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’) AS Data
    CROSS APPLY TargetData.nodes (‘//RingBufferTarget/event’) AS XEventData (XEvent)
    where XEventData.XEvent.value(‘@name’, ‘varchar(4000)’) = ‘xml_deadlock_report’

    Reply

  4. At an OLTP table with a lot of concurrent inserts and also heavy computing queries there are many blocking and deadlock in the database.
    The above table has 900 million records and READ_COMMITTED_SNAPSHOT = ON at the database. (The tempdb database is optimal)
    The cause of the deadlock is concurrency between inserts and also the heavy computing queries consumes server resources.
    The server has 128 GB RAM and a strong CPU.

    How can I solve this problem?
    Thanks!

    Reply

    1. We’d need to see what’s going on. Sounds like you could with some consulting help for your environment with all the problems you’ve been leaving comments on – let us know if you’re interested.

      Reply

  5. Thanks!
    I need to talk about your suggestion with the manager. but:
    In general: What’s an OLTP system?
    According to Microsoft, an OLTP system is a system with a lot of concurrent inserts or heavy calculation queries. my system has both conditions.
    Even though I can’t archives the data because the system cannot withstand the latency, can be archiving be a solution?
    My main question is that :
    At an OLTP table, due to the many insertions, the table is locked for a long time and other queries are in trouble.
    How to overcome this problem?

    Reply

  7. When the sql statement contains inequality condition then it cannot be CAST to xml since “<" is a special character in xml. How can I work around with that. Please help.

    Reply

      1. Thanks for your reply !!
        Tried this one. It works but gives blank results. I know there are deadlocks because system health xevent shows them. May be it will take a few more tries and tweaking …

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course