query_pre_execution_showplan

query_pre_execution_showplan to zdarzenie Extended Events przechwytujące szacowany plan przed uruchomieniem zapytania.

Rola zdarzenia query_pre_execution_showplan w SQL Server

query_pre_execution_showplan jest zdarzeniem Extended Events w Microsoft SQL Server. Rejestruje plan wykonania przygotowany przez optymalizator zapytań przed rozpoczęciem wykonywania instrukcji. Chodzi o plan szacowany, czyli opis sposobu, w jaki silnik zamierza odczytać dane, połączyć wyniki, przefiltrować wiersze i zwrócić rezultat. Zdarzenie nie jest raportem z faktycznego przebiegu zapytania. Nie pokazuje więc rzeczywistej liczby wierszy przetworzonych przez poszczególne operatory ani rzeczywistego czasu pracy każdego z nich.

Najważniejszym elementem zdarzenia jest plan w formacie Showplan XML. XML opisuje między innymi operator główny, operatory podrzędne, kolejność ich wykonania, użyte indeksy, predykaty, estymowane koszty oraz przewidywaną liczbę wierszy. W planie mogą pojawić się operatory takie jak Index Seek, Index Scan, Table Scan, Nested Loops, Merge Join lub Hash Match. Konkretna zawartość zależy od zapytania, schematu tabel, dostępnych indeksów, statystyk i ustawień optymalizatora.

Jak SQL Server tworzy plan szacowany

Przed wykonaniem zapytania SQL Server analizuje tekst instrukcji po wcześniejszym parsowaniu i algebrze zapytania, a następnie wybiera plan spośród możliwych sposobów realizacji. Optymalizator bierze pod uwagę typy danych, warunki w klauzuli WHERE, relacje użyte w JOIN, sortowanie, grupowanie, ograniczenie liczby wyników oraz dostępne ścieżki dostępu do tabel. W tym procesie korzysta ze statystyk kolumn i indeksów, ponieważ na ich podstawie szacuje selektywność predykatów oraz liczbę wierszy przekazywanych między operatorami.

Plan przechwycony przez query_pre_execution_showplan może ujawnić, że optymalizator przewidział użycie indeksu, ale wybrał skan, ponieważ uznał go za tańszy dla przewidywanej liczby wyników. Może również pokazać kosztowne sortowanie, agregację, dużą operację Hash Match albo zagnieżdżone pętle dla tabel, które według estymacji mają niewiele pasujących wierszy. Sam wysoki koszt operatora nie dowodzi jeszcze błędu. Jest sygnałem do porównania założeń planu z rzeczywistą strukturą danych i sposobem używania zapytania.

Warto rozróżnić plan szacowany od planu aktualnego. Plan szacowany opisuje przewidywania dostępne przed wykonaniem, natomiast plan aktualny zawiera także informacje zebrane podczas działania instrukcji, takie jak rzeczywista liczba wierszy i liczba wykonanych iteracji operatorów. Zdarzenie query_pre_execution_showplan służy przede wszystkim do obserwacji decyzji podejmowanej przed wykonaniem, a nie do pełnego pomiaru rezultatów runtime.

Wnioski z odczytu Showplan XML

Analizę należy rozpocząć od porównania estymowanej liczby wierszy na kolejnych operatorach. Duża rozbieżność między przewidywaną a rzeczywistą licznością jest typowym źródłem nietrafionego wyboru algorytmu złączenia, kolejności operacji albo sposobu dostępu do danych, choć jej potwierdzenie wymaga danych z wykonania. W samym planie szacowanym można natomiast sprawdzić, jakie wartości i rozkłady danych zakłada optymalizator, które predykaty zostały zastosowane oraz czy filtr jest użyty bezpośrednio przy odczycie tabeli lub indeksu.

Przydatne jest także sprawdzenie, czy warunek może zostać wykorzystany jako predykat wyszukiwania indeksowego, czy tylko jako filtr wykonywany po odczytaniu większego zakresu. Wyrażenia na kolumnach, niezgodne typy danych oraz konwersje niejawne mogą zmienić sposób użycia indeksu. W Showplan XML można szukać informacji o ostrzeżeniach, konwersjach, estymowanych kosztach i właściwościach operatorów. Należy jednak interpretować je razem z definicją tabeli, indeksami, statystykami i tekstem zapytania, ponieważ pojedynczy operator nie opisuje całego problemu.

Przechwytywanie tego zdarzenia powinno być ograniczone filtrami odpowiadającymi analizowanemu przypadkowi, na przykład identyfikatorem bazy danych, nazwą aplikacji, identyfikatorem sesji lub innymi dostępnymi predykatami zdarzenia. Showplan XML może być obszerny, a generowanie i zapisywanie planów wpływa na narzut diagnostyczny. Sesję Extended Events należy więc projektować tak, aby nie gromadziła bez ograniczeń wszystkich planów z obciążonego środowiska. Po zebraniu materiału trzeba ocenić rozmiar danych, czas działania sesji i sposób przechowywania plików wyjściowych.

Praktyczne zastosowanie przy analizie zapytań

query_pre_execution_showplan jest użyteczne podczas badania problemów z kompilacją i wyborem planu w SQL Server. Pozwala uchwycić plan związany z konkretnym wykonaniem jeszcze przed rozpoczęciem pracy operatorów. Dzięki temu można zestawić tekst instrukcji, kontekst bazy danych i parametry zdarzenia z decyzją optymalizatora. Taki materiał pomaga ustalić, czy zapytanie korzysta z przewidzianego indeksu, czy odczytuje niepotrzebnie duży zakres danych oraz czy kolejność złączeń odpowiada przewidywanej liczności wyników.

Sam zapis zdarzenia nie stanowi automatycznej diagnozy. Zmiana planu może wynikać między innymi z aktualizacji statystyk, zmiany danych, modyfikacji indeksów, parametrów zapytania albo ustawień wpływających na optymalizację. Przed zmianą indeksu lub przepisywaniem instrukcji należy porównać kilka reprezentatywnych przypadków, przeanalizować plan XML i potwierdzić wnioski pomiarem wykonania. W zastosowaniach produkcyjnych trzeba również uwzględnić narzut zbierania planów oraz uprawnienia wymagane do konfiguracji i obserwacji sesji Extended Events.

Ze względu na związek z diagnostyką silnika bazy danych jest to termin dotyczący środowisk serwerowych i administracji SQL Server, a nie typowej naprawy sprzętu konsumenckiego. W praktyce odczyt takiego zdarzenia powinien wykonywać administrator lub inżynier posiadający odpowiedni dostęp do instancji, sesji Extended Events i danych diagnostycznych. Prawidłowa interpretacja wymaga znajomości Showplan XML, statystyk oraz sposobu optymalizacji zapytań przez konkretną wersję SQL Server.