Search Wiki:

Troubleshooting Deadlocks in SQL Server


Jonathan Kehayias, March 17, 2008

One of the more difficult/frustrating things to isolate and eliminate in SQL Server are deadlocks. In all but the rarest exception, deadlocks are a side effect of blocking and not a SQL Server Bug. They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur. In some cases, there may be more than just two statements that contribute to a deadlock. The methods available to troubleshoot deadlocks in SQL Server differ between SQL Server 2000 and SQL Server 2005.



SQL 2000 Trace Flags


Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks. The output of these flags can be sent to the Errorlog with flag 3605, or to your query analyzer 3604. You will also need to combine any of these flags with the -1 flag which activates them for all of the connections in SQL Server.

An Example statement to enable tracing to the ErrorLog for all connections:

DBCC TRACEON (1204, 3605, -1)
DBCC TRACEON (1205, 3605, -1)

The output of these flags can seem very cryptic however, it doesn't take much to figure out how to read them. Bart Duncan has provided a wonderful detail of the 1204 output at http://blogs.msdn.com/bartd/attachment/747119.ashx.

Return to Top


SQL 2005 Trace Flags

SQL 2005 also supports the same trace flags that existed in SQL 2000. Added to the list however is Trace Flag 1222 which is far superior to the previous flags. In general, the 1222 flag provides enough details in it to prevent you from having to use any further commands to identify where the problem exists. It includes detailed lock data including the object being locked, the type of lock being held for each node, along with the actual executing SQL Statement that is causing the locking.

An Example statement to enable tracing to the ErrorLog for all connections in 2005 is :

DBCC TRACEON (1222, 3605, -1)

Once again, Bart Duncan has provided excellent information on decifering the output of this trace flag. His series on Deadlock elimination in SQL 2005 is located http://blogs.msdn.com/bartd/archive/2006/09/13/Deadlock-Troubleshooting_2C00_-Part-2.aspx

Return to Top


SQL Profiler


The SQL Profilercan also be used to help in identifying deadlocking. It does not however, output in SQL 2000 the specific page information necessary to isolate the exact problem. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.

Locks - Lock:Deadlock
Locks - Lock:Deadlock Chain
Stored Procedure - SP:StmtStarting
TSQL - SQL:StmtStarting

When a deadlock is encountered, the Deadlock chain event will output the SPID's associated with the deadlock. From there, you can look at the StmtStarting event for the SPID's immediately following the Deadlock Event to isolate the problem statements.

In SQL 2005 the same trace events listed in the SQL 2000 section can be used for deadlock identification. SQL 2005 however, adds to these events the newer Locks - Deadlock Graph event which occurs with the Locks:Deadlock event, and provides an XML output of the deadlocking events. This XML output is displayed graphically in SQL Profiler and helps you to better understand what is happening that resulted in the deadlock.

Additional Resources


http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2733075&SiteID=1
Last edited Apr 8 2008 at 6:39 PM  by JonathanKehayias, version 4
Updating...
Page view tracker