Smart Indexing Part II - Conditional Rebuilding
The Maintenance plan Wizard in SQL Server 2005 allows us to create an SSIS package that ReIndexes all Indexes. This is great for smaller systems, but on larger systems (over 10GB or so), the re-indexing can take a long time reducing the maintenance window and hammering the poor disks.
The most elegant solution is to use the sys.dm_db_index_physical_stats dynamic management function to return the fragmentation data for all indexes in a database and then to cursor through these issuing a rebuild command as appropriate.
In this example I have a stored procedure that does just that. It can also adjust the fill factor. In systems with incoming data it is good practice to set a fill factor below 100 so that upserts do not result in a leaf split.
The stored procedure goes into the master database and can be called from a Maintenance plan with the following syntax to rebuild all indexes in all databases
We can rebuild all indexes on all databases that are fragmented with the following command:
exec sp_MSforeachdb 'print ''use [?]''; use ?;exec sp_index_rebuild'