Deadlock Troubleshooting

Deadlock troubleshooting has always been a bit tricky. A deadlock is a special type of blocking scenario, but blocking and deadlocking are not the same thing. Deadlocks are a natural side effect of blocking. The most typical deadlock solution is either a stored proc/app code optimization, or a change in the database schema or indexing strategy.

To capture a deadlock graph in the SQL Server ERRORLOG, use the following trace flags:

• For SQL Server 2000: 1204 & 3605
• For SQL Server 2005: 1112

Refer Bart Duncan's Weblog for deadlock troubleshooting. Bart was an Escalation Engineer for SQL Server and his blog article spanning three parts for deadlock troubleshooting is probably the most definitive guide for this topic.

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

The -T1204 output can be interpreted using the following file: http://blogs.msdn.com/bartd/attachment/747119.ashx

If you find that your SQL Server is encountering deadlocks, then what data do you need to collect:

1. SQL Server Profiler traces with the following events:

• All statement level events
• Showplan All events because the statement that is chosen as the deadlock victim will not have a Statistics Profile Event as it would have been terminated
• Lock: Deadlock and if you are using SQL Server 2005, then capture Deadlock Graphs as well

2. Perfmon Logs

3. SQL 2000 Blocker Script output/SQL 2005 PerfStats

Additionally, if as a developer if you are concerned about your statements being terminated due to deadlocks, then you must build a retry logic into your code for handling the deadlock and resubmitting the query. This can be done using a try-catch block withing the query batch that you are executing.