How to reorganize and rebuild all indexes

  • what is the best way to reorganize and rebuild all indexes of database?

  • Should be done during downtime - rebuilding indexes places table locks and prevents access during the rebuild process - unless you use REBUILD WITH ONLINE = ON.

    USE my_db

    GO

    EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?...' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)"

    print 'Done.'

    GO

    EXEC sp_updatestats

    http://msdn.microsoft.com/en-us/library/ms188388.aspx

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Seth provided exactly what you asked for, but I must ask, why do you need to " reorganize and rebuild all indexes of database" at one time?

    Typically you only rebuild or reorganize an index if it were fragmented more than a certain threshold, otherwise you would be wasting your systems resources.

    The only time I can think of when rebuilding all indexes might be a good idea is after upgrading a database to a new major version, e.g. from 2005 to 2008.

    Have a look at this index and stats maintenance procedure. It is coded to only alter the indexes that are fragmented above a certain threshold, and based on that it will either rebuild, reorg or skip it, which is a good way to manage your indexes: SQL Server Index and Statistics Maintenance by Ola Hallengren

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply