r/SQLServer • u/markinatlanta • 8h ago
Discussion Anyone stopped nightly index rebuilds and seen no downside?
For example an index hits 30% fragmentation, the job rebuilds it. Does anyone check whether performance improves enough to justify the work?
I posted about this on LinkedIn a while back and got two useful examples.
One DBA stopped nightly rebuilds spilling into business hours and addressed the query problems with targeted statistics updates. Another described a page-split storm after rebuilding nonclustered indexes at fill factor 100.
We’ve been working on page-density checks ourselves. Checking every index was expensive before we’d even rebuilt anything, so we narrowed the checks to heavily used indexes.
For rowstore indexes, low page density deserves attention. Half-empty pages still occupy buffer-pool space when cached. But packing everything tightly won’t necessarily help if the write workload quickly splits those pages again.
And if a query improves after a rebuild, was the benefit from updating the index statistics? Would a stats update alone have been enough?
My view is to start with the indexes the workload depends on and establish what needs fixing. Crossing a fragmentation threshold isn’t enough reason on its own.
Anyone here reduced scheduled rebuilds? What improved, and what eventually needed attention?