Rebuilding SQL Indexes - When?

When should one rebuild indexes?开发者_如何学C Nightly? Weekly? Hourly?

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

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.

It depends on the degree of fragmentation of Indexes.

If avg_fragmentation_in_percent value is > 5% and < = 30% then you should Reorganize Index. If avg_fragmentation_in_percent value is > 30% then you should Rebuild Index.

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)