Database SQL Server 2017 Enterprise CU16 14.0.3076.1
We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren
IndexOptimize. The default Index Rebuild jobs had been running for a couple of months without any issues, and the queries and updates were working with acceptable execution times.
IndexOptimize on the database:
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 = 30, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y'
performance was extremely degraded. An update statement that took 100ms before
IndexOptimize took 78.000ms afterwards (using an identical plan), and queries were also performing several orders of magnitude worse.
Since this still is a test database (we're migrating a production system from Oracle) we reverted to a backup and disabled
IndexOptimize and everything returned to normal.
However, we would like to understand what
IndexOptimize does differently from the "normal"
Index Rebuild that could have caused this extreme performance degradation in order to make sure we avoid it once we go to production. Any suggestions on what to look for would be greatly appreciated.