How to find what's blocking in SQL Server

What blocking looks like

Requests that normally take milliseconds start timing out. CPU is quiet, yet the application is stuck. Most of the time the cause is blocking: one session holds locks that other sessions need, and they queue up behind it.

Blocking is normal in small doses. It becomes a problem when one session holds its locks for a long time, often an open transaction that was never committed. Everything that touches the same rows waits behind it, and the queue grows into a chain.

You need three things: who is waiting, who is at the root, and what the root session is doing. Then you can decide whether to wait, fix it at the source, or end the session.

1. See who is waiting

Every request that is blocked shows the session blocking it in blocking_session_id.

SELECT r.session_id,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time / 1000 AS wait_seconds,
       r.wait_resource,
       DB_NAME(r.database_id) AS database_name,
       t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

Wait types starting with LCK_M_ are lock waits: LCK_M_U waits for an update lock, LCK_M_X for an exclusive lock, LCK_M_S for a shared lock. wait_resource tells you which object or row is contended.

2. Find the root blocker

In a chain, session A blocks B and B blocks C. Ending B fixes nothing. The session that matters is the one that blocks others but is not blocked itself.

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

3. See what the root session is doing

Replace 51 with the session from step 2.

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.open_transaction_count,
       s.last_request_end_time,
       t.text AS last_sql
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.session_id = 51;

Read it like this:

• status = sleeping with open_transaction_count > 0: an idle session holding a transaction open. The usual culprit.
• program_name and host_name tell you which application or job owns it.
• last_request_end_time tells you how long it has been idle.
• last_sql is the last statement it ran, not necessarily the one that took the locks.

4. Decide what to do

• A batch job that will finish soon: waiting may be cheaper than a rollback.
• An idle session with an open transaction: contact the owner, or end it. Its work will be rolled back.
• The same blocker keeps returning: fix the source. Commit sooner, keep transactions short, add the index the blocked query needs, or move reporting reads to a replica.

Before ending anything, know what you will lose: ending a session rolls back its open transaction. A long transaction can take a long time to roll back, and it keeps its locks until the rollback finishes.

5. End it safely

Double-check the session number. You are about to roll back someone's work.

KILL 51;

-- progress of the rollback (run again until it reports complete)
KILL 51 WITH STATUSONLY;

6. Check it worked

Run step 1 again. The chain should be empty, and the waiting requests should complete. Then confirm from the application side: response times back to normal, no new timeouts. A successful KILL is not proof the business problem is solved.

Write down what you found: the root session, the program that owned it, how long it held its locks, and what you did. If it happens again, you start from evidence instead of from scratch.

The same steps in Quorum

Quorum draws the blocking chain for you, with the root blocker identified, its transaction age and the program that owns it. Observed facts are kept apart from likely explanations.

session 51  reporting-sync   open txn 08:12
   ├── 1203  orders-api   LCK_M_U   00:03:12
   ├── 1207  orders-api   LCK_M_U   00:02:58
   └── … 10 more waiting
rollback estimate ~6 s · 3,100 rows

1. See the chain. Who waits for whom, with the root blocker named, instead of three separate queries.
2. Review the change. The exact statement, the target server and database, and the known side effects, such as the rollback cost, before you run it.
3. Run it once. The confirmation is single-use; a changed statement or target invalidates it.
4. Know the outcome. Confirmed, failed or unknown, never a silent retry. Then check again with real traffic and save the evidence as a runbook.