Ocena wydajności zapytań przy użyciu planów wykonania i dynamicznych widoków zarządzania (DMV)

  • 10 min

Gdy zapytanie działa wolniej niż oczekiwano, pierwszym krokiem jest zrozumienie sposobu jego wykonywania przez aparat bazy danych. Plany wykonywania pokazują dokładne operatory, metody dostępu do danych i koszty zasobów wybrane przez optymalizator dla zapytania. Dynamiczne widoki zarządzania (DMV) uzupełniają to, ujawniając dane wydajności środowiska uruchomieniowego we wszystkich zapytaniach w bazie danych, dzięki czemu można znaleźć najdroższe przed rozpoczęciem dowolnego planu.

Czytanie planów wykonywania

Plan wykonywania to zestaw instrukcji generowanych przez optymalizator zapytań w celu pobierania i przetwarzania danych. Definiuje, które tabele mają być dostępne najpierw, czy użyć indeksów, czy przeszukiwać tabele, oraz jak połączyć, filtrować, sortować i agregować wyniki. Optymalizator ocenia wiele planów kandydatów i wybiera ten z najniższym szacowanymi kosztami.

Zrzut ekranu przedstawiający graficzny rzeczywisty plan wykonywania w programie SQL Server Management Studio przedstawiający operatory, strzałki wskazujące przepływ danych i wartości procentowe kosztów dla każdego kroku.

Istnieją dwa typy planów wykonywania:

  • Szacowany plan wykonania: wygenerowany bez uruchamiania zapytania. Pokazuje planowane operatory i szacowane liczby wierszy na podstawie statystyk. Użyj szacowanych planów do szybkiej analizy bez wpływu na bazę danych.
  • Rzeczywisty plan wykonywania: przechwycony podczas wykonywania zapytania. Obejmuje szacowany plan oraz rzeczywiste liczby wierszy, rzeczywiste czasy wykonywania, przydziały pamięci i ostrzeżenia. Rzeczywisty plan ujawnia rozbieżności między oczekiwaniami optymalizatora a tym, co się stało.

Aby wyświetlić szacowany plan, uruchom polecenie SET SHOWPLAN_XML ON przed zapytaniem lub wybierz pozycję Wyświetl szacowany plan wykonania w programie SQL Server Management Studio (SSMS). Aby przechwycić rzeczywisty plan, uruchom SET STATISTICS XML ON lub wybierz pozycję Uwzględnij rzeczywisty plan wykonania w programie SSMS przed wykonaniem zapytania.

Mimo że szacowane i rzeczywiste plany wyglądają podobnie, metryki wykonawcze rzeczywistego planu mają kluczowe znaczenie dla diagnozowania problemów z wydajnością. Jeśli na przykład szacowana liczba wierszy skanowania tabeli wynosi 100, ale rzeczywista liczba wierszy wynosi 10 000, może to wskazywać na nieaktualne statystyki prowadzące do złego wyboru planu. Optymalizator kompiluje plan na podstawie statystyk podczas pierwszego napotkania zapytania. Jeśli te statystyki nie odzwierciedlają bieżącej dystrybucji danych, plan może działać źle.

Identyfikowanie typowych problemów w planach wykonywania

Plany wykonywania są odczytywane od lewej do prawej, od góry do dołu. Pierwsze operatory uzyskują dostęp do tabel podstawowych, a końcowy operator generuje wynik zapytania. Poszukaj tych typowych problemów:

Typy operatorów informują o tym, jak aparat uzyskuje dostęp do danych. Istnieje wiele typów operatorów, z których każda reprezentuje inną metodę pobierania lub przetwarzania danych. Na przykład operator wyszukiwania indeksu reprezentuje wysoce wydajną metodę, która jest przeznaczona dla określonych wierszy przy użyciu kluczy indeksu. Z drugiej strony operator skanowania tabel lub skanowania indeksu reprezentuje mniej wydajną metodę odczytującą każdy wiersz. Jeśli widzisz skanowanie dużej tabeli, prawdopodobnie potrzebujesz indeksu. Jeśli na przykład aplikacja do handlu elektronicznego wysyła zapytania o zamówienia według daty, a plan pokazuje skanowanie indeksu klastrowanego w Orders tabeli, dodanie indeksu nieklastrowanego w OrderDate kolumnie może zmienić to skanowanie w wyszukiwanie. Należy pamiętać, że nie wszystkie skanowania są złe. Jeśli tabela jest mała lub warunek wyszukiwania zwraca większość wierszy w tabeli, skanowanie może być najbardziej wydajną metodą dostępu. Zawsze należy wziąć pod uwagę kontekst zapytania i rozmiar danych. Poznaj dane i używaj planów wykonywania, aby potwierdzić, czy metoda dostępu ma sens.

Szacowane i rzeczywiste liczby wierszy pokazują, czy założenia optymalizatora są zgodne z rzeczywistością. Optymalizator opiera swój plan na statystykach, metadanych opisujących rozkład i gęstość danych w tabelach. Jeśli statystyki są nieaktualne, szacowane liczby wierszy różnią się od rzeczywistych. Gdy optymalizator lekceważy liczbę wierszy, może wybrać sprzężenie zagnieżdżonej pętli (które przetwarza jeden wiersz naraz z wewnętrznej tabeli sprzężenia), gdy sprzężenie skrótu (które tworzy tabelę skrótów w pamięci w celu szybkiego wyszukiwania) byłoby szybsze lub przydzielić zbyt małą ilość pamięci dla operacji sortowania. Statystyki mogą stać się nieaktualne po znaczących zmianach danych, więc aktualizowanie statystyk za pomocą UPDATE STATISTICS lub włączanie automatycznych aktualizacji statystyk może pomóc optymalizatorowi podejmować lepsze decyzje.

Operatory wyszukiwania kluczy są wyświetlane, gdy aparat wyszukuje wiersze za pośrednictwem indeksu nieklastrowanego, ale wymaga dodatkowych kolumn z indeksu klastrowanego. Dla każdego pasującego wiersza mechanizm wykonuje dodatkowe wywołanie do indeksu klastrowanego, aby odzyskać te kolumny. Jeśli filtr zwraca wiele wierszy, te dodatkowe wyszukiwania szybko się sumują. Jeśli na przykład aplikacja do handlu elektronicznego filtruje zamówienia według CustomerID, ale także wybiera OrderDate, TotalAmount i ShippingAddress, a indeks nieklastrowany na CustomerID nie zawiera tych kolumn, plan wyświetla wyszukiwanie klucza dla każdego zgodnego zamówienia. Można wyeliminować wyszukiwanie kluczy, dodając brakujące kolumny jako kolumny uwzględnione w indeksie. Należy pamiętać, że zawarte kolumny zwiększają rozmiar indeksu, co może spowalniać operacje zapisu, dlatego należy rozważyć korzyści z wydajności odczytu względem obciążenia związanego z zapisem.

Grube strzałki między operatorami reprezentują liczbę wierszy przepływających między nimi. Nieoczekiwanie gruba strzałka na początku planu (odczytywana od lewej do prawej, od góry do dołu) często oznacza brak filtra lub indeksu, pozwalając przepuścić zbyt wiele wierszy.

Brakujące sugestie indeksu są wyświetlane jako tekst zaznaczony na zielono na górze graficznego planu wykonywania w programie SSMS. Gdy optymalizator wykryje, że indeks może znacząco obniżyć koszt zapytania, wyświetla rekomendację bezpośrednio w planie. Kliknij prawym przyciskiem myszy sugestię i wybierz pozycję Missing Index Details (Brakujące szczegóły indeksu ), aby wygenerować instrukcję CREATE INDEX , którą możesz przejrzeć i uruchomić. Ta sugestia jest jedną z najprostszych korzyści, które można uzyskać ze zrozumienia planu realizacji.

Ostrzeżenia są wyświetlane jako żółty trójkąt z wykrzyknikiem (⚠) dla operatora, którego dotyczy problem. Każde ostrzeżenie wskazuje szansę optymalizacji. Typowe ostrzeżenia obejmują:

  • Brakujące statystyki: Optymalizator nie może odnaleźć statystyk dla kolumny, więc odgadnął liczbę wierszy zamiast używać rzeczywistego rozkładu danych. Aby rozwiązać ten problem, utwórz statystyki dotyczące kolumn używanych w zapytaniach lub zaktualizuj istniejące statystyki, jeśli są nieaktualne.
  • Nadmierne przydzielanie pamięci: zapytanie zażądało większej ilości pamięci niż potrzebne, marnowanie zasobów, których mogą używać inne zapytania. Ten problem często występuje, gdy optymalizator przewyższa liczbę wierszy. Aktualizowanie statystyk lub ponowne zapisywanie zapytania w celu wcześniejszego filtrowania wierszy może pomóc zmniejszyć przydziały pamięci.
  • Brak predykatu sprzężenia: dwie tabele są łączone bez odpowiedniego warunku, tworząc produkt kartezjański, który zwraca każdą możliwą kombinację wierszy. Sprawdź zapytanie pod kątem brakującej lub nieprawidłowej ON klauzuli.
  • Niejawna konwersja: niezgodność typu danych wymusza, aby aparat konwertował wartości podczas wykonywania, co może przekształcić wyszukiwanie indeksowe w skanowanie. Jeśli na przykład klauzula WHERE porównuje nvarchar parametr z kolumną varchar , aparat konwertuje każdy wiersz w kolumnie na nvarchar przed porównaniem. Aby naprawić niejawne konwersje, dopasuj typy danych w parametrach zapytania do definicji kolumn.
  • Spill sortowania lub hash: Operacja sortowania lub hash przekroczyła przyznaną pamięć i zrzuciła wyniki pośrednie do bazy danych tempdb. Te operacje są drugim najczęstszym czynnikiem wysokiego użycia procesora poza skanowaniem. Jeśli zostanie wyświetlone ostrzeżenie dotyczące rozlania, optymalizator prawdopodobnie zaniżył liczbę wierszy i zażądał zbyt małej ilości pamięci. Uruchomienie polecenia UPDATE STATISTICS w celu odświeżenia statystyk tabeli lub ponownego zapisania zapytania w celu zmniejszenia liczby wierszy, zanim sortowanie może często wyeliminować wyciek.

Plany wykonywania to zaawansowane narzędzie do zrozumienia wydajności zapytań. Pokazują one dokładnie, jak silnik wykonuje zapytanie i gdzie znajdują się wąskie gardła. Dzięki efektywnemu odczytywaniu planów wykonywania możesz szybko identyfikować i rozwiązywać problemy z wydajnością zapytań bazy danych.

Wykonywanie zapytań dynamicznych widoków zarządzania dla danych wydajności środowiska uruchomieniowego

Dynamiczne widoki zarządzania uwidaczniają dane wydajności w czasie rzeczywistym i skumulowane z silnika bazy danych. Usługa Azure SQL Database wymaga VIEW DATABASE STATE uprawnień do wykonywania zapytań. Podczas gdy plany wykonywania pokazują, jak działa pojedyncze zapytanie, widoki dynamiczne zarządzania (WDM) pokazują, co dzieje się jednocześnie we wszystkich zapytaniach, co pomaga najpierw zidentyfikować te najbardziej kosztowne.

Znajdowanie najdroższych zapytań

Czas procesora CPU, operacje odczytu logicznego i liczba wykonań to najczęstsze metryki służące do identyfikowania kosztownych zapytań. Wysokie użycie procesora CPU lub odczyty logiczne wskazują, że zapytanie intensywnie obciąża zasoby, a duża liczba wykonań oznacza, że nawet średnio kosztowne zapytanie może mieć duży wpływ na ogólną wydajność. Zacznij od przejrzenia najważniejszych zapytań według średniego czasu procesora CPU lub odczytów logicznych, aby znaleźć kandydatów do optymalizacji.

sys.dm_exec_query_stats Zwraca zagregowane statystyki wydajności dla buforowanych planów zapytań. Połącz to z sys.dm_exec_sql_text, aby zobaczyć tekst zapytania, i sys.dm_exec_query_plan pobrać plan wykonania.

Następujące zapytanie znajduje 10 pierwszych zapytań według średniego czasu procesora CPU:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Ten skrypt pomaga określić, które zapytania zasługują na Twoją uwagę. Wysoki avg_logical_reads względem rozmiaru zestawu wyników często wskazuje brakujące indeksy lub nieefektywne plany. Należy jednak zachować ostrożność podczas interpretowania tych wyników. Zapytanie z wysokim średnim czasem procesora CPU, które jest uruchamiane tylko raz dziennie, może być mniej istotne niż umiarkowane zapytanie uruchamiane tysiące razy na godzinę. Zawsze należy rozważyć zarówno średni koszt, jak i liczbę wykonań podczas określania priorytetów. Można również sortować po avg_logical_reads, aby znaleźć zapytania, które intensywnie wykorzystują operacje we/wy, co często wskazuje na brakujące indeksy lub nieefektywne metody dostępu.

Sprawdzanie aktualnie wykonywanych zapytań

Chociaż poprzednie zapytanie pokazuje najdroższe historyczne zapytania w pamięci podręcznej planu, sys.dm_exec_requests udostępnia migawkę każdego aktualnie uruchomionego żądania. Zawiera kolumny dla czasu CPU, odczytu, zapisu, typu oczekiwania, czasu oczekiwania i identyfikatora sesji blokującej. Użyj tego widoku, aby wykryć aktywne zapytania, które zużywają zbyt wiele zasobów lub czekają na blokady. Ten widok jest jednym z najważniejszych widoków DMV do monitorowania wydajności w czasie rzeczywistym i rozwiązywania problemów.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

To zapytanie filtruje sesje systemowe (identyfikatory sesji 1–50) oraz sortuje według czasu CPU. Możesz również posortować według logical_reads, aby znaleźć zapytania o dużą liczbą operacji I/O. Kolumny wait_type i wait_time ułatwiają określenie, czy zapytanie czeka na blokady, wejście/wyjście lub inne zasoby.

Odnajdywanie brakujących indeksów

Wcześniej zobaczyliśmy, jak plany wykonywania mogą pokazywać brakujące sugestie dotyczące indeksu dla pojedynczego zapytania. Brakujące dynamiczne widoki zarządzania indeksami zapewniają szerszy widok indeksów, których optymalizator będzie używać we wszystkich zapytaniach, jeśli istniały. Te widoki to doskonały sposób znajdowania możliwości optymalizacji, które wpływają na wiele zapytań. sys.dm_db_missing_index_details wyświetla tabelę, kolumny równości i nierówności oraz kolumny zawarte. sys.dm_db_missing_index_group_stats zapewnia miarę poprawy, która szacuje obniżenie kosztów.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

To zapytanie oblicza improvement_measure dla każdej brakującej rekomendacji indeksu, które jest produktem średniego kosztu zapytań, które mogłyby skorzystać z indeksu, średniej procentowej poprawy oraz liczby razy, kiedy te zapytania zostały wykonane. Sortowanie według tej miary pomaga w ustaleniu priorytetów dotyczących tworzenia brakujących indeksów. Należy jednak pamiętać, że te wyniki są tylko rekomendacjami opartymi na zapytaniach aktualnie znajdującymi się w pamięci podręcznej planów. Zawsze przejrzyj sugerowane kolumny indeksów i przetestuj ich wpływ na wydajność zapytań i obciążenie zapisu przed dodaniem ich do środowiska produkcyjnego.

Uwaga / Notatka

Brakujące zalecenia dotyczące indeksu to sugestie, a nie dyrektywy. Zawsze przetestuj wpływ nowego indeksu zarówno na wydajność zapytań, jak i na obciążenie zapisu przed dodaniem go do środowiska produkcyjnego.

Monitoruj aktywne sesje i oczekujące zadania

sys.dm_exec_sessions Zawiera informacje o wszystkich uwierzytelnionych sesjach, w tym czas logowania, nazwę hosta, nazwę programu oraz skumulowany czas procesora (CPU) i odczyty. Połącz to z elementem sys.dm_os_waiting_tasks, aby zobaczyć, które zadania czekają i na jakie zasoby. Te widoki stają się niezbędne podczas diagnozowania blokowania i rywalizacji o zasoby w kolejnej jednostce.

Połącz wszystko

Plany wykonywania i dynamiczne widoki zarządzania zapewniają pełny obraz zachowania zapytań. Zacznij od widoków zarządzania dynamicznego (DMV), aby zidentyfikować najdroższe zapytania. Następnie przejdź do ich planów wykonania, aby zrozumieć, dlaczego są kosztowne. Czy brakuje indeksu powodującego skanowanie? Nieaktualne statystyki powodujące błędy oszacowania wierszy? Wyszukiwanie klucza, które można wyeliminować? To systematyczne podejście, od widoku całego systemu do pojedynczej analizy zapytań, jest najbardziej efektywnym sposobem znajdowania i naprawiania wąskich gardeł wydajności.

Kluczowe wnioski

Plany wykonywania ujawniają strategię optymalizatora dla zapytania, a rzeczywiste plany obejmują metryki środowiska uruchomieniowego, które ujawniają rozbieżności między szacowaną i rzeczywistą liczbą wierszy. Podczas odczytywania planu skoncentruj się na typach operatorów (wyszukiwanie a skanowanie), oszacowaniach liczby wierszy, ostrzeżeniach i operatorach wyszukiwania kluczy. Dynamiczne widoki zarządzania (DMV) zapewniają dane dotyczące wydajności dla całego systemu: sys.dm_exec_query_stats służy do znajdowania najdroższych zapytań, sys.dm_exec_requests do aktualnie uruchomionych zapytań, a dla brakujących indeksów DMV do możliwości optymalizacji. Zacznij od dynamicznych widoków zarządzania, aby określić, gdzie występują największe problemy, a następnie przejść do szczegółów poszczególnych planów wykonywania, aby zrozumieć, dlaczego.