Estymowany plan zapytania. Dlaczego (nieraz) w zupełności wystarczy?

W większości przypadków, walcząc z kłopotliwym zapytaniem chcemy mieć dostęp do aktualnego planu zapytania. Wtedy najprostszą metodą jest wykonanie zapytania z wyświetleniem aktualnego planu zapytania. Co w sytuacji, gdy zapytanie wykonuję się zdecydowanie za długo? Bądź po prostu nie możemy pozwolić na wykonanie zapytania na bazie produkcyjnej?

Aby móc odpowiedzieć na te pytania, wyjaśnię w skrócie, co się dzieje z zapytaniem przed jego wykonaniem.

Parsowanie, Przepisywanie, Optymalizacja i… Wykonanie

W pierwszym etapie, parsowanie (query parsing) sprawdza zapytanie pod kątem poprawności składni. Etap ten jest również odpowiedzialny za przekształcenie zapytania w drzewo logicznych operatorów niezbędnych do wykonania zapytania. Nazywany jest często drzewem parsowania (parse tree) lub niekiedy drzewem zapytania (query tree).

W chwili, gdy składnia jest poprawna zapytanie przechodzi do etapu algebraizacji (algebrizer), który nazywany jest również pojęciem przypisywania. Wykonywane jest rozpoznawanie wszystkich obiektów użytych w zapytaniu, tabele, kolumny, itd. Wynikiem tego procesu jest drzewo wyrażeń oraz hash zapytania, który trafia do optymalizatora zapytań.

Następnie przekazany hash jest wykorzystywany do sprawdzenia czy dla danego zapytania istnieje już plan. Jeśli tak to tutaj jego zadanie się kończy. W przeciwnym wypadku optymalizator (query optimizer) na podstawie istniejących statystyk generuję i ocenia wiele planów. Wybrany zostaje optymalny plan o szacowanym najniższym koszcie, który optymalizator był wstanie wygenerować w możliwe krótkim czasie.

Ostatni etap to wykonanie zapytania (query execution) na podstawie wygenerowanego lub wyszukanego planu z pamięci podręcznej.

Estymowany vs aktualny plan zapytania

Cała procedura która została opisana wyżej jest przedstawiona w bardzo dużym uproszczeniu. Jednak pozwoli to lepiej zobrazować różnicę pomiędzy estymowanym a aktualnym planem zapytania.

Jednym słowem, estymowany to plan który jest prezentowany po etapie optymalizacji (query optimizer). Natomiast aktualny plan zapytania to plan który jest zwracany po etapie jego wykonania (query execution).

To co ważne. Trzeba zdać sobie sprawę, że aktualny plan zapytania to bardzo często nadal plan estymowany lecz za statystykami aktualnego wykonania. Może jak najbardziej posłużyć do analizowania problemów z wydajnością zapytania.

Różnicę pomiędzy planami będą dotyczyć metryk, których SQL Server nie jest wstanie zebrać przed jego wykonaniem. Dobrym i najbardziej obrazującym przykładem jest Estimeted Number of Rows i Actual Number of Rows.

Procesor zapytań zna faktyczną liczbę rekordów dopiero po wykonaniu tego zapytania, ponieważ wcześniej tylko szacuję na podstawie statystyk. Ich trafność zależy w dużej mierze od ich aktualności.

Było sporo teorii, to na koniec coś praktycznego czyli rodzaje i sposoby dostępu do planu zapytań.

Plan graficzny

Plan graficzny według mnie najbardziej przyjazny, przedstawia zbiór ikon. Każda z ikon przedstawia operator logiczny oraz fizyczny niezbędny do wykonania zapytania.

Plan graficzny wyświetlimy przede wszystkim w narzędziu SQL Server Management Studio poprzez skrót CTRL+L dla planu estymowanego i CTRL+M dla planu aktualnego. Wartą uwagi alternatywą dla SSMS jest darmowy program SentryOne Plan Explorer.

Plan w formacie XML

Pod planem graficznym kryję się XML który Management Studio potrafi przekształcić w bardziej przyjazną formę. Polecenie SET SHOWPLAN_XML należy użyć w celu otrzymania planu estymowanego, bądź SET STATISTICS XML dla planu aktualnego.

Przeglądanie planu w takim formacie nie należy do najprzyjemniejszych. XML odkrywa swoje możliwości, gdy zaczyna się odpytywać plany przechowywane w pamięci podręcznej w celu odszukania grupy zapytań. Poniżej przedstawiam przykład polecenia, które wyszuka zapytań z odczytem sterty (Table Scan) w konkretnej bazie.

Plan tekstowy

Polecenia SET SHOWPLAN_TEXT i SET_SHOWPLAN_ALL pozwalają wyświetlić estymowany plan zapytania. Przy czym SET_SHOWPLAN_ALL zwróci większą liczbę szczegółów. Wyświetlenie aktualnego planu wiąże się z dodaniem, przed zapytaniem polecenia SET STATISTICS PROFILE. Przykład poniżej.

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

Leave a Reply

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *