Should I rebuild index in non clustered index?

All we need is an easy explanation of the problem, so here it is.

I’ll be rebuilding our index in SQL Server since our fragmentation rate on all indexes for all tables are so high. Should I include to rebuild my non-clustered index also? Or rebuilding of clustered index is enough?

Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In most cases your non-clustered indexes were added after the fact to address a certain query, which might be very important to your business/application. As usual the answer is, it depends on the importance of the queries utilizing the indexes (be it clustered on non-clustered).

If you can, restore the database to a development server, and run the 3x Ola Hallengren scripts ( IndexOptimize.sql, CommandExecute.sql & CommandLog.sql) from https://ola.hallengren.com/downloads.html/.

Once deployed you can for example run this script.

EXECUTE dbo.IndexOptimize
    @Databases = 'ALL_Databases',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @PartitionLevel = 'Y',
    @MaxDOP = 0,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable = 'Y',
    @Indexes = 'ALL_INDEXES'

If you are happy with the run durations, and the database log did not grow too much, run the same deploy to your production environment, along with the above script during your allowed maintenance window.

Additional to the above you can run the following script to check how fragmented your indexes were before you reorganized/rebuilt them.

SELECT  [ID]
      , [StartTime]
      , [DatabaseName]
      , [ObjectName] = [SchemaName] + '.' + [ObjectName]
      , [IndexName]
      , [PartitionNumber]
      , [SizeMB] = ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') /128
      , Fragmentation = ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'decimal(10,1)')
      , [Duration(s)] = DATEDIFF(SECOND, [StartTime],[EndTime])
      , [Command]
FROM [dbo].[CommandLog]
WHERE CommandType NOT IN ('UPDATE_STATISTICS')          --only disply index defragmentation information
ORDER BY [StartTime]

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply