SQL Server: Collect Page Split events using Extended Event session

Earlier this week someone tried to show me how to capture page split events using Extended Events (XE) but unfortunately, the demo failed. This is a good opportunity for me to refresh my knowledge about page split and set up a simple demo about this. Hopefully, this one will be working.

It’s not necessarily a bad thing when a page split occurs. It’s a totally fine behavior when we INSERT a row in a table with a clustered index (e.g.: column with identity property). SQL Server will create a page to the right-hand side of the index (at the leaf level). This event is a kind of page split; SPLIT_FOR_INSERT

In this post, I will focus on the SPLIT_FOR_UPDATE page split which occurs when we UPDATE a row. The updated row size doesn’t fit anymore in its current page. A new page is allocated by SQL Server, the row is moved to this new page before being written to disk and to the transaction log. Moreover, pages in all the index pointing to the data pages are updated. This type of page split can be problematic.

Let’s start this demo. I’ll use the WildWorldImporters database on SQL Server 2019.

The page_split Extended Event

The Extended Event page_split provides a splitOperation element. The value 3 stands for SPLIT_FOR_UPDATE.
This event did not provide such detailed information before SQL Server 2012.

CREATE EVENT SESSION [PageSplit] ON SERVER
ADD EVENT sqlserver.page_split(
    WHERE (
             [splitOperation]=   3
             AND [database_id]=  9      -- Change to your database_id
       )
)
ADD TARGET package0.ring_buffer
GO
ALTER EVENT SESSION [PageSplit] ON SERVER STATE=START;
GO

Page split demo

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'fill factor (%)', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
use WideWorldImporters
go
create sequence Sequences.PageSplitOdd start with 1 INCREMENT by 2;
create sequence Sequences.PageSplitEven start with 2 INCREMENT by 2;

create table DemoPageSplit (
    id int not null primary key,
    col varchar(8000) null
);

First, I make sure the Fill Factor is set to 100% (value 0) and for the sake of the demo I use sequences to insert only odd numbers in my primary key.
An SQL Server page is 8KB. With this table structure, 2 rows can easily fit on the same page if the varchar column is not fully used.

insert into DemoPageSplit(id, col)
    select next value for [Sequences].PageSplitOdd, replicate('a', 4000)
go 10

I just inserted 10 rows with only 4000 bytes of data each for the varchar column.
Let’s have a look at the clustered index leaf-level pages stats.

SELECT index_type_desc, alloc_unit_type_desc, index_depth, avg_page_space_used_in_percent
    , page_count, record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(
    DB_ID('WideWorldImporters')
    , OBJECT_ID('DemoPageSplit'), NULL, NULL , 'DETAILED')
where index_level = 0 -- 0 for index leaf levels

We can see 5 pages at the leaf-level of the index. Pages are almost 100% full and can fit 2 rows each as expected.

I can now UPDATE one of the rows to trigger a page split.

update DemoPageSplit set col = CONCAT(col, replicate('c', 1000)) where id = 5

The SPLIT_FOR_UPDATE event has been generated successfully.

Now, I run the previous query again about index stats, the result shows a new page has been created while the number of rows is still 10.

Querying the XE data with sys.dm_db_page_info

Using the new SQL Server 2019 DMV in a CROSS APPLY I can directly see in SQL some in-depth details about the pages changed by the page split.

;with xe AS (
    select
        xed.event_data.value('(data[@name="database_id"]/value)[1]', 'int') as databaseId
        , xed.event_data.value('(data[@name="file_id"]/value)[1]', 'int') as fileId
        , xed.event_data.value('(data[@name="new_page_page_id"]/value)[1]', 'int') as new_page
        , xed.event_data.value('(data[@name="page_id"]/value)[1]', 'int') as pageId
        , xed.event_data.value('(data[@name="splitOperation"]/value)[1]', 'varchar') as SplitOperation
    FROM (
        SELECT CAST(target_data as XML) target_data
        FROM sys.dm_xe_sessions AS s
            JOIN sys.dm_xe_session_targets t
                ON s.address = t.event_session_address
        WHERE s.name = 'PageSplit'
          AND t.target_name = 'ring_buffer' 
    ) as tab
        CROSS APPLY target_data.nodes('RingBufferTarget/event') as xed(event_data)
)
select p.page_id, p.page_type_desc, p.free_bytes, p.free_bytes_offset
from xe
    cross apply sys.dm_db_page_info(
        xe.databaseId, xe.fileId, xe.new_page, 'DETAILED'
    ) AS p
union
select p.page_id, p.page_type_desc, p.free_bytes, p.free_bytes_offset
from xe
    cross apply sys.dm_db_page_info(
       xe.databaseId, xe.fileId, xe.pageId, 'DETAILED'
    ) AS p

The page with id 8114 has about 3KB of free space. It’s is the new page produced by the Page Split. The page 8118 contains the row we updated to a 4KB varchar.
In addition to this demo, if you want to go further I suggest you remove the filter on splitOperation from the XE session and run the following batchs;

insert into DemoPageSplit(id, col)
    select next value for [Sequences].PageSplitEven, replicate('a', 4000)
go 10
insert into DemoPageSplit(id, col)
    select next value for [Sequences].PageSplitEven, replicate('a', 8000)
go 10

What kind of Page split operation is produced and what is the consequence on the index page count?

In this blog post, we’ve seen how to easily trigger a page split events of type UPDATE and capture them using an Extended Event Session. We saw that such XE session data can be used with the new DMV sys.dm_db_page_info.