Rozwiązywanie problemów z powolnymi zapytaniami, których dotyczy limit czasu optymalizatora zapytań

Artykuł
03/24/2023

Dotyczy: SQL Server

W tym artykule przedstawiono limit czasu optymalizatora, jego wpływ na wydajność zapytań oraz sposób optymalizowania wydajności.

Co to jest limit czasu optymalizatora?

SQL Server używa opartego na kosztach optymalizatora zapytań (QO). Aby uzyskać informacje na temat funkcji QO, zobacz Przewodnik po architekturze przetwarzania zapytań. Optymalizator zapytań oparty na kosztach wybiera plan wykonywania zapytań o najniższym koszcie po utworzeniu i ocenie wielu planów zapytań. Jednym z celów SQL Server optymalizatora zapytań jest spędzenie rozsądnego czasu na optymalizacji zapytań w porównaniu z wykonywaniem zapytań. Optymalizacja zapytania powinna być znacznie szybsza niż jego wykonanie. Aby osiągnąć ten cel, funkcja QO ma wbudowany próg zadań do rozważenia przed zatrzymaniem procesu optymalizacji. Gdy próg zostanie osiągnięty, zanim funkcja QO rozważy wszystkie możliwe plany, osiągnie limit czasu optymalizatora. Zdarzenie limitu czasu optymalizatora jest zgłaszane w planie zapytania jako limit czasu w obszarze Przyczyna wczesnego zakończenia optymalizacji instrukcji. Ważne jest, aby zrozumieć, że ten próg nie jest oparty na czasie zegara, ale na liczbie możliwości rozważanych przez optymalizator. W bieżących wersjach SQL Server QO ponad pół miliona zadań jest branych pod uwagę przed osiągnięciem limitu czasu.

Limit czasu optymalizatora został zaprojektowany w SQL Server i w wielu przypadkach nie ma wpływu na wydajność zapytań. Jednak w niektórych przypadkach wybór planu zapytania SQL może mieć negatywny wpływ na przekroczenie limitu czasu optymalizatora, co może spowodować wolniejszą wydajność zapytań. Gdy wystąpią takie problemy, zrozumienie mechanizmu limitu czasu optymalizatora i tego, jak złożone zapytania mogą mieć wpływ, może pomóc w rozwiązywaniu problemów i zwiększeniu szybkości zapytania.

Wynikiem osiągnięcia progu limitu czasu optymalizatora jest to, że SQL Server nie uwzględnia całego zestawu możliwości optymalizacji. Oznacza to, że mogły zostać pominięte plany, które mogą spowodować skrócenie czasu wykonywania. Funkcja QO zatrzyma się na progu i rozważy w tym momencie plan zapytań o najniższych kosztach, mimo że mogą istnieć lepsze, niezbadane opcje. Należy pamiętać, że plan wybrany po osiągnięciu limitu czasu optymalizatora może zapewnić rozsądny czas wykonywania zapytania. Jednak w niektórych przypadkach wybrany plan może spowodować wykonanie zapytania, które jest nieoptymalne.

Jak wykryć przekroczenie limitu czasu optymalizatora?

Poniżej przedstawiono objawy wskazujące limit czasu optymalizatora:

Złożone zapytanie

Masz złożone zapytanie, które obejmuje wiele połączonych tabel (na przykład jest przyłączonych co najmniej osiem tabel).
Wolne zapytanie

Zapytanie może działać wolno lub wolniej niż w innej wersji lub systemie SQL Server.

Plan zapytania przedstawia wartość StatementOptmEarlyAbortReason=Timeout

Plan zapytania jest wyświetlany StatementOptmEarlyAbortReason="TimeOut" w planie zapytań XML.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

Sprawdź właściwości operatora planu po lewej stronie w usłudze Microsoft SQL Server Management Studio. Możesz zobaczyć wartość Przyczyna wcześniejszego zakończenia optymalizacji instrukcji to Limit czasu.

Co powoduje przekroczenie limitu czasu optymalizatora?

Nie ma prostego sposobu określenia warunków, które mogłyby spowodować osiągnięcie lub przekroczenie progu optymalizatora. W poniższych sekcjach przedstawiono kilka czynników, które wpływają na liczbę planów eksplorowanych przez QO w poszukiwaniu najlepszego planu.

W jakiej kolejności tabele powinny być przyłączone?

Oto przykład opcji wykonywania sprzężeń z trzema tabelami (Table1, , Table2Table3):
- Table2 Dołącz do Table1 elementu i wynik za pomocą poleceniaTable3
- Table3 Dołącz do Table1 elementu i wynik za pomocą poleceniaTable2
- Table3 Dołącz do Table2 elementu i wynik za pomocą poleceniaTable1
Uwaga: Tym większa jest liczba tabel, tym większe są możliwości.
Jaka struktura dostępu sterty lub drzewa binarnego (HoBT) służy do pobierania wierszy z tabeli?
- Indeks klastrowany
- Indeks nieklastruowany1
- Indeks nieklastruowany2
- Sterta tabeli
Jakiej metody dostępu fizycznego użyć?
- Wyszukiwanie indeksu
- Skanowanie indeksu
- Skanowanie tabeli
Jakiego operatora sprzężenia fizycznego użyć?
- Sprzężanie zagnieżdżonych pętli (NJ)
- Sprzężanie skrótu (HJ)
- Scal sprzężenie (MJ)
- Sprzężanie adaptacyjne (począwszy od SQL Server 2017 r. (14.x))
Aby uzyskać więcej informacji, zobacz Sprzężenia.
Czy wykonać części zapytania równolegle czy szeregowo?

Aby uzyskać więcej informacji, zobacz Przetwarzanie zapytań równoległych.

Chociaż następujące czynniki zmniejszą liczbę rozważanych metod dostępu, a tym samym rozważane możliwości:

Predykaty zapytań (filtry w klauzuli WHERE )
Istnienie ograniczeń
Kombinacje dobrze zaprojektowanych i aktualnych statystyk

Uwaga: To, że QO osiągnie próg, nie oznacza, że będzie ono kończyć się wolniejszym zapytaniem. W większości przypadków zapytanie będzie działać dobrze, ale w niektórych przypadkach może wystąpić wolniejsze wykonywanie zapytania.

Przykład sposobu, w jaki czynniki są brane pod uwagę

Aby to zilustrować, weźmy przykład sprzężenia między trzema tabelami (t1, t2i t3) i każda tabela ma indeks klastrowany i indeks nieklastruowany.

Najpierw należy wziąć pod uwagę typy sprzężenia fizycznego. Istnieją dwa sprzężenia zaangażowane tutaj. A ponieważ istnieją trzy możliwości sprzężenia fizycznego (NJ, HJ i MJ), zapytanie można wykonać na 3² = 9 sposobów.

NJ — NJ
NJ — HJ
NJ — MJ
HJ — NJ
HJ — HJ
HJ — MJ
MJ — NJ
MJ — HJ
MJ — MJ

Następnie rozważ kolejność sprzężenia, która jest obliczana przy użyciu permutacji: P (n, r). Kolejność pierwszych dwóch tabel nie ma znaczenia, więc mogą istnieć możliwości P(3,1) = 3:

Dołącz do t1 programu t2 , a następnie za pomocą polecenia t3
Dołącz do t1 programu t3 , a następnie za pomocą polecenia t2
Dołącz do t2 programu t3 , a następnie za pomocą polecenia t1

Następnie rozważ indeksy klastrowane i nieklastruowane, które mogą być używane do pobierania danych. Ponadto dla każdego indeksu mamy dwie metody dostępu, wyszukiwanie lub skanowanie. Oznacza to, że dla każdej tabeli jest^{2 2} = 4 opcje. Mamy trzy tabele, więc może być 4³ = 64 wyborów.

Na koniec, biorąc pod uwagę wszystkie te warunki, może istnieć 9 * 3 * 64 = 1728 możliwych planów.

Teraz załóżmy, że w zapytaniu jest dołączonych n tabel, a każda tabela ma indeks klastrowany i indeks nieklastruowany. Weź pod uwagę następujące czynniki:

Zamówienia sprzężenia: P(n,n-2) = n!/2
Typy sprzężeń: 3^n-1
Różne typy indeksów z metodami wyszukiwania i skanowania: 4ⁿ

Pomnóż wszystkie powyższe elementy i możemy uzyskać liczbę możliwych planów: 2*n!*12^n-1. Gdy n = 4, liczba to 82 944. Gdy n = 6, liczba to 358 318 080. Dlatego wraz ze wzrostem liczby tabel biorących udział w zapytaniu liczba możliwych planów zwiększa się geometrycznie. Ponadto, jeśli uwzględnisz możliwość równoległości i innych czynników, możesz sobie wyobrazić, ile możliwych planów zostanie uwzględnionych. W związku z tym zapytanie z dużą liczbą sprzężeń jest bardziej prawdopodobne, że osiągnie próg limitu czasu optymalizatora niż zapytanie z mniejszą liczbą sprzężeń.

Należy pamiętać, że powyższe obliczenia ilustrują scenariusz najgorszego przypadku. Jak już wspomniano, istnieją czynniki, które zmniejszą liczbę możliwości, takich jak predykaty filtrów, statystyki i ograniczenia. Na przykład predykat filtru i zaktualizowane statystyki zmniejszą liczbę metod dostępu fizycznego, ponieważ bardziej efektywne może być użycie wyszukiwania indeksu niż skanowanie. Spowoduje to również mniejszy wybór sprzężeń itd.

Dlaczego widzę limit czasu optymalizatora z prostym zapytaniem?

Nic z optymalizatorem zapytań nie jest proste. Istnieje wiele możliwych scenariuszy, a stopień złożoności jest tak wysoki, że trudno jest zrozumieć wszystkie możliwości. Optymalizator zapytań może dynamicznie ustawić próg limitu czasu na podstawie kosztu planu znalezionego na określonym etapie. Jeśli na przykład zostanie znaleziony plan, który wydaje się stosunkowo wydajny, limit zadań wyszukiwania lepszego planu może zostać zmniejszony. W związku z tym niedoszacowane szacowanie kardynalności (CE) może być jednym ze scenariuszy wcześniejszego przekroczenia limitu czasu optymalizatora. W tym przypadku przedmiotem dochodzenia jest CE. Jest to rzadszy przypadek w porównaniu ze scenariuszem dotyczącym uruchamiania złożonego zapytania omówionego w poprzedniej sekcji, ale jest to możliwe.

Rozwiązania

Limit czasu optymalizatora wyświetlany w planie zapytania nie musi oznaczać, że jest to przyczyna niskiej wydajności zapytań. W większości przypadków może nie być konieczne nic z tą sytuacją. Plan zapytania, który SQL Server kończy się, może być rozsądny, a uruchomione zapytanie może działać dobrze. Być może nigdy nie wiesz, że napotkano przekroczenie limitu czasu optymalizatora.

Jeśli znajdziesz potrzebę dostrojenia i optymalizacji, wykonaj następujące kroki.

Krok 1. Ustanawianie punktu odniesienia

Sprawdź, czy możesz wykonać to samo zapytanie z tym samym zestawem danych w innej kompilacji SQL Server, przy użyciu innej konfiguracji CE lub w innym systemie (specyfikacje sprzętowe). Przewodnią zasadą dostrajania wydajności jest "nie ma problemu z wydajnością bez punktu odniesienia". Dlatego ważne byłoby ustanowienie punktu odniesienia dla tego samego zapytania.

Krok 2. Wyszukaj "ukryte" warunki, które prowadzą do przekroczenia limitu czasu optymalizatora

Przeanalizuj zapytanie szczegółowo, aby określić jego złożoność. Po wstępnym zbadaniu może nie być oczywiste, że zapytanie jest złożone i obejmuje wiele sprzężeń. Typowy scenariusz polega na tym, że w tym przypadku są zaangażowane widoki lub funkcje o wartości tabeli. Na przykład na powierzchni zapytanie może wydawać się proste, ponieważ łączy dwa widoki. Jednak podczas badania zapytań wewnątrz widoków może się okazać, że każdy widok łączy siedem tabel. W związku z tym po połączeniu obu widoków zostanie dołączone sprzężone z 14 tabelami. Jeśli zapytanie używa następujących obiektów, przejdź do szczegółów każdego obiektu, aby zobaczyć, jak wyglądają bazowe zapytania wewnątrz tego obiektu:

W przypadku wszystkich tych scenariuszy najczęstszym rozwiązaniem byłoby ponowne zapisania zapytania i podzielenie go na wiele zapytań. Aby uzyskać więcej szczegółów , zobacz Krok 7. Uściślij zapytanie .

Podzapyty lub tabele pochodne

Poniższe zapytanie jest przykładem, które łączy dwa oddzielne zestawy zapytań (tabele pochodne) z 4–5 sprzężeniami w każdym z nich. Jednak po przeanalizowaniu przez SQL Server zostanie on skompilowany w jedno zapytanie z dołączonymi ośmioma tabelami.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Typowe wyrażenia tabeli (CTE)

Używanie wielu typowych wyrażeń tabeli (CTE) nie jest odpowiednim rozwiązaniem, które upraszcza zapytanie i pozwala uniknąć przekroczenia limitu czasu optymalizatora. Wiele cte tylko zwiększy złożoność zapytania. W związku z tym stosowanie klawiszy CTE podczas rozwiązywania przekroczenia limitu czasu optymalizatora przynosi efekt przeciwny do zamierzonego. Operacje cte wyglądają jak logiczne przerwanie zapytania, ale zostaną one połączone w pojedyncze zapytanie i zoptymalizowane jako pojedyncze duże sprzężenie tabel.

Oto przykład cte, który zostanie skompilowany jako pojedyncze zapytanie z wieloma sprzężeniami. Może się wydawać, że zapytanie względem my_cte jest sprzężenie proste dwóch obiektów, ale w rzeczywistości istnieje siedem innych tabel połączonych w CTE.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Widoki

Upewnij się, że sprawdzono definicje widoku i pobrano wszystkie zaangażowane tabele. Podobnie jak w przypadku cte i tabel pochodnych, sprzężenia mogą być ukryte wewnątrz widoków. Na przykład sprzężenia między dwoma widokami może ostatecznie być pojedyncze zapytanie z ośmiu tabel zaangażowanych:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Funkcje o wartości tabeli (TVF)

Niektóre sprzężenia mogą być ukryte wewnątrz tfv. W poniższym przykładzie pokazano, co jest wyświetlane jako sprzężenie między dwoma tfvami, a tabela może być sprzężeniem z dziewięcioma tabelami.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Unii

Operatory unii łączą wyniki wielu zapytań w jeden zestaw wyników. Łączą one również wiele zapytań w pojedyncze zapytanie. Następnie możesz uzyskać pojedyncze, złożone zapytanie. W poniższym przykładzie zostanie wyświetlony pojedynczy plan zapytania obejmujący 12 tabel.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

Krok 3. Jeśli masz zapytanie punktu odniesienia, które działa szybciej, użyj planu zapytania

Jeśli ustalisz, że określony plan punktu odniesienia uzyskiwany z kroku 1 jest lepszy dla zapytania za pomocą testowania, użyj jednej z następujących opcji, aby wymusić wybór tego planu za pomocą funkcji QO:

Krok 4. Zmniejszanie możliwości wyboru planów

Aby zmniejszyć prawdopodobieństwo przekroczenia limitu czasu optymalizatora, spróbuj zmniejszyć możliwości, które należy wziąć pod uwagę podczas wybierania planu. Ten proces obejmuje testowanie zapytania przy użyciu różnych opcji wskazówek. Podobnie jak w przypadku większości decyzji dotyczących QO, wybory nie zawsze są deterministyczne na powierzchni, ponieważ istnieje wiele różnych czynników, które należy rozważyć. W związku z tym nie ma jednej gwarantowanej strategii pomyślnej, a wybrany plan może zwiększyć lub zmniejszyć wydajność wybranego zapytania.

Wymuszanie zamówienia JOIN

Użyj polecenia OPTION (FORCE ORDER) , aby wyeliminować permutacje zamówień:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

Zmniejszanie możliwości JOIN

Jeśli inne alternatywy nie pomogły, spróbuj zmniejszyć kombinacje planów zapytań, ograniczając wybór operatorów sprzężeń fizycznych za pomocą wskazówek dotyczących sprzężenia. Na przykład: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) lub OPTION (MERGE JOIN).

Uwaga: Należy zachować ostrożność podczas korzystania z tych wskazówek.

W niektórych przypadkach ograniczenie optymalizatora z mniejszą liczbą opcji sprzężenia może spowodować, że opcja najlepszego sprzężenia nie będzie dostępna i może faktycznie spowolnić zapytanie. Ponadto w niektórych przypadkach optymalizator wymaga określonego sprzężenia (na przykład celu wiersza), a zapytanie może nie wygenerować planu, jeśli to sprzężenie nie jest opcją. W związku z tym po skierowaniu wskazówek dotyczących sprzężenia dla określonego zapytania sprawdź, czy znajdziesz kombinację, która oferuje lepszą wydajność i eliminuje limit czasu optymalizatora.

Poniżej przedstawiono dwa przykłady użycia takich wskazówek:

Umożliwia OPTION (HASH JOIN, LOOP JOIN) zezwalanie tylko na sprzężenia skrótu i pętli oraz unikanie scalania sprzężeń w zapytaniu:
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

Wymuszanie określonego sprzężenia między dwiema tabelami:

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

Krok 5. Zmiana konfiguracji CE

Spróbuj zmienić konfigurację CE, przełączając się między starszą i nową CE. Zmiana konfiguracji CE może spowodować, że funkcja QO wybierze inną ścieżkę, gdy SQL Server oceni i utworzy plany zapytań. Dlatego nawet jeśli wystąpi problem z przekroczeniem limitu czasu optymalizatora, możliwe jest, że zostanie wyświetlony plan, który będzie działać bardziej optymalnie niż ten wybrany przy użyciu alternatywnej konfiguracji CE. Aby uzyskać więcej informacji, zobacz Jak aktywować najlepszy plan zapytań (szacowanie kardynalności).

Krok 6. Włączanie poprawek optymalizatora

Jeśli nie włączono poprawek optymalizatora zapytań, rozważ włączenie ich przy użyciu jednej z następujących dwóch metod:

Poziom serwera: użyj flagi śledzenia T4199.
Poziom bazy danych: użyj ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON lub zmień poziomy zgodności bazy danych dla SQL Server wersji 2016 i nowszych.

Poprawki QO mogą powodować, że optymalizator przejmie inną ścieżkę podczas eksploracji planu. W związku z tym może wybrać bardziej optymalny plan zapytań. Aby uzyskać więcej informacji, zobacz SQL Server model obsługi śledzenia poprawek poprawki optymalizatora zapytań 4199.

Krok 7. Uściślij zapytanie

Rozważ podzielenie pojedynczego zapytania z wieloma tabelami na wiele oddzielnych zapytań przy użyciu tabel tymczasowych. Podział zapytania to tylko jeden ze sposobów uproszczenia zadania dla optymalizatora. Zobacz następujący przykład:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Aby zoptymalizować zapytanie, spróbuj podzielić pojedyncze zapytanie na dwa zapytania, wstawiając część wyników sprzężenia w tabeli tymczasowej:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Udostępnij przez