Page splits can have a significant impact on the performance of your SQL Server instance. They occur when a new row is inserted into a page that is already full, causing the page to split into two. This can lead to fragmentation and decreased query performance.

But how can you track page splits in SQL Server? The easiest way is to use PerfMon Counters. One useful counter is “SQL Server: Access Methods -> Page Splits/sec”. This counter allows you to monitor if page splits are occurring in your SQL Server instance.

However, there is another way to track page splits in more detail using Extended Events. Extended Events is a powerful feature in SQL Server that allows you to capture and analyze detailed information about various events occurring within the database engine.

Here is an example of how you can use Extended Events to track page splits:

-- Create a dummy database
CREATE DATABASE PageSplits

-- Get the DB ID, we will use it in the next step
SELECT DB_ID('PageSplits')

-- Create Session
CREATE EVENT SESSION [Catch_PageSplit] ON SERVER 
ADD EVENT sqlserver.page_split (WHERE ([package0].[equal_uint64]([database_id], (10))))
-- NOTE: Please make sure you change the DATABASE_ID

ADD TARGET package0.ring_buffer 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
)

-- Start the event session
ALTER EVENT SESSION [Catch_PageSplit] ON SERVER STATE = START;

Once you have created the event session, you can start monitoring for page splits. In this example, we will create a table and update a row to introduce a page split scenario:

USE PageSplits

CREATE TABLE mytest (
    myCompany CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
    FillData VARCHAR(3000) NOT NULL
)

INSERT mytest (myCompany, FillData)
VALUES
    ('00001', REPLICATE('A', 3000)),
    ('00002', REPLICATE('B', 1000)),
    ('00003', REPLICATE('C', 3000)),
    ('00004', REPLICATE('A', 3000)),
    ('00005', REPLICATE('B', 1000)),
    ('00006', REPLICATE('C', 3000)),
    ('00007', REPLICATE('A', 3000)),
    ('00008', REPLICATE('B', 1000)),
    ('00009', REPLICATE('C', 3000))

-- Update to introduce a page split
UPDATE mytest SET FillData = REPLICATE('B', 3000) WHERE myCompany = '00002'

By monitoring the live data feed, you can observe the page split that occurred due to the update:

As you can see, this page split happened because of an update. The two pages involved in the split are also shown, along with the database ID. This information can be valuable for troubleshooting and optimizing your SQL Server performance.

Once you have finished monitoring, you can clean up the database and event session:

USE MASTER

DROP DATABASE PageSplits

DROP EVENT SESSION [Catch_PageSplit] ON SERVER

Tracking page splits using Extended Events is a powerful technique that can help you gain insights into the inner workings of SQL Server. By understanding when and why page splits occur, you can make informed decisions to optimize your database performance.

Hope you found this article interesting and informative. Let us know how you have used Extended Events to learn something new inside SQL Server!