Rebuilding every index each night is a common but expensive habit. It consumes IO, expands the transaction log, increases replication traffic, and can hold disruptive locks. High fragmentation on a tiny index may have no measurable impact because the object already fits in memory. Maintenance decisions should be driven by workload evidence rather than a universal percentage.

Fragmentation is not enough

A low page-count index is cheap to scan. On large indexes, reduced page density may matter because more pages must be read. Sequential keys, random GUID inserts, and fill-factor choices create different patterns. Fresh statistics are often more important to optimizer decisions than physical fragmentation itself.

Finding maintenance candidates

This query lists indexes large enough to deserve investigation. The thresholds are not prescriptions; tune them to the maintenance window and measured query impact of the server.

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

Combine this result with Query Store and index-usage DMVs. If an index is never read but frequently updated, questioning its existence is more valuable than rebuilding it. Record maintenance duration, log growth, and query IO after each intervention.

A measured approach

1. Exclude small indexes with a minimum page-count threshold.
2. Evaluate fragmentation and page density together on large frequently used indexes.
3. Choose reorganize, online rebuild, or no action according to the maintenance window.
4. Plan statistics updates independently from index rebuild operations.
5. Track duration, log usage, and query performance as time-series measurements.

Common mistakes

• Automatically rebuilding every index above thirty percent fragmentation.
• Ignoring additional traffic imposed on Availability Groups or log shipping.
• Lowering fill factor broadly and reducing page splits at the cost of unnecessary read IO.

Conclusion

Good index maintenance is evidence-based rather than calendar-based. Identify the queries and indexes that matter, then choose the least expensive intervention. On some nights the best maintenance operation is no operation at all. Measurement is safer and cheaper than an automatic rebuild loop.