Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

A google search turns up some scripts that run a query against system tables and iterate over the the results rebuilding (or alternatively, reorganizing) each of your database table indexes in turn. This seems reasonable but the he reason I ask the question here is that taking SQL scripts from an unknown source that query system tables makes me nervous, e.g. maybe it will miss some kinds of index or try and rebuild special system indexes that should be left alone. As it happens the first couple I tried didn't work because they didn't handle tables in different schemas.

So, what is the consensus on the best and simplest way to rebuild all indexes across all tables for an SQL Server 2005 database?

Thankyou.

share|improve this question
add comment

5 Answers

If you don't want to risk a third party script, the native maintenance plans include the option of rebuilding all of the indexes for each database.

share|improve this answer
add comment

Ola Hallegren has a mature & well written index optimize script for indexes which i can highly recommend. It uses the SQL Server 2005 ALTER INDEX REBUILD/REORGANIZE syntax which allows for online rebuilds in Enterprise.

share|improve this answer
add comment

As is often the case, BOL has a good starting point, with explanation of how it does what it does and what you can change to make it have different effects.http://msdn.microsoft.com/en-us/library/ms177571.aspx. I started with this script and added to it so that all databases are handled (on some servers). I also set a different value for @maxfrag depending on when the scrpt is running - weekends get a low value and weekdays get a higher one and whether there is a rebuild or a reorganize. This ensures the job runs within the time limits we have and that the database doesnt get a big lock

share|improve this answer
add comment

There's a really good "index defrag" script at http://sqlfool.com/2009/06/index-defrag-script-v30/ which selectively rebuilds certain indices, reorganizes others, and leaves some alone.

This is all based on index fragmentation thresholds which you can specify in the script.

Very useful and extremely helpful indeed! You can just simply configure this script and then run it on all your databases.

Marc

share|improve this answer
1  
Sqlfool's script runs against all databases by default, no need for sp_MSforeachDB. –  SuperCoolMoss Nov 1 '09 at 18:02
    
Oh and I use this script on a number of Production instances - so can recommend it. –  SuperCoolMoss Nov 1 '09 at 18:07
add comment

Not as complete/complex as sqlfool's script mentioned in other replies, but I use:

DECLARE @sSQL NVARCHAR(1024), @sName NVARCHAR(1024)
DECLARE curTables CURSOR FOR SELECT name FROM sysobjects WHERE xtype='U'
OPEN curTables
FETCH NEXT FROM curTables INTO @sName
WHILE @@fetch_status = 0 BEGIN
    SET @sSQL = 'DBCC DBREINDEX ('''+@sName+''', '''', 0)'
    EXEC (@sSQL)
    FETCH NEXT FROM curTables INTO @sName
END
CLOSE curTables
DEALLOCATE curTables

to rebuild all indexes in the database currently logged into.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.