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.
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
Nice work. I haven’t started playing with parsing it yet. I’m so excited about having the capability easily accessible.
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)
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.
I wasn’t aware of a wmi event, the so that makes one more. It’s probably the same xml.
[…] SQL Server 2008 R2 or SQL Server 2012 T-SQL changes where I can. I noticed Grant Fritchey recently talking about Extended Events and the advantages of using them with deadlocks, and also using them for performance tuning. Learning Extended Events is an area I need to spend […]