Blog

SQL Server’s blocked process report is an XML event fired when a lock wait exceeds the blocked process threshold. Set sp_configure 'blocked process threshold' in seconds, create a Service Broker queue and an Event Notification for BLOCKED_PROCESS_REPORT, then read the XML to identify the blocking and blocked sessions.

This guide captures blocked process reports with Event Notifications on SQL Server 2016 or later, so you can identify blocking and blocked sessions without a profiler trace. You need ALTER ANY EVENT NOTIFICATION server permission and a database enabled for Service Broker. The steps were tested on SQL Server 2019 CU18; behaviour differs before 2016 SP1.

What SQL Server’s blocked process report is

SQL Server fires the BLOCKED_PROCESS_REPORT event when a lock wait exceeds the blocked process threshold, measured in seconds. The threshold is set with sp_configure 'blocked process threshold', N; a value of 0 disables the report. The event arrives as an XML document through Event Notifications. It sits alongside Extended Events: Event Notifications cover server-level events, while an XE session is often used for trace-like collection. The XML report identifies the blocked session, the blocking session, and the resource they wait on. Use Event Notifications when you want a message-based capture without a continuous trace or an XE session.

Enable the blocked process threshold

Run sp_configure to show advanced options, then set the threshold. The value is in seconds and applies to the whole instance.

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

The RECONFIGURE calls apply the settings immediately. The threshold in this example is 20 seconds; choose a value that avoids noise but catches waits that matter. Verify with sp_configure 'blocked process threshold'; both config_value and run_value should show 20 after reconfiguration. Set the value back to 0 to disable the report. The threshold is a server-level setting, so it affects blocked process reports for all databases on the instance.

Create the Service Broker queue and service

Use a dedicated queue so blocked process events do not mix with other Service Broker traffic. The queue holds messages until you receive them; activation is not required when you consume with RECEIVE. The service must advertise the contract that Event Notifications uses, or delivery will fail.

CREATE QUEUE BlockedProcessQueue;

CREATE SERVICE BlockedProcessService
ON QUEUE BlockedProcessQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

A contract defines the message types a service can send and receive. If the service does not list the correct contract, delivery fails. Use unique queue and service names in a shared environment. Service Broker delivery is transactional; if the database is offline or broker is disabled, messages wait in sys.transmission_queue instead of reaching the queue.

Create the event notification for BLOCKED_PROCESS_REPORT

Create the notification at server scope so it fires for blocked process reports from any database. The event name in T-SQL is BLOCKED_PROCESS_REPORT, not BLOCKED_PROCESS.

CREATE EVENT NOTIFICATION BlockedProcessNotification
ON SERVER
FOR BLOCKED_PROCESS_REPORT
TO SERVICE 'BlockedProcessService', 'current database';

The ON SERVER clause is mandatory for this event. Messages go to the queue backing BlockedProcessService in the current database; the queue retains them until you run RECEIVE. Confirm the object exists with sys.server_event_notifications.

SELECT name, service_name
FROM sys.server_event_notifications
WHERE name = N'BlockedProcessNotification';

The service_name returned by the view must match the name used in TO SERVICE. 'current database' resolves the broker instance of the current database. The notification is server-scoped, so check sys.server_event_notifications rather than the database-level sys.event_notifications view.

Read the blocked process report XML

The XML payload begins at blocked-process-report and contains one blocked-process element and at least one blocking-process element. Each process element carries id, spid, waitresource, and optionally cpu; inputbuf is a child of process and holds the last statement submitted by the session.

<blocked-process-report monitorLoop="1044">
  <blocked-process>
    <process id="process4b8c1c8" spid="52" waitresource="KEY: 5:72057594044284928 (1a39e609515c)" cpu="1400" status="suspended" lockMode="U">
      <inputbuf>UPDATE dbo.Orders SET Status = 'Packed' WHERE OrderId = 1001;</inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process id="process4b8c1c9" spid="51" waitresource="KEY: 5:72057594044284928 (1a39e609515c)" cpu="0" status="sleeping" lockMode="X">
      <inputbuf>BEGIN TRANSACTION; UPDATE dbo.Orders SET Status = 'Held' WHERE OrderId = 1001;</inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

In this example, session 52 waits on the key for dbo.Orders.OrderId = 1001, and session 51 holds the incompatible lock. Both sessions show the same waitresource, so the block is on one index key.

Element	What it shows
blocked-process-report	Root of the report; monitorLoop is the monitoring cycle identifier.
blocked-process	Session that is waiting.
blocking-process	Session holding the lock.
process	Session details: id, spid, waitresource, and optionally cpu, status, and lockMode.
waitresource	Resource identifier that shows the object or key being waited on.
cpu	CPU time attribute when present in the report.
inputbuf	Child of process; the last input buffer for a session, useful for the query text.

Open the message_body from the queue to see the full report for your incident.

Verify the notification and troubleshoot common failures

To test, open two sessions. In session A, begin a transaction and update a row; in session B, run a statement that waits on the same row. After the threshold has passed, receive the report from the queue.

RECEIVE TOP (1)
    message_type_name,
    CAST(message_body AS XML) AS blocked_report
FROM BlockedProcessQueue;

Check sys.service_queues and sys.transmission_queue if no message arrives. Common failures are Service Broker disabled in the database, a missing service contract, or missing ALTER ANY EVENT NOTIFICATION permission.

FAQ

Where is the documentation for SQL Server blocked process report?

There is no single consolidated page. Reconstruct it from Microsoft Learn articles on the threshold, Event Notifications, and the event class.

How do I enable blocked process threshold?

Use sp_configure 'blocked process threshold', N in seconds and RECONFIGURE; 0 disables it.

Why is my blocked process alert showing no object or empty process name?

Application Name can be empty in the connection string, and the XML does not guarantee an object name.

Does Event Notifications work on SQL Server 2016, 2019 and 2022?

Yes. The pattern is the same, but the threshold and Service Broker require the listed permissions.

How do I read the blocked process report XML?

Start with blocking-process and blocked-process, then waitresource, cpu, and inputbuf.

Sources

• Microsoft Learn, “blocked process threshold Server Configuration Option” — https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/blocked-process-threshold-server-configuration-option
• Microsoft Learn, “Event Notifications” — https://learn.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications
• Microsoft Learn, “CREATE EVENT NOTIFICATION (Transact-SQL)” — https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql

A page split in SQL Server happens when an inserted or updated row no longer fits in its 8 KB data page. SQL Server allocates a new page, moves about half the rows to it, and links the pages in index order. End-of-index splits are cheap; mid-index splits fragment pages and add I/O.

What a page split is

SQL Server stores rows on 8 KB data pages, as described in the pages and extents architecture guide. A page split is the operation that runs when an insert or update needs to place a row on a page that has no room for it.

The engine allocates a new page, moves roughly half of the existing rows across, and rewrites the page pointers so the B-tree keeps its logical key order. The term applies at the leaf level of a clustered index and at the leaf level of a nonclustered index, but it can also affect an intermediate index page when the page chain must grow.

A data page is the fixed-size unit that SQL Server reads and writes as a whole; an index page stores keys and child-page pointers for navigation. After a split, the parent index page stores an extra pointer to the new page. A split is normal B-tree growth. It becomes a cost only when it happens frequently in the middle of an ordered page range, or when the table is large enough for the extra I/O and transaction log writes to show up in wait statistics.

How a page split happens

The cause of a page split is a single condition: SQL Server must place a row on a specific page, and that page cannot hold the whole row. The common SQL Server page split causes follow the same sequence:

1. An INSERT or UPDATE identifies the target page from the clustered key or nonclustered key.
2. SQL Server checks the free space on that 8 KB data page. Under the default fill factor of 100, pages are filled to capacity and leave little room for new rows.
3. If the row does not fit, SQL Server allocates a new page and moves about half the rows from the original page.
4. It updates the page pointers and records the page movement in the transaction log.

Where the split lands matters. An IDENTITY or sequence key inserted at the end of the sorted key range produces an append split, so it is cheap. A non-sequential GUID inserted between existing keys produces a mid-page split, leaving free space on both pages and creating fragmentation. An UPDATE that grows a variable-length column follows the same path; Extended Events records it as SPLIT_FOR_UPDATE.

Why page splits matter

A page split is not just a logical bookkeeping change. It has physical costs:

• I/O: SQL Server writes the original page, the new page, and often the parent index page. Splits therefore multiply the writes of a single insert or update.
• Transaction log: the row movement is logged; frequent splits increase log generation and can slow log backups.
• Index fragmentation: mid-page splits leave roughly half-empty pages. avg_fragmentation_in_percent in sys.dm_db_index_physical_stats rises as pages become out of order.
• Buffer pool pressure: more pages must be read for the same number of rows, which can lower cache efficiency.

Not all splits are equal. An append split at the right-hand edge of a clustered index moves no existing rows and leaves the original page full, so its main cost is allocating a page and logging the new page chain. A mid-page split moves half the rows and turns one full page into two half-full pages; that is the pattern that shows up later as fragmentation.

The fill factor documentation shows how a split moves rows and how free space on a page delays the next split.

How to detect page splits in SQL Server with DMVs

The quick way to quantify splits without setting up Extended Events is to query sys.dm_db_index_operational_stats. The leaf_allocation_count column counts page allocations at the leaf level and is a direct signal of page splits; compare it with leaf_insert_count and leaf_update_count to see whether most inserts reach an empty page or force a split.

SELECT
    DB_NAME(ios.database_id) AS database_name,
    OBJECT_NAME(ios.object_id) AS table_name,
    i.name AS index_name,
    ios.leaf_insert_count,
    ios.leaf_update_count,
    ios.leaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
JOIN sys.indexes AS i
    ON i.object_id = ios.object_id
   AND i.index_id = ios.index_id
WHERE i.index_id > 0
ORDER BY ios.leaf_allocation_count DESC;

Run it in the target database; the function takes a database ID and returns one row per index. A high leaf_allocation_count compared with leaf_insert_count points to frequent splits. leaf_update_count is useful when updates, not inserts, are the likely trigger. Expect leaf_allocation_count to stay close to leaf_insert_count when inserts land at the end of an increasing key. With a non-sequential GUID, the allocation count normally climbs faster than the insert count. If you only want one table, join to sys.partitions or filter by OBJECT_ID.

These counters are cumulative since the service last started, so take two snapshots and compare the difference across a known workload. After a split-heavy period, check avg_fragmentation_in_percent in sys.dm_db_index_physical_stats; page splits are one cause, but not the only one. Column definitions are documented in sys.dm_db_index_operational_stats (Transact-SQL).

Tracking page splits with Extended Events

Extended Events exposes the sqlserver.page_split event for cases where you need to attribute a split to a specific query or session. The event includes a splitOperation field with two values: SPLIT_FOR_INSERT, for a split caused by an insert, and SPLIT_FOR_UPDATE, for a split caused by an update that grows the row on its current page. Create an event session on the busy database, add actions for sql_text or session_id if you need to find the source, and keep the session short. Extended Events capture only what happens while the session runs; the DMV counters above are cumulative and need less setup.

SQL Server fill factor and page splits

Fill factor sets how full SQL Server makes each index page when it rebuilds or creates an index. The default is 100, which leaves no free space for later inserts. A fill factor of 80 leaves 20 percent free, so more rows can be added before a split is needed. That reduces mid-page splits for indexes that receive random inserts, such as a non-sequential GUID key.

The trade-off is storage and cache: pages are less full, so the same index uses more space and more buffer pool. A fill factor of 80 is not a global fix; it helps workloads that insert into random key values, and it does little for append-only inserts, which split at the leaf edge anyway. Set the fill factor at build or rebuild time, and test it on a copy first. Microsoft Learn’s fill factor guide documents the setting and its effect on page splits.

When to worry about page splits

Context matters more than the raw split count. Use this table as a starting point before you change fill factor or rebuild an index.

Scenario	Usually fine	Investigate
Sequential clustered key, such as an IDENTITY column	Append splits at the end of the leaf level; pages stay full and little fragmentation appears	If insert volume is so high that log writes or latch contention become visible
Non-sequential GUID as the clustered key	Small, rarely written tables can tolerate the occasional split	Frequent mid-page splits on large tables, rising leaf_allocation_count
Updates that grow variable-length columns	A fill factor below 100 leaves room for longer rows	Repeated SPLIT_FOR_UPDATE events, extra I/O, growing avg_fragmentation_in_percent
Read-mostly table with low insert volume	Page splits are rare and usually not worth tuning	If a single batch triggers a large number of splits and then queries slow down

FAQ

Is a page split bad in SQL Server?

Not always; append splits at the end of an increasing key are cheap, while mid-index splits fragment pages.

How do page splits affect index fragmentation?

They move rows to a new page, leaving free space and out-of-order pages, which raises avg_fragmentation_in_percent.

How does fill factor reduce page splits?

Fill factor below 100 leaves free space on each page so more rows fit before a split is needed.

How do you track page splits in SQL?

Use Extended Events with sqlserver.page_split, or DMVs like sys.dm_db_index_operational_stats.

Does a page split happen on insert or update?

Both; an update that grows a row can trigger SPLIT_FOR_UPDATE when the row no longer fits.

Sources

• Microsoft Learn, “Pages and Extents Architecture Guide” — page structure and 8 KB data pages.
• Microsoft Learn, “Specify Fill Factor for an Index” — fill factor and page split behaviour.
• Microsoft Learn, “sys.dm_db_index_operational_stats (Transact-SQL)” — DMV columns used in the detection script.

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!