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'


 

sp_index_rebuild.sql

Comments (5)
  1. Ashish Gilhotra says:

    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….

  2. MSDN Archive says:

    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 😉

  3. Lxocram says:

    line 89 : SELECT @command = 'ALTER INDEX ' + QUOTENAME(@indexname,'[') +' ON ' + QUOTENAME(@schemaname,'[') + '.' +QUOTENAME(@objectname,'[') + ' REBUILD'

  4. Gert says:

    Hi.

    Just wondered if the existing version of the script has been updated with the re-org for LOB indexes?

  5. Bob Duffy says:

    Hi Gert,

    I'm not working for MS anymore – blogging over at http://blogs.prodata.ie/bob

    I 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

Comments are closed.

Skip to main content