SQL Requests Are Waiting: Find the Head Blocker Before Ending Sessions

When SQL requests wait behind another session, identify the blocking chain and its transaction owner before ending connections. The session with the longest wait is often the customer of the problem, not its cause. A useful diagnosis connects the waiting request, the resource it needs, the session holding that resource and the application operation that should release it.

This guide uses SQL Server 2022. It provides a read-only starting point and a decision process, not an automated session-termination procedure. The examples were checked against Microsoft documentation but have not been executed in a laboratory or on a customer instance.

Why the head of the chain matters

A suspended request does not necessarily mean locking. It may be waiting for storage, memory, a parallel worker or another resource. Start with the actual wait type and blocking-session information rather than interpreting every slow request as a blocked transaction. Microsoft documents these fields in sys.dm_exec_requests, including special negative values that do not identify an ordinary client connection.2

Consider a hypothetical chain: an order query waits for an update, which itself waits for an older transaction. Ending the order query makes the visible complaint disappear, but the older transaction still prevents other work. Repeatedly treating the newest complaint can increase retries without restoring throughput.

There is also a distinction between a session and a request. The sessions view includes authenticated connections that currently have no executing request. Its Sleeping state means no request is running; its open_transaction_count reports open transactions. Those are separate observations. A request-only report therefore cannot establish that an idle connection has no unfinished transaction.3

Microsoft's blocking guidance recommends finding the head blocker, examining the transaction and then investigating why it retains locks. Application cancellation, result consumption and transaction handling can matter as much as server settings.1

Capture a bounded incident record

Record the instance, database, UTC incident interval and affected business operation. Include application request identifiers when available. Keep a separate record of when each diagnostic sample was taken. A session number observed after a connection ended is not durable identity for the earlier incident.

Use an existing authorised diagnostic connection. On SQL Server 2022, the request and session views require VIEW SERVER PERFORMANCE STATE for the broad visibility needed here; the corresponding permission on SQL Server 2019 and earlier is VIEW SERVER STATE. Do not solve incomplete visibility by granting an application account administrator rights.2, 3

The following author-composed example only selects current metadata. It excludes SQL text and parameter values to keep the first capture small. It still exposes session and programme information, so store results with incident access controls.

SELECT
    SYSUTCDATETIME() AS captured_utc,
    r.session_id,
    r.request_id,
    r.database_id,
    r.status AS request_status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS current_wait_ms,
    r.wait_resource,
    s.status AS session_status,
    s.open_transaction_count,
    s.program_name,
    s.last_request_start_time,
    s.last_request_end_time
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.wait_time DESC;

The columns and their meanings come from the two DMV references.2, 3 This is an incident snapshot, not a complete transaction graph. It omits sleeping sessions without requests, internal work and history. It does not fetch execution plans or run the application statement.

Capture every distinct positive blocker missing from the first result with this companion SELECT. Replace the example IDs with the IDs retained from that specific sample; do not regenerate the list from a later request scan. Negative values and zero are not session targets. This is another observation, not an atomic extension of the first one.

-- Replace these example IDs with all missing positive blockers from the saved sample.
WITH observed_blockers AS (
    SELECT DISTINCT v.session_id
    FROM (VALUES (57), (88)) AS v(session_id)
    WHERE v.session_id > 0
)
SELECT SYSUTCDATETIME() AS captured_utc,
       b.session_id AS observed_blocker_id,
       s.session_id AS currently_visible_session_id,
       s.login_time,
       s.status AS session_status,
       s.open_transaction_count,
       s.last_request_start_time,
       s.last_request_end_time,
       s.host_name,
       s.program_name,
       s.login_name,
       st.transaction_id,
       st.is_user_transaction,
       atx.transaction_begin_time
FROM observed_blockers AS b
LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = b.session_id
LEFT JOIN sys.dm_tran_session_transactions AS st ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS atx
  ON atx.transaction_id = st.transaction_id;

The added transaction views require VIEW SERVER PERFORMANCE STATE on SQL Server 2022, or VIEW SERVER STATE on SQL Server 2019. They map sessions to transactions and expose transaction start time.4, 5 Multiple transactions can produce multiple rows for one session; a distributed transaction may map to several sessions. Their transaction counts need not equal the sessions view's count.4

A sleeping session with an open transaction is a common shape of an application-held transaction, but it still needs owner and lock evidence. A null session row means it is no longer visible at this sample, not that no blocker existed earlier. A reused ID can identify a different connection: correlate login_time, original sample time and retained connection evidence before attributing ownership or intervening. DMV datetime fields are left in their documented server-time form; record the server time zone separately from captured_utc. Host and programme names are attribution hints, not proof of identity. Do not assume the latest input statement originally acquired the retained lock. These SELECTs do not open an explicit transaction or change business data, but visibility and workload can change while they run.

Interpret the chain without guessing

Build a short table with the waiting session, blocking session, database, wait resource, current request status and sample time. Follow positive blocking identifiers until the chain reaches a session that is not waiting on another ordinary session. That is a candidate head, subject to the visibility and timing limits of the capture.

Treat these observations differently:

Specifically, -2 identifies an orphaned distributed transaction, -3 a deferred recovery transaction, and -4 a latch owner that cannot currently be determined during an internal transition. -5 means ownership is not tracked for that latch type; by itself it does not establish a performance fault. These values are not ordinary session IDs. 0 or a null value can mean no blocking session is available to identify. Parallel requests may also have tasks waiting on different resources; the request-level row is a summary rather than a complete account of every worker.2

If more samples are needed, agree a bounded interval and retention policy and use existing monitoring or a controlled collection script. Avoid an unbounded loop that repeatedly collects every plan and statement on a busy server. Collection cost, data sensitivity and the visibility gap should be recorded alongside the results.

Connect database evidence to application ownership

Ask the owner of the candidate transaction to explain what should happen next. Is the application still processing results? Is a user confirmation holding the workflow open? Did an external dependency stall? Did a timeout return control to the caller while leaving the application's transaction handling incomplete?

These are investigation questions, not diagnoses established by a programme name. Session metadata helps locate an owner; application traces and the transaction path establish the behaviour. Preserve the full sequence around the failure, including cancellation and connection disposal, rather than collecting only the final SQL exception.

Separate work that is slow from work that cannot progress. An approved import performing required updates has a different remedy from a connection abandoned by a failed request. Record the business reason for the transaction, the affected rows or objects, the last observed progress and the consequences of interruption.

Do not introduce NOLOCK as a universal response to waiting readers. Whether different isolation semantics are acceptable is an application correctness decision. Similarly, changing a timeout can move the complaint without fixing the transaction that causes it. A change proposal should state which part of the ownership or access pattern it corrects.

Decide on intervention and verify the correction

A DBA and application owner should agree whether to let the work finish, correct the originating application path or terminate a confirmed session as an incident action. Termination can require transaction rollback and does not offer a guaranteed immediate return to service. This article deliberately supplies no copy-and-paste termination command.

Before an intervention, preserve the diagnostic sample, identify the current target again and establish what external effects may already have happened. A database rollback does not by itself reverse an email, payment request or message already sent elsewhere. The incident plan must include reconciliation where those effects are possible.

For a proposed application fix, define a disposable test that reproduces the relevant overlap between requests, including timeout and cancellation paths. No such test was run for this article. Acceptance should demonstrate that the intended transaction completes or rolls back, its connection becomes available and concurrent work meets the agreed response limit. Merely observing that a session disappeared is insufficient.

Compare like-for-like workload intervals after the approved change. Keep the old application release or configuration available through the normal release process, but do not mistake reverting a binary for reversing business transactions. If the blocking graph changes, investigate the new evidence rather than assuming that every subsequent delay has the original cause.

Limits and learning notes

This is a live blocking workflow, not a deadlock-graph guide. It also does not establish root cause from one DMV sample or cover Azure SQL Database's different visibility model.

• A waiting request identifies affected work; it does not automatically identify the cause.
• A sleeping connection and an open transaction can coexist.
• A corrected transaction lifecycle matters more than repeatedly clearing sessions.

Acceptance checklist

• [ ] Incident samples retain wait type, blocker ID, time and visibility limits.
• [ ] Missing positive blockers were checked for sleeping sessions and open transactions; negative IDs were interpreted separately.
• [ ] The transaction owner and cancellation/rollback path are evidenced, not inferred from a programme name.
• [ ] Approved concurrency checks show correct transaction completion and acceptable waiting, with external effects reconciled.

References

1. Understand and Resolve SQL Server Blocking Problems - SQL Server | Microsoft Learn. Accessed 2026-11-01.
2. sys.dm_exec_requests (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.

3. sys.dm_exec_sessions (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.

4. sys.dm_tran_session_transactions (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-02.

5. sys.dm_tran_active_transactions (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-02.

Related articles

• How to optimise SQL Server on VMware using waits and measured I/O
• SQL Transaction Log Keeps Growing: Find the Real Cause
• Server Up, Application Unusable: Build Better Health Checks