The review meeting opened with a graph: SQLServer:Access MethodsPage Splits/sec hovering between 300 and 450 during business hours, and a proposal to schedule full index rebuilds every night across the customer database. The database was 800 gigabytes, the maintenance window was ninety minutes, and the proposal would have consumed all of it to fix a number that was mostly not measuring a problem. Meanwhile the table that actually hurt — a two-hundred-million-row events table clustered on a NEWID() primary key, fragmenting to 97 percent logical fragmentation within days of every rebuild — was generating mid-index splits that bloated the transaction log by an extra forty gigabytes a week and showed up in nobody's graph, because the one counter everyone watches cannot tell a harmless page allocation from a destructive split.
Page splits are one of those SQL Server topics where the folk wisdom and the machinery have drifted far apart. This piece is the machinery: what a split actually is, why the famous counter lies, what mid-index splits really cost, how to measure the real thing, and when fillfactor and rebuilds are the answer. Applies to SQL Server 2016 through 2022.
What is a page split, mechanically?
A page split happens when a row must be inserted into a full data or index page, so the engine allocates a new page, moves roughly half the rows onto it, stitches the pages back into the index's linked list, and writes the row — one logical insert becoming several physical page writes plus a burst of transaction log records describing all of it. There are two species, and confusing them is the root of most bad page-split advice. An end-of-index split — insert a row whose key sorts after every existing key on a full rightmost page — is barely a split at all: the engine allocates the next page, links it in, and moves on, and the operation is cheap, sequential, and unavoidable in any growing table. A mid-index split — insert a row whose key belongs in the middle of a full page — forces the fifty-fifty row migration, leaves both pages half full, scatters the index's physical order away from its logical order, and writes the whole dance to the log as multiple records. The first species is how a healthy index grows; the second is the one that earns the name.
Why does the Page Splits/sec counter lie?
Because it counts both species indiscriminately — every page allocation through the split code path, including the harmless end-of-index growth — so on any insert-heavy workload the counter is dominated by healthy allocations and tells you almost nothing about destructive mid-index activity. A table clustered on an ever-increasing key, inserting ten thousand rows a minute, posts a big Page Splits/sec number while doing zero damage; a GUID-keyed table inserting a tenth that volume can be tearing itself apart at a lower counter value. The review-meeting graph was exactly that: an identity-keyed ingest table contributing most of the count, with the GUID events table invisible in the noise. Measuring the real thing requires an instrument that sees the split's location. Extended Events' page_split event fires per split and carries the page and file, so you can distinguish "new page at the end" from "page allocated into the middle" — it is heavy enough that I run it in targeted bursts, not permanently:
CREATE EVENT SESSION [SplitWatch] ON SERVER
ADD EVENT sqlserver.page_split(
WHERE database_id = DB_ID(N'Orders'))
ADD TARGET package0.ring_buffer(SET max_memory = 4096)
WITH (STARTUP_STATE = OFF);
ALTER EVENT SESSION [SplitWatch] ON SERVER STATE = START;
-- run for a controlled window during the insert peak, then:
ALTER EVENT SESSION [SplitWatch] ON SERVER STATE = STOP;The cheaper standing instrument is sys.dm_db_index_physical_stats sampled on the suspect tables: fragmentation climbing back within days of a rebuild, page fullness trending toward half, and record counts growing while page counts grow faster are the mid-index split signature written across the index itself.
What do mid-index splits actually cost?
They cost transaction log volume, lock duration, buffer pool waste, and eventually read efficiency — and the log cost is the one that surprises people. Every mid-index split is fully logged: the page allocation, the row movements, the linked-list repairs. On the GUID events table we measured the insert workload generating roughly three times the log volume of the raw row data, which mattered concretely because the log shipped to a DR secondary and the backups ran against a fixed window — the splits were spending log throughput and backup time, not just buffer pool space. Half-full pages also mean the same rows occupy twice the buffer pool and twice the read I/O when scanned, and the scattered physical order costs you on large range reads from spinning media or cold cache. And the split itself takes latches and locks on three pages where a clean insert takes one, so a hot splitting index serializes writers — the close cousin of the last-page insert contention from my latch contention notes, with the contention moved into the middle of the tree.
Does fillfactor actually fix page splits?
Fillfactor fixes the right table and wastes space on the wrong one: it leaves free space in each page at build time so mid-index inserts have room to land without splitting, which transforms the GUID-keyed table's behavior and does nothing for an append-only identity-keyed table except make it permanently larger. On the events table, rebuilding with FILLFACTOR = 90 stretched the time-to-fragmented from three days to three weeks, and the log volume per insert dropped measurably within the first day. The discipline is targeting: fillfactor belongs on indexes with proven mid-index split damage — random keys, or heavy updates to variable-width columns that grow rows in place — chosen one index at a time from the physical-stats evidence, not sprayed across the database. A blanket fillfactor of 80 on every index, a real recommendation I have been handed twice, inflates every table by twenty-five percent forever to help the handful that split. The pad_index option and the reservable-page behavior on inserts have their place too, but the honest summary is: measure, pick the indexes that split, give them headroom, leave everyone else alone.
When do rebuilds and reorgs actually help?
They help when fragmentation costs measurable I/O — large range scans on cold cache or rotational storage — and they are waste when the workload is small point lookups against a warm buffer pool, because logical fragmentation barely touches a seek that reads three pages regardless of order. The events table earned a scheduled rebuild because the nightly aggregation scanned tens of gigabytes and the scan time tracked fragmentation almost linearly. The same rebuild applied to the whole database every night would have been pure log generation and window consumption. The fuller accounting of when index maintenance pays and when it is ritual — including why statistics matter more than fragmentation most weeks — is in my index maintenance reality check. For splits specifically, the sequence that works is: prove mid-index splitting with physical stats or the XE session, fix the key or apply fillfactor to the proven offenders, then schedule maintenance sized to what the evidence shows, not to what the counter suggests.
Watching split behavior with MonPG when SQL Server support lands
The signals that matter here are fragmentation and page fullness on the proven offender tables trended over time, log bytes per second during insert peaks as the cost meter, and index rebuild durations so maintenance stays inside its window. A Page Splits/sec graph with no per-table evidence underneath it is an invitation to schedule a nightly full rebuild, and it should be treated as such. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, sample sys.dm_db_index_physical_stats on your five largest tables weekly, run the SplitWatch session during your next insert peak, and let the evidence pick your fillfactors.