23Jan 2012 by Grant Fritchey 6 Comments

Deadlock Monitoring

T-SQL
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…
Read More
07Mar 2011 by Grant Fritchey 1 Comment

Execution Plan Stability

SQL Server
I’ve talked before about one of the primary things that the Query Optimizer team at Microsoft tries to avoid, regressions. Basically, they want a plan that worked well in SQL Server 2005 to work well in SQL Server 2008 R2. What’s more, they want everything to work well between service packs, updates and cumulative updates. Ever wonder how they do it? Well, they cheat. OK, that’s mean and not entirely accurate, but it gets the idea across. No, what they do is, identify when they have a breaking change, when they’ve got a special cumulative update or service pack that fixes some bad behavior, but that can cause plans to “break,” they wall it off. Note, in most cases, this “breakage” is actually a question of plans working correctly, but…
Read More