I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this approach to improve performance and it worked perfectly for a long time. However, recently this approach has started to take too much time.
So, instead of using the above, I execute index rebuild. It completed with one-six of the time taken in the first approach.
Why did the first approach become slow over time, and why is the full index rebuild better?
EXECUTE dbo.IndexOptimize @Databases = 'MyDBname' , @FragmentationLow = NULL , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' , @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' , @FragmentationLevel1 = 5 , @FragmentationLevel2 = 30 , @UpdateStatistics = 'ALL' , @OnlyModifiedStatistics = 'Y'