I'm attempting to reduce fragmentation in most from the indexes for any database running on SQL Server 2005. Presently I'm attempting to use ALTER INDEX along with sp_MSforeachtable, to use it to all the indexes its the tables:
sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;"
But for whatever reason this doesn’t always appear to operate? Basically check it out for any single index, or all the indexes for any single table then your fragmentation is cleared up, it simply appears to become after i put it on towards the whole database which i get problems.
Formerly I would used DBCC DBREINDEX but BOL states it will likely be removed within the next version of SQL Server, and so i shouldn't utilize it.
Can anybody produce any suggestions about the easiest method to tackle cleaning all the indexes inside a database?
If you wish to fully automate your SQL Server Index maintenance i quickly seriously reccomend that you simply take a look at Michelle Ufford's saved technique of this.
It's things i say is the very best index maintenance script I've ever read.
Among the best features relating to this script are that you could personalize the threshold values that you employ to be able to determine if you should REBUILD or REORGANIZE confirmed index strucutre.
Additionally, it offers the choice to limit the amount of CPU cores which are employed by the process. A great option if you plan to operate the script on the busy live production database.
Warning: As with every internet available code, make sure to test drive it completely before using inside a production atmosphere. Additionally, you will probably wish to add your own customisation featuring too.
Browse the article and associated sample script additional task at SQL Fool (Michelle Ufford's website):
This is a reasonably nice solution additional for good!
The very best practice recommendation would be to reorganize your index for those who have 5-30% of fragmentation, and just rebuild it whether it has a lot more than 30% fragmentation. It is simple to begin using these thresholds or specify your personal by using this script.
The most secure and many portable strategy is to decrease the indices and also to re-add them.