The first thing I did at one job was delete a maintenance job. Every night at 1 a.m. it rebuilt every index over 1,000 pages in every database, and every night it generated around 90 GB of transaction log, blew out the log shipping queue, delayed the ETL window by forty minutes, and produced exactly zero measurable query improvement. It had run for three years because nobody could prove it was useless and everyone was afraid to find out. The proof, when I finally gathered it, was a week of before-and-after latency percentiles that did not move.
Index maintenance is not useless. But the version of it most shops run, nightly rebuilds driven by the 5 percent and 30 percent fragmentation thresholds, is cargo cult carried over from an era of spinning disks and 8 MB of read-ahead cache. Here is the reality check, piece by piece.
Does fragmentation still matter on flash storage?
Logical-order fragmentation, the number sys.dm_db_index_physical_stats reports as avg_fragmentation_in_percent, barely matters on SSDs for the random-read workloads that dominate OLTP. That number describes how out of order the pages are on disk, and the historical fear was that out-of-order pages kill sequential read-ahead, which was true when a spinning disk's whole performance model depended on reading in order. On flash, a random 8 KB read and a sequential one cost nearly the same. The thresholds everyone quotes trace back to old guidance written when that was not true.
What still matters is page density, the sibling column avg_page_space_used_in_percent. An index whose pages average 60 percent full is wasting 40 percent of every read and 40 percent of the buffer pool it occupies, and that cost is real on any storage. Density craters under random inserts and heavy delete churn. So when I look at physical stats at all, I sort by low page density on big indexes, not by high fragmentation. A 95 percent fragmented index with 98 percent full pages on an NVMe volume is, in my experience, a non-event.
What does REBUILD buy that REORGANIZE never will?
A rebuild constructs a brand-new b-tree from scratch: pages rewritten compactly to the fill factor, every level of the tree fresh, and, as a side effect, the statistics on that index updated with a full scan, unless the index is partitioned or the rebuild is resumable, in which case you get a sampled update instead. REORGANIZE shuffles leaf-level pages in place, never touches the non-leaf levels, and does not update statistics at all. That stats side effect is the sleeper fact of this whole topic, and I will come back to it, because it is frequently the only thing a rebuild did that mattered.
The costs are wildly asymmetric too. REBUILD is fully logged under the FULL recovery model, so every rebuilt gigabyte is a gigabyte of log that then has to ship to your availability group replicas, your log shipping secondaries, and your backups. That is where my 90 GB a night was going. (Under SIMPLE or BULK_LOGGED an offline rebuild can be minimally logged, which matters for staging databases, but most production OLTP lives in FULL.) ONLINE = ON keeps the index queryable during the build but is an Enterprise edition and Azure SQL feature, still takes brief locks at the start and end, and on 2017 and later you can make it resumable so a failure does not restart from zero. REORGANIZE is always online, always interruptible, and generates less log, but it also does less. If you rebuild, you should be able to say which of its benefits you are buying, because you are definitely paying for all of them.
How expensive is it to even measure fragmentation?
Expensive enough that measuring can be its own maintenance problem. sys.dm_db_index_physical_stats in DETAILED mode reads every page of every index you point it at; SAMPLED mode reads about one percent, which on a two-terabyte table is still a lot of I/O for a number you will mostly ignore. Pointing it at an entire database nightly is how a monitoring habit becomes the heaviest query on the box. Scope it hard, and be honest about what the filter does: the page-count predicate below keeps small indexes out of your report, not out of the scan, because the function inspects every index you point it at before the WHERE clause runs, and SAMPLED silently upgrades to DETAILED for anything under 10,000 pages. Filter anyway, because an index of a few hundred pages cannot fragment in any way that matters, and keep the whole thing for a quiet window:
SELECT OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.index_type_desc,
ips.page_count,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
AND ips.avg_page_space_used_in_percent < 70
ORDER BY ips.page_count DESC;Note what that query optimizes for: big indexes with low page density, run weekly in a quiet window, SAMPLED mode. It is a hunting query, not a nightly ritual. On very large databases I go further and pass explicit object IDs for the ten biggest tables rather than letting it walk the catalog.
Why is updating statistics usually the real win?
Because the optimizer chooses plans from estimates, estimates come from statistics, and stale statistics are behind far more "the index was fragmented" stories than fragmentation ever was. The classic case: a table grows past the point where the histogram's last step reflects reality, a query that should estimate two million rows estimates one, the optimizer picks nested loops and a seek, and the query runs for four minutes. Rebuilding the index "fixes" it because the rebuild refreshed the stats with FULLSCAN as a side effect. The b-tree was never the problem.
Auto-update statistics fires when roughly 20 percent plus 500 rows have changed, with a dynamic lower threshold on big tables becoming the default behavior in SQL Server 2016, but on a half-billion-row table even the dynamic threshold lets a lot of staleness accumulate. Updating statistics directly is cheap compared to any index operation because it moves no data:
-- the surgical option for a known hot column:
UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate WITH FULLSCAN;
-- the nightly broad pass, sampled to stay cheap:
EXEC sp_updatestats;A nightly sampled stats update plus targeted FULLSCAN updates on the handful of columns that drive your critical plans is the highest-value line item in this entire article. It costs a fraction of a rebuild cycle, generates minimal log, and attacks the failure mode that actually produces bad plans.
When do page splits and fill factor justify touching indexes?
When you have proven a density problem caused by insert pattern, which is narrower than folklore says but real. An index on a random GUID key inserts into the middle of the tree constantly, splits pages constantly, and drifts toward that 60-percent-full density. A fill factor of 90 or 95 on exactly that index can genuinely reduce the split storm and the log volume splits generate. That is a surgical prescription for a diagnosed condition, not a reason to set fill factor 80 server-wide, which just makes everything 20 percent bigger forever.
The opposite insert pattern has the opposite problem. A hot identity or datetime key funnels every insert into the last page of the index, and under high concurrency you get PAGELATCH_EX waits on that final page, the last-page insert contention that looks like tempdb contention but lives in your user database. Rebuilds do not fix this at all; the next insert storm re-queues on the same hot page. The fixes are schema-level: a non-sequential key, hash partitioning, or SQL Server 2019's OPTIMIZE_FOR_SEQUENTIAL_KEY index option. The lesson under both patterns is the same: page splits and latches are insert-pattern diseases, and maintenance jobs are not insert-pattern medicine.
What maintenance policy fits inside a real maintenance window?
One that spends most of its budget on statistics and treats index work as exception handling. Mine looks like this: nightly, a sampled stats update plus FULLSCAN on the known hot columns, typically done in minutes. Weekly, the scoped physical-stats query above, and only indexes that show up big and under-dense get considered for a rebuild, with ONLINE and RESUMABLE where edition allows, tempdb sort spill watched, and log space headroom confirmed first because FULL recovery means every rebuilt byte replicates. Everything else is left alone on purpose.
The window this buys back is not trivial. On the system with the 90 GB rebuild job, the replacement policy ran in under fifteen minutes a night and the log shipping queue stopped being a daily negotiation. Nothing got slower. A few things got faster, because the hot columns now had fresh full-scan statistics instead of whatever the last rebuild happened to leave behind.
Where MonPG's SQL Server support stands
MonPG monitors PostgreSQL today, and I should be plain that SQL Server support is on the roadmap and in active development, not shipped. When it lands, the maintenance story above is the shape of what should be graphed: page density trends on your largest indexes, statistics age on hot columns, log generation rate during the maintenance window, and last-page latch waits, so the "is the nightly job earning its window" question gets answered with evidence instead of fear. Progress is tracked on the SQL Server monitoring (coming soon) page. Until it ships, the two queries in this article and a calendar reminder to question every rebuild job you inherit will do most of the work.