sys.dm_exec_query_stats to jedno z najczęściej wykorzystywanych DMV do analizy najbardziej kosztownych zapytań.
Rola sys.dm_exec_query_stats w SQL Server
sys.dm_exec_query_stats jest dynamicznym widokiem zarządzania (DMV, Dynamic Management View) silnika Microsoft SQL Server. Udostępnia zagregowane statystyki wykonania instrukcji języka T-SQL, dla których plan wykonania znajduje się w pamięci podręcznej planów. Jeden wiersz opisuje konkretną instrukcję znajdującą się w konkretnym skompilowanym planie, dlatego wynik nie jest prostą listą wszystkich zapytań kiedykolwiek uruchomionych na serwerze.
Widok służy przede wszystkim do wyszukiwania zapytań zużywających dużo procesora, czasu wykonania, odczytów logicznych, odczytów fizycznych albo zapisów logicznych. Nie jest trwałym dziennikiem historii. Dane mogą zniknąć po usunięciu planu z pamięci podręcznej, ponownej kompilacji instrukcji, zmianie planu lub restarcie instancji SQL Server. W praktyce wynik przedstawia stan dostępnych obecnie statystyk, a nie pełny raport historyczny.
Jak czytać wartości kosztu wykonania
Kolumna execution_count pokazuje liczbę wykonań instrukcji uwzględnionych w danym wpisie. total_worker_time oznacza łączny czas pracy procesora przypisany do tych wykonań, natomiast total_elapsed_time przedstawia łączny czas upływający od rozpoczęcia do zakończenia. Wartości czasu są raportowane w mikrosekundach. Sam czas procesora nie musi być równy czasowi upływającemu, ponieważ instrukcja może oczekiwać na blokady, operacje wejścia-wyjścia, pamięć albo inne zasoby.
Do oceny średniego kosztu pojedynczego wykonania można odnieść wartości całkowite do execution_count. Przykładowo wysokie total_worker_time przy małej liczbie wykonań wskazuje na kosztowne jednostkowo zapytania, a duża liczba wykonań przy umiarkowanym koszcie jednostkowym może ujawnić problem wynikający z nadmiernej częstotliwości wywołań. total_logical_reads informuje o liczbie stron odczytanych z pamięci bufora, total_physical_reads o odczytach wymagających dostępu do dysku, a total_logical_writes o zapisach stron w pamięci bufora.
Połączenie statystyk z tekstem i planem
Sam DMV nie zawsze zwraca kompletny tekst zapytania w wygodnej postaci. Do identyfikacji instrukcji wykorzystuje się między innymi sql_handle, plan_handle, statement_start_offset i statement_end_offset. Uchwyty można połączyć z funkcją sys.dm_exec_sql_text, aby odczytać tekst partii T-SQL, procedury albo instrukcji ad hoc. plan_handle można przekazać do sys.dm_exec_query_plan, aby pobrać plan wykonania w formacie XML i sprawdzić, w jaki sposób optymalizator zaplanował dostęp do danych.
Wyniki warto porządkować według właściwej miary, na przykład łącznego czasu procesora, średniego czasu wykonania albo średniej liczby odczytów logicznych. Kolumny last_execution_time, creation_time oraz informacje o generacji planu pomagają ustalić, czy wpis jest aktualny i czy plan był wielokrotnie tworzony. Należy jednak pamiętać, że identyczny tekst zapytania może wystąpić w kilku wpisach, jeżeli został skompilowany w różnych kontekstach, z innymi parametrami, ustawieniami sesji lub planami wykonania.
Ograniczenia analizy i bezpieczna interpretacja
Statystyki z sys.dm_exec_query_stats opisują zapytania zakończone i obecne w pamięci podręcznej. Nie pokazują pełnego obrazu zapytań wykonywanych w przeszłości, jeżeli ich plany zostały usunięte. DMV może także nie być wystarczający do wyjaśnienia chwilowego przeciążenia, ponieważ wartości są agregowane od momentu utworzenia danego wpisu, a nie od początku ustalonego okresu raportowego. Do analizy trendów i zdarzeń historycznych potrzebne są odrębne mechanizmy monitorowania SQL Server, na przykład odpowiednio skonfigurowane sesje Extended Events.
Nie należy utożsamiać wysokiego kosztu zapytania z jednym automatycznym rozwiązaniem. Przyczyną mogą być między innymi nieadekwatne indeksy, nieaktualne statystyki, niekorzystne szacowanie liczby wierszy, parametryzacja, blokowanie albo duża liczba powtarzanych wywołań. Plan powinien być analizowany razem z tekstem instrukcji, modelem danych, czasem wystąpienia problemu i obciążeniem instancji. W środowisku z komputerem używanym jako klient SQL Server diagnoza może obejmować także sprawdzenie opóźnień sieciowych, dostępności zasobów systemowych oraz poprawności konfiguracji aplikacji, bez przypisywania tych objawów samemu DMV.
