Menu
When should one rebuild indexes? Nightly? Weekly? Hourly?
728
If an index contains less than 100 pages, I will perform no maintenance. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. If an index is over 30% fragmented, I will REBUILD the index - with no UPDATE STATISTICS , as this is taken care of by the REBUILD .
The Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data.
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.
Performing an index rebuild eliminates fragmentation, compacts the pages based on the existing fill factor setting to reclaim storage space, and also reorders the index rows into contiguous pages.
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
106
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.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
