Optimization: SQL Server index and statistics maintenance - stop rebuilding everything every night

#Why "rebuild everything" is wasteful

• On SSD/NVMe and cloud storage, logical fragmentation matters much less for random reads.
• Rebuilds generate heavy log, which slows log backups, log shipping and AG synchronisation.
• Many slowdowns blamed on fragmentation are actually stale statistics, which a rebuild fixes as a side effect.

#Statistics first

-- Statistics with many modifications since last update
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS sch, OBJECT_NAME(s.object_id) AS tbl,
       s.name AS stat, sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND sp.modification_counter > 0.1 * sp.rows
ORDER BY sp.modification_counter DESC;

• Update statistics on volatile tables daily (UPDATE STATISTICS ... WITH SAMPLE or FULLSCAN for skewed columns).
• Keep AUTO_UPDATE_STATISTICS on; consider AUTO_UPDATE_STATISTICS_ASYNC for OLTP to avoid query waits.

#Index maintenance by evidence

SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name AS idx,
       ips.avg_fragmentation_in_percent, ips.page_count, ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.page_count DESC;

#Use the right options

ALTER INDEX IX_Orders_Date ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES, MAXDOP = 4);

• Online and resumable rebuilds need Enterprise edition (or Azure SQL); otherwise schedule offline rebuilds carefully.
• Spread large rebuilds across nights instead of one big window.
• Watch log backups and AG send queue during maintenance.

#Tooling

Ola Hallengren's IndexOptimize with sensible thresholds, or your own job, but driven by page count, density and usage rather than fragmentation alone. Run UpdateStatistics modes separately from index rebuilds.

#Measure

• Maintenance window duration and log generated (log backup sizes) before/after.
• Query performance on key workloads (Query Store) to confirm nothing regressed.

Tip

Try a month with statistics updates daily and index rebuilds only for low page density. Most teams see shorter windows, smaller log backups and no change in query performance.

#Checklist

• Statistics freshness checked and maintained daily on volatile tables
• Rebuild decisions based on density, size and scan usage
• Online/resumable options where edition allows
• Log and AG impact monitored during maintenance
• Before/after window and query performance compared