Room for the next row

A series on index maintenance

Fragmentation, fill factor and index maintenance are discussed regularly in the SQL Server community, including on LinkedIn. This three-part series offers an accessible introduction and a common basis for practice: What happens on the index pages? What do REORGANIZE and REBUILD change? And how can you tell whether maintenance actually helps? We explain the mechanisms with page pictures, test them in our own lab experiments and put the results into context for different write and read patterns.

The maintenance job completed successfully. Fragmentation is low, and yet the next inserts cause splits again. To make sense of this, we need to keep three properties apart: the order of the pages, how full they are, and the free space on the next target page.

A few pages with numbered entries are enough for that. Part 2 and Part 3 then test the picture with measurements.

Rows live on pages

SQL Server stores data in pages. A page is 8 KiB. After the page header, 8,096 bytes remain for the entries and their slot array (Microsoft: Pages and extents). In a clustered index, the rows themselves sit on the pages of the lowest level, the leaf pages, ordered by the key.

So that SQL Server does not have to search every page, there is a signpost above them: “up to 40 on page 1, up to 80 on page 2, after that page 3”. In large tables, this signpost itself has several levels. For everything that follows, the leaf pages and one sign above them are enough.

In our pictures, every page has four slots, and all entries are the same size. The numbers show the logical key order, not where the bytes sit on a real page. Depending on row width, real pages hold dozens or hundreds of rows. The principle stays the same.

Reading: how many pages do I have to touch?

SQL Server always reads whole pages. A point lookup for a single row follows the signpost to exactly one leaf page. A scan over all rows from 10 to 120 reads all leaf pages one after another.

That is page density: how much of the space on the pages is used on average. In the first picture 100%, in the second 75%. In the documentation, the value is called avg_page_space_used_in_percent (Microsoft: sys.dm_db_index_physical_stats). For scans, high density is good: the same data occupies fewer leaf pages, in memory as well as on disk. Pages that are already in the buffer pool do not have to be read from disk again.

If that were all, every page should always be filled to the brim. But new rows come in.

The key determines the target page

A new row cannot go just anywhere. The key determines which page it belongs on. 25 belongs between 20 and 30, so on page 1.

When new keys lie above the existing range, as is usual with IDENTITY, the index grows mostly at the edge. That is the second case. Randomly distributed keys, such as GUIDs from NEWID(), also hit existing ranges in the middle of the index. That is the first case, and it is the main subject of this series.

The target page is full: the page split

What if 25 arrives and page 1 is full?

That is a page split. It costs more than a normal insert: entries are moved, a new page is allocated, the signpost is updated, and all of it is recorded in the transaction log. In our model, the entries are split exactly in half; in SQL Server, the ratio is not always exactly 50:50. A single split is no big deal. Recurring splits are extra write work for some of the inserts.

After the split, there is room on both pages again for the next rows. At the same time, density drops.

In his post about page splits, Brent Ozar uses animations to show the extra write work this involves.

Fragmentation does not change the key order

Look again at the result of picture 4: page 1, then page 7, then page 2. Reading the index in key order means jumping in the file from the front to the back and back again.

That is logical fragmentation: the share of leaf pages whose position in the file does not match the key order. The logical order of the index is fully preserved in both pictures; the index finds every entry. A fragmented index is not a broken index. (Incidentally, only ORDER BY guarantees a specific order in a query result, with or without fragmentation.)

Fragmentation and page density are two different properties. Microsoft says so explicitly and considers page density the more important one in many cases (Microsoft: Index fragmentation and page density). Fragmentation matters mainly when large ranges are read from slow storage.

Reserve: room on the target page

Splits happen because there is no room on the target page. The obvious idea: leave room. But where?

Both states contain the same six entries and two free slots in total. The average density is 75% in both. Now 25 arrives, and the target page is page 1. In state B there is a free slot there, and it fits. In state A, page 1 is full: a split, even though two slots are free – just on page 2.

That is the core of this series: For inserts into existing key ranges, what counts is the free space on the target page, not just the average. With randomly distributed keys, the target page can be almost any page. Whether reserve pays off depends on how often such inserts arrive and what the additional pages cost when reading.

Fill factor: reserve at build time

SQL Server can create reserve deliberately. The fill factor influences how full the leaf pages become when an index is built or rebuilt (Microsoft: Specify fill factor).

Three things matter here:

• The fill factor is a target at build and REBUILD time. The actual fill depends on the row size and does not match the percentage exactly on every page.
• Afterwards, inserts fill up the reserve again. SQL Server does not keep the space free permanently.
• Reserve costs pages. In the picture, fill factor 75 means four pages instead of three, which a scan has to touch and memory has to hold.

For indexes that receive randomly distributed writes, this can pay off. For indexes that are mostly read or only grow at the edge, it is often wasted space. There is no value that fits all indexes.

REORGANIZE and REBUILD

After a while with many random inserts, the index looks mixed: some pages have just been split and are half empty, others have used up their reserve and are full. The pages are scattered all over the file. SQL Server offers two ways to change that.

REORGANIZE works on the existing leaf pages. It reorders them and moves rows together from sparsely filled pages, at most up to the most recently set fill factor. It does not empty pages that are already fuller (Microsoft: Increase page density or reduce page splits). It runs online and can be stopped, but it still puts load on the system.

REBUILD rebuilds the index and applies the fill factor to the leaf pages in the process. It needs more resources at once. Offline, it can block access to the table for its duration; an online REBUILD also has short locking phases (Microsoft: How online index operations work).

Both reduce fragmentation. Which one fits depends on the goal: for more compact pages for reading, REORGANIZE can be enough. For new reserve on full pages, you need a REBUILD. Whether either of them is worth the effort is decided by the workload.

Statistics: a second path of effect

Besides the pages, every index has statistics: a small summary of how the values are distributed, for example “about 30% of the entries are below 50”. The optimizer uses them to estimate how many rows a query returns, and chooses its plan accordingly (Microsoft: Statistics).

A statistics update does not reorder the data on the leaf pages and creates no reserve there. Conversely, a REBUILD updates the statistics of that index along the way, REORGANIZE does not (Microsoft: Statistics update during index build). If a query is faster after a REBUILD, there can therefore be two reasons: different pages or a different plan.

What the measurements say

So much for the model. Whether SQL Server actually behaves this way, we tested in the lab, on SQL Server 2022 and 2025, with a synthetic index and randomly distributed GUID keys:

• Part 2: After REORGANIZE, the index was only 0.28% fragmented. The next 100,000 inserts nevertheless produced 14,870 split log records, after a REBUILD with fill factor 81 only 2,632. That matches picture 8. → Tidy, but no room
• Part 3: With no maintenance at all, the average page density in our test stabilized around 68%, and split log records kept occurring. That is picture 4, repeated millions of times. → When the index grows without maintenance

We did not count the well-known perfmon counter Page Splits/sec, because it also counts adding a new page at the end, as in picture 3. We count the log operation LOP_DELETE_SPLIT; part 2 explains what it captures and what it does not.

Making a maintenance decision

The pictures do not lead to a fixed rule, but to a sequence of questions. Our proposal as a guideline:

For a first assignment, a single index is enough: pick the most important one, write down how it is read and written, set the goal and record the starting state. Part 2 shows what such a comparison looks like.

Summary

• An index stores rows on pages of fixed size; a signpost leads to the right page.
• Page density tells you how full the pages are on average. High density helps scans.
• The key determines the target page of a new row. If that page is full, a page split occurs.
• Fragmentation means that the pages are not in key order in the file. The logical order of the index is preserved.
• Reserve helps inserts if it is on the target page. The average does not reveal that.
• REORGANIZE compacts and orders, but creates no reserve on full pages; REBUILD rebuilds it with the fill factor.
• Statistics act through the plan, not through the pages.

Further reading

• PSG: Part 2 · Tidy, but no room
• PSG: Part 3 · When the index grows without maintenance
• Jeff Moden: “Black Arts” Index Maintenance #1
• Brent Ozar: You've Heard of Page Splits. Here's What They Really Mean.
• Microsoft: Index architecture and design guide