Finding What Is Blocking Your Query in SQL Server

Posted by Kyle Hankinson August 25, 2026

---

An UPDATE that normally takes milliseconds has been sitting there for two minutes. Or your application just threw "timeout expired" on a query you know is fine. Nine times out of ten nothing is slow at all: your session is waiting for a lock that another session holds and has not released.

That is blocking, and it is worth separating from its noisier cousin. A deadlock is two sessions each waiting on the other; SQL Server detects that cycle within seconds, kills one side, and raises error 1205. Blocking is one-directional and SQL Server will happily let it continue forever, because as far as the engine is concerned, everyone is just politely queueing. Nobody gets an error. Things simply stop.

Everything below is plain T-SQL against dynamic management views, so it works from any client on any platform, on SQL Server 2012 and later plus Azure SQL Database. You need the VIEW SERVER STATE permission. To verify the queries, I reproduced a real blocking chain on SQL Server 2022: one session opened a transaction, updated a row, and held the transaction open without committing, then two more sessions ran conflicting updates against the same row.

Step 1: is anything blocked right now?

One query answers it:

SELECT session_id, blocking_session_id, wait_type, wait_time, command
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Against my stuck sessions this returned:

session_id	blocking_session_id	wait_type	wait_time	command
66	65	LCK_M_X	10098	UPDATE

Read it as: session 66 has been waiting 10 seconds (wait_time is milliseconds) for an exclusive lock, and session 65 is the one holding it. LCK_M_X is an exclusive lock wait; LCK_M_S (shared) and LCK_M_U (update) are the other common ones. Empty result set: no blocking at this instant, and your problem lies elsewhere.

Step 2: who is blocked, and what are they running?

Join to sys.dm_exec_requests' sibling views to turn session numbers into people and SQL:

SELECT r.session_id       AS blocked_session,
       r.blocking_session_id AS blocked_by,
       r.wait_type,
       r.wait_time / 1000 AS wait_seconds,
       s.login_name, s.host_name, s.program_name,
       t.text             AS blocked_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

In my repro this showed the waiting UPDATE statement along with the login and hostname it came from. Small heads-up: the SQL text often comes back in its parameterized form (UPDATE [dbo].[orders] SET [amount] = [amount]+@1 WHERE [id]=@2), which is the plan's view of the statement, not the literal text the user typed.

Step 3: find the head blocker (it is probably idle)

Here is the trap that makes people miss the culprit. The session at the head of the chain frequently is not running anything. It executed its UPDATE long ago inside an explicit transaction, never committed, and went idle. Since it has no active request, it does not appear in sys.dm_exec_requests at all. It only shows up as a number in other rows' blocking_session_id.

First, isolate the head: a session that blocks others but is not itself blocked.

SELECT DISTINCT blocking_session_id AS head_blocker
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
  AND blocking_session_id NOT IN (SELECT session_id
                                  FROM sys.dm_exec_requests
                                  WHERE blocking_session_id <> 0);

With three sessions queued (67 waiting on 66, 66 waiting on 65) this correctly returned just 65. Then pull what that session last executed, plus whether it is sitting on an open transaction:

SELECT s.session_id, s.status, s.login_name, s.host_name,
       tr.open_tran_count,
       t.text AS last_sql
FROM sys.dm_exec_sessions s
JOIN (SELECT session_id, COUNT(*) AS open_tran_count
      FROM sys.dm_tran_session_transactions
      GROUP BY session_id) tr ON tr.session_id = s.session_id
JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id
                       FROM sys.dm_exec_requests
                       WHERE blocking_session_id <> 0);

The most_recent_sql_handle trick is what makes idle blockers visible: it retrieves the last statement the connection sent even when nothing is currently running. In my case it returned the offending batch verbatim, BEGIN TRAN and the UPDATE holding the lock, with open_tran_count = 1 confirming the transaction was still open.

You may know sp_who2, which still works and shows blocking in its BlkBy column, but it cannot show you the SQL text, which is usually the piece you need. For ongoing monitoring rather than live firefighting, the community procedure sp_whoisactive and the blocked process report are the standard next steps; Microsoft's blocking troubleshooting guide covers both.

Deciding what to do about it

The diagnosis usually lands in one of a few buckets: an application that opened a transaction and forgot to close it (my repro, and the classic), a legitimately long-running write, a query doing a full scan under the covers and locking far more rows than it should, or lock escalation turning row locks into a table lock. If the blocker belongs to a colleague's forgotten session, a message to them beats any T-SQL.

When the session must die, KILL does it:

KILL 65;

Two cautions, both learned by watching it happen. First, KILL does not stop work, it undoes work: SQL Server rolls back the session's open transaction, and rolling back a large transaction can take as long as, or longer than, the work already done. Killing a session that has been updating rows for an hour buys you up to another hour of rollback, during which the locks are still held. You can watch the progress:

KILL 65 WITH STATUSONLY;

which in my test reported: SPID 65: transaction rollback in progress. Estimated rollback completion: 10%. Estimated time remaining: 0 seconds. Second, KILL is a hammer, not a fix. If the blocker was an application connection, the app will reconnect and do the same thing tomorrow; the durable fix is committing or shortening the transaction in the code. The KILL documentation lists further restrictions, including that it requires ALTER ANY CONNECTION and cannot target your own session or system sessions.

One related scenario deserves its own playbook: when blocking sessions are what stops you from dropping a database, see how to drop a database with active connections.

A workflow note

This investigation is naturally multi-window: the stuck query in one place, the DMV queries in another, so you can re-run the diagnostics and watch wait_time climb. Since everything here is an ordinary query, any client works, from sqlcmd (which I used for the repro above) to a GUI like SQLPro for MSSQL, whose multiple query tabs and side-by-side result sets fit the pattern of holding one session open while diagnosing from another. Keep the step 2 query saved somewhere close at hand. When you need it, something is on fire, and pasting beats remembering.

---