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.
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.
The 12-page whitepaper “Troubleshooting Locking with DMVs” 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.
Click here to read the whitepaper.
The author, Robert L. Davis, was a senior database administrator and technical lead at Microsoft. He was a speaker and a trainer as well as a writer for SQL Server Magazine and co-authored "Pro SQL Server 2008 Mirroring" (Apress).
Monitor the performance of SQL Server for physical, virtual, and cloud environments with SQL Diagnostic Manager with proactive alerting, prescriptive analysis, and comprehensive reporting.
View the infographic “Why Use SQL Diagnostic Manager”, read a case study, browse the datasheet, download a fully functioning 14-day trial, request a one-on-one product demonstration, and request a price quotation.