Databases Reference
In-Depth Information
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, as part of the immediate solution!
Getting ready
As we are going to see how to find blocked and blocking queries, we will first create a scenario
so that we can create a blocking query.
The following are the prerequisites for this recipe:
F
An instance of SQL Server 2012 Developer or Enterprise Evaluation edition.
F
A sample
AdventureWorks2012
database on the SQL Server instance. For more
details on how to install the
AdventureWorks2012
database, please refer to the
Preface
of this topic.
How to do it...
The following are the steps for detecting blocked and blocking queries:
1.
Start SQL Server Management Studio and establish a connection to the SQL Server
hosting the
AdventureWorks2012
database.
2.
In the query window (Connection-1), type the following T-SQL statements and then
execute them, to begin a transaction from Connection-1:
--Execute this script from Connection-1
USE AdventureWorks2012
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
--Beginning a transaction.
BEGIN TRANSACTION
--Fetching SessionID
SELECT @@SPID AS Connection1_SessionID
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 121316
Search WWH ::
Custom Search