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:
- An INSERT or UPDATE identifies the target page from the clustered key or nonclustered key.
- 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.
- If the row does not fit, SQL Server allocates a new page and moves about half the rows from the original page.
- 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_percentinsys.dm_db_index_physical_statsrises 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.