Once you have recorded locks and deadlocks with SQL Deadlock Detector™ , you can immediately identify terminated processes in a deadlock event and the SQL statements responsible for the termination of those processes.
Step 1 of 4: When you first connect to a monitored SQL Server, you will see a list of locks and deadlocks in the event monitor, sorted by date and time.
Step 2 of 4: Locate and expand a deadlock event. To identify the terminated process in the deadlock, locate and highlight the SPID that is crossed out. A crossed out SPID indicates a terminated process in an event.
Step 3 of 4: In the process details window to the right of the “Lock Dependencies” diagram, identify the “Blocker SPID” for the terminated process.
Step 4 of 4: To identify the SQL statement responsible for the termination of the process, locate and highlight the record of the blocker SPID then click on the “SQL Statement” tab below the event monitor.
Being able to identify blocking code and SQL statements responsible for terminating another process is essential in solving a deadlock. SQL Deadlock Detector™ allows you to easily locate offending code, as well as other code and objects participating in a lock or deadlock event.
Optimizing code and tuning indexes mitigate problematic locks and can help eliminate the occurrence of deadlocks. For example, fine tuning SQL statements or stored procedures identified to block other processes reduces wait time. Tuning indexes, on the other hand, improves not only query execution time but also efficiency in managing locks by SQL Server.