How to identify and resolve blocking queries in SQL Server?

Question

0

During peak load, our database server experiences severe performance degradation due to query blocking. Transactions stall, causing API timeouts across our microservices. What scripts can I run to identify the lead blocker and resolve lock contention quickly?

Finding Active Blockers

You can query dynamic management views (DMVs) to isolate blocking sessions immediately.

-- Select blocked and blocking session details
SELECT 
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    t.text AS sql_text
FROM sys.dm_exec_requests r
-- Cross apply to retrieve the exact T-SQL query text
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

asked by Rana Sunny , modified by Rana Sunny in SQL Server

1 Answer

Write Your Answer

Answer 1

When a SQL Server request appears stuck, check whether it is waiting on another session before changing the query or restarting anything. The key clue is blocking_session_id in sys.dm_exec_requests. A nonzero value identifies the session currently blocking that request.

Find blocked requests and their blockers

This query shows the wait, the blocked statement, and—if the blocker is currently running—its statement too. It also returns blocking session details that can help you trace the request to an application or host.

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    r.status AS blocked_request_status,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    blocked_sql.text AS blocked_statement,
    blocker_s.status AS blocker_session_status,
    blocker_s.host_name AS blocker_host,
    blocker_s.program_name AS blocker_application,
    blocker_sql.text AS blocker_running_statement
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS blocker_s
    ON blocker_s.session_id = r.blocking_session_id
LEFT JOIN sys.dm_exec_requests AS blocker_r
    ON blocker_r.session_id = r.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_sql
OUTER APPLY sys.dm_exec_sql_text(blocker_r.sql_handle) AS blocker_sql
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Follow the blocking_session_id values to trace the blocking chain. The first session in that chain that is not itself waiting on another session is often the one to investigate first. Check wait_type and wait_resource as well: they help distinguish lock waits from other kinds of waits.

When the blocker has no active request

A blocker may be sleeping while still holding locks—for example, if an application left an open transaction uncommitted. In that case, the query above may show session details but no current blocker statement. Inspect the session’s most recently submitted batch with sys.dm_exec_input_buffer:

DECLARE @session_id int = 57; -- Replace 57 with the blocking session ID.

SELECT *
FROM sys.dm_exec_input_buffer(@session_id, NULL); -- Shows the session's last submitted input.

Also check the application logs and transaction state. The last submitted input is a useful clue, but it does not prove that the statement is still running or explain every lock held by the session. Access to these DMVs generally requires VIEW SERVER STATE; on SQL Server 2022, the relevant permission is typically VIEW SERVER PERFORMANCE STATE.

Resolve the cause, not just the symptom

If the transaction is legitimate, let it finish or have the application commit or roll it back promptly.
If the same statements repeatedly block each other, review their execution plans, indexes, and transaction length. Keeping transactions short often reduces lock contention.
Consider row-versioning options such as read committed snapshot only after checking their effects on the workload and tempdb.
Use KILL only when you have confirmed the session is safe to terminate and understand the rollback cost. Killing a session can trigger a lengthy rollback; it does not instantly clear the problem.

Blocking is not the same as a deadlock: blocking can persist while one session waits, whereas SQL Server detects a deadlock and selects a victim. If blocking returns after the immediate wait clears, look for the transaction or application behavior causing it rather than treating each incident as an isolated session to kill.