Tidy, but no room

A green check mark, and the splits continue

Two indexes can be almost free of fragmentation after maintenance and still behave differently on the next inserts. We compare six variants from the same starting state. What matters is whether the pages keep room for the following rows and what the maintenance costs.

This is part 2 of a series. Part 1 explains pages, splits and reserve with pictures instead of measurements; if you know picture 8 from there, you will see the same thing here in numbers. Part 3 asks what an index does when nobody maintains it.

The setup

A table with a GUID as the clustering key and 200 bytes of payload. The keys come from a reproducible, widely spread hash sequence, so new rows hit randomly distributed places in the existing key range. We build one million rows with fill factor 81 and then insert 100,000 rows one at a time. Afterwards the index is 16.69% fragmented. We save this state.

Each maintenance variant starts from exactly this backup:

Then another 100,000 single-row inserts follow, the same keys in the same order for every variant. Each comparison runs three times in alternating order, on SQL Server 2022 and 2025 in separate containers. What we measured is a synthetic single index under serial load, not production user latency.

What we count

What REORGANIZE and REBUILD do with the reserve

Fill factor 81 means that about 19% of each leaf page stays free when the index is built. Because only whole rows fit on a page, the measured density after the build is 83.4%, not exactly 81%. This reserve later takes rows that fall into the middle of the page's key range. Once it is used up, SQL Server has to split the page.

After 100,000 randomly distributed inserts, the index looks uneven. Some pages have just been split and are half empty. Others have used up their reserve and are full to the brim.

Schematic

Schematic comparison with the same total reserve

REORGANIZE works on the existing leaf pages. It puts them in the right order and moves rows together from sparsely filled pages, at most up to the fill factor. It does not empty pages that are already fuller. Microsoft describes exactly this limit in the documentation for sys.dm_db_index_physical_stats.

REBUILD rebuilds the index and fills the leaf pages up to the fill factor again.

Both operations reduce fragmentation: REORGANIZE to 0.28%, the offline REBUILD to 0%. By the percentage, both are a success.

The next 100,000 inserts

Chart 1

After REORGANIZE, more than five times as many split log records as after REBUILD FF81

Median of three runs, SQL 2022 and 2025 identical. Highlighted: the two main arms.

Data for the chart

Split log records and fragmentation after the next 100,000 inserts per variant

Variant	Split log records	Fragmentation afterwards
REORGANIZE	14,870	58.2%
REBUILD FF81 offline	2,632	12.8%
No maintenance	15,066	69.8%
Statistics only	15,066	69.8%
REBUILD FF81 online	2,538	12.8%
REBUILD FF100	30,455	96.8%

Median of three runs, SQL Server 2022; SQL Server 2025 delivers the same medians.

After REORGANIZE, the same inserts produce more than five times as many split log records as after the REBUILD with reserve; in other words, the REBUILD leads to about 82% fewer than REORGANIZE. Both engines deliver the same numbers; the key sequence is the same on both.

The control arms show why:

So a fresh, ordered index is not automatically a good starting point for the next inserts into existing ranges. And statistics maintenance can improve a plan, but it does not create room on a page. Conversely, a REBUILD also updates the index statistics. If you see faster queries after a REBUILD, check which of the two effects was at work.

Fewer split log records do not mean proportionally faster inserts. In the lab, insert time after the REBUILD dropped by 17 to 26% compared with REORGANIZE. That is an indication, not a latency measurement: the counting itself, commits in groups of 1,000 rows and background I/O distort absolute times.

The average alone does not explain it

After REORGANIZE, the index is on average even slightly fuller than after the REBUILD, 85.3% versus 83.4% page density. The average alone therefore does not explain the subsequent insert behavior. What also counts is on which pages the free space is.

Chart 2

Full pages stay full after REORGANIZE

Fill level of all captured leaf pages after REORGANIZE and after REBUILD FF81 (SQL 2022, first run). Marked: pages with less than 225 bytes free.

How to read this: Horizontal: how full is a page? Vertical: share of pages with this fill level.

Data for the chart

Leaf pages per row count after REORGANIZE and after REBUILD FF81

Rows per page	Fill level	REORGANIZE pages	Share	REBUILD FF81 pages	Share
1–16	up to 44.5%	121	0.34%	0	0%
17	47.2%	1,298	3.62%	0	0%
18	50.0%	468	1.31%	0	0%
19	52.8%	1,605	4.48%	0	0%
20	55.6%	512	1.43%	1	0.003%
21	58.4%	182	0.51%	0	0%
22	61.1%	101	0.28%	0	0%
23	63.9%	19	0.05%	0	0%
24	66.7%	84	0.23%	0	0%
25	69.5%	74	0.21%	0	0%
26	72.3%	61	0.17%	0	0%
27	75.0%	58	0.16%	0	0%
28	77.8%	65	0.18%	0	0%
29	80.6%	1,392	3.89%	0	0%
30	83.4%	1,888	5.27%	36,666	99.997%
31	86.2%	5,032	14.05%	0	0%
32	88.9%	7,164	20.00%	0	0%
33	91.7%	7,080	19.76%	0	0%
34	94.5%	5,318	14.85%	0	0%
35	97.3%	3,301	9.21%	0	0%

Captured leaf pages: 35,823 after REORGANIZE, 36,667 after REBUILD FF81. Fill level = (8,096 − free bytes) / 8,096. A page holds at most 35 rows of 225 bytes.

In this setup, a new test row takes 223 bytes plus 2 bytes in the page's slot array. After REORGANIZE, 9.21% of the captured leaf pages have less than 225 bytes free. Another row on one of these pages triggers a split. After REBUILD FF81, this applies to no captured page; almost all pages there are in the same fill class.

Both the total amount and the distribution of the reserve differ in this test. 9.21% of the pages is not a split probability of 9.21%: how often inserts hit these pages depends on the key distribution. But the distribution shows why a good average does not predict the next insert load.

What it costs

Maintenance costs log, time and availability. In our frozen comparison, offline, measured as generated log record bytes:

Chart 3

The recovery model changes the cost ratio

Generated log and duration per intervention, SIMPLE and FULL, REBUILD FF81 offline. SQL 2022, median of three runs.

Under SIMPLE, the offline REBUILD is minimally logged, under FULL it is not. The statement “REORGANIZE always writes orders of magnitude more log” is therefore not true in general. We measured the sum of the generated log records, not the size of the log file or the log backup. Other operating conditions, such as an online REBUILD (34 MiB in the same comparison), can change the ratio considerably.

Availability is also part of the decision. An offline REBUILD can block access to the table for the duration of the operation; an online REBUILD also has short locking phases (Microsoft: How online index operations work). REORGANIZE runs online, but it also puts load on the system. We did not measure this under concurrent writes here.

Microsoft generally recommends REORGANIZE because of its lower resource consumption, and at the same time asks you to check benefit and cost against the actual workload (Index maintenance strategy). Our case does not contradict this. It shows a goal that REORGANIZE does not achieve, namely new reserve for inserts into existing ranges.

What we conclude

For the case we examined, repeated inserts into existing key ranges, a REBUILD with reserve is an effective candidate to test. Here, REORGANIZE reduced logical fragmentation but did not renew the reserve on full pages. The difference we observed matches the cross-check shown by Jeff Moden, now on two current versions.

Whether one of these effects justifies the intervention depends on the goal and the workload. Indexes with ascending keys, rarely modified tables or mostly read indexes have different needs, and reserve costs pages that scans have to touch. Better density for reads can also be a legitimate maintenance goal.

Our proposal for the series:

A fragmentation value after the job answers none of these three points.

Coming in part 3: What happens with no maintenance at all? We let two indexes grow to over three million rows without any intervention. Where page density stabilizes and why split log records keep occurring anyway follows in part 3.

Limits of the measurement

• Synthetic data, one clustered index without nonclustered indexes, fixed row width, no deletes, no compression, no LOBs, no partitions.
• Serial load, MAXDOP 1, no concurrent writers; locking impact of online and offline REBUILD not measured.
• Linux containers on a lab VM, with data, log and TempDB on the same SATA SSD. SQL Server 2022 (16.0.4295.3) and 2025 (17.0.5005.3), Developer Edition.
• Auto statistics, Query Store and ADR turned off for the test.
• Maintenance comparison: three repetitions per variant, median. The 60-day series are one deterministic trajectory each, without a measure of spread; they are not independent workloads.
• The page distribution (chart 2) comes from the first run on SQL 2022, not from a median.
• Log volumes: sum of log record lengths after an LSN boundary, not log file or log backup size.
• Checked on 5 October 2026. The test conditions are documented; we will provide separate information about a public reproduction package.

Further reading

• PSG: Part 1 · Room for the next row
• 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: Optimize index maintenance to improve query performance and reduce resource consumption