Friday, January 09, 2009

Debugging Deadlocks in SQL Server Sucks Less These Days

I'm debugging deadlocks in SQL Server 2005. I hate debugging deadlocks.

The good news is that debugging deadlocks in SQL Server 2005 sucks a lot less than it did in SQL Server 2000.

Bart Duncan expresses the difference well:

Turn on trace flag 1222 with "DBCC TRACEON (1222, -1)" or by adding "-T1222" as a SQL startup parameter. This trace flag is a new trace flag in SQL 2005, a much improved version of the tried-and-true -T1204. If you’re running SQL 2005, you should be using 1222 instead of 1204 unless you have deep-seated masochistic tendencies. Alternatives to 1222:
  • If you are using SQL 2000 or SQL 7.0, you’ll have no choice but to fall back on the older -T1204.
  • There’s a "Deadlock graph" Profiler trace event that provides the same info as -T1222. Feel free to use this instead of -T1222 if you’re on SQL 2005. But don’t waste your time with the "Lock:Deadlock" and "Lock:Deadlock Chain" trace events that are in SQL 2000, as they provide an unacceptably incomplete picture of the deadlock.

Hooray!

That quote is taken from the first part in an excellent three-part series on debugging deadlocks in SQL Server 2005:

Also, there's Basic SQL Server Deadlock Debugging over on Tog's Blom.

I also like the Troubleshooting Deadlocking section in Inside Microsoft SQL Server 2005: Query Tuning and Optimization by Kalen Delaney.