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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.