When the index grows without maintenance
Why a stable page density does not mean a quiet index – and how to compare the cost of intervening with the cost of waiting.
The findings in 30 seconds
- Without maintenance, the average page density settled around 68% in our test, for both starting states tested.
- That is not a state of rest: on the plateau, around 225 split log records per 10,000 inserts kept occurring.
- Waiting costs space: after 60 simulated days, the unmaintained index occupied 24% more leaf pages than one with early REBUILD. Intervening costs too; which calculation works out is decided by the workload.
- With ascending keys, the reserve created by a REBUILD does not help freshly appended rows if they grow later.
Waiting is a strategy, too
One possible maintenance strategy is not to intervene at all, at least at first. What happens then under continued, randomly distributed inserts? In our tests, the average page density settles while further split log records keep occurring. That leads to the practical question: is this state acceptable for your own workload, or is a targeted intervention worth it?
This is part 3 of a series. Part 1 explains pages, splits and reserve with pictures. Part 2 compares REORGANIZE and REBUILD from the same starting state.
The setup
A GUID as the clustering key plus 100 bytes of payload, 123 bytes per row. The keys come from a reproducible, widely spread hash sequence.
- Two dense starts: 100,000 rows, built once with fill factor 100 and once with fill factor 81. Then the same randomly distributed inserts, one at a time, with no index or statistics maintenance at all. Measured after every step: page density across all leaf pages, fragmentation, split log records (
LOP_DELETE_SPLIT, as explained in part 2). - Plateau criterion, defined in advance: over the last million inserted rows, density varies by at most one percentage point and rises or falls by at most 0.5 percentage points per million. We let the index grow to at least 3.1 million rows and then until this holds for at least eleven measurement points.
- Cross-check: the unmaintained runs from part 2, started empty, on SQL Server 2022 and 2025.
The dense starts ran on SQL Server 2022 only, the cross-check on both versions.
The plateau
Page density comes to rest, the split log records keep coming
Two dense starts with 100,000 rows, then only randomly distributed single-row inserts. SQL Server 2022.
Data for the chart
| Rows (millions) | FF100 density | FF100 split log records per 10,000 | FF81 density | FF81 split log records per 10,000 |
|---|---|---|---|---|
| 0.10 | 98.78% | – | 81.82% | – |
| 0.11 | 53.96% | 1,593 | 89.20% | 22 |
| 0.12 | 58.70% | 9 | 71.28% | 696 |
| 0.13 | 63.59% | 0 | 58.27% | 846 |
| 0.14 | 68.48% | 0 | 58.06% | 282 |
| 0.15 | 73.12% | 11 | 61.23% | 60 |
| 0.16 | 77.05% | 39 | 65.09% | 13 |
| 0.17 | 78.30% | 146 | 68.94% | 12.0 |
| 0.18 | 76.22% | 299 | 72.57% | 22 |
| 0.19 | 72.04% | 427 | 75.37% | 63 |
| 0.20 | 67.30% | 515 | 76.77% | 130 |
| 0.21 | 64.36% | 449 | 76.37% | 229 |
| 0.22 | 62.66% | 385 | 74.58% | 309 |
| 0.23 | 62.00% | 309 | 71.34% | 424 |
| 0.24 | 62.36% | 214 | 68.16% | 459.0 |
| 0.25 | 63.40% | 146 | 65.68% | 440 |
| 0.26 | 64.69% | 119.0 | 64.12% | 384.0 |
| 0.27 | 66.37% | 77 | 63.20% | 339 |
| 0.28 | 68.10% | 68 | 63.07% | 260 |
| 0.29 | 69.82% | 68 | 63.32% | 216 |
| 0.30 | 71.18% | 96.0 | 63.92% | 175.0 |
| 0.40 | 67.83% | 260.4 | 72.45% | 129 |
| 0.50 | 65.63% | 266.5 | 67.23% | 296.5 |
| 0.60 | 69.86% | 150.8 | 65.75% | 261.8 |
| 0.70 | 70.20% | 215.0 | 68.93% | 159.8 |
| 0.80 | 67.84% | 281.6 | 70.12% | 195.2 |
| 0.90 | 66.56% | 267.3 | 69.09% | 250.2 |
| 1.00 | 66.81% | 224.7 | 67.50% | 276.4 |
| 1.10 | 68.13% | 182.7 | 66.86% | 253.5 |
| 1.20 | 69.06% | 191.9 | 67.05% | 224.9 |
| 1.30 | 69.53% | 204.3 | 67.70% | 201.8 |
| 1.40 | 69.31% | 234 | 68.69% | 184.4 |
| 1.50 | 68.72% | 251.6 | 69.23% | 198.8 |
| 1.60 | 68.12% | 256.9 | 69.26% | 222.6 |
| 1.70 | 67.63% | 254.7 | 68.95% | 241.2 |
| 1.80 | 67.33% | 247.6 | 68.56% | 247.1 |
| 1.90 | 67.34% | 230 | 68.21% | 248 |
| 2.00 | 67.61% | 211.4 | 67.90% | 247.4 |
| 2.10 | 67.83% | 214.6 | 67.68% | 243.7 |
| 2.20 | 68.09% | 210.5 | 67.54% | 239.6 |
| 2.30 | 68.38% | 204.7 | 67.56% | 227.6 |
| 2.40 | 68.74% | 198.1 | 67.73% | 214.8 |
| 2.50 | 68.97% | 208.9 | 67.90% | 215.8 |
| 2.60 | 68.99% | 222.8 | 68.09% | 213.3 |
| 2.70 | 68.97% | 225.6 | 68.32% | 206.9 |
| 2.80 | 68.91% | 230.3 | 68.51% | 208.7 |
| 2.90 | 68.79% | 236.5 | 68.65% | 213.6 |
| 3.00 | 68.53% | 250.9 | 68.70% | 221.9 |
| 3.10 | 68.37% | 242 | 68.76% | 220.4 |
| 3.20 | 68.20% | 244.3 | 68.70% | 230.5 |
| 3.30 | – | – | 68.69% | 226.1 |
| 3.40 | – | – | 68.65% | 230.8 |
| 3.50 | – | – | 68.65% | 226.2 |
| 3.60 | – | – | 68.48% | 245.8 |
First 20 steps of 10,000 inserts each, then steps of 100,000. Split log records per 10,000 = split log records of the step, converted to 10,000 new rows. Plateau window: the last million rows of each start (11 measurement points).
| Start | Density at start | Rows at end | Density at end | Density over the last million |
|---|---|---|---|---|
| FF100 | 98.8% | 3.2 million | 68.20% | 68.09–68.99% |
| FF81 | 81.8% | 3.6 million | 68.48% | 68.09–68.76% |
In the first step, split activity on the densely built index is especially high; density drops from 98.8% to 54.0% within the first 10,000 inserts. After that it settles. From about 1.4 million rows on, the two curves are at most a good percentage point apart; at 3.2 and 3.6 million rows, our plateau criterion over the respective last million is met. The starting state fades away.
The empty-started runs from part 2 end at 68.24% on SQL Server 2022 and 2025. That is a rounded agreement between two different experiments, not proof of precision. The 68% are an observation in this test: neither a universal limit nor a maintenance target.
The splits do not stop
A stable average sounds like rest. It is not. On the plateau, 226.4 split log records per 10,000 inserts occur after the FF100 start and 223.1 after the FF81 start. The empty-started runs show the same: 4,559 split log records in the last 200,000 rows, around 228 per 10,000.
Density and split rate answer different questions. Density describes the state: how full are the pages on average? The split rate describes the change: how much restructuring is needed to keep it that way? A stable average also does not mean that all pages are similarly full. Freshly split, half-full pages and nearly full pages balance out on average. The average stands still because the restructuring continues at an even pace.
Waiting versus intervening
Without maintenance, there is no maintenance cost. Instead, the index pays in two other places: with recurring splits for some of the inserts and with more leaf pages.
After 60 days without maintenance: more leaf pages and more split log records
600,000 rows, 10,000 inserts per day, SQL 2022 and 2025 identical.
Data for the chart
| Strategy | Leaf pages 2022 | Leaf pages 2025 | Density at end | Split log records 2022 | Split log records 2025 | REBUILD / REORGANIZE |
|---|---|---|---|---|---|---|
| No maintenance | 13,574 | 13,574 | 68.2% | 13,631 | 13,631 | 0 / 0 |
| Early REBUILD FF80 | 10,962 | 10,962 | 84.5% | 4,199 | 4,199 | 20 / 0 |
Day 60 after 600,000 single-row inserts, empty start, GUID plus 100 bytes. The last REBUILD ran on day 57; the density of the REBUILD index is the state 3 days later.
| Strategy (60 days) | Leaf pages at end | Density at end | Split log records | Maintenance effort (SQL 2022) |
|---|---|---|---|---|
| No maintenance | 13,574 | 68.2% | 13,631 | – |
| Early REBUILD FF80 | 10,962 | 84.5% | 4,199 | 20 REBUILDs, 4.4 s, 4.3 MiB log |
At the end of the test, the unmaintained index occupies 24% more leaf pages and has produced a good three times as many split log records. A scan over the same data has to touch more leaf pages at lower density; pages already in the buffer pool do not cause another read from disk. For point lookups, the page count hardly matters. So 24% more pages do not translate into 24% slower queries; we did not measure read times here.
The cross-check is part of the picture: the REBUILD strategy needed 20 interventions. Our maintenance figures apply to this synthetic single index, offline under SIMPLE, measured as generated log record bytes. An online REBUILD writes considerably more log (part 2: 34 instead of 1.6 MiB in the frozen comparison), and an offline REBUILD can block access to the table for its duration. For a large production index, these costs shift considerably. No routine maintenance can therefore be the better overall decision.
When intervening can pay off
For an index with randomly distributed inserts, the question “wait or intervene?” comes down to four points:
- How write-heavy is it? The more inserts into existing ranges, the more the recurring splits count.
- How is it read? Many scans over large ranges benefit from higher density; point lookups hardly.
- What does the intervention cost? Index size, recovery model, online or offline operation, maintenance window.
- What does the measurement show? The same workload before and after a trial intervention, including the time until the next one.
Deep dive: A year with reserve
How often does an early REBUILD have to run? We extended the reserve strategy over 365 simulated days, 10,000 inserts every day, REBUILD as soon as fragmentation exceeds 1%. A simulated day is a fixed number of inserts, not a calendar day.
The gaps between two REBUILDs get longer as the index grows
Early REBUILD with fill factor 70 and 80, 365 simulated days, SQL 2022. SQL 2025 identical.
Data for the chart
| REBUILD no. | FF70 day | FF70 gap | FF80 day | FF80 gap |
|---|---|---|---|---|
| 1 | 5 | first | 5 | first |
| 2 | 6 | 1 | 6 | 1 |
| 3 | 8 | 2 | 7 | 1 |
| 4 | 10 | 2 | 8 | 1 |
| 5 | 12 | 2 | 9 | 1 |
| 6 | 15 | 3 | 10 | 1 |
| 7 | 18 | 3 | 12 | 2 |
| 8 | 22 | 4 | 14 | 2 |
| 9 | 27 | 5 | 16 | 2 |
| 10 | 33 | 6 | 18 | 2 |
| 11 | 40 | 7 | 20 | 2 |
| 12 | 48 | 8 | 23 | 3 |
| 13 | 58 | 10 | 26 | 3 |
| 14 | 70 | 12 | 29 | 3 |
| 15 | 84 | 14 | 33 | 4 |
| 16 | 101 | 17 | 37 | 4 |
| 17 | 121 | 20 | 41 | 4 |
| 18 | 146 | 25 | 46 | 5 |
| 19 | 174 | 28 | 51 | 5 |
| 20 | 208 | 34 | 57 | 6 |
| 21 | 249 | 41 | 63 | 6 |
| 22 | 298 | 49 | 70 | 7 |
| 23 | 356 | 58 | 77 | 7 |
| 24 | – | – | 85 | 8 |
| 25 | – | – | 94 | 9 |
| 26 | – | – | 104 | 10 |
| 27 | – | – | 115 | 11 |
| 28 | – | – | 127 | 12 |
| 29 | – | – | 140 | 13 |
| 30 | – | – | 155 | 15 |
| 31 | – | – | 171 | 16 |
| 32 | – | – | 189 | 18 |
| 33 | – | – | 208 | 19 |
| 34 | – | – | 230 | 22 |
| 35 | – | – | 254 | 24 |
| 36 | – | – | 280 | 26 |
| 37 | – | – | 309 | 29 |
| 38 | – | – | 341 | 32 |
| Year end | 365 | open: 9 | 365 | open: 24 |
The first REBUILD ran on day 5 in both runs, as soon as the index exceeded 1,000 leaf pages and fragmentation was above 1%. Gap = days since the previous REBUILD. SQL Server 2025: the same REBUILD days.
| Fill factor | REBUILDs per year | Longest completed interval | Split log records | Maintenance total (SQL 2022) | Leaf pages at end |
|---|---|---|---|---|---|
| 70 | 23 | 58 days | 5,327 | 19.5 s · 19.8 MiB | 77,392 |
| 80 | 38 | 32 days | 8,805 | 29.8 s · 29.4 MiB | 65,592 |
The intervals get longer over time. The same number of rows arrives every day, but the index grows. The same 10,000 rows spread across more and more pages, and the reserve on each page lasts longer. An interval from the end of the year therefore says nothing about a young or small index, and it is not a calendar plan.
Split log records also occur between two REBUILDs, just few of them: in the longest FF70 interval, from day 298 to day 356, 539 accumulated over 580,000 new rows. Extrapolated with the plateau rate, it would have been around 13,000 without maintenance; that is an extrapolation, not a separate control run for this interval.
FF70 needed fewer interventions than FF80 and produced fewer split log records, but occupied 18% more pages at the end of the year. This end state also depends on how long ago the last REBUILD ran in each case. Which value is better depends on what is more expensive: writing or reading.
Growing rows with ascending keys
With IDENTITY or NEWSEQUENTIALID, new rows land at the end of the index in the simple case. New pages are appended there and filled completely. The fill factor of an earlier REBUILD has no effect on these new pages: it only applied to the pages that existed at the time of the REBUILD.
This becomes visible when rows grow after being inserted. We built 90,000 rows with FF70 or FF100, appended 10,000 and then enlarged 333 of the newly appended rows by 100 bytes each.
Growing updates on new pages: the same split log records at FF70 and FF100
Per key type and fill factor, median of three runs, SQL 2022 and 2025 identical.
LOP_DELETE_SPLIT); the values 0 and 1 are labeled as numbers. This test says nothing about updates on older pages that existed at the time of the REBUILD. Source: Our own measurement, LAB-12, SQL Server 2022/2025, 5 October 2026Data for the chart
| Key | Fill factor | Pages before | after appending | after updates | Density after appending | Split log records appending | Split log records updates |
|---|---|---|---|---|---|---|---|
| IDENTITY | 70 | 1,837 | 1,982 | 2,127 | 72.9% | 1 | 145 |
| IDENTITY | 100 | 1,305 | 1,450 | 1,595 | 99.7% | 0 | 145 |
| NEWSEQUENTIALID | 70 | 2,046 | 2,207 | 2,368 | 72.2% | 0 | 162 |
| NEWSEQUENTIALID | 100 | 1,452 | 1,613 | 1,773 | 98.8% | 0 | 162 |
Median of three runs; all three runs and both versions deliver the same numbers. The new pages at the end of the index are filled completely, regardless of the fill factor of the existing pages.
| Key | Fill factor | Split log records when appending | Split log records during 333 updates |
|---|---|---|---|
| IDENTITY | 70 | 1 | 145 |
| IDENTITY | 100 | 0 | 145 |
| NEWSEQUENTIALID | 70 | 0 | 162 |
| NEWSEQUENTIALID | 100 | 0 | 162 |
In this test, only freshly appended rows grow. The reserve created by the REBUILD in the older range does not help them: FF70 and FF100 produce the same numbers. If, on the other hand, rows on the freshly rebuilt pages grow, the free space there can matter; we did not measure that here. For the decision, you therefore have to check where and by how much rows grow. Hotspots at the end of the index under many concurrent writers and the behavior of NEWSEQUENTIALID after restarts are outside the scope of this test.
Deep dive: Why about two thirds?
A split leaves behind two roughly half-full pages. They then fill up until they are full again and split again. With randomly distributed inserts, the pages are therefore spread across many fill levels, and the average is well below “full”. Andrew Yao's analysis of randomly growing B-trees of high order offers a theoretical orientation for this with ln 2 ≈ 69% (Acta Informatica 9, 1978). SQL Server pages with whole rows, overhead and concrete split behavior are not equated with that. That our observation lies in the same region is an orientation, not a derivation.
What this means for maintenance
The state without maintenance is neither a target nor a threshold. It shows where an index with randomly distributed inserts goes without intervention: about two thirds full on average, with splits continuing at an even pace. Whether that is a problem is decided by the workload.
The guiding principle of this series:
Index maintenance needs a measurable goal. What matters is whether its effect on the workload justifies the cost and the disruption to operations.
For the patterns from this series, as a starting point for your own check:
| Pattern | What happens without maintenance | Candidate to test |
|---|---|---|
| Randomly distributed inserts, write-heavy | Plateau around two thirds density, splits continue | REBUILD with reserve; measure interval and fill factor |
| Randomly distributed inserts, read-heavy with many scans | More leaf pages for every scan | REBUILD with a higher fill factor, less often |
| Ascending keys, rows stay unchanged | Pages are appended full | hardly any routine physical maintenance |
| Ascending keys, freshly inserted rows grow | Splits on the new pages at the end | check the data model, row growth and its timing |
| Growing updates on older rows | Splits in the existing range | additionally check local reserve and fill factor |
| Hardly any changes | nothing | no routine physical maintenance; update statistics only when there is a proven need |
No row of this table is a rule. Each is a hypothesis to be measured against your own workload, before and after.
Origins and further work
The experiment ideas and cross-checks in this series build in particular on Jeff Moden's work on index maintenance. In his talk “Black Arts” Index Maintenance #1, he places a year-long simulation without maintenance next to several maintenance strategies and gives an average page density of 68.24% for the unmaintained curve. He calls this comparison curve without maintenance the “Brent Line”, after Brent Ozar; it is a reference curve, not a SQL Server feature and not a maintenance threshold. He has used the name since at least 2020 on SQLServerCentral. He suggested the test with a densely built start to us in our exchange on LinkedIn. He makes slides and scripts freely available for learning purposes. We thank him for the suggestions. Our measurements come from the PSG lab; their conditions and limits are stated in each case.
Limits of the measurement
- Synthetic data, one clustered index without nonclustered indexes, fixed row width, no deletes, no compression, no partitions.
- One deterministic trajectory per starting state; no measure of spread across different random sequences. The plateau is a limited observation according to our criterion, not proof of a permanent limit.
- The dense starts ran on SQL Server 2022 (16.0.4295.3) only; the empty-started controls and the year-long runs on 2022 and 2025 (17.0.5005.3). Maintenance times and log volumes in the tables are SQL 2022 values; on 2025 they differ slightly, while pages, split log records and intervals are identical.
- Serial load, MAXDOP 1, no concurrent writers. Maintenance offline under SIMPLE; locking impact and online costs in the year-long run not measured.
- Read costs derived from the page count only; we did not measure scans or read times.
- A simulated day is a quantity of inserts, not a calendar forecast.
- Checked on 5 and 6 October 2026. The test conditions are documented; we will provide separate information about a public reproduction package.
What does your index do when nobody maintains it?
Every answer is explained right away, with a link to the relevant part of the article. At the end, you get an assessment and a link you can share. No cookies.
Further reading
- PSG: Part 1 · Room for the next row
- PSG: Part 2 · Tidy, but no room
- Jeff Moden: “Black Arts” Index Maintenance #1
- Brent Ozar: Stop Using Page Splits to Justify Lowering Fill Factor
- Andrew C. Yao: On random 2–3 trees, Acta Informatica, 1978
- Microsoft: Specify fill factor for an index