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.
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
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.
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.
The above SQL Script gives you a lot of useful information about open transactions such as
- Session Id
- Login Name
- Database Name
- Transaction Begin Time
- 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:
- Right Click on the Server Name in Object Explorer and then select the “Activity Monitor” option.
- In the “Activity Monitor” window expand the Processes section
- Finally, right click on the associated “Session ID” and select “Kill Process” from the context menu as shown in the below image.
Killing the process using SQL command:
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.
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.