SQL Deadlock Detector™ allows you to easily identify causes of long wait times that slow down your database application’s performance.
Step 1 of 5: Click on the Lock/Deadlock Filter toolbar button (labeled “All”) at the top of the event monitor grid.
Step 2 of 5: In the Filter Locks/Deadlocks dialog, tick on the “Hide locks with duration less than” checkbox, and specify the wait time (ms) threshold for lock events you wish to display in the event monitor. Click on OK.
Step 3 of 5: Click on “Expand all locks/deadlocks” toolbar button at the top left of event monitor. Examine the “Application Name” column and locate lock events for the application in question.
Step 4 of 5: In the process details window to the right of the “Lock Dependencies” diagram, identify the “Blocker SPID” for the selected lock event.
Step 5 of 5: To identify the cause of the long wait time for the selected event, locate and highlight the record of the blocker SPID then click on the “SQL Statement” tab below the event monitor.
Being able to identify causes of long running locks is necessary in optimizing your database application’s performance. SQL Deadlock Detector™ allows you to easily locate code responsible for long running locks, as well as tables and views accessed by blocking and waiting processes.
Optimizing code and tuning indexes mitigate the occurrence of long running locks that slow down performance. For example, fine tuning SQL statements or stored procedures identified to block other processes significantly reduces wait time for blocked query executions. Tuning indexes also improves query execution, allowing for less time required to release locks that block waiting processes.