SQL Server Extended Events to track PAGE SPLITS in SQL Database

What is the extended event to track page split?SQL Server page split occurs when the page in the buffer is full of data, and a new page is generated to write data to disk. It's often necessary to track page splits so that we can find out what is causing the page split to occur. In this post, I will show you how to use the extended events to track page split in SQL Server.

Page splits can slow queries and waste I/O resources when retrieving relational data from a SQL server. Monitoring for page splits and identifying their source can prevent them altogether, allowing databases to run more efficiently.

We can create an extended event with the following script to track down page split for individual indexes.

The following extended event script checks the event sqlserver.transaction_log and looks for the LOP_DELETE_SPLIT operation (value 11). The event sql_server.transaction_log tracks the deletion of rows due to data movement from one page to another as part of page split. It stores collected data in a histogram.

The Histogram target counts the event counts based on the defined action. An asynchronous target stores data into a memory buffer and then transfers it to the target. Since it stores data into memory, the captured data is lost upon SQL Service restart. Therefore, if required, you should save data into an event file for longer retention.

CREATE EVENT SESSION [TrackPageSplits] ON SERVER 
ADD EVENT sqlserver.transaction_log(
    WHERE ([operation]=(11)))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.transaction_log',source=N'alloc_unit_id',source_type=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

You can start the extended event session with the script below.

ALTER EVENT SESSION [TrackPageSplits]
ON SERVER
STATE=START;
GO

It starts data collection as a page split occurs in any SQL database on the instance where you configured an extended event session. You can use the following query( from GitHub Link) to fetch data from the histogram.

https://github.com/rajendragp/RajendraScripts/blob/master/ViewXEventData.sql