Index maintenance without the nightly rebuild ritual

Many SQL Servers inherit a maintenance plan that rebuilds all indexes nightly. It feels responsible. On large or busy systems it is often wasteful — and occasionally harmful.

What nightly rebuilds cost

Heavy I/O and transaction log generation
Longer backup chains / log backup volume on Full recovery
Plan cache churn and unexpected plan changes
Blocking if maintenance overlaps with early business hours
SSDs reduced the “fragmentation = always slow” story for many workloads

Fragmentation still matters in some cases (large range scans, certain storage patterns), but page count, usage, and workload shape matter more than a single percentage.

A better policy

1. Separate statistics from rebuilds

Out-of-date statistics cause bad plans more often than 12% fragmentation does. Make sure statistics strategy is intentional:

Auto-update behaviour and sample rates for large tables
Scheduled updates for critical tables after large loads
Avoid assuming “rebuild fixes stats so we are fine” as your only plan

2. Use thresholds that include size

Classic starting point (tune to your estate):

Ignore tiny indexes (low page counts)
Reorganise moderate fragmentation
Rebuild higher fragmentation on indexes that are actually used

3. Prefer targeted maintenance

Prioritise:

Large, frequently read indexes with meaningful fragmentation
Partition-level work where available
Tables with known ETL windows

4. Watch for over-indexing

Every extra index is a tax on inserts, updates, and deletes. Performance tuning often removes indexes as much as it adds them.

Sample decision flow

Is the table in a known pain query?
Does the plan show scans/lookups that an index change would help?
Are stats recent relative to data change?
Only then: is physical fragmentation contributing?

Maintenance scripts are tools — not a substitute for that reasoning.

Operational tips

Align maintenance with backup and CHECKDB windows so they do not stack
Log what was rebuilt and why (for change history)
Alert when maintenance overruns into business hours
Re-evaluate after major version upgrades or storage changes

Need a plan for your estate?

We design index and statistics strategies that match your SLAs and hardware — especially for systems that have “always been rebuilt at midnight” without anyone knowing why.

Contact Allay Data Solutions for a maintenance review.