Narzędzia zewnętrzne do diagnostyki (np. Redgate

Zewnętrzne narzędzia diagnostyczne udostępniają szerszą analizę wydajności niż wbudowane widoki systemowe SQL Server.

Zakres analizy narzędzi zewnętrznych

Narzędzia zewnętrzne do diagnostyki SQL Server rozszerzają informacje dostępne w SQL Server Management Studio oraz wbudowanych widokach systemowych. Ich zadaniem jest zebranie danych o pracy instancji, baz danych, zapytań i zasobów systemowych, a następnie przedstawienie ich w formie ułatwiającej wykrycie przyczyny problemu. Przykładem jest SQL Monitor firmy Redgate, który służy do obserwowania parametrów pracy środowiska SQL Server i prezentowania zmian w czasie. Sama obecność takiego programu nie oznacza jednak automatycznej naprawy problemu. Wyniki wymagają interpretacji w odniesieniu do konfiguracji instancji, obciążenia oraz rodzaju wykonywanych operacji.

Diagnostyka może obejmować procesor, pamięć operacyjną, operacje wejścia i wyjścia, czas oczekiwania, blokady, zakleszczenia, aktywne sesje oraz zapytania zużywające znaczną część zasobów. Narzędzie może zestawiać dane z kilku poziomów: systemu operacyjnego, instancji SQL Server i konkretnej bazy danych. Dzięki temu można odróżnić sytuację, w której zapytanie wykonuje się długo z powodu nieefektywnego planu, od sytuacji, w której serwer czeka na dysk, blokadę albo przydzielenie pamięci.

Źródła danych wykorzystywane przy badaniu SQL Server

Podstawą wielu analiz są dynamiczne widoki zarządzania, czyli DMV, oraz funkcje zarządzania SQL Server. Dostarczają one informacji między innymi o aktywnych żądaniach, sesjach, oczekiwaniach, blokadach i wykorzystaniu pamięci. Do diagnostyki zapytań przydatne są również plany wykonania, ponieważ pokazują sposób dostępu do danych, użycie indeksów, operacje sortowania, agregacji i łączenia tabel. Narzędzie zewnętrzne może wizualizować te dane i ułatwiać porównanie kilku obserwacji, ale nie zmienia znaczenia samych pomiarów.

W analizie historii zapytań ważną rolę może pełnić Query Store, jeżeli został włączony i skonfigurowany dla danej bazy danych. Mechanizm ten przechowuje informacje o zapytaniach, planach oraz statystykach ich wykonania, co pozwala porównywać wcześniejsze i późniejsze zachowanie tego samego zapytania. Uzupełnieniem mogą być zdarzenia rejestrowane przez Extended Events, na przykład informacje o zakleszczeniach lub wybranych błędach. Przy interpretacji trzeba uwzględnić okres zbierania danych, obciążenie w tym czasie i ewentualne ograniczenia retencji. Pojedynczy pomiar nie powinien być traktowany jako dowód stałej przyczyny problemu.

Wykrywanie blokad, oczekiwań i wolnych zapytań

Jednym z praktycznych zastosowań zewnętrznego monitora jest obserwowanie sesji, które długo oczekują na wykonanie. W SQL Server zapytanie może być aktywne, lecz nie zużywać intensywnie procesora, ponieważ czeka na zwolnienie blokady utrzymywanej przez inną transakcję. Może także oczekiwać na zakończenie operacji wejścia i wyjścia albo na dostęp do określonego zasobu wewnętrznego. Analiza typów oczekiwań, czasu ich trwania i powiązanych sesji pomaga ustalić, czy problem dotyczy samego zapytania, transakcji, indeksu, dysku czy konkurencji między operacjami.

W przypadku wolnych zapytań warto porównywać czas trwania, liczbę odczytanych stron, zużycie procesora oraz liczbę wykonań. Zapytanie wykonywane przez kilka sekund raz na godzinę może mieć mniejsze znaczenie niż krótkie zapytanie uruchamiane tysiące razy na minutę. Plan wykonania może wskazać między innymi skanowanie dużej tabeli, nieoczekiwany sposób łączenia danych albo znaczną różnicę między szacowaną a rzeczywistą liczbą wierszy. Taka różnica może prowadzić do wyboru niekorzystnego planu, ale jej interpretacja wymaga sprawdzenia statystyk, parametrów zapytania, indeksów i aktualnego rozkładu danych. Zewnętrzne narzędzie porządkuje te informacje i ułatwia korelację zdarzeń, lecz nie zastępuje analizy technicznej.

Bezpieczne wykorzystanie wyników diagnostyki

Przed rozpoczęciem analizy należy ustalić, jaki problem ma zostać wyjaśniony: nagły wzrost czasu odpowiedzi, przeciążenie procesora, zapełnienie przestrzeni dyskowej, blokowanie transakcji czy niestabilność planu wykonania. Następnie trzeba określić przedział czasu i porównać go z okresem, w którym użytkownicy rzeczywiście odczuwali spowolnienie. Pomiar wykonany po ustąpieniu objawów może nie pokazać przyczyny. Warto także zachować identyfikatory zapytań, informacje o planach, typy oczekiwań i dane o obciążeniu, aby możliwe było porównanie z kolejnymi wystąpieniami problemu.

Wynik diagnostyki powinien prowadzić do sprawdzalnej hipotezy, na przykład: „czas odpowiedzi wzrósł, ponieważ zapytanie czekało na blokadę utrzymywaną przez długą transakcję” albo „plan wykonywał skanowanie, ponieważ estymacja liczby wierszy znacznie odbiegała od rzeczywistości”. Dopiero później można rozważyć zmianę zapytania, indeksu, statystyk lub konfiguracji. Każdą zmianę należy ocenić na podstawie pomiaru przed i po jej wprowadzeniu. Przy diagnostyce komputera lub oprogramowania łączącego się z SQL Server ważne jest również ustalenie, czy opóźnienie powstaje w aplikacji, sieci, sterowniku czy samym silniku bazy danych. Pozwala to nie mylić objawu widocznego na ekranie użytkownika z rzeczywistym miejscem powstawania problemu.