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.
After running 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.
Execution plan for the update statement when it is slow. i.e.
After IndexOptimize
Actual execution plan (coming asap)
I haven't been able to spot a difference.
Plan for the same query when it is fast
Actual execution plan