Jak pobrać 10 najbardziej kosztownych zapytań SQL

Koszt w SQL Server

Pojęcie kosztu nie jest tak oczywiste, jak by się mogło wydawać. Być może w systemie istnieje długie i skomplikowane zapytanie, ale wykonywane jest raz w miesiącu. Z drugiej strony, zapytanie może być krótkie, ale wywoływane setki lub tysiące razy. Kolejną rzeczą jest przyjęcie kryterium. Zdarza się, że koszt zapytania nie jest definiowany przez całkowity czas wykonywania się, lecz przez liczbę fizycznych odczytów z dysku.

SQL Server gromadzi sporo informacji, w tym plany wykonania, które mogą być wielokrotnie używane. Gromadzi także wspomniane powyżej czasy wykonań tych planów, liczbę fizycznych odczytów i zapisów (w stronach po 8 kB) oraz liczbę wykonań. W tym celu udostępnia dynamiczny widok systemowy sys.dm_exec_query_stats.

Najdłuższe czasowo zapytania w SQL Server

Wydobycie informacji o najdłuższych zapytaniach nie jest szczególnie trudne. Przyjrzyjmy się przykładowemu zapytaniu:

Większość kolumn pochodzi bezpośrednio z dynamicznego widoku sys.dm_exec_query_stats. Problem w tym, że nie ma tam treści samego zapytania. To zapytanie należy pobrać przy pomocy funkcji tablicowej sys.dm_exec_sql_text (stąd użycie CROSS APPLY). Zapytanie zwraca sumaryczny czas wykonania wszystkich wyrażeń tej samej treści (pomijając parametryzację!).

Wydobywanie treści zapytania

To, co zwraca funkcja sys.dm_exec_sql_text nie jest czystym zapytaniem. To treść wsadu, poprzedzona definicjami parametrów. Ten sam plan wykonania może być użyty dla różnych zestawów danych wejściowych, stąd właśnie parametry. Wsad może zawierać wiele instrukcji, może zawierać komentarze. Aby ułatwić dostęp do właściwej treści zapytania, konkretnego wyrażenia we wsadzie (ang. batch), widok systemowy udostępnia kolumny statement_start_offset oraz statement_end_offset. Co ważne, offset, czyli przesunięcie, pozycja początku, jak również pozycja końca, podawane są w bajtach. Sama treść jest tekstem UNICODE, zatem każdy znak zajmuje po dwa bajty. Pociąga to za sobą konieczność dzielenia wskaźnika przez 2, przez co uzyskujemy pozycję nie w bajtach, lecz w znakach. Co ważne, jeżeli treść wyrażenia kończy się w tym samym miejscu co cały wsad, wskaźnik końca przyjmuje wartość -1. Gdyby nie te drobne niedogodności, treść zapytania byłaby jeszcze łatwiejsza do analizy.

Wartości średnie i odchylenia

Wartości z widoku sys.dm_exec_query_stats pozwalają na dużo bogatszą analizę statystyczną niż się może wydawać. Być może interesuje nas średnia, którą można wyliczyć dzieląc całkowity czas przez ilość wykonań wybranych instrukcji. Zdarza się jednak, że zapytanie wykonuje się szybko, ale dla szczególnych przypadków trwa długo. Tutaj pomoże nam inna miara. Nazwałem ją odchyleniem, ale nie należy tej wartości mylić ze standardowym odchyleniem, które też jest wykorzystywane w statystyce, ale liczy się je trochę inaczej. Co to odchylenie ma oznaczać? Będzie to stosunek minimalnego i maksymalnego czasu wykonania do średniej. Jeżeli zatem minimalny czas wynosi 100, a średni 1000, to minimalne odchylenie będzie wynosiło 10[%] (100[%]*100/1000 = 10[%]). Podobnie liczone jest ochylenie w drugą stronę. Jeżeli średni czas wynosi 1000, a maksymalny czas to 10000, wtedy odchylenie górne wynosi 1000[%] (100[%]*10000/1000 = 1000[%]).

Jeżeli dolne odchylenie jest niewiele mniejsze niż 100, a górne niewiele większe niż 100, wtedy możemy śmiało powiedzieć - wszystkie zapytania z tego planu wykonania realizowane są w podobnym czasie (koszcie). Jeżeli wartości te odbiegają od 100[%], wtedy wniosek jest inny - istnieją zapytania, które wykonują się bardzo szybko, ale istnieją też takie, które wykonują się bardzo wolno. Może to być spowodowane pobieraniem różnej liczby rekordów lub przyjęciem przez optymalizator metody polegającej na całkowitym bądź częściowym skanowaniu tabeli lub indeksuTabela bez indeksu CLUSTERED zwana jest stosem (ang. heap), tabela z indeksem CLUSTERED staje się indeksem w całości. Stos też jest indeksowany, ale odbywa się to poprzez wewnętrzny indeks alokacji (IAM).. Wartości równe 100 oznaczają, że: 1) wszystkie zapytania wykonują się w takim samym czasie i mają ten sam koszt lub 2) analizowany plan wykonania użyty jest przez jedno zapytanie. Przyjrzyjmy się metodzie wyliczania wspomnianych wartości:

Wynikiem powyższego skryptu może być tabela podobna do zaprezentowanej poniżej:

Widać, że w przypadku trzeciego zapytania różnica między najszybszym a najwolniejszym zapytaniem jest blisko czterokrotna.

Czas gromadzenia statystyk

Jak długo przechowywane są statystyki zapytań? Wszystko zależy od SQL Server, jego zasobów i potrzeb. Czas pierwszego użycia planu i stworzenia statystyk przechowywany jest w kolumnie creation_time widoku sys.dm_exec_query_stats. Warto również zbadać, jaka jest częstotliwość wykonywania się badanego zapytania. Ile razy na dobę, godzinę, a może nawet na minutę. Przyjrzyjmy się jeszcze jednemu zapytaniu:

Częstotliwość wykonywania się zapytań to kolejny dość ważny współczynnik, który może decydować o wyborze zapytania do optymalizacji. Jak to zwykle bywa, decyzja i tak należy do nas.

Kategoria:Optymalizacja SQLSQL Server