Context
Index maintenance changes physical structures, while statistics maintenance changes the estimates used to choose plans. They can interact, but they are not the same task. A blanket rebuild schedule can spend substantial resources without addressing the query problem that prompted it. Choose work from measured need and verify the result against the workload.
Prerequisites
Collect SQL Server version, index type, table size, maintenance history, representative plans, Query Store evidence, storage characteristics, and available maintenance capacity. Obtain authorized diagnostic access. Distinguish rowstore, columnstore, and other index types because their maintenance rules differ. Use a representative test copy for expensive operations, and include transaction-log, tempdb, and availability-replica effects in the change plan.
Separate the questions
- Identify the workload requiring improvement. Does the query perform large scans, use unexpected estimates, wait on I/O, or return far more data than before? Preserve a representative plan and resource baseline. A high fragmentation percentage on a small or rarely used index is weak evidence for a disruptive maintenance operation by itself.
- Inspect physical characteristics using an appropriately scoped diagnostic method. Review page density as well as fragmentation for rowstore indexes. Limit scan depth and scope according to production overhead. Storage behavior and query access patterns determine whether these characteristics materially affect the workload. Avoid a universal percentage threshold detached from index size and observed demand.
- Inspect relevant statistics age, modification evidence, and estimation quality. A targeted statistics update may address a demonstrated estimate problem without rebuilding the entire index. Conversely, refreshed estimates do not physically reorganize pages. Reorganization and rebuild have different statistics effects and version-specific details, so document the exact expected change for the selected operation.
- Rehearse the chosen maintenance on representative data. Measure operation duration, logging, blocking, temporary space, and downstream replica effects. Compare a targeted statistics action with physical maintenance when both appear plausible. Prefer the least costly operation that resolves the demonstrated problem, and retain a rollback or mitigation plan for changed query plans.
Diagnostic table
| Evidence | Likely focus | Next check |
|---|---|---|
| Estimates diverge after data change | Statistics | Relevant distribution and modification history |
| Large scans read many sparse pages | Physical layout or design | Density and access pattern |
| Rebuild improves one plan but harms another | Plan tradeoff | Representative workload set |
| Maintenance causes replica backlog | Operational cost | Logging and apply capacity |
Illustrative scenario
An overnight import changes the distribution used by a reporting query. The scheduled maintenance job rebuilds many unrelated indexes, consuming the entire window. The team isolates the affected estimate, tests a targeted statistics update, and compares the report with other important queries. It redesigns the schedule around demonstrated benefit and resource cost instead of using the number of rebuilt indexes as a success measure.
Correct and verify
Run acceptance checks under representative concurrency and parameter values. Compare query results, resource use, and plan behavior with the baseline, then verify that log and replica pressure return to expected levels. If the main benefit came from a statistics change, record that finding explicitly so the next cycle does not automatically repeat an expensive structural operation.
Operating procedure
Maintain a record of why each maintenance class runs, its measured cost, and the workload it benefits. Monitor maintenance failures and overruns alongside application regressions. Review the policy after storage, version, or workload changes. Keep automatic statistics behavior and targeted manual work coordinated so maintenance remains a purposeful part of operations rather than an unexamined nightly ritual.
Explore Clouvenix server performance for implementation and operational handover.