Up to this point, I have not made much mention of deadlocks even though deadlocks are considered by many to be the ultimate in blocking. This has been intentional. In the final analysis, deadlocks are just cases of blocking that form a blocking chain with a cycle. This means that nearly everything that has been covered thus far is applicable to determining the cause and finding a resolution to deadlocks. This may sound a bit simplistic, and in certain respects it is, because deadlock avoidance may require more extensive modification than blocking avoidance. An example of this is reordering access to resources so that they are accessed in the same order so as to prevent deadlocks. However, both blocking and deadlocks can be lessened by holding resources for shorter durations, but neither is completely eliminated, because an increase in the workload could cause the blocking and deadlocking to become more prevalent.
The new deadlock output in SQL Server 2005 is far superior to the output available in previous versions. Collecting the new output does require changes to existing deadlock graph collection scripts, because it is enabled by a new trace flag. This trace flag is 1222. As with the old trace flag, the output is sent to the error log. This output can also be captured in traces, and the Profiler tool can display the deadlock graph in graphical format, which can also be saved as an XML file for more detailed analysis.
Among the improvements in the SQL Server 2005 deadlock output are the inclusion of session state, the start time of the current statement, and the transaction isolation level. Object IDs are also resolved to names when possible. The current statements of each participant are now more detailed, because they include a T-SQL call stack that shows the stored procedures and other objects in the current execution location. In addition, the SQL handle is available, so it can be used to query DMVs related to queries such as sys.dm_exec_sql_text and sys.dm_exec_query_stats.