Queries and updates extremely slow after IndexOptimize 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
I suspect you've got a different sample rate defined between your two maintenance approaches.  I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it doesn't look like you're currently doing.

At this point, this is speculation, but  you can check to see what sampling rate is currently being used on your statistics by running the following query in your database:

SELECT  OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS TableName
    ,   col.name AS ColumnName
    ,   st.name AS StatsName
    ,   sp.last_updated
    ,   sp.rows_sampled
    ,   sp.rows
    ,   (1.0*sp.rows_sampled)/(1.0*sp.rows) AS sample_pct
FROM sys.stats st 
    INNER JOIN sys.stats_columns st_col
        ON st.object_id = st_col.object_id
        AND st.stats_id = st_col.stats_id
    INNER JOIN sys.columns col
        ON st_col.object_id = col.object_id
        AND st_col.column_id = col.column_id
    CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) sp
ORDER BY 1, 2


If you see this is coming through a 1s (e.g. 100%) chances are this is your issue.  Maybe try Ola's scripts again including the @StatisticsSample parameter with the percentage getting returned by this query and see if that fixes your problem?



As additional supporting evidence for this theory, the execution plan XML shows vastly different sampling rates for the slow query (2.18233 %):

&lt;StatisticsInfo LastUpdate="2019-09-01T01:07:46.04" ModificationCount="0" 
    SamplingPercent="2.18233" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]" 
    Schema="[SVALA]" Database="[ulek-sva]" /&gt;


Versus the fast query (100 %):

&lt;StatisticsInfo LastUpdate="2019-08-25T23:01:05.52" ModificationCount="555" 
    SamplingPercent="100" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]" 
    Schema="[SVALA]" Database="[ulek-sva]" /&gt;