Detecting blocked and blocking queries
If a transaction is waiting for some resources because the same resources are locked by other transactions, that transaction is considered a blocked transaction. On the contrary, a transaction that has locked the resources and caused other transactions to wait is considered a blocking transaction.
Long-running transactions can block other transactions and queries for a long time. In a heavily transacted database, many times we face the "blocking" problem. If a transaction is not completed because it is blocked, it can take time to complete, which in turn blocks the other transactions.
In this recipe, we will learn how to find which queries are blocked by which queries, and how to kill those blocking queries, ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
