Blocked Process Reports: From Capture to Root Cause
“The application was slow for about a minute around 10:15, then it cleared up on its own.” That is the entire bug report, and by the time you log in, whatever was blocking has long since committed and gone. sys.dm_exec_requests shows you blocking happening right now, which is wonderful at 10:15 and useless at 10:45. There is no DMV that remembers the chain of sessions that piled up half an hour ago.
SQL Server can remember it for you, but only if you ask first. The blocked_process_report event fires whenever a request waits longer than a threshold you set, and it carries both sides of the jam: the session that was stuck and the session holding the lock it needed. This is the second post in the Extended Events for the working DBA series, and it reuses the same capture-and-persist pipeline from the deadlock post, pointed at blocking instead. If you read that one, the session and Agent-job mechanics here will look familiar; the interesting part is the threshold you have to enable and the XML you shred at the end.
The Switch You Have to Flip First
Deadlocks are captured for free. Blocked process reports are not. The event never fires until you set the blocked process threshold, a server configuration that defaults to zero, meaning off.[1] Set it to a number of seconds and SQL Server will raise a blocked_process_report for any request that has been blocked at least that long, and again at each multiple of the interval while it stays blocked.
|
1 2 3 4 5 6 7 |
EXEC [sys].[sp_configure] @configname = N'show advanced options', @configvalue = N'1'; RECONFIGURE; /* Raise a report for anything blocked 5 seconds or longer. Pick a value above your normal, healthy waits so you capture problems, not routine contention. */ EXEC [sys].[sp_configure] @configname = N'blocked process threshold (s)', @configvalue = N'5'; RECONFIGURE; |
Choose the threshold deliberately. Too low and you will drown in reports for ordinary short waits that resolve themselves. Too high and you miss the sub-ten-second stalls that users still notice. Five seconds is a sensible starting point on most systems: long enough that a healthy query will not trip it, short enough to catch the “it was slow for a minute” complaints. Tune it from there based on what your workload considers normal.
A Session for Blocked Process Reports
The session is the same shape as the deadlock session, capturing a single event into a small, capped ring buffer with STARTUP_STATE = ON so it survives a restart. The only real change is the event name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
IF NOT EXISTS ( SELECT 1 FROM [sys].[server_event_sessions] AS [ss] WHERE [ss].[name] = N'blocked_processes' ) BEGIN CREATE EVENT SESSION [blocked_processes] ON SERVER ADD EVENT [sqlserver].[blocked_process_report] ( ACTION ( [sqlserver].[client_app_name] , [sqlserver].[client_hostname] , [sqlserver].[database_name] ) ) ADD TARGET [package0].[ring_buffer] ( SET max_memory = 2048 /* KB. Capped to avoid XML truncation. */ , occurrence_number = 0 , max_events_limit = 0 ) WITH ( MAX_MEMORY = 10 MB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 5 SECONDS , STARTUP_STATE = ON ); END; IF NOT EXISTS ( SELECT 1 FROM [sys].[dm_xe_sessions] AS [xs] WHERE [xs].[name] = N'blocked_processes' ) BEGIN ALTER EVENT SESSION [blocked_processes] ON SERVER STATE = START; END; |
The ring buffer caveats from the deadlock post apply unchanged: keep it small, harvest it often, and remember that a service restart empties it.[3] The persistence pattern is identical, so I will keep it short here.
Persisting the Reports
The harvesting procedure drains the blocked_processes session into a table in the DBA utility database. Blocked process reports do not carry a tidy natural key the way a deadlock victim id does, so this version dedups on the event timestamp alone. That is slightly crude, two genuinely separate reports in the same instant would collapse to one, but in practice the timestamp resolution is fine and it keeps the harvest simple.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
USE [DBA]; GO CREATE OR ALTER PROCEDURE [dbo].[GatherBlockedProcessEvents] AS BEGIN SET XACT_ABORT, NOCOUNT ON; IF OBJECT_ID(N'dbo.BlockedProcess_xml_events', N'U') IS NULL BEGIN CREATE TABLE [dbo].[BlockedProcess_xml_events] ( [xeTimeStamp] datetimeoffset(7) NOT NULL , [xeXML] xml NOT NULL ); CREATE CLUSTERED INDEX [BlockedProcess_xml_events_cx] ON [dbo].[BlockedProcess_xml_events] ([xeTimeStamp]); END; DECLARE @target_data xml; SELECT @target_data = CONVERT(xml, [t].[target_data]) FROM [sys].[dm_xe_sessions] AS [s] INNER JOIN [sys].[dm_xe_session_targets] AS [t] ON [t].[event_session_address] = [s].[address] WHERE [s].[name] = N'blocked_processes' AND [t].[target_name] = N'ring_buffer'; ;WITH [src] AS ( SELECT [xeXML] = [xm].[s].query('.') FROM @target_data.nodes('/RingBufferTarget/event') AS [xm]([s]) ) INSERT INTO [dbo].[BlockedProcess_xml_events] ( [xeTimeStamp] , [xeXML] ) SELECT [xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)') , [src].[xeXML] FROM [src] WHERE NOT EXISTS ( SELECT 1 FROM [dbo].[BlockedProcess_xml_events] AS [dxe] WHERE [dxe].[xeTimeStamp] = [src].[xeXML].value('(/event/@timestamp)[1]', 'datetimeoffset(7)') ); END; GO |
Schedule it exactly as in the deadlock post: a SQL Server Agent job calling EXEC [dbo].[GatherBlockedProcessEvents]; every five minutes. I will not repeat the full sp_add_job block here; copy it from part one and change the job name, step command, and description.
Reading Both Sides of the Jam
This is where blocked process reports earn their keep. The XML holds a blocked-process-report element with two children: blocking-process and blocked-process.[2] Pull both side by side and a stalled-application complaint turns into a precise statement of who held what while who waited. For each side you get the SPID, the status, the procedure name, the input buffer (the actual SQL text), and the client app, host, and login.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE [DBA]; GO DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -4, SYSUTCDATETIME()); DECLARE @EndTime datetimeoffset = SYSUTCDATETIME(); DECLARE @timezone sysname = N'Central Standard Time'; SELECT [Blocking_Spid] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@spid)[1]', 'int') , [Blocking_Status] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@status)[1]', 'varchar(100)') , [Blocking_Proc] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/executionStack/frame/@procname)[1]', 'nvarchar(128)') , [Blocking_Text] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(4000)') , [Blocking_Login] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@loginname)[1]', 'varchar(128)') , [Blocked_Spid] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@spid)[1]', 'int') , [Blocked_Status] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@status)[1]', 'varchar(100)') , [Blocked_Proc] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/executionStack/frame/@procname)[1]', 'nvarchar(128)') , [Blocked_Text] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(4000)') , [EventTimeLocal] = [x].[xeTimeStamp] AT TIME ZONE @timezone FROM [dbo].[BlockedProcess_xml_events] AS [x] WHERE [x].[xeXML].exist('/event[@name="blocked_process_report"]') = 1 AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1 AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1 ORDER BY [x].[xeTimeStamp] DESC; |
The status column on each process is the detail people overlook. A blocking process with a status of sleeping is the classic abandoned-transaction signature: a session that ran a statement inside a transaction, never committed, and is now sitting idle holding locks while everything behind it waits. That is a very different problem from a blocker with a status of running, which is simply a slow statement that needs tuning. The report tells you which one you are looking at.
Ranking the Repeat Offenders
As with deadlocks, a single report identifies one incident; the value of keeping history is aggregation. Group the stored reports by the blocking and blocked procedure pair and you get a ranked list of the contention hotspots over your whole window.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
;WITH [src] AS ( SELECT [Blocking_Proc] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/executionStack/frame/@procname)[1]', 'nvarchar(128)') , [Blocked_Proc] = [x].[xeXML].value('(/event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/executionStack/frame/@procname)[1]', 'nvarchar(128)') FROM [dbo].[BlockedProcess_xml_events] AS [x] WHERE [x].[xeXML].exist('/event[@name="blocked_process_report"]') = 1 AND [x].[xeXML].exist('/event[@timestamp>=sql:variable("@StartTime")]') = 1 AND [x].[xeXML].exist('/event[@timestamp<=sql:variable("@EndTime")]') = 1 ) SELECT [src].[Blocking_Proc] , [src].[Blocked_Proc] , [Number of Reports] = COUNT_BIG(1) FROM [src] GROUP BY [src].[Blocking_Proc] , [src].[Blocked_Proc] ORDER BY [Number of Reports] DESC; |
Run against a few hours of history, the ranking points straight at the contention:
|
1 2 3 4 5 |
Blocking_Proc Blocked_Proc Number of Reports -------------------------- -------------------------- ----------------- dbo.RebuildNightlyTotals dbo.GetCustomerDashboard 52 dbo.RebuildNightlyTotals dbo.GetOpenOrders 31 dbo.ApplyPayment dbo.ApplyPayment 6 |
That top row is a story you can act on. A batch procedure, dbo.RebuildNightlyTotals, is holding locks that two read-path procedures keep running into, and it is responsible for the bulk of the blocking. The fix is not in the readers, it is in the batch job: a smaller transaction, a snapshot-based read for the dashboard, or simply running the rebuild outside business hours. The third row, a procedure blocking itself, is the signature of two concurrent callers of the same proc serialising on the same rows, a different fix again. None of that is visible from a live sys.dm_exec_requests snapshot after the fact. It is only visible because the reports were captured and kept.
Caveats Worth Stating
- The threshold is global.
blocked process thresholdapplies to the whole instance; there is no per-database setting. Set it once, for the busiest workload you care about. - Reports repeat while blocking persists. A request blocked for 30 seconds at a 5-second threshold generates several reports, not one. That is useful for seeing how long a stall lasted, but factor it in when you count: a high report count can mean one long block, not many short ones.
- Timestamp-only dedup is approximate. This harvest treats one timestamp as one event. If you need exact fidelity, add the blocked SPID or a hash of the XML to the key.
- The ring buffer and restart caveats carry over. Everything said about the ring buffer target and
STARTUP_STATEin the deadlock post applies here without change. - Add retention. Delete reports older than your review window so the table stays small.
The Takeaway
Live blocking is easy to see and impossible to see after the fact, which is exactly when the bug reports arrive. Flip on the blocked process threshold, capture blocked_process_report into your own table on the same pipeline you built for deadlocks, and the next “it was slow for a minute” complaint becomes a query that names the blocker, the blocked, the SQL each was running, and how often the pair collides. The blocker’s status usually tells you whether you are chasing an abandoned transaction or a slow statement, and the frequency ranking tells you which collision is worth fixing first.
How do you catch intermittent blocking, live monitoring, the blocked process report, or something else entirely? I would like to hear what has worked for you. Find me on Bluesky or LinkedIn.
For the background on detecting blocking in the first place, I covered it earlier in Blocked process detection and analysis and the worked blocking example code. This post is the production-grade version: capture the reports, keep them, and rank the contention.
More in this series
- Part 1: From deadlock graph to repeat offender
- Part 2: Blocked process reports, from capture to root cause (this post)
- Part 3: A lightweight error audit with Extended Events
- Part 4: Mining the system_health session you already have
- Part 5: Catching slow file I/O with a filtered session
References
- Configure the blocked process threshold server configuration option – Microsoft Learn. What the threshold does, its default of zero, and how the report interval works. ↩
- Blocked process report – Microsoft Learn. The structure of the report, including the blocking-process and blocked-process elements and their attributes. ↩
- Why I hate the ring_buffer target in Extended Events – Jonathan Kehayias, SQLskills. The ring buffer truncation behaviour that drives the small-and-frequent harvest design. ↩