May 3, 2012 at 9:28 am
what is the best way to reorganize and rebuild all indexes of database?
May 3, 2012 at 9:42 am
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
May 3, 2012 at 1:53 pm
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