I’ve wrote many scripts over the years to selectively reindex indexes and update statistics on Microsoft SQL Server. However more recently I’ve been using Hallengren’s SQL reindex script as it’s very powerful and pretty easy to use and schedule via SQL Agent.

Hallengren’s SQL Reindex Script

I currently use the following script run daily as it reorganises indexes with fragmentation of between 5% and 20%, and performs an online rebuild if over 20%. Fragmentation under 5% is ignored (it’s not worth doing anything with these). It also updates statistics on records which have been modified.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 20,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'