SQL Server blocking: find the blocking session

A query that should take a second is still spinning. SSMS shows no error — just the progress animation and a cursor that will not come back. Something is holding a lock your statement needs, and until you know what, you are guessing. It is the oldest question in database operations, and the only one that matters in the moment: who is blocking whom?

SQL Sage for SSMS answers it in place, for free, with no AI. A live view called What’s blocking now — part of SQL Sage History — shows the blocking happening on the server right now: which session is waiting, which session is holding the lock, and, more often than not, the quiet culprit behind the whole thing.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool described here.

Who is blocking whom: a T-SQL query on the DMVs

Before the tool — the answer you can get in any query window, with nothing to install. SQL Server exposes the current requests in sys.dm_exec_requests, and the blocking_session_id column tells you which session a request is waiting on (Microsoft Learn: sys.dm_exec_requests). Join sys.dm_exec_sessions to see who it is (Microsoft Learn: sys.dm_exec_sessions) and sys.dm_exec_sql_text to see what it is running. The query only reads system views and is valid on SQL Server 2016+ and Azure SQL — how much it shows depends on your permissions (more below):

-- Who is blocking whom: requests currently waiting on another session (read-only)
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time                AS wait_time_ms,
    r.wait_resource,
    r.status,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id)     AS database_name,
    SUBSTRING(t.text,
              r.statement_start_offset / 2 + 1,
              (CASE r.statement_end_offset
                   WHEN -1 THEN DATALENGTH(t.text)
                   ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

How to read it: each row is a session that is waiting, and blocking_session_id points at whom it waits on. A wait_type starting with LCK_M_ means a lock wait (for example LCK_M_X — waiting for an exclusive lock), wait_time_ms is how long it has been waiting, and wait_resource names the resource (a key, a page, an object). statement_text is the exact statement inside the batch that is stuck, not the whole batch. Negative values in blocking_session_id are special cases described in the documentation (for example, -2 is an orphaned distributed transaction).

A blocking chain has a head: a session that blocks others but is not waiting on anyone itself. It is often missing from sys.dm_exec_requests altogether, because it is not executing anything right now — so look for it in sys.dm_exec_sessions:

-- Head of the chain: sessions that block others but are not blocked themselves
SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    s.last_request_end_time
FROM sys.dm_exec_sessions AS s
WHERE s.session_id IN (SELECT r.blocking_session_id
                       FROM sys.dm_exec_requests AS r
                       WHERE r.blocking_session_id > 0)
  AND NOT EXISTS (SELECT 1
                  FROM sys.dm_exec_requests AS r2
                  WHERE r2.session_id = s.session_id
                    AND r2.blocking_session_id > 0);

A head blocker with status sleeping and an open_transaction_count above zero is the classic picture: transaction open, work finished, locks still held — more on that below. One caveat: without the right permission you only see your own session. SQL Server 2019 and earlier need VIEW SERVER STATE, SQL Server 2022 and later VIEW SERVER PERFORMANCE STATE; for Azure SQL Database, the sys.dm_exec_requests documentation states the view is limited to the current connection. So an empty result does not automatically mean “no blocking”.

Do not KILL blindly. KILL on a session with an open transaction rolls back all of its work, and the rollback can take as long as the work itself or longer — with the locks held the whole time. Check login_name, host_name and program_name first: is it an ETL job, someone’s SSMS session, or an application connection pool that will simply reconnect? If you can, ask the owner to COMMIT or ROLLBACK. And once you do run KILL, KILL <session_id> WITH STATUSONLY reports the rollback progress.

Both queries are worth keeping bookmarked. If you would rather not dig them out of a snippets file mid-incident, SQL Sage for SSMS shows the same picture as a live view.

What “What’s blocking now” shows you

Open What’s blocking now and you get a live picture of the lock contention on the server, not a query you have to write and remember. It shows who blocks whom, the open transactions involved, and it marks the session that is probably yours — so you are not reading a wall of sessions trying to work out which line is you.

That last detail matters more than it sounds. In a real incident you are under pressure, the list is unfamiliar, and the first mistake people make is acting on the wrong session. Telling you which row is likely your own connection removes a whole category of panic error before you touch anything.

The usual suspect: an idle session with an open transaction

When a server seizes up, the instinct is to hunt for a monster query — some report scanning a billion rows. Often that is not it at all. The blocker is a session doing nothing: someone opened a transaction, ran one statement, and then walked away — or an application started a transaction and never sent the COMMIT. The work is long finished; the locks are still held; everyone else queues behind a connection that is idle.

This is exactly why a raw session list can mislead you. An idle session looks harmless — zero CPU, no query running — so it is the last thing you suspect, and it is frequently the cause. What’s blocking now surfaces open transactions and flags that idle-but-holding session directly, so you stop chasing a runaway query and find the connection that actually needs to go.

KILL and GRANT are scripts, not buttons

Here is the part we are most deliberate about. SQL Sage does not reach into the server and end a session for you. When you decide a connection has to be killed — or a permission granted to unstick something — SQL Sage hands you the KILL (or GRANT) as a script in a new query window. Nothing more. Nothing runs on its own, and pressing F5 asks you before a KILL actually executes.

That is not friction for its own sake; it is the same principle that runs through the rest of the product, the one we have written about at length: a tool should show you the exact statement and let a human pull the trigger, especially when the action is irreversible. Killing the wrong session on production is the kind of mistake you cannot take back. So the diagnosis is instant and automatic; the destructive act is a statement you read, aim, and run yourself.

SQL Sage History: what you ran, and when

Diagnosing blocking is half of it. The other half, during and after an incident, is reconstructing what you did — which script you ran against which server, and whether it worked.

SQL Sage History records every query you run in SSMS. It is kept on this computer only and encrypted for your Windows account (DPAPI) — it is not uploaded anywhere. Each entry carries the server, the database, the status (including the SQL Server error number when a run failed), the duration, the rows returned, and whether it was a READ, WRITE or DDL statement. The list is newest-first and searchable, so “the ALTER I ran twenty minutes ago on that server” is a quick find rather than a scroll through memory.

Two things are off-limits by design. Query results are never stored — only the statements, not the data they returned. And passwords and other secrets (for example in a CREATE LOGIN) are replaced with ? before anything is saved; the rest of the text is kept as you typed it. When you want to re-run something, “Open in new window” reconnects to the same server and opens the script — it runs nothing. Retention is 30 days by default, and you can pause it or switch it off whenever you want.

Recently closed: the tab you can get back

Incidents are where tabs get closed in a hurry. You had a diagnostic script half-built, something else caught fire, and the tab is gone — unsaved. Recently closed (unsaved) keeps query tabs you closed without saving, so a lost script can be reopened instead of rewritten from memory at exactly the moment you can least afford to.

Where it runs — and where Incident Mode goes further

All of the above is free and uses no AI. It is edition-aware across box SQL Server and Azure SQL; some capabilities that depend on server-level DMVs behave differently on Azure SQL Database, because the platform exposes less there, and SQL Sage uses what each platform actually offers.

To be straight about the boundary: What’s blocking now answers “who is blocking whom, right now.” When you want the full shape of a struggling server — the entire blocking chain, the top waits, and the most expensive queries, with every claim cited to a real number from a DMV — that is Incident Mode, part of the paid, AI-assisted Proven & Accountable layer. It is also honest about its own limits: if your login cannot see other sessions, it tells you so instead of reporting a reassuring “no blocking.” But for the immediate question that interrupts your day, the free live view is enough on its own.

The takeaway

When a query hangs, you do not need a theory — you need to see who is blocking whom, and the answer is usually an idle session sitting on an open transaction. SQL Sage for SSMS puts that answer one click away, free and with no AI, without leaving the tool you are already in. It surfaces the real culprit, marks which session is yours, and keeps the destructive part — KILL, GRANT — as a script you read and run, never something that fires on its own. SQL Sage History gives you the record of what you ran and when, and Recently closed catches the tab you closed in the rush.

SQL Sage for SSMS is an add-in for SQL Server Management Studio that puts an AI copilot in the query editor — writing, explaining and optimizing SQL grounded in your real schema — and stays read-only by default and keyless (you sign in with your own Claude or ChatGPT account, no API key to manage). The blocking view, SQL Sage History and Recently closed described above are part of the free tier and use no AI at all. You can see it on the SQL Sage for SSMS product page. SQL Sage is made by LumaSoft (LUMA sp. z o.o.) and is not affiliated with Microsoft.