Best way to rebuild all indexes on all tables in SQL Server 2005

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.