CODEWITHSUNDEEP
t-sqlindexing
0xB33F
0xB33F

Reputation: 61

Rebuilding SQL Indexes - When?

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

Upvotes: 6

Views: 3980

Answers (4)

Chirag
Chirag

Reputation: 4186

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.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37364

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)

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 6

gbn
gbn

Reputation: 432261

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.

Upvotes: 3

Related Questions