Whitepaper : Troubleshooting Locking with DMVs

Database professionals are often called upon to investigate performance problems related to locking, but most investigations into locking rarely go beyond checking for blocks or investigating deadlocks. It is not very often that database professionals try to get a deeper understanding of locks that are occurring.

The simplest way to think of the relationship between locking, blocking, and deadlocks is that locking causes blocking, and blocking causes deadlocks.

Locks are a logical mechanism for controlling access to an object. Locks control concurrent access to an object for non-conflicting operations (such as reading data rows) and prevent concurrent access to an object for conflicting operations (such as updating the same data rows).

Blocks are incidents where one query is waiting for a resource that another query is consuming. The most common scenario is caused by conflicting locks for objects. Blocks may be caused by other conflicts, such as a wait for memory grants.

Deadlocks are blocking scenarios where two or more queries are blocking each other while waiting for locks held by another participating query. The most common situation is for two queries that each hold locks for which the other query is waiting. Deadlocks may involve many queries that involve a circular locking chain that results in a deadlock. While deadlocks are a product of blocking, the blocking may be caused by resource waits other than locks for objects, such as waits for memory grants.

When troubleshooting deadlocks, it is necessary to look at the blocking that generated the deadlocks. It is essential to look at the locks that were being held during the participating transactions to understand blocking (that resulted from locks) that causes deadlocks.

This whitepaper outlines some techniques using Dynamic Management Views (DMVs) for understanding the full impact of the locks generated by SQL queries. The whitepaper also shows how to combine the transaction locks DMV with the execution DMVs to capture a snapshot to give the complete picture of locking activity hitting a specific table. Benefit from understanding the whole picture around locking generated by queries. Realize the full impact of queries, especially those with broad search criteria, Use the techniques described in this whitepaper to look at the total locks generated by queries. When lock contention occurs, grab a snapshot of locking activity, by using the transaction locks and execution DMVs to investigate queries that cause or experience lock contention. Consequently, do more than merely kill blocking queries. Instead, take a proactive approach to prevent future contention for the locks on objects.