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 thoughts on “Deadlock Monitoring

  1. 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)

  2. 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.

Leave a Reply