1

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!

Improve this question
5
  • Duplicate on SO
    – SMor
    May 31, 2021 at 11:19
  • SO removed my thread.
    – Dwightqwerty
    May 31, 2021 at 11:21
  • 1
    Just some advice, make sure to have a couple test queries for before and after you rebuild. You may find it's not even worth doing in the future (for any of your indexes). I've never seen it make much of a difference except in edge cases where vendor database systems didn't believe in using clustered indexes, for example, or specific big data cases.
    – J.D.
    May 31, 2021 at 12:52
  • 1
    You're likely better off skipping the index rebuilds, since scripts don't measure a meaningful metric, and just updating statistics instead.
    – Erik Darling
    May 31, 2021 at 14:32
  • 1
    I did a few blog posts on whether fragmentation actually cost anything nowadays, along with attempts to quantify. To cut it short, I very much agree with Erik. The longer monologue: sqlblog.karaszi.com/?s=fragmentation (start with the last one).
    – Tibor Karaszi
    May 31, 2021 at 15:11

1 Answer 1

Reset to default
0

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]
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.