The Blocked Process Report

A query waits, but the blocking session has already left the screen. The blocked process report records both sides once a wait crosses a configured threshold. Capture it with Extended Events so the next incident has more than a screenshot.

Set a Threshold That Answers a Question

SQL Server produces no blocked process reports by default. The blocked process threshold option is measured in seconds and needs a value of at least five for useful reports. The lock monitor checks on its own cycle, so the event is best effort rather than an exact stopwatch. Choose a threshold that identifies waits worth investigating without flooding the target during routine short blocking.

I start with the application’s tolerance, not a number from another server. How long can a user wait before the operation is clearly wrong? Read the current configured and running values first. The query below does that without changing the instance. Record the chosen value and its owner before enabling collection.

SELECT
    name,
    value AS ConfiguredValue,
    value_in_use AS RunningValue
FROM sys.configurations
WHERE name = 'blocked process threshold';

Enable the Blocked Process Report Deliberately

Set the threshold through sp_configure during an approved change. The documented range starts at five seconds for effective reporting, while zero disables it. Run RECONFIGURE after changing the value. This is an instance setting, so consider the volume from every database on that instance. Do not set one second and expect one-second reports. The lock monitor does not work that way.

I test the change on a nonproduction instance with a controlled blocking example. The test proves that events arrive and that the threshold is reasonable for the workload. A setting in sys.configurations alone does not prove anyone captures the event.

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'blocked process threshold', 20;
RECONFIGURE;

Capture With Extended Events

Create an Extended Events session for sqlserver.blocked_process_report. A ring buffer is convenient for a short test. An event file is better for ongoing collection because it survives more diagnostic activity and can be retained under a policy. Choose a file path and rollover limit that the SQL Server service can write. Avoid capturing every statement just to investigate blocking.

The sample session below uses a small ring buffer and starts it. Run it once under the required privileges on a test instance or adapt its name to your configuration. I keep the session definition in the operations runbook, then verify it is running before the next workload test.

CREATE EVENT SESSION BlockedProcessWatch ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.ring_buffer
(
    SET MAX_MEMORY = 1024
);
ALTER EVENT SESSION BlockedProcessWatch ON SERVER
STATE = START;

Read the Blocked Process Report XML as Two Sides

The blocked process event contains XML with a blocked-process node and a blocking-process node. Compare their session IDs, input buffers, wait resource, lock mode, transaction state, and application details. The blocked statement tells you who waited. The blocking side tells you who held the resource at that moment. A head blocker can be sleeping after an earlier statement left a transaction open, so a current request view alone can miss the cause.

I read the XML before asking anyone to kill a session. Which process owns the transaction, and what work will roll back if it ends? The answer matters more than the size of the waiting queue. The report is a clue tied to one time, not a permanent account of the session’s whole life.

Pull the Blocked Process Report From the Ring Buffer

The query below returns target data as XML for the named running session. In SSMS, open the XML result and inspect each blocked process event. If the session has stopped, a ring buffer can disappear; use an event file for durable incident history. Save only the necessary event data under the organization’s logging and privacy rules.

I compare event timestamps with application errors and the error log. A blocked process report can repeat while a long wait continues. Count incidents by blocking chain and time window rather than assuming every XML event is a new problem.

SELECT
    CAST(t.target_data AS xml) AS TargetData
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
  ON t.event_session_address = s.address
WHERE s.name = N'BlockedProcessWatch'
  AND t.target_name = N'ring_buffer';

Find the Root Cause of the Wait

Blocking is a normal part of transaction isolation until its duration hurts the workload. Look for a long transaction, missing index, wide update, application pause inside a transaction, or a competing maintenance task. Check the execution plan of the statements involved and the lock footprint. Change one cause at a time and measure the next run.

I keep the blocker and blocked query together in the case notes. Tuning only the victim query can be wasted effort if it waited behind an idle transaction. The server has already drawn the relationship for you; use it.

Use a File Target for Routine Capture

For ongoing monitoring, move the XE session to an event_file target with bounded file size and rollover. Confirm the SQL Server service account can write to the folder and that collection tools can read the files. Test cleanup and retention. A file target gives you evidence after a restart and avoids a ring buffer being overwritten during a busy incident.

I review the event volume after enabling it. A flood of reports can indicate a real blocking pattern or a threshold too low for normal work. Either result needs an operational decision. Keep the threshold and target configuration documented together.

Close the Loop After a Fix

Reproduce the workload on a safe system, apply the proposed change, and compare blocking reports, request duration, and user impact. Verify that the change did not move the contention somewhere else. If you changed an index or transaction boundary, keep the rollback plan with the result. Remove temporary capture after the investigation if it is not part of the monitoring baseline.

The blocked process report is most valuable when it answers who waited on whom and why. I use it to replace a vague complaint with a specific transaction path that the application and database teams can fix together.

Plan the Review Window

Set the blocked-process threshold around a delay that matters to the service. If a client times out sooner than the threshold, the report will miss that complaint. I test the capture during a known slow operation in a safe environment, then check event volume under ordinary traffic. Keep the XML with a timestamp and server build. A report is one observation of a changing chain, not a complete history. Review the event file after busy periods and adjust retention before it rolls over. The threshold needs a documented owner who knows when to change it.

Related reading on this blog: Blocking Tree: Identifying Blocking Chain Using SQL Scripts and Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices.

A blocked process report is not a list of slow queries, it is a timed picture of a waiting session and its blocker.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.