Investigate SQL Server Blocking and Timeouts

This diagnostic path is part of our SQL Server performance guide. If you don't yet know which path fits your problem, start there.

In this path, you investigate such lock conflicts. You learn to tell the waiting sessions from the start of the blocking chain, and to recognize a sleeping head blocker.

How blocking, timeouts and deadlocks differ

These terms describe different situations:

A query timeout therefore only tells you that time ran out. Blocking is one possible cause, but not the only one.

A connection timeout, by contrast, occurs while the connection is being established. If no command has been sent to SQL Server yet, searching for a blocking query does not fit that message. Microsoft explains query timeouts and how they differ from connection timeouts.

For a lock timeout, the LOCK_TIMEOUT setting is what matters. The default is -1, which means waiting indefinitely. It applies to the individual connection and is not a general runtime limit for every query. Microsoft documents SET LOCK_TIMEOUT.

Why a sleeping head blocker can hold up other sessions

Two terms first: a session is a connection to SQL Server. A request is a command that this session is currently executing. A session can exist without a request running.

A session can begin a transaction, modify data, and then wait for the application's next command. As long as the transaction is still open, its locks remain in place.

The session state sleeping therefore only means that no request is currently running. It does not mean that all transactions have ended and all locks have been released. Many sleeping sessions are perfectly normal. A session becomes interesting when others are waiting on it and it has an open transaction. Microsoft explains the session states.

Example with a sleeping head blocker

In our synthetic test, three connections worked with two rows of data:

1. A began a transaction, modified row 1, and then waited for the next command. The transaction stayed open.
2. B modified row 2 within its own transaction. When it then tried to modify row 1, B had to wait for A.
3. C wanted to modify row 2 and therefore had to wait for B.

The chain is C → B → A. Each arrow means "waits for".

If you only look at running requests, you see B and C, the waiting sessions. You find the start of the chain only when you also include sessions without a running request.

In the lab, the transaction in A was deliberately rolled back. After that, B and C could continue.

Step 1: Capture the error message and the time

First, note the complete error text and the affected task. "Timeout when saving" is a good starting point, but it is not yet a cause.

Record:

• time and affected application;
• database and, if possible, the specific operation;
• error text and error number, if available (1205, 1222, or an application timeout);
• after how many seconds the operation was canceled;
• whether the connection already existed or was still being established;
• whether other operations were also waiting during the same period.

A general query timeout does not prove a deadlock. To see the circular wait, you need the matching deadlock graph. Microsoft describes deadlocks and how the victim is chosen.

Your first finding: You know what kind of cancellation was reported and which time window needs to be investigated.

Step 2: Check the current blocking chain

If the problem is happening right now, start with a live view of the blocked operations.

In SQL Server Management Studio, you can find the current blocking relationships at the instance level under Reports → Standard Reports → Activity – All Blocking Transactions. In Activity Monitor, the process list shows the columns Blocked By and Head Blocker; a 1 in Head Blocker marks the start of a chain. Labels can differ depending on the interface language. Microsoft lists these entry points for blocking diagnosis.

Start from one affected session:

1. Which session is blocking it right now?
2. Is that blocking session itself waiting for another session?
3. Where does the chain end?
4. Which session is at that point, even if it has no running request at the moment?

The following query answers these questions as well. It joins two system views: sys.dm_exec_requests shows running requests with wait type, blocking_session_id and the locked resource. sys.dm_exec_sessions also contains sessions without a running request and, with open_transaction_count, the number of their open transactions. Only together do they reveal the sleeping head blocker.

/* PSG Stack · SQL Server Performance Guide · Query 2
   Current blocking chain, including sleeping head blockers.

   - Read-only. Ends no session and changes nothing.
   - Runs with MAXDOP 1 so the diagnostic does not put additional
     parallel load on the server.
   - Shows only what is blocked at the moment you run it.
   - Permissions: VIEW SERVER STATE
     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).
     Without these permissions, the query fails with error 300.
   - SQL Server 2016 SP1 and later (sys.dm_exec_input_buffer).
     Tested in the PSG lab on 2017, 2019, 2022, and 2025.
   - level 0 = head blocker (start of the chain). path reads
     "head blocker <- waits <- waits".
   - Special values of blocking_session_id (-2, -3, -4) and sessions
     that only wait for their own parallel workers are left out.
*/
WITH requests AS (
    SELECT session_id, blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
      AND blocking_session_id <> session_id
),
heads AS (             -- block someone, but do not wait for anyone themselves
    SELECT DISTINCT a.blocking_session_id AS session_id
    FROM requests AS a
    WHERE NOT EXISTS (SELECT 1 FROM requests AS b
                      WHERE b.session_id = a.blocking_session_id)
),
chain AS (
    SELECT k.session_id,
           CAST(NULL AS smallint)                AS waits_for,
           0                                     AS level,
           k.session_id                          AS head_blocker,
           CAST(k.session_id AS varchar(400))    AS path
    FROM heads AS k
    UNION ALL
    SELECT a.session_id,
           CAST(a.blocking_session_id AS smallint),
           ke.level + 1,
           ke.head_blocker,
           CAST(ke.path + ' <- ' + CAST(a.session_id AS varchar(10)) AS varchar(400))
    FROM requests AS a
    JOIN chain AS ke ON a.blocking_session_id = ke.session_id
)
SELECT
    ke.level,
    ke.path,
    ke.session_id,
    ke.waits_for,
    s.status                       AS session_status,      -- sleeping = no command right now
    r.status                       AS request_status,      -- NULL = no running request
    s.open_transaction_count       AS open_transactions,
    tx.transaction_since,
    r.wait_type,
    r.wait_time                    AS wait_ms,
    r.wait_resource,
    DB_NAME(s.database_id)         AS database_name,
    s.program_name,
    s.host_name,
    s.login_name,
    s.login_time,
    SUBSTRING(st.text, r.statement_start_offset / 2 + 1,
        (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1)
                                   AS current_statement,
    ib.event_info                  AS last_batch             -- last command sent
FROM chain AS ke
JOIN sys.dm_exec_sessions AS s        ON s.session_id = ke.session_id
LEFT JOIN sys.dm_exec_requests AS r   ON r.session_id = ke.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
OUTER APPLY sys.dm_exec_input_buffer(ke.session_id, NULL) AS ib
OUTER APPLY (SELECT MIN(at.transaction_begin_time) AS transaction_since
             FROM sys.dm_tran_session_transactions AS sto
             JOIN sys.dm_tran_active_transactions AS at
                  ON at.transaction_id = sto.transaction_id
             WHERE sto.session_id = ke.session_id) AS tx
ORDER BY ke.head_blocker, ke.level, ke.session_id
OPTION (MAXDOP 1, MAXRECURSION 100);

Example output from our lab (SQL Server 2022, abbreviated):

level path           session_id waits_for session_status request_status open_transactions wait_type wait_ms last_batch
0     54             54         NULL      sleeping       NULL           1                 NULL      NULL    BEGIN TRAN; UPDATE dbo.t SET v = v + 1 WHERE id = 1;
1     54 <- 56       56         54        running        suspended      2                 LCK_M_X   6152    UPDATE dbo.t SET v = v + 1 WHERE id = 1;
2     54 <- 56 <- 80 80         56        running        suspended      2                 LCK_M_X   4026    UPDATE dbo.t SET v = v + 1 WHERE id = 2;

Session 54 is the start of the chain: it is sleeping and has no running request, but it holds an open transaction. Sessions 56 and 80 are only waiting. To resolve the chain, you have to deal with session 54, not with the waiting sessions. For sessions with a running request, open_transactions was one higher than expected in our test; the column is most meaningful for sleeping sessions.

A wait such as LCK_M_X means that this request is waiting for an exclusive lock. The wait_resource column shows what exactly it is waiting for, for example a row (KEY: …), a page (PAGE: …) or an entire object (OBJECT: …). The wait alone does not yet name the responsible application step. Follow the chain to its start instead.

Step 3: Investigate the head blocker and its transaction

For the head blocker, the main question is why the lock persists. In addition to the session ID, collect the following:

The last batch is only a clue. A longer transaction may already have executed other statements earlier, so the last batch does not necessarily explain all locks that are still held.

Check with the application or its development team whether a completion step is missing: Is the flow waiting for user input? Was part of the transaction logic skipped after an error? Has the actual command already been canceled while the transaction stayed open?

A timeout message is not proof that the transaction has ended. After a query timeout or lock timeout, SQL Server cancels only the running statement. An explicitly started transaction stays open until the application rolls it back or closes the connection. With connection pooling, that can be considerably later. Microsoft describes this blocking scenario after a timeout or cancellation.

Step 4: Investigate past blocking with the right evidence

If the timeout is over and no chain is visible anymore, the current session list does not show what happened earlier.

Then check what data already exists for the time window: saved blocking snapshots from a monitoring tool, matching events, or application logs. Query Store wait data can supplement the investigation, but it does not replace a recorded chain with the sessions involved.

Blocked Process Reports document lock conflicts that persist for a longer time. Two things are required: the server option blocked process threshold (s) (default 0, meaning off) and an Extended Events session that captures the blocked_process_report event. The system_health session, which runs by default, does not capture these reports. Short conflicts can stay entirely below the threshold.

What our comparison with Blocked Process Reports shows

In the same three-connection lab, we examined three cases:

Four reports here did not mean four independent incidents. They were repeated observations of two relationships within the same chain. The five seconds were a test condition, not a recommendation. A threshold also does not guarantee a report at exactly that moment; Microsoft describes the generation as best effort. Documentation on the reporting threshold.

For a suspected deadlock, check the deadlock events instead. The system_health session captures these by default. Whether the case you are looking for is still included depends on how much has been recorded since. Microsoft explains the deadlock events in system_health.

Your finding may also name a gap: "The conflict is no longer visible and was not recorded during the period in question." That leads to a targeted plan for the next observation.

Step 5: Verify the action and its effect

The appropriate action depends on the demonstrated flow:

• A transaction stays open after an error or timeout: In this case, the application has to roll it back. The simplest way is SET XACT_ABORT ON at the start of procedures or of the connection: SQL Server then rolls back the transaction on a runtime error and when the application cancels the command. Alternatively, a TRY…CATCH with ROLLBACK catches the error. We verified this in the lab on SQL Server 2022: without XACT_ABORT, the transaction and the lock remained after an application timeout and after a lock timeout (error 1222); with XACT_ABORT ON, both were gone.
• A necessary transaction holds locks for a very long time: Examine its scope, access path and the transaction boundaries the business logic actually requires.
• Operations repeatedly conflict on the same resources: Look at the order and flow of the accesses together.
• A SELECT holds an unexpectedly large number of locks: Examine the specific plan. Our article Lock Escalation from a SELECT in SQL Server goes deeper into such a special case.

Ending a session with KILL can be an urgent decision, but it does not replace finding the cause. SQL Server then has to roll back the open transaction, and that can take time itself. KILL <session ID> WITH STATUSONLY shows the progress. Before intervening, document which transaction is affected and what the business consequences of canceling it would be.

A higher query timeout initially only changes the time budget. Whether that budget suits the task is a legitimate question. The cause of a demonstrated blocking chain still has to be addressed.

Changing the isolation approach is a business decision of its own. Read Committed Snapshot Isolation (RCSI) helps readers that previously had to wait for writers: they then read the last committed state. The chain shown here, however, consists of conflicts between writers, and RCSI does not resolve those.

Record the blocking finding clearly

For our lab, the finding can be worded like this:

C was waiting for B. B was waiting for A and already held a lock that C needed. A had no running request but an open transaction. After the deliberate rollback in A, the other operations could continue. The start of the chain was therefore at A.

For your own case, add the time, the application, the start of the transaction and the evidence you have. After a change, observe whether the same conflict recurs under comparable conditions and whether the affected operations complete without a timeout. The fact that a chain has just disappeared only marks the end of that single observation; the lasting effect shows only when you compare several comparable periods.

More diagnostic paths