The nightly index rebuild job has died after a long illness. It was born in 1998 to a hard disk that could not seek. It never believed the disk had been replaced. It is survived by page density, which did most of its work all along.
It was a job of habit. Every night at two, whether or not anything had changed, it rebuilt every index in the database. Every morning the database was exactly as fast as it had been the night before.
Nobody remembers who scheduled it. Everybody remembers being told not to remove it.
Early life of the index rebuild job
It was created in an age of spinning disks. A table whose pages lay out of order cost a read head real movement. Fragmentation was a physical thing then. You could hear it.
The job earned its place in those years. Its rebuilds put pages back in order, and the disk stopped travelling. It was, for a decade, unarguably right.
The disks changed in the 2010s. The job did not notice.
The examination
Before writing this notice I gave the deceased a fair hearing on SQL Server 2025. Three tables were built to be as fragmented as a table can be.
A million rows each, clustered on a random GUID. They were inserted in a thousand batches of a thousand, so the pages split as they filled. Each came out at 99.3 percent fragmentation and 68 percent page density. That’s 38,000 pages where an orderly table would need 26,000.
One was left alone. One got REORGANIZE. One got REBUILD. Then each was read cold and read warm. The seek test is further down, because my first version of it was wrong.
clustered index scan fragmentation pages density logical reads cold physical cold warm left alone 99.26% 38,269 68% 38,445 42,833 884 ms 383 to 431 ms after REORGANIZE 0.48% 26,409 98% 26,579 26,623 696 ms 292 to 317 ms after REBUILD 0.01% 26,317 98% 26,402 26,457 610 ms 283 to 314 ms
Cold means CHECKPOINT and then DROPCLEANBUFFERS. Warm is the spread over three passes, none of which touched the disk. My first version of this table dropped clean buffers without the CHECKPOINT. Its cold column measured something I can’t name. Those numbers are withdrawn.
One more trap, and it cost me a run. My first attempt at this rerun summed a column the seek index covers. So it measured that index and never touched the clustered one. The figures above sum a column only the clustered index holds.
What it was good at
The scan got faster. The reads fell from 38,445 to 26,402 after the rebuild, and 26,579 after the reorganize. The warm time fell by about a quarter.
Look at why. The reads fell by the same fraction as the page count. The rebuild didn’t make the pages faster to read. It made fewer of them, by filling each one to 98 percent instead of 68. The seek figures below come from a separate run on tables built the same way.
Density and fragmentation moved together in this test, so it cannot separate them. What it can show is that the reads fell exactly as the page count fell. A scan reads pages, not rows.
What it was less good at
The seeks. My first run created the seek index after the maintenance. All three tables seeked through a fresh index and the numbers came out identical. That measured nothing, and a reviewer caught it.
The corrected run built the seek index first and let it fragment with the table. Then it was maintained along with everything else. It started at 99 percent fragmentation and 71 percent density on all three.
seek index (ix_n) fragmentation pages density left alone 99.36% 4,520 71% after REORGANIZE 0.52% 3,249 99% after REBUILD 0.00% 3,216 100% 10,000 seeks logical reads physical reads CPU elapsed left alone, cold 30,113 4,592 447 ms 604 ms after REORGANIZE, cold 30,089 3,288 338 ms 437 ms after REBUILD, cold 30,071 3,176 411 ms 568 ms left alone, warm 30,094 0 320 ms 320 ms after REORGANIZE, warm 30,070 0 321 ms 321 ms after REBUILD, warm 30,055 0 319 ms 318 ms
Cold means CHECKPOINT and then DROPCLEANBUFFERS before each table. My second attempt skipped the CHECKPOINT and got numbers that flattered maintenance. This is the third attempt.
Warm, the three sit within forty reads and three milliseconds of each other. Close, not identical. Cold, the maintained indexes needed about thirty percent fewer physical reads, because they had a third fewer pages to fetch. That is the only seek benefit this run found, and it lasts until the pages are cached.
Each seek averaged about three logical reads, 30,055 over 10,000. The tree is three levels deep whatever state the leaf is in.
Most transactional systems are seeks all day, on pages that stay cached. For them this run found nothing to feel.
The cost of the habit
REORGANIZE took 16 seconds on a million rows and wrote 595 MB to the transaction log. It moves pages one at a time and logs every move.
REBUILD took 0.85 seconds and wrote 1.1 MB. This database was in simple recovery, and a rebuild is minimally logged there. In full recovery it would have logged the whole index. Rebuild every index every night in full recovery and the log volume becomes its own problem. I didn’t measure how big, so I won’t put a number on it.
Both of them held locks while they worked. On a table of real size at two in the morning, that was the outage nobody attributed to the job.
Cause of death
Solid-state storage, which removed the read head the job was built to protect.
Better statistics maintenance, which turned out to be what people were mostly getting from the rebuild without knowing it. An ordinary rowstore rebuild refreshes that index’s own statistics with a full scan, as a side effect. Partitioned and resumable rebuilds have their own rules. The habit survived on that side effect for years.
And resumable, online operations. They let the rare table that needs it be rebuilt without the rest of the schema keeping it company.
Survived by
Page density and fragmentation, which this experiment could not tell apart. Check both on tables that get scanned, and rebuild when the reads say so.
Statistics, which should be updated on their own schedule, because that’s the part that was doing the good.
And one specific index in one specific database, which does need rebuilding, and whose owner knows exactly why.
In lieu of flowers
Query your fragmentation and your density, and measure maintenance against your own workload. This experiment could not separate the two, because they changed together. Ask which tables are scanned. Rebuild those when the measurement says so, and let the seek tables sleep.
If the job still runs in your shop and nobody knows what it’s for, I’ll help you write the eulogy.
Fragmentation is not the disease, it is the symptom the old disk happened to feel.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
