Share via

Wykrywalne typy wąskich gardeł wydajności zapytań w programie SQL Server i usłudze Azure SQL Managed Instance

  • Artykuł

Dotyczy:SQL ServerAzure SQL Managed Instance

Próbując rozwiązać problem z wąskim gardłem wydajności, zacznij od ustalenia, czy wąskie gardło występuje w czasie, gdy zapytanie jest w stanie uruchomienia, czy w stanie oczekiwania. W zależności od tego ustalenia mają zastosowanie różne rozwiązania. W tym artykule omówiono problemy i rozwiązania dotyczące każdego typu problemu.

Widoki DMV programu SQL Server umożliwiają wykrywanie tego typu wąskich gardeł wydajności.

Problemy związane z uruchamianiem: Problemy związane z uruchamianiem są zwykle związane z problemami kompilacji, co powoduje nieoptymalny plan zapytania lub problemy z wykonywaniem związane z niewystarczającą lub nadmierną ilością zasobów. Problemy związane z oczekiwaniem: Problemy związane z oczekiwaniem są zwykle związane z:

  • Blokady (blokowanie)
  • WE/WY
  • Rywalizacja związana z użyciem tempdb
  • Oczekiwanie na udzielanie pamięci

Ten artykuł dotyczy programu SQL Server i usługi Azure SQL Managed Instance. Zobacz również Wykrywanie typów wąskich gardeł wydajności zapytań w usłudze Azure SQL Database.

Problemy z kompilacją powodujące nieoptymalny plan zapytania

Nieoptymalny plan wygenerowany przez optymalizator zapytań SQL może być przyczyną niskiej wydajności zapytań. Optymalizator zapytań SQL może utworzyć nieoptymalny plan z powodu brakującego indeksu, nieaktualnych statystyk, nieprawidłowego oszacowania liczby wierszy do przetworzenia lub niedokładnego oszacowania wymaganej pamięci. Jeśli wiesz, że zapytanie zostało wykonane szybciej w przeszłości lub w innym wystąpieniu, porównaj rzeczywiste plany wykonywania, aby sprawdzić, czy są one inne.

Przykład dostrajania zapytań i wskazówek w artykule Dostrajanie aplikacji i baz danych pod kątem wydajności pokazuje wpływ nieoptymalnego planu zapytania ze względu na sparametryzowane zapytanie, sposób wykrywania tego warunku i sposobu używania wskazówki dotyczącej zapytania do rozwiązania.

  • Aby uzyskać więcej informacji na temat przetwarzania zapytań, zobacz Przewodnik po architekturze przetwarzania zapytań.
  • Aby zmienić poziomy zgodności bazy danych i przeczytać więcej na temat różnic między poziomami zgodności, zobacz ALTER DATABASE.
  • Aby dowiedzieć się więcej na temat szacowania kardynalności, zobacz Szacowanie kardynalności

Rozpoznawanie zapytań z nieoptymalnymi planami wykonania zapytania

W poniższych sekcjach omówiono sposób rozwiązywania zapytań z nieoptymalnym planem wykonywania zapytań.

Zapytania, które mają problemy z planem poufnym parametrów (PSP)

Problem z planem poufnym parametru (PSP) występuje, gdy optymalizator zapytań generuje plan wykonywania zapytania, który jest optymalny tylko dla określonej wartości parametru (lub zestawu wartości), a buforowany plan nie jest optymalny dla wartości parametrów używanych w kolejnych wykonaniach. Plany, które nie są optymalne, mogą następnie powodować problemy z wydajnością zapytań i obniżać ogólną przepływność obciążenia.

Aby uzyskać więcej informacji na temat wąchania parametrów i przetwarzania zapytań, zobacz Przewodnik po architekturze przetwarzania zapytań.

Kilka obejść może rozwiązać problemy PSP. Każde obejście wiąże się z kompromisami i wadami:

  • Nowa funkcja wprowadzona w programie SQL Server 2022 (16.x) to optymalizacja planu wrażliwego na parametry, która próbuje zminimalizować większość nieoptymalnych planów zapytań spowodowanych przez czułość parametrów. Jest to włączone z poziomem zgodności bazy danych 160.
  • Użyj wskazówki zapytania RECOMPILE na każdym wykonaniu zapytania. To obejście powoduje wymianę czasu kompilacji i zwiększenie wydajności procesora CPU w celu uzyskania lepszej jakości planu. Opcja RECOMPILE ta często nie jest możliwa w przypadku obciążeń wymagających wysokiej przepływności.
  • Użyj wskazówki zapytania OPTION (OPTIMIZE FOR...), aby zastąpić rzeczywistą wartość parametru wartością typową parametru, która generuje plan, który jest wystarczająco dobry dla większości możliwości wartości parametrów. Ta opcja wymaga dobrego zrozumienia optymalnych wartości parametrów i skojarzonych cech planu.
  • Użyj wskazówki zapytania OPTION (OPTIMIZE FOR UNKNOWN), aby zastąpić rzeczywistą wartość parametru, a zamiast tego użyj średniej wektora gęstości. Można to również zrobić, przechwytując wartości parametrów przychodzących w zmiennych lokalnych, a następnie używając zmiennych lokalnych w predykatach, zamiast używać samych parametrów. W przypadku tej poprawki średnia gęstość musi być wystarczająco dobra.
  • Wyłącz wąchanie parametrów w całości przy użyciu wskazówki DISABLE_PARAMETER_SNIFFING zapytania.
  • Użyj wskazówki zapytania KEEPFIXEDPLAN, aby zapobiec ponownej kompilacji w pamięci podręcznej. To obejście zakłada, że wystarczająco dobry wspólny plan jest już używany w pamięci podręcznej. Można również wyłączyć automatyczne aktualizacje statystyk, aby zmniejszyć prawdopodobieństwo, że dobry plan zostanie wykluczony, a nowy zły plan zostanie skompilowany.
  • Wymuś plan, jawnie używając wskazówki zapytania USE PLAN , ponownie zapisując zapytanie i dodając wskazówkę w tekście zapytania. Możesz też ustawić określony plan przy użyciu magazynu zapytań lub przez włączenie automatycznego dostrajania.
  • Zastąp pojedynczą procedurę zagnieżdżonym zestawem procedur, które mogą być używane na podstawie logiki warunkowej i skojarzonych wartości parametrów.
  • Utwórz dynamiczne alternatywy wykonywania ciągów do statycznej definicji procedury.

Aby zastosować wskazówki dotyczące zapytań, zmodyfikuj zapytanie lub użyj wskazówek magazynu zapytań, aby zastosować wskazówkę bez wprowadzania zmian w kodzie. W wersjach programu SQL Server przed programem SQL Server 2022 użyj przewodników planu.

Aby uzyskać więcej informacji na temat rozwiązywania problemów PSP, zobacz następujące wpisy w blogu:

Działanie kompilacji spowodowane niewłaściwą parametryzacją

Gdy zapytanie zawiera literały, aparat bazy danych automatycznie sparametryzuje instrukcję lub użytkownik jawnie sparametryzuje instrukcję, aby zmniejszyć liczbę kompilacji. Duża liczba kompilacji dla zapytania przy użyciu tego samego wzorca, ale różnych wartości literałów mogą powodować wysokie wykorzystanie procesora CPU. Podobnie, jeśli tylko częściowej parametryzacji zapytania, które nadal korzysta z literałów, aparat bazy danych nie parametryzuje dalej zapytania.

Oto przykład częściowo sparametryzowanego zapytania:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

W tym przykładzie przyjmuje @p1wartość t1.c1 , ale t2.c2 nadal przyjmuje identyfikator GUID jako literał. W takim przypadku, jeśli zmienisz wartość parametru c2, zapytanie będzie traktowane jako inne zapytanie i zostanie wykonana nowa kompilacja. Aby zmniejszyć kompilacje w tym przykładzie, należy również sparametryzować identyfikator GUID.

Następujące zapytanie przedstawia liczbę zapytań według skrótu zapytania w celu określenia, czy zapytanie jest prawidłowo sparametryzowane:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Czynniki wpływające na zmiany planu zapytania

Ponowne skompilowanie planu wykonywania zapytania może spowodować wygenerowanie planu zapytania, który różni się od oryginalnego buforowanego planu. Istniejący oryginalny plan może zostać automatycznie ponownie skompilowany z różnych powodów:

  • Zmiany w schemacie są przywołyne przez zapytanie
  • Zmiany danych w tabelach są przywołyane przez zapytanie
  • Zmieniono opcje kontekstu zapytania

Skompilowany plan może zostać wyrzucony z pamięci podręcznej z różnych powodów, takich jak:

  • Ponowne uruchomienia wystąpień
  • Zmiany konfiguracji w zakresie bazy danych
  • Wykorzystanie pamięci
  • Jawne żądania w celu wyczyszczenia pamięci podręcznej

Jeśli używasz wskazówki RECOMPILE, plan nie zostanie zapisany w pamięci podręcznej.

Ponowne kompilowanie (lub nowa kompilacja po eksmisji pamięci podręcznej) może nadal spowodować wygenerowanie planu wykonania zapytania, który jest identyczny z oryginałem. Gdy plan zmieni się z poprzedniego lub oryginalnego planu, te wyjaśnienia są prawdopodobnie następujące:

  • Zmieniono projekt fizyczny: na przykład nowo utworzone indeksy bardziej efektywnie obejmują wymagania zapytania. Nowe indeksy mogą być używane w nowej kompilacji, jeśli optymalizator zapytań zdecyduje, że użycie tego nowego indeksu jest bardziej optymalne niż użycie struktury danych, która została pierwotnie wybrana dla pierwszej wersji wykonywania zapytania. Wszelkie fizyczne zmiany w obiektach, do których odwołuje się odwołanie, mogą spowodować wybór nowego planu w czasie kompilacji.

  • Różnice zasobów serwera: jeśli plan w jednym systemie różni się od planu w innym systemie, dostępność zasobów, taka jak liczba dostępnych procesorów, może mieć wpływ na generowany plan. Jeśli na przykład jeden system ma więcej procesorów, można wybrać plan równoległy. Aby uzyskać więcej informacji na temat równoległości, zobacz Konfigurowanie maksymalnego stopnia równoległości Opcji konfiguracji serwera.

  • Różne statystyki: Statystyki skojarzone z obiektami, do których odwołuje się odwołanie, mogły ulec zmianie lub mogą się znacznie różnić od statystyk oryginalnego systemu. Jeśli zmieni się statystyka i ponowna kompilacja, optymalizator zapytań używa statystyk rozpoczynających się od momentu ich zmiany. Zmienione rozkłady i częstotliwości danych statystyk mogą różnić się od tych z oryginalnej kompilacji. Te zmiany są używane do tworzenia oszacowań kardynalności. (Szacowanie kardynalności to liczba wierszy , które mają przepływać przez drzewo zapytań logicznych). Zmiany szacowania kardynalności mogą prowadzić do wyboru różnych operatorów fizycznych i skojarzonych zamówień operacji. Nawet drobne zmiany w statystykach mogą spowodować zmianę planu wykonywania zapytania.

  • Zmieniono poziom zgodności bazy danych lub wersję narzędzia do szacowania kardynalności: zmiany na poziomie zgodności bazy danych mogą włączać nowe strategie i funkcje, które mogą spowodować inny plan wykonywania zapytań. Poza poziomem zgodności bazy danych wyłączona lub włączona flaga śledzenia 4199 lub zmieniony stan konfiguracji o zakresie bazy danych QUERY_OPTIMIZER_HOTFIXES może również wpływać na opcje planu wykonywania zapytań w czasie kompilacji. Flagi śledzenia 9481 (force legacy CE) i 2312 (force default CE) również wpływają na plan.

Problemy z limitami zasobów w usłudze Azure SQL Managed Instance

Niska wydajność zapytań nie jest związana z nieoptymalnymi planami zapytań i brakującymi indeksami są zwykle związane z niewystarczającą lub nadmierną ilością zasobów. Jeśli plan zapytania jest optymalny, zapytanie (i baza danych) może osiągać limity zasobów dla wystąpienia zarządzanego. Przykładem może być nadmiar przepływności zapisu dziennika dla poziomu usługi.

Jeśli zidentyfikujesz problem jako niewystarczający zasób, możesz uaktualnić zasoby, aby zwiększyć pojemność bazy danych w celu wychwytowania wymagań dotyczących procesora CPU. Aby uzyskać informacje na temat skalowania wystąpienia zarządzanego, zobacz Limity zasobów warstwy usługi

Problemy z wydajnością spowodowane zwiększonym woluminem obciążenia

Zwiększenie ruchu aplikacji i woluminu obciążenia może spowodować zwiększenie użycia procesora CPU. Należy jednak zachować ostrożność, aby prawidłowo zdiagnozować ten problem. Gdy widzisz problem z wysokim użyciem procesora CPU, odpowiedz na te pytania, aby ustalić, czy wzrost jest spowodowany zmianami woluminu obciążenia:

  • Czy zapytania z aplikacji są przyczyną problemu z wysokim użyciem procesora CPU?

  • W przypadku zapytań zużywających najwięcej procesora CPU, które można zidentyfikować:

    • Czy wiele planów wykonywania skojarzonych z tym samym zapytaniem? Jeśli tak, dlaczego?
    • Czy w przypadku zapytań z tym samym planem wykonywania były spójne czasy wykonywania? Czy liczba wykonań wzrosła? Jeśli tak, zwiększenie obciążenia prawdopodobnie powoduje problemy z wydajnością.

Podsumowując, jeśli plan wykonywania zapytania nie został wykonany inaczej, ale użycie procesora CPU zwiększyło się wraz z liczbą wykonań, problem z wydajnością prawdopodobnie jest związany ze wzrostem obciążenia.

Nie zawsze łatwo jest zidentyfikować zmianę woluminu obciążenia, która napędza problem z procesorem CPU. Rozważ następujące czynniki:

  • Zmienione użycie zasobów: rozważmy na przykład scenariusz, w którym użycie procesora CPU zwiększyło się do 80 procent przez dłuższy czas. Użycie procesora CPU nie oznacza zmiany woluminu obciążenia. Regresje w planie wykonywania zapytań i zmiany w dystrybucji danych mogą również przyczynić się do większego użycia zasobów, nawet jeśli aplikacja wykonuje to samo obciążenie.

  • Wygląd nowego zapytania: aplikacja może prowadzić nowy zestaw zapytań w różnym czasie.

  • Zwiększenie lub zmniejszenie liczby żądań: ten scenariusz jest najbardziej oczywistą miarą obciążenia. Liczba zapytań nie zawsze odpowiada większemu wykorzystaniu zasobów. Jednak ta metryka jest nadal znaczącym sygnałem, zakładając, że inne czynniki pozostają niezmienione.

  • Równoległość: Nadmierna równoległość może pogorszyć wydajność innych współbieżnych obciążeń, głodując inne zapytania dotyczące zasobów procesora CPU i wątku roboczego. Aby uzyskać więcej informacji na temat równoległości, zobacz Konfigurowanie maksymalnego stopnia równoległości Opcji konfiguracji serwera.

Po wyeliminowaniu nieoptymalnego planu i problemów związanych z oczekiwaniem, które są związane z problemami z wykonywaniem, problem z wydajnością jest na ogół zapytania prawdopodobnie oczekują na jakiś zasób. Problemy związane z oczekiwaniem mogą być spowodowane przez:

Metody wyświetlania kategorii oczekiwania najwyższego poziomu

Te metody są często używane do wyświetlania najważniejszych kategorii typów oczekiwania:

  • Użyj magazynu zapytań, aby znaleźć statystyki oczekiwania dla każdego zapytania w czasie. W magazynie zapytań typy oczekiwania są łączone w kategorie oczekiwania. W sys.query_store_wait_stats można znaleźć mapowanie kategorii oczekiwania na oczekiwania.
  • Użyj sys.dm_os_wait_stats , aby zwrócić informacje o wszystkich oczekiwaniach napotkanych przez wątki wykonywane podczas operacji zapytania. Ten zagregowany widok służy do diagnozowania problemów z wydajnością wystąpienia usługi Azure SQL Managed Instance lub programu SQL Server. Zapytania mogą czekać na zasoby, oczekiwania w kolejce lub oczekiwania zewnętrzne.
  • Użyj sys.dm_os_waiting_tasks , aby zwrócić informacje o kolejce zadań oczekujących na jakiś zasób.

W scenariuszach wysokiego użycia procesora CPU statystyki magazynu zapytań i oczekiwania mogą nie odzwierciedlać użycia procesora CPU, jeśli:

  • Zapytania zużywające duże użycie procesora CPU nadal są wykonywane.
  • Zapytania zużywające wysokie użycie procesora CPU były uruchamiane po przejściu w tryb failover.

Dynamiczne widoki zarządzania, które śledzą magazyn zapytań i statystyki oczekiwania, pokazują wyniki tylko dla zakończonych pomyślnie i przekroczono limit czasu zapytań. Nie wyświetlają danych dla aktualnie wykonywanych instrukcji, dopóki instrukcje nie zostaną zakończone. Użyj dynamicznego widoku zarządzania sys.dm_exec_requests do śledzenia aktualnie wykonywanych zapytań i skojarzonego czasu roboczego.

Napiwek

Dodatkowe narzędzia:

Następne kroki