Zapytania i aktualizacje są bardzo wolne po IndexOptimize

Baza danych SQL Server 2017 Enterprise CU16 14.0.3076.1

Niedawno próbowaliśmy przejść z domyślnych zadań konserwacyjnych Index Rebuild do Ola Hallengren IndexOptimize. Domyślne zadania przebudowy indeksu działały przez kilka miesięcy bez żadnych problemów, a zapytania i aktualizacje działały z akceptowalnymi czasami wykonania. Po uruchomieniu IndexOptimizew bazie danych:

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'

wydajność bardzo się pogorszyła. Instrukcja aktualizacji, która wcześniej zajęła 100 ms, IndexOptimizezajęła później 78 000 ms (przy użyciu identycznego planu), a zapytania były również gorsze o kilka rzędów wielkości.

Ponieważ nadal jest to testowa baza danych (migrujemy system produkcyjny z Oracle), przywróciliśmy kopię zapasową i wyłączono, IndexOptimizea wszystko wróciło do normy.

Chcielibyśmy jednak zrozumieć, co IndexOptimizeróżni się od „normalnego”, Index Rebuildco mogło spowodować ten ekstremalny spadek wydajności, aby mieć pewność, że unikniemy go po przejściu do produkcji. Wszelkie sugestie dotyczące tego, czego szukać, byłyby bardzo mile widziane.

Plan wykonania instrukcji aktualizacji, gdy jest ona wolna. tj.
po IndexOptimize
Rzeczywisty plan wykonania (jak najszybciej)

Nie byłem w stanie dostrzec różnicy.
Zaplanuj to samo zapytanie, gdy jest szybkie
Rzeczywisty plan wykonania

Podejrzewam, że zdefiniowano inną częstotliwość próbkowania między dwoma podejściami do konserwacji. Wierzę, że skrypty Oli używają domyślnego próbkowania, chyba że podasz @StatisticsSampleparametr , który nie wygląda tak, jak obecnie.

W tym momencie jest to spekulacja, ale możesz sprawdzić, jaka częstotliwość próbkowania jest obecnie używana w statystykach, uruchamiając następujące zapytanie w bazie danych:

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

Jeśli widzisz, że nadchodzi 1s (np. 100%), to jest twój problem. Może spróbuj ponownie skryptów Oli, w tym @StatisticsSampleparametru z procentem zwracanym przez to zapytanie i sprawdź, czy to rozwiąże Twój problem?

Jako dodatkowy dowód potwierdzający tę teorię, XML planu wykonania pokazuje znacznie różne częstotliwości próbkowania dla powolnego zapytania (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]" />

W porównaniu do szybkiego zapytania (100%):

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

Odpowiedź Johna jest poprawnym rozwiązaniem, jest to tylko dodatek do tego, które części planu wykonania uległy zmianie i na przykład, jak łatwo dostrzec różnice w eksploratorze Sentry One Plan

Instrukcja aktualizacji, która zajęła 100 ms, zanim IndexOptimize zajęła później 78 000 ms (przy użyciu identycznego planu)

Przeglądając wszystkie plany zapytań, kiedy wydajność spadła, można łatwo dostrzec różnice.

Obniżona wydajność

Dwie liczby ponad 35 sekund czasu procesora i upływu czasu

Oczekiwana wydajność

Dużo lepiej

Główna degradacja występuje dwukrotnie w tym zapytaniu dotyczącym aktualizacji:

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

plan wykonania dla tego zapytania o obniżonej wydajności

Szacowany plan zapytań dla tego zapytania o aktualizację ma bardzo wysokie oszacowania, gdy wydajność spadła:

Podczas gdy w rzeczywistości (rzeczywisty plan wykonania) wciąż musi działać, po prostu nie jest to szalona kwota, którą pokazują szacunki.

Największy wpływ na wydajność mają poniższe dwa skany i połączenia dopasowania mieszania:

Rzeczywiste skanowanie przy obniżonej wydajności # 1

Rzeczywiste skanowanie przy obniżonej wydajności # 2

Plan wykonania dla tego zapytania z oczekiwaną wydajnością

Gdy porównasz to z szacunkami (lub wartościami rzeczywistymi) planu zapytań z normalną oczekiwaną wydajnością, różnice są łatwe do wykrycia.

Ponadto poprzednie dwa dostępy do tabeli nawet się nie zdarzyły:

Nie widać tej eliminacji przy łączeniu mieszającym, ponieważ wejście kompilacji (górne) jest najpierw wstawiane do tablicy mieszającej. Następnie w tej tabeli mieszającej sondowane są wartości zerowe, zwracające wartości zerowe.

Bez dodatkowych informacji możemy tylko w ciemności wykonać lekkie, świadomie dźgnięcia, więc powinieneś edytować pytanie, aby podać nieco więcej. Na przykład plany zapytań dla tej instrukcji aktualizacji, dla których podano czasy, zarówno przed operacjami konserwacji indeksu, jak i po nim, ponieważ plany mogą się różnić z powodu zaktualizowania statystyk indeksu ( https://www.brentozar.com/pastetheplan / jest przydatny do tego, zamiast wypełniać pytanie, co może być ogromną częścią XML lub dać zrzut ekranu, który nie zawiera niektórych istotnych informacji zawartych w tekście planu).

Dwa bardzo proste punkty od nietoperza:

1. Czy optymalizacja została zakończona? Jeśli twoje testy konkurują z IO długotrwałych przebudowań indeksu, będzie to miało wpływ na czasy.
2. Czy testowałeś wiele razy? Jeśli aktualizacja jest oparta na danych z zapytania, które uwzględnia wiele danych (zamiast prostego `UPDATE TheTable SET ThisColumn = 'A Static Value'), być może dane te są normalnie w pamięci, ale zostały wypłukane które pierwsze serie powiązanych zapytań będą wolniejsze niż zwykle z powodu uderzenia w dysk, a nie znalezienia potrzebnych stron już w puli buforów w pamięci.