Queries and updates extremely slow after IndexOptimize

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 %):

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

Versus the fast query (100 %):

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

John's answer is the correct solution, this is just an addition as to what parts of the execution plan changed and en example on how to easily spot the differences with Sentry One Plan explorer

An update statement that took 100ms before IndexOptimize took 78.000ms afterwards (using an identical plan)

When looking at all the query plans when your performance was degraded, you can easily spot the differences.

Degraded performance

enter image description here

Two counts of over 35 seconds of cpu time & elapsed time

Expected performance

enter image description here

Much better

The main degradation is twice on this update query:

UPDATE SVALA.INGÅENDEANALYS
                           SET 
                              UPPDRAGAVSLUTAT = @NEW$AVSLUTAT
                        WHERE INGÅENDEANALYS.ID IN 
                           (
                              SELECT IA.ID
                              FROM 
                                 SVALA.INGÅENDEANALYS  AS IA 
                                    JOIN SVALA.INGÅENDEANALYSX  AS IAX 
                                    ON IAX.INGÅENDEANALYS = IA.ID 
                                    JOIN SVALA.ANALYSMATERIAL  AS AM 
                                    ON AM.ID = IA.ANALYSMATERIALID 
                                    JOIN SVALA.ANALYSMATERIALX  AS AMX 
                                    ON AMX.ANALYSMATERIAL = AM.ID 
                                    JOIN SVALA.INSÄNTMATERIAL  AS IM 
                                    ON IM.ID = AM.INSÄNTMATERIALID 
                                    JOIN SVALA.INSÄNTMATERIALX  AS IMX 
                                    ON IMX.INSÄNTMATERIAL = IM.ID
                              WHERE IM.UPPDRAGSID = SVALA.PKGSVALA$STRIPVERSION(@NEW$ID)
                      )

the execution plan for this query with degraded performance

The estimated query plan of this update query has very high estimates when performance was degraded:

enter image description here

While in reality (the actual execution plan) it still has to do work, just not the crazy amount that the estimates show.

The biggest impact on performance is the two scans & hash match joins below:

Actual scan on degraded performance #1

enter image description here

Actual scan on degraded performance #2

enter image description here


The execution plan for this query with expected performance

When you compare that to the estimates (or actuals) of the query plan with normal expected performance, the differences are easy to spot.

enter image description here

Also, the previous two table accesses did not even happen:

enter image description here

enter image description here

enter image description here

You don't see this elimination on the hash join because the build (top) input is inserted into the hash table first. Afterwards zero values are probed in this hash table, returning zero values.