By Kalman Toth, M.Phil., M.Phil., MCDBA

December 17, 2005

When I was working at Swiss Bank, 48th St. and Park Avenue in Manhattan, as a database developer in Sybase for repo reconciliation, the entire development group was welcomed in the morning with a bunch of deadlock message emails from the various database servers: Report X was a deadlock victim at 4:04 am, Report Z was a deadlock victim at 5:44 am, and so on. The responsible group member had to rerun that report manually after arrival in the morning.

Pre SQL 2005 versions you got the following message if deadlock occurred in the server log:

Server: Msg 1205, Level 13, State 50, Line 3
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The trouble was that a DBA had no idea what was running in process id 52 or what report query or stored proc was involved without some heavy-duty additional logging and investigation. Even more troublesome, the other process is not even identified by Process ID.

SQL Server 2005 Profiler has a brand new deadlock visualization chart feature. When you position the cursor on the deadlock trace line, the chart appears automatically. The victim process is crossed over with a big X. All the queries are listed which were parties to the deadlock.

On the Performance Tuning CD a unit is dedicated to Deadlock Visualization and Avoidance. I managed to create a 3-way deadlock. The deadlock visualization graph has perfectly represented the situation.

Since deadlocks cannot be avoided completely, the deadlock visualization feature adds a very powerful tool to the arsenal of a SQL 2005 DBA.