How to find blocking queries in MSSQL

Last month I was working on a database hanging issue which is when a user tries to update a table of the MSSQL DB it gets hung. And UI gets hanged also without giving any error msgs in the backend logs. At first, I thought this would have occurred because of the database deadlock. But later I figured that it occurred due to a blocking query.

First, let’s find out the difference between deadlock and blocking queries.

Blocking queries

Blocking occurs when two or more processes need the same resource/table. In the MSSQL server, only one process can have access to a resource/table at a time. So if one process is accessing a resource/table, then the other process is blocked from accessing it. The MSSQL server will force the second process to wait until the first process is finished. Most of the time this would occur when there is an adjacent update and select, 2 updates or insert and select queries. Given these scenarios, the first query will block the resources which results in the second query preventing from accessing the resource.

Deadlocks

Deadlock happens when two processes reach a “stalemate” in which neither process can obtain the resources it requires, because they depend on a lock on the other’s processes to complete their tasks. By rolling back the process that has put in the least amount of effort, Microsoft SQL Server will most of the time break the deadlock on its own, allowing the other process to continue and, hopefully, complete without interruption. The process that was rolled back will eventually restart and go without interruption as well.

Most of the time MSSQL DB gets hanged due to the blocking queries. Because most of the time MSSQL will handle the deadlocks, now let's see how to identify these two types of blocking.

Identify a Deadlock in the MSSQL server

There are several ways in the MSSQL server to track down the queries that are causing deadlocks. One of the options is to use SQL Server’s traceflag1222 to get the deadlock information to the SQL server error log. By default, deadlock logging is disabled in the MSSQL server. To enable the deadlock logging, first, let’s find out the status of the deadlock logger. We can use the below query to find the deadlock logging status.

DBCC TraceStatus(1222)

Status 1 means the deadlock logging is enabled and 0 means disabled. If it is disabled then we can use the query below to enable the logging.

DBCC Traceon(1222)
It will enable the deadlock logging by session levelDBCC Traceon(1222, -1)
It will enable the deadlock logging at the global level

After enabling the deadlock logging, then we can get log details of the deadlock. The log result is given in the below image.

Identify a Blocking query

In MSSQL server documentation there are many custom-made queries available to identify the blocking queries. The query given below is the one I used to find the solution to my problem.

SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

By using this query we can find the blocking query. And also this query provides the reason for the blocking query.

This is the result I got after running this query. The blue underlined one is the blocking query and the red underlined one is the reason for that blocking query. You can find more about identifying blocking queries by referring to this documentation by Microsoft. https://learn.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking