Jan 23 2012

Deadlock Monitoring

There are four different ways you can get information about deadlocks in your system. These are:

  • traceflag 1204
  • traceflag 1222
  • trace events
  • extended events

For years I’ve been pushing traceflag 1222 as the best of the lot. Well, that’s over. I’ve been learning more and more about extended events and I’m currently in love with xml_deadlock_report event. Why? Simple, it has everything that traceflag 1222 has, but there are two glorious things about it.

First, it’s not going to be filling up my error log with, for the error log, noise. Seriously. As much as I liked the information displayed from traceflag 1222, I didn’t like what it did to the log, but I saw it as a necessary evil.

Second, it’s XML baby! That means you can set up queries to pull information out if you need to. Yeah, I know you can hop through hoops to set up the text from the error log for querying, but, that’s nothing like pointing to the file and referencing the nodes. Way too cool.

On top of that, for simple deadlocks, the graphical version of the deadlock graph gives you most of what you need to quickly identify the issue. You absolutely don’t get that with the traceflag.

Nope. I’m sold more than ever on extended events. If you haven’t started exploring them, I strongly recommend you do. Especially with SQL Server 2012 just around the corner. The functionality around extended events there completely makes these things accessible in ways they weren’t before.

6 Comments

  • By WIDBA, January 23, 2012 @ 10:00 am

    Looking forward to what you come up with for mining. Here was my first shot at it awhile back. Being a complete hack with XQuery, I got a few bits of data out.

    http://widba.blogspot.com/2011/03/deadlocks-there-are-too-many-of-them.html

  • By Grant Fritchey, January 23, 2012 @ 11:02 am

    Nice work. I haven’t started playing with parsing it yet. I’m so excited about having the capability easily accessible.

  • By Chris Harshman, January 23, 2012 @ 1:45 pm

    I agree that extended events look promising for a number of things in SQL Server. How do you get notifications when a deadlock happens? I’ve been using the service broker technique like the one here for some time and it typically works well:

    http://weblogs.sqlteam.com/mladenp/archive/2008/07/18/Immediate-deadlock-notifications-without-changing-existing-code.aspx

    Personally I’ve tried to spend more time avoiding deadlocks than dealing with them after the fact. Using row versioning has helped tremendously. (going from averaging over 250 deadlocks a month to less than 1 a month)

  • By Antony, January 24, 2012 @ 8:23 am

    Is it the same XML you get from the WMI deadlock event? I use SQL Agent jobs triggered by the WMI DEADLOCK_GRAPH event and the token-replacement to capture the graphs and log them to a table for analysis – and that works on 2005 as well.

  • By Grant Fritchey, January 24, 2012 @ 10:02 am

    I wasn’t aware of a wmi event, the so that makes one more. It’s probably the same xml.

Other Links to this Post

  1. Use Your Tools « Voice of the DBA — February 15, 2012 @ 11:28 am

RSS feed for comments on this post. TrackBack URI

Leave a comment