showplan_xml

showplan_xml to format zapisu planu wykonania zapytania w postaci ustrukturyzowanego dokumentu XML.

Czym jest showplan_xml w SQL Server

showplan_xml to ustrukturyzowany format XML używany przez Microsoft SQL Server do przedstawiania planu wykonania zapytania. Dokument opisuje sposób, w jaki aparat optymalizatora zamierza pobrać, połączyć, przefiltrować i zwrócić dane. W planie mogą znaleźć się między innymi operatory skanowania i wyszukiwania indeksów, złączenia tabel, sortowania, agregacji, filtrów oraz przepływu wierszy pomiędzy kolejnymi etapami wykonania. XML przechowuje również właściwości operatorów, takie jak szacowana liczba wierszy, koszt, predykaty, użyte indeksy i informacje o równoległości.

W SQL Server plan XML może opisywać plan szacowany albo plan rzeczywisty. Plan szacowany powstaje bez uruchamiania zapytania i pokazuje przewidywania optymalizatora. Plan rzeczywisty zawiera dodatkowo dane z wykonania, na przykład rzeczywistą liczbę przetworzonych wierszy, liczbę wykonanych iteracji operatora oraz informacje o czasie i zużyciu zasobów, zależnie od sposobu zebrania planu. Dzięki temu można porównać założenia optymalizatora z faktycznym przebiegiem operacji.

Jak uzyskać plan wykonania w postaci XML

Plan szacowany można uzyskać za pomocą instrukcji SET SHOWPLAN_XML ON. Po jej włączeniu SQL Server nie wykonuje kolejnych instrukcji zapytań, lecz zwraca ich plan w formacie XML. Ustawienie pozostaje aktywne dla bieżącej sesji do momentu użycia SET SHOWPLAN_XML OFF. Instrukcja musi być wykonywana jako osobna partia, dlatego w narzędziach obsługujących partie polecenia poprzedzające i następujące po niej należy rozdzielić zgodnie z regułami danego klienta SQL Server. Użytkownik musi mieć odpowiednie uprawnienia do wyświetlania planu.

Plan XML można także odczytać z pamięci podręcznej za pomocą widoków i funkcji dynamicznego zarządzania. Funkcja sys.dm_exec_query_plan zwraca plan dla uchwytu planu, a sys.dm_exec_text_query_plan udostępnia jego tekstową reprezentację w formacie XML dla wskazanego planu i zakresu instrukcji. Do połączenia informacji o zapytaniu, planie i statystykach wykonania często wykorzystuje się sys.dm_exec_cached_plans, sys.dm_exec_sql_text oraz sys.dm_exec_query_stats. Plan znajdujący się w pamięci podręcznej może zniknąć po usunięciu wpisu z cache, ponownej kompilacji albo restarcie instancji.

Jak czytać strukturę i właściwości planu

Dokument showplan XML opisuje plan jako hierarchię operatorów. Operator znajdujący się wyżej w strukturze reprezentuje późniejszy etap przepływu danych, natomiast jego elementy podrzędne opisują wcześniejsze źródła wierszy. Atrybuty i elementy planu zawierają między innymi typ operatora, koszty szacowane, liczbę szacowanych wierszy oraz informacje o wymaganych i przekazywanych kolumnach. W zależności od zapytania można znaleźć operatory takie jak Index Seek, Index Scan, Table Scan, Nested Loops, Hash Match, Merge Join, Sort i Stream Aggregate.

Podczas diagnostyki należy porównywać przede wszystkim wartości szacowane z rzeczywistymi, a nie traktować procentowego kosztu jako bezpośredniej miary czasu. Duża różnica między szacowaną a rzeczywistą liczbą wierszy może wskazywać na nieaktualne statystyki, niepasujący model rozkładu danych, predykat trudny do oszacowania albo problem z formą warunku. W planie mogą pojawić się ostrzeżenia dotyczące konwersji typów, niedostatecznej pamięci dla sortowania lub operacji hash oraz użycia dysku jako obszaru roboczego. Warto również sprawdzić, czy filtr jest wykonywany blisko źródła danych, czy zapytanie korzysta z właściwego indeksu oraz czy złączenie nie przetwarza znacznie większej liczby wierszy, niż wymaga wynik.

XML planu jest przeznaczony zarówno do odczytu przez narzędzia graficzne, jak i do analizy maszynowej. SQL Server Management Studio może przedstawić plan w widoku graficznym, a oryginalny dokument można zapisać lub otworzyć jako XML. Przy analizie automatycznej trzeba uwzględnić przestrzeń nazw schematu Showplan, ponieważ elementy XML są kwalifikowane nazwą przestrzeni. Nazwa schematu i dostępne właściwości zależą od wersji SQL Server, dlatego parser nie powinien zakładać, że każdy plan zawiera identyczny zestaw elementów.

Znaczenie planu XML w diagnostyce SQL Server

Analiza showplan_xml pomaga ustalić, dlaczego zapytanie wykonuje się długo, zużywa dużo procesora, odczytuje znaczną ilość danych albo zajmuje pamięć operacyjną. Sam plan nie stanowi jednak pełnego pomiaru obciążenia. Wnioski należy zestawić z tekstem zapytania, parametrami, indeksami, statystykami, blokadami, równoległością oraz rzeczywistymi czasami wykonania. Plan szacowany może wskazać przewidywaną ścieżkę, lecz nie potwierdza, że wszystkie operatory zachowały się tak samo podczas pracy na aktualnym zbiorze danych.

Przy diagnostyce warto zachować plan przed zmianą indeksów, statystyk lub zapytania, a następnie porównać go z planem po zmianie. Trzeba sprawdzić, czy poprawa dotyczy całej instrukcji, czy tylko jednego przypadku parametrów. W SQL Server plan może być ponownie używany dla kolejnych wykonań, a sposób optymalizacji może zależeć od wartości parametrów oraz momentu kompilacji. Z tego powodu jeden dokument showplan_xml nie zawsze opisuje wszystkie warianty zachowania zapytania. Rzetelna ocena wymaga powtarzalnego testu z reprezentatywnymi parametrami i obserwacji zarówno planu, jak i faktycznych statystyk wykonania.