Rebuilding SQL Indexes - When?

Question

When should one rebuild indexes? Nightly? Weekly? Hourly?

Answer 1

It depends on the fragmentation levels not on the timeframe in general, check out the Automated Index Defrag Script here by Michelle Ufford, it will check the fragmentation levels and only rebuild/reorg when needed

Answer 2

Run an intelligent script (from SQL Fool) , nightly say, and it will decide to do nothing, defrag or rebuild.

Basically, do the minimum commensurate with your fragmentation levels.

I would run it every night, personally, as a general rule. I'd rebuild stats every night at least.

Answer 3

From SQL Server 2005 documentation :
Execute ALTER INDEX ... REORGANIZE to defragment indexes that fall under the following fragmentation thresholds: (avg_page_space_used_in_percent < 75 and >60) or (avg_fragmentation_in_percent > 10 and <15)

Execute ALTER INDEX ... REBUILD to defragment indexes that fall under the following fragmentation thresholds: (avg_page_space_used_in_percent <60) or (avg_fragmentation_in_percent > 15)