Rebuild indexes in a SQL Server database: when and how

Inherited maintenance plan rebuilds every index in the database every night, which takes hours and bloats the log. Surely this is not how it should be done.

What are the actual thresholds and the smarter procedure?

Your instinct is right, rebuild everything nightly is the maintenance equivalent of repaving every road daily. The measured approach:

1

Measure fragmentation before touching anything: sys.dm_db_index_physical_stats in LIMITED mode per database reports avg_fragmentation_in_percent per index, joined to sys.indexes for names. Add page_count to the output and ignore small indexes below roughly 1000 pages entirely, fragmentation on tiny indexes costs nothing worth maintenance.

2

Apply the standard thresholds to what remains: between about 5 and 30 percent fragmentation, ALTER INDEX yours ON table REORGANIZE, the light online operation that compacts in place. Above 30 percent, ALTER INDEX yours REBUILD, the full reconstruction. These are starting points rather than physics, tune by observing whether your workload feels index fragmentation at all.

3

Know the operational differences: REBUILD refreshes statistics as a side effect while REORGANIZE does not, so follow reorganizes with UPDATE STATISTICS on those tables. REBUILD offline locks the table for the duration, the ONLINE = ON option avoiding that on Enterprise edition. REORGANIZE is always online, always interruptible without rollback pain and, connecting to the page locking thread linked alongside, requires page locks to run.

4

Automate with logic rather than a blanket: the free Ola Hallengren IndexOptimize procedure implements exactly this measured approach, thresholds, edition awareness, statistics handling and logging included and replacing the inherited rebuild everything plan with it is a one evening change most instances deserve. Schedule it after the nightly backup rather than before, so the log it generates lands in the next chain rather than swelling tonight's.

Expect the win to be dramatic: measured maintenance on a typical database touches a fraction of the indexes nightly, turning the hours into minutes and the log bloat into a footnote, with the rebuilds that do run concentrated where queries actually benefit.

IndexOptimize deployed in place of the blanket plan: first night touched 31 indexes instead of 400 and finished in 12 minutes. Log growth normal for the first time in memory. The previous plan's author has been forgiven but not forgotten.