Share via

Smart Indexing Part II - Conditional Rebuilding

  • Article

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'

sp_index_rebuild.sql

Comments

  • Anonymous
    May 22, 2009
    What about if we have LOB data if we have LOB data in our table and have index on actually at that column so in my understanding we reorganize instead of rebuild....
  • Anonymous
    May 29, 2009
    Your right, internal LOB_COMPACTION does seem to only happen in the re-org, on the flip side the rebuild/recreate may give better page de-fragmentation.I would think it makes sense to use a re-org for LOB indexes in most cases and a rebuild in servere cases (or manually).Must update the script ;-)
  • Anonymous
    July 31, 2011
    The comment has been removed
  • Anonymous
    September 26, 2011
    Hi.Just wondered if the existing version of the script has been updated with the re-org for LOB indexes?
  • Anonymous
    October 04, 2011
    Hi Gert,I'm not working for MS anymore - blogging over at http://blogs.prodata.ie/bobI didn't maintain this script as Ola Hallengren has invested a lot of time in making some more comprehensive scripts available that cover the likes of LOB compaction. http://ola.hallengren.com/If you are using this to help roll your own, then STATISTICS_NORECOMPUTE = ON  should also be STATISTICS_NORECOMPUTE = OFF in nearly all cases as turning off stats updates  is very much an