Narzędzie Performance Dashboard Reports w użyciu, cz. II
Szczegółowy opis dostępnych raportów w narzędziu SQL Server Performance Dashboard
Zawartość strony
Informacja o wykorzystaniu zasobów systemowych
Jak już wcześniej wspomniałem, główne okno narzędzia SQL Server Performance Dashboard Reports podzielone jest na pięć sekcji (obszarów). Pierwszy obszar – System CPU Utilization – czyli informacja o wykorzystaniu zasobów systemowych – jest raportem graficznym, dostarczającym błyskawicznie informacji o wykorzystaniu zasobów maszyny w ciągu ostatniego kwadransa. Można w łatwy sposób określić w jakim stopniu proces SQL Server wykorzystuje zasoby systemowe w kontekście całej maszyny. Dane zbierane są w interwałach minutowych, więc na ekranie zostanie zaprezentowanych maksymalnie 15 słupków, z których każdy jest podzielonych na dwie grupy (patrz rysunek 1):
| • | pomarańczową – określającą wykorzystanie zasobów maszyny przez inne procesy niż SQL Server |
| • | niebieską – wskazującą na wykorzystanie zasobów systemowych przez proces SQL Server |
Rys. 1. Raport z wykorzystania zasobów systemowych - System CPU utilization.
Uwaga: Po restarcie serwera musi upłynąć 15 minut, aby wszystkie dane były dostępne, a pierwsze informacje pojawią się dopiero po upływie minuty. Dane sprzed restartu nie będą dostępne! |
Dane prezentowane na wykresie pochodzą z dynamicznego widoku zarządczego sys.dm_os_ring_buffers.
Chciałbym również zwrócić uwagę, iż według firmy Microsoft, prezentowane dane to jedynie aproksymacja, ale z punktu widzenia administratora baz danych jest ona w zupełności wystarczająca. Zaprezentowany na rys. 1 wykres kryje jeszcze jedną tajemnicę, a w zasadzie odkrywa sedno raportów, tzw. metodę „drill down”, którą można interpretować jako „od ogółu do szczegółu”. Wystarczy bowiem kliknąć w niebieską część któregokolwiek słupka, aby otrzymać listę kwerend, które najbardziej obciążają SQL Server (jak pokazano na rys. 2 poniżej)
Rys. 2 Lista kwerend najbardziej obciążających serwer.
Dla każdego zapytania znajdującego się na liście wyświetlane są następujące informacje:
| • | ilość wykonań | ||||||||
| • | ilość wygenerowanych planów dla kwerendy | ||||||||
| • | datę ostatniego planu zapytania | ||||||||
| • | datę ostatniego uruchomienia zapytania | ||||||||
| • | statystyczne informacje – wartość maksymalną, minimalną, średnią oraz sumę czasów dla:
|
Dla każdej kwerendy na liście, używając omówionej wcześniej metody „drill down”, można wyświetlić dokładniejsze informacje, takie jak:
| • | Tekst kwerendy |
| • | Zapisany plan kwerendy (podobny wynik można uzyskać wykonując komendę SHOWPLAN_ALL) |
| • | Zapisany plan w formacie XML (podobny wynik można uzyskać wykonując komendę SHOWPLAN XML) |
| • | Parametry raportu |
Jeżeli kwerenda składa się z wielu zapytań, to zostaną wyświetlone informacje dla wszystkich zapytań wchodzących w skład kwerendy. Dodatkowo, jeżeli istnieje rekomendacja odnośnie zmiany bądź konieczności dodania nowego indeksu, to także zostanie ona wyświetlona. Dużym ułatwieniem jest także kolorowanie w odpowiednich miejscach planu tekstowego kwerendy, a ma na celu szybkie zwrócenie uwagi, że dla danego obiektu brak jest aktualnych statystyk bądź indeksu (rysunek 3).
Rys. 3. Raport z planu kwerendy (dokumentacja Performance Dashboard Reports).
Oczekiwania w serwerze baz danych
Sekcja Current Waiting Requests dostarcza informacji o stanach oczekiwania w serwerze baz danych. SQL Server definiuje wiele grup (kategorii) stanów oczekiwania, które dostępne są poprzez dynamiczne widoki lub funkcje zarządcze. W omawianej sekcji może, ale nie musi, zostać zaprezentowany wykres obrazujący stany oczekiwania serwera. Wykres zostanie pokazany w sytuacji kiedy w systemie będą nieobsłużone żądania. Oś pionowa wykresu – inaczej niż w innych przypadkach, gdzie rozpatrywane są zależności czasowe – wyskalowana jest w jednostce czasu (ms). Na osi poziomej zawarte są informacje kategoriach stanów oczekiwań w systemie. W przypadku, gdy narzędzie Performance Dashboard Reports stwierdzi, iż aktualne wartości stanów oczekiwania są znaczące z punktu widzenia serwera (tzn. mogą sugerować problemy z wydajnością), to wyświetli odpowiedni komunikat tuż nad wykresem. Na rysunku 4 pokazano przykładowy wykres obrazujący stany oczekiwania w systemie. Jak widać, oczekiwanie należało do kategorii Other i trwało ponad 80 ms. Czas trwania oczekiwania z punktu widzenia Performance Dashboard Reports był zbyt długi i użytkownik został o tym poinformowany za pomocą komunikatu.
Rys. 4. Raport z aktualnych stanów oczekiwania na serwerze.
Tak jak w przypadku poprzednio omówionych raportów, narzędzie udostępnia administratorowi więcej szczegółów („drill down”). Po wybraniu kategorii na wykresie Current Waiting Requests można otrzymać dokładne informacje czym spowodowane są oczekiwania.
Rys. 5. Szczegółowy raport ze stanów oczekiwania na serwerze z podziałem na kategorie oczekiwań.
Na raporcie szczegółowym General Waits (patrz rys. 5) najpierw wyświetlane są bardziej ogólne informacje o kategoriach oczekiwań, liczbie procesów (# of waiters), sumarycznym czasie trwania oraz procentowym udziale danej kategorii oczekiwań w stosunku do wszystkich występujących w systemie. Każda kategoria może zostać rozwinięta i administrator uzyska wtedy dokładniejszą wiedzę o poszczególnych procesach powodujących oczekiwania. Dla każdego typu oczekiwania pokazywane są następujące informacje:
1. | Identyfikator sesji lub żądania |
2. | Typ oczekiwania |
3. | Czas oczekiwania |
4. | dTekst zapytania |
Dane posortowane są według czasu oczekiwania malejąco, łatwo więc jest zidentyfikować najbardziej czasochłonne procesy z każdej kategorii. Można również kliknąć w pole Query Text i otrzymać informacje jak pokazano na rysunku 13 powyżej. Administrator baz danych może również uzyskać kompleksowe informacje na temat identyfikatora sesji użytkownika generującego oczekiwania w systemie. Informacje te są dostępne po kliknięciu na identyfikator sesji w kolumnie SessionID:RequestID, a ich dokładny opis zostanie przedstawiony w dalszej części artykułu.
Dla trzech kategorii oczekiwań, o ile występują takie w systemie, dostępne są dodatkowe raporty:
1. | Latch waits |
2. | Buffer latch |
3. | Buffer IO |
Dla każdej wybranej kategorii prezentowane jest 20 najistotniejszych zapytań bądź sesji generujących najdłuższe stany oczekiwania na serwerze.
Bieżące aktywności
Dane prezentowane w sekcji Current Activity (bieżąca aktywność) wydają się być na pierwszy rzut oka oczywiste, ale ich interpretacja wymaga kilku zdań wyjaśnienia. Omawiana sekcja jest podzielona na dwie grupy – sesje i żądania użytkowników. Liczby zawarte w polu Count dla obydwu grup oznaczają ilość żądań i aktywnych sesji użytkowników liczone od ostatniego odświeżenia raportu. Z kolei liczniki odnoszące się do czasu oraz współczynnika trafień do pamięci podręcznej są kumulatywne, tzn. pokazują sumaryczne wartości od czasu ostatniego uruchomienia serwera (rys. 6).
Rys. 6. Raport ‘Current activity’ informujący o bieżącej aktywności serwera.
Po kliknięciu w nagłówek User Requests administrator otrzymuje szczegółowe informacje o ostatnich żądaniach użytkowników. Dla każdego żądania dostępne są m.in. takie jego parametry jak:
1. | Identyfikator sesji /żądania |
2. | Tekst zapytania |
3. | Status żądania |
4. | Typ, czas oczekiwania |
5. | Ilość fizycznych i logicznych zapisów/odczytów |
6. | Poziom izolacji transakcji |
Omawiany raport (patrz rys. 7) w całości bazuje na dynamicznym widoku zarządczym sys.dm_exec_requests. Tak jak we wcześniejszych omawianych przypadkach użytkownik może podejrzeć plan zapytania i otrzymać wyczerpujące informacje na jego temat, co zostało już omówione wcześniej w niniejszym artykule.
Rys. 7. Aktualne żądania na serwerze baz danych.
Drugą możliwością oferowaną przez sekcję Current Activity jest grupa informująca o sesjach użytkowników. Po wybraniu odpowiedniego nagłówka kolumny otrzymujemy raport podobny do zaprezentowanego na rysunku 8:
Rys. 8. Raport - informacje o sesjach użytkowników.
Jeden rzut oka wystarczy, aby administrator baz danych stwierdził, iż podobne informacje można uzyskać w SQL Server Management Studio po wybraniu opcji Management/Activity Monitor (patrz rysunek 9).
Rys. 9. Informacje o sesjach użytkowników dostarczane przez Monitor Aktywności.
Główna różnica polega na tym, iż Monitor Aktywności w SSMS dostarcza zdecydowanie więcej informacji. Raport Sessions Overview może posłużyć do określenia jacy użytkownicy są podłączeni do serwera a dane do jego wypełnienia pochodzą z widoku z dynamicznego widoku zarządczego sys.dm_exec_sessions.
Dla każdej sesji można uzyskać podstawowe informacje, takie jak nazwę użytkownika, ilość odczytów i zapisów danych wykonanych w ramach istniejącej sesji, odczytać informację kiedy rozpoczęto i ukończono ostatnią kwerendę oraz zestaw opcji SET obowiązujący w ramach tej sesji.
Raport bazuje na dwóch dynamicznych widokach zarządczych:
1. | sys.dm_exec_sessions |
2. | sys.dm_exec_sessions |
W przypadku, gdy w ramach sesji istnieją żądania będące w trakcie wykonywania, to można podejrzeć ich detale (plan, parametry) w sposób omówiony wcześniej w artykule. W przypadku, gdy nie ma aktywnych żądań, ale istnieje ważny identyfikator sql_handle opisujący ostatnie żądanie, to informacja o tym żądaniu zostanie także wyświetlona. Jest to niezwykle ważne przy analizie scenariuszów związanych oczekiwaniem serwera baz danych na informacje pochodzące z aplikacji klienckich, powiązanych z zatwierdzeniem lub wycofaniem transakcji. Rysunek 10 zawiera przykładowy raport Sessions Overview.
Rys. 10. Raport z aktywnej sesji na serwerze baz danych.
Inne informacje
Następna sekcja Performance Dashboard Reports traktuje o innych informacjach (stąd jej tytuł – Miscellaneous Information), które mogą być przydatne dla każdego administratora baz danych. Obszar ten zawiera trzy odnośniki do szczegółowych raportów:
| • | Aktywne ślady (Active traces) |
| • | Bazy danych (Databases) |
| • | Brakujące indeksy (Missing indexes) |
Przy każdym odnośniku można odczytać liczbę obiektów, które będą wyświetlone na raporcie szczegółowym (rysunek 11).
Rys. 11. Raport Miscellaneous Information.
Jeżeli dla danego raportu liczba aktywnych obiektów będzie zerowa, to odnośnik do raportu jest niewidoczny.
Pierwszy raport – Active Traces – pokazuje wszystkie aktywne ślady uruchomione w monitorowanej instancji SQL Server. Nawet jeżeli użytkownik nie włączył śledzenia za pomocą narzędzia SQL Server Profiler czy procedury składowanej sp_trace_create, to jeden ślad działa zawsze w tle. Należy pamiętać, iż zbyt duża ilość aktywnych śladów powoduje pogorszenie wydajności serwera baz danych, co ma istotne znaczenie zwłaszcza na serwerze produkcyjnym, gdzie zaleca się zapis aktywnego śladu bezpośrednio na dysk. Przykład raportu Active Traces znajduje się na rys. 12 poniżej.
Rys. 12 Raport z aktywnych śladów (active traces) w SQL Server.
Informacje pokazane na rys. 12 są oczywistymi dla każdego administratora baz danych i informują o:
| • | Identyfikatorze śladu (Trace ID) |
| • | Statusie |
| • | Miejscu składowania pliku ze śladem |
| • | Dacie rozpoczęcia / zakończenia śledzenia |
| • | Ilości zapisanych zdarzeń (Events Traced) |
Drugi raport w grupie Miscellaneous Information dotyczy baz danych znajdujących się w monitorowanej instancji SQL Server. Omawiany raport pozwala szybko uzyskać informacje o podstawowych parametrach baz danych, takich jak poziom kompatybilności, zastosowany model odzyskiwania danych, rozmiar pliku danych oraz pliku dziennika transakcji oraz procentowym wypełnieniu pliku dziennika transakcji (patrz rys. 13).
Rys. 13 Raport - informacja o bazach danych w analizowanej instancji SQL Server.
Brakujące indeksy
Ostatnim, ale być może najciekawszym raportem w omawianej grupie jest Missing Indexes Report – raport informujący o brakujących indeksach. Aparat SQL Server 2005 optymalizując zapytania potrafi wskazać brakujące indeksy, które poprawiłyby wykonywanie analizowanych kwerend. Rekomendacje proponowane przez SQL Server można odnaleźć w dynamicznych widokach zarządczych sys.dm_db_missing*. SQL Server jest w stanie zapamiętać aż do 500 najistotniejszych rekomendacji. Należy pamiętać, iż wykonanie operacji DDL na tablicy (ALTER TABLE, CREATE INDEX) oraz usuwanie czy ustawianie bazy danych w tryb offline spowoduje usunięcie rekomendacji odnoszących się do tej bazy danych (czy tablicy). Niestety sposób znajdowania brakujących indeksów nie jest tak dokładny, jak można to osiągnąć za pomocą Database Engine Tuning Advisor (DTA). Dlatego zawsze przed utworzeniem nowego indeksu bazującego na rekomendacjach omawianego raportu należy skonfrontować go z wynikami proponowanymi przez DTA. Raport przedstawiony na rys. 14 pokazuje listę rekomendowanych brakujących indeksów. Za pomocą menu kontekstowego można listę wydrukować bądź wyeksportować do pliku *.xls lub *.pdf.
Rys. 14 Raport zawierający rekomendacje odnośnie indeksów.
Informacje historyczne
Ostatnia sekcja Performance Dashboard Reports zawiera historyczne informacje o oczekiwaniach, statystykach I/O oraz kwerendach, które obciążają w znacznym stopniu serwer baz danych. Jak wspomniałem wcześniej, dane historyczne gromadzone są przez niektóre DMV, nie gromadzi ich natomiast w żaden sposób omawiane narzędzie.
Pierwszy z raportów – Waits – pokazuje historyczne informacje o stanach oczekiwania w SQL Server. W każdym momencie, w którym wątek robotnika (worker thread) oczekuje na zasoby, ustawia odpowiedni typ oczekiwania. Informacje te są dostępne po wykonaniu kwerendy na dwóch dynamicznych widokach zarządczych:
1. | sys.dm_exec_requests |
2. | sys.dm_os_waiting_tasks |
SQL Server przechowuje m.in. informacje ile razy wystąpił dany typ oczekiwania oraz jaki był całkowity czas poświęcony na konkretny stan oczekiwania. Te dane są przechowywane dla każdej instancji SQL Server a liczniki są inkrementowane począwszy od uruchomienia serwera (lub ich wyczyszczenia za pomocą komendy DBCC SQLPERF) i ich wartości można uzyskać wykonując zapytanie na widoku sys.dm_os_wait_stats. Informacja o zasobach, na które trzeba oczekiwać jest niezwykle istotna, ponieważ pozwala określić „wąskie gardło” dla danej instancji SQL Server. Więcej informacji na ten temat można znaleźć tutaj:
SQL Server 2005 definiuje ponad 200 typów stanów oczekiwań, więc ich śledzenie i wyciąganie na tej podstawie wniosków byłoby zadaniem niezwykle trudnym. Performance Dashboard używa więc pojęcia kategorii stanów oczekiwań, które pozwalają grupować poszczególne stany oczekiwań, przez co możliwa jest łatwiejsza analiza danych. Na rysunku 15 zawarto wydruk przykładowego raportu o stanach oczekiwania w serwerze baz danych. Można zauważyć, iż zwłaszcza dwie kategorie będą pośród tych, dla których sumaryczny czas oczekiwania jest największy: Sleep oraz Other. Kategoria Sleep składa się z zadań wykonywanych w tle i zazwyczaj na jej podstawie nie można wnioskować o występujących problemach z wydajnością serwera. Kategoria Other zawiera wszystkie stany oczekiwania, które w jasny sposób nie mogły zostać zaklasyfikowane do żadnej z istniejących kategorii. Najczęściej spotykanym stanem oczekiwania w tej grupie jest BROKER_TASK_STOP, które jednak nie wskazuje na problemy z wydajnością.
Rys. 15 Raport z historycznych stanów oczekiwania na serwerze.
Jak widać na rys. 15 powyżej, dla każdego stanu oczekiwania dostępne są szczegółowe informacje takie jak całkowita ilość wystąpień, całkowity czas oczekiwania (ms), maksymalny oraz średni czas oczekiwania (ms) oraz procentowo – jaki procent zajmuje każdy stan oczekiwania w ramach kategorii, do której należy.
Drugim raportem, który dostarcza informacji historycznych jest Historical IO Report (historyczne dane o operacjach wejścia – wyjścia). Za pomocą tego raportu można określić, która z baz danych (dotyczy jednej instancji) ma największy udział w operacjach wejścia – wyjścia (rozumiane także jako odczyt – zapis danych na – z dysku). Pierwsza tablica (rys. 16) prezentuje informacje ile operacji I/O przypada na każdą bazę danych, ile było procesów odczytu i zapisu danych. Dodatkowo, informacje te rozbite są na poszczególne pliki danych oraz dziennika transakcji, które wchodzą w skład każdej bazy danych. Tak jak dla poprzedniego raportu – prezentowane wyniki są kumulatywne, czyli zawierają sumy wskaźników liczone od ostatniego uruchomienia serwera baz danych lub przywrócenia bazy danych w tryb online. Dane prezentowane na tym raporcie pochodzą z widoku sys.dm_os_virtual_file_stats.
Rys. 16. Raport z historycznej aktywności I/O.
Druga tabela prezentuje dla każdej bazy danych 20 obiektów odpowiedzialnych za najdłuższe czasy operacji I/O. Tablica ta jest wypełniana danymi pochodzącymi z dynamicznej funkcji zarządczej sys.dm_db_index_operationa_stats, która przekazuje informacje o wewnętrznych (umieszczonych w pamięci) strukturach danych. Nie jest możliwe precyzyjne ustalenie czasu, w którym struktura została załadowana do pamięci, a więc w konsekwencji nie jest również możliwe ustalenie początkowego momentu, od którego wykonywane są statystyki związane z operacjami I/O. Można natomiast odczytać liczbę przeszukiwań i skanowań tablic. Duża wartość fizycznych operacji wejścia – wyjścia na tablicy wiąże się z zazwyczaj brakiem odpowiedniego indeksu (lub indeksów), dlatego dla każdej tablicy z raportu zaprezentowanego na rys. 17 wyświetlana jest informacja, czy znaleziono rekomendacje dotyczące brakujących indeksów. Przykład rekomendacji pokazany na rys. 18. Raport dotyczący brakujących indeksów został omówiony wcześniej.
Rys. 17. Raport prezentujący 20 tablic w każdej bazie danych, dla których znaleziono największe czasy oczekiwania związane z operacjami I/O.
Rys. 18. Rekomendowane indeksy pogrupowane dla każdej tablicy.
Koszt zapytań
Ostatnia grupa raportów dotyczy zapytań, które w zależności od wybranego kontekstu, powodują największy koszt. Performance Dashboard Reports oferują w sumie 6 kontekstów, pod kątem których można analizować kwerendy:
| • | by CPU – zużycia zasobów |
| • | by duration – czasu trwania |
| • | by logical reads – odczytów logicznych |
| • | by logical Wites – zapisów logicznych |
| • | by physical reads – odczytów fizycznych |
| • | by clr time – czasu wykonania kodu .NET |
Wszystkie informacje służące do generowania tych raportów pochodzą z dynamicznego widoku zarządczego sys.dm_exec_query_stats. Należy zwrócić uwagę na fakt iż SQL Server nie przechowuje planów zapytań dla komend innych niż SELECT, INSERT, DELETE i UPDATE. Inne komendy DML, takie jak CREATE, BACKUP lub EXEC, konsumują wiele zasobów systemowych, ale nie są uwzględniane na raportach. Dodatkowo, jeżeli koszt wykonania zapytania jest bardzo niski to nie ma gwarancji, iż jego plan zostanie przechowany. Innym problemem mogą być zapytania ‘ad hoc’ niewiele się różniące od siebie, ale skutkujące generowaniem różnych (choć bardzo podobnych) planów. Zapytania będące z punktu widzenia aplikacji takie same nie są agregowane w dynamicznym widoku zarządczym sys.dm_exec_query_stats do jednego planu zapytania. W przypadku, gdy aplikacje używają parametryzowanych zapytań lub procedur składowanych, to w omawianym raporcie można znaleźć bardzo wiarygodne informacje na temat najbardziej kosztownych kwerend. Ostatnią istotną rzeczą wartą zapamiętania jest fakt, iż zapytanie nie jest dostępne poprzez dynamiczny widok zarządczy sys.dm_exec_query_stats dopóki jego wykonanie nie zakończy się sukcesem.
Każdy z raportów w tej sekcji składa się z 20 najbardziej obciążających kwerend, w zależności od wybranego atrybutu, a to z kolei pozwala na szybką wizualną interpretację tych zapytań. Zazwyczaj największy wpływ na wydajność systemu ma kilka kwerend, więc ich identyfikacja (łatwa za pomocą Performance Dashboard Reports) oraz ewentualna poprawa ma duży wpływ na poprawę wydajności serwera. Na rys. 19 zaprezentowano jeden z 6 dostępnych raportów w tej sekcji. Dla każdej kwerendy administrator baz danych otrzymuje także szczegółowe informacje, takie jak: ile razy dana kwerenda została uruchomiona, kiedy po raz ostatni została uruchomiona, ile razy SQL Server wygenerował dla niej plan, kiedy po raz ostatni plan został zapisany w pamięci procedur oraz informacje dotyczące wartości średnich, maksymalnych oraz minimalnych odnoszących się m. in. do zużycia zasobów CPU, logicznych oraz fizycznych zapisów i odczytów na dysku.
Rys. 19. Raport prezentujący 20 najbardziej ‘kosztownych’ kwerend w wybranym kontekście – zużycia zasobów procesora.
Podsumowanie
Performance Dashboard Reports są narzędziem w znaczny sposób ułatwiającym pracę z serwerem baz danych. W klarowny sposób dostarczają wymaganych informacji potrzebnych każdemu administratorowi baz danych. Mają szereg innych zalet, do których zaliczyłbym nie tylko możliwość łatwego rozszerzania funkcjonalności, ale również minimalny wpływ na wydajność serwera baz danych. Można bez najmniejszych obaw zainstalować raporty na wielu serwerach produkcyjnych i nie martwić się o gwałtowny spadek wydajności serwera przy pobieraniu danych do raportów.
Omawiane narzędzie, jak każda aplikacja, posiada pewną liczbę ograniczeń, które tylko w znikomym stopniu wpływają na jej użytkowanie i z całą pewnością nie przesłaniają jej zalet. Aplikacja na pewno nie wyczerpuje wszystkich potrzeb, które posiada administrator baz danych w zakresie monitorowania serwera, ale jest dobrym, pierwszym krokiem w kierunku dostarczenia wartościowych informacji.
Litertaura
| • | |
| • | Dokumentacja Performance Dashboard Reports |
| • | http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx |
| • |
Przeczytaj pozostałe części tego artykułu
| • |
 | Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) |