SYS.DM_EXEC_QUERY_PLAN

sys.dm_exec_query_plan zwraca plan wykonania zapytania na podstawie jego uchwytu.

Uchwyt planu jako argument funkcji

sys.dm_exec_query_plan jest funkcją tablicową przyjmującą jeden parametr typu varbinary(64) — uchwyt planu, czyli plan_handle. Uchwyt pobiera się z widoków sys.dm_exec_requests dla zapytań wykonywanych w tej chwili albo z sys.dm_exec_query_stats dla zapytań już zakończonych, których plan nadal leży w pamięci podręcznej. Wywołanie wykonuje się przez operator CROSS APPLY, bo funkcja musi dostać wartość z każdego wiersza źródłowego. Zwracana kolumna query_plan ma typ XML i zawiera pełny plan w formacie Showplan XML — ten sam, który SQL Server Management Studio rysuje graficznie po naciśnięciu Ctrl+M i wykonaniu zapytania.

Plan szacowany kontra rzeczywisty przebieg

Zwracany dokument to plan szacowany: zawiera przewidywane liczby wierszy i koszty, ale nie zawiera rzeczywistej liczby przetworzonych wierszy ani czasu spędzonego w poszczególnych operatorach. To zasadnicza różnica wobec planu rzeczywistego zbieranego przy włączonej opcji Include Actual Execution Plan. Do porównania jednego z drugim służy widok sys.dm_exec_query_plan_stats, dostępny dopiero po włączeniu ustawienia bazy LAST_QUERY_PLAN_STATS. W praktyce diagnozującego interesuje właśnie rozjazd między szacunkiem a rzeczywistością — jeśli optymalizator spodziewał się kilkuset wierszy, a operacja przemieliła miliony, przyczyną są zdezaktualizowane statystyki albo predykat, którego silnik nie potrafi oszacować.

Kiedy funkcja zwraca pustą wartość

Najczęstsze zaskoczenie to NULL w kolumnie query_plan mimo poprawnego uchwytu. Powód bywa prozaiczny: plan został usunięty z cache między pobraniem uchwytu a wywołaniem funkcji, na przykład po przebudowie indeksu albo po instrukcji DBCC FREEPROCCACHE. Drugi powód to ograniczenie parsera XML — plany zawierające więcej niż 128 poziomów zagnieżdżenia nie dają się zwrócić jako XML i funkcja oddaje wartość pustą. Dotyczy to zapytań z bardzo długimi łańcuchami UNION ALL albo widoków zbudowanych warstwowo jeden na drugim. Trzecia pułapka to uprawnienia: potrzebne jest VIEW SERVER STATE, którego zwykłe konto aplikacyjne nie ma, więc zapytanie kończy się błędem odmowy dostępu, a nie pustym wynikiem.

Diagnostyka baz w firmowych wdrożeniach

Do tego widoku sięga się zwykle wtedy, gdy program handlowy zaczyna zwalniać w konkretnej operacji — wystawianiu dokumentu, przeliczaniu stanów magazynowych, generowaniu zestawienia za okres. Warszawskie Pogotowie Komputerowe serwisuje wdrożenia Subiekta, WAPRO i Comarch oparte o SQL Server, gdzie takie zatory pojawiają się po latach pracy razem z przyrostem tabel dokumentów. Zdjęcie planu wykonania dla zapytania trzymającego blokadę pokazuje, czy silnik wykonuje skanowanie całej tabeli zamiast wyszukiwania po indeksie. Pracujemy całodobowo, więc analizę obciążonej bazy da się przeprowadzić poza godzinami pracy firmy, kiedy sesje użytkowników nie zaburzają obrazu.