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!
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI'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!
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]