How to Find Blocking Queries in SQL Server

How to Find Blocking Queries in SQL Server

In this article, I am going to discuss how to find blocking queries in SQL Server.  In SQL Server, blocking occurs if there are some open transactions. Let us understand this with an example. 

We are going to use the following table in this demo.

How to Find Blocking Queries in SQL Server

Please use the following SQL Script to Create and populate the table with the required data.

-- Create table TableA
CREATE TABLE TableA
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableA values (101, 'Anurag')
INSERT INTO TableA values (102, 'Mohanty')
Go

Now execute the following 2 SQL Statements

BEGIN TRANSACTION

UPDATE TableA SET Name = ‘Anurag Updated’ WHERE Id = 101

Now from a different window, try to execute any of the following commands and you will notice that all the queries are blocked. 

SELECT Count(*) FROM TableA

DELETE FROM TableA WHERE Id = 101

TRUNCATE TABLE TableA

DROP TABLE TableA

The reason is there is an open transaction. So, once the open transaction is completed, then only you will be able to execute the above queries. 

So the important question that comes to our mind is- 

How to identify all the active transactions which are not completed?  

One way to do identify this is by using the DBCC OpenTran command. The DBCC OpenTran statement will display only the oldest active transaction. It is not going to show you all the open transactions.
DBCC OpenTran 

The following SQL script you can use to identify all the active transactions. 

SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] DESC;
GO

When you execute the above SQL Script, it will give you the following output.

How to Find Blocking Queries in SQL Server

The above SQL Script gives you a lot of useful information about open transactions such as

  1. Session Id
  2. Login Name
  3. Database Name
  4. Transaction Begin Time
  5. The actual query that is executed 

So once you got the above information, then you can ask the respective developer to either commit or rollback the transactions that have left open unintentionally so that you can proceed further.

That’s cool. But if for some reason the person who initiated the transaction is not available, then also you have the option to KILL the associated process. However, this may have unintended consequences, so use it with extreme caution. 

There are 2 ways available in SQL Server to kill the process are as follows.

Killing the process using SQL Server Activity Monitor:
  1. Right Click on the Server Name in Object Explorer and then select the “Activity Monitor” option.
  2. In the “Activity Monitor” window expand the Processes section
  3. Finally, right click on the associated “Session ID” and select “Kill Process” from the context menu as shown in the below image.

How to Find Blocking Queries in SQL Server

Killing the process using SQL command: 

KILL Process_ID 

For example:

KILL 42

What happens when you kill a session?

All the work that has been done by the transaction will be rolled back. The database must be put back in the state it was before the transaction was started. 

SUMMARY

In this article, I try to explain the how to find blocking queries in SQL Server. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *