Monitoring zapytań

Monitoring zapytań to śledzenie najbardziej kosztownych poleceń SQL, np. przez Query Store.

Monitoring zapytań SQL — czym jest i czemu służy

Monitoring zapytań oznacza systematyczne obserwowanie poleceń wykonywanych przez bazę danych oraz ocenianie ich wpływu na czas odpowiedzi, zużycie procesora, pamięć, operacje wejścia i wyjścia oraz współbieżność pracy. Nie chodzi wyłącznie o znalezienie jednego zapytania, które w danym momencie działa wolno. Istotne jest także rozpoznanie poleceń często uruchamianych, kosztownych w przeliczeniu na pojedyncze wykonanie, zwracających zbyt dużo danych albo powodujących blokowanie innych operacji.

W praktyce analizuje się między innymi tekst zapytania, plan wykonania, liczbę uruchomień, średni i maksymalny czas realizacji, zużycie zasobów oraz zmiany tych parametrów w czasie. Dzięki temu można odróżnić chwilowe spowolnienie od problemu powtarzalnego. Monitoring może obejmować zarówno pojedynczą bazę danych, jak i aplikację korzystającą z wielu tabel, procedur, widoków oraz mechanizmów automatyzujących obsługę danych.

Ważne jest zachowanie kontekstu. To samo polecenie SQL może działać sprawnie dla małego zakresu danych, a znacznie wolniej po rozrośnięciu tabeli. Wynik zależy również od indeksów, aktualności statystyk, rodzaju filtrów, liczby jednoczesnych użytkowników i sposobu, w jaki aplikacja przekazuje parametry. Z tego powodu monitoring zapytań jest procesem obserwacji i porównywania, a nie jednorazowym odczytem jednej wartości.

Monitoring zapytań a plan wykonania i obciążenie systemu

Podstawowym punktem odniesienia pozostaje plan wykonania. Pokazuje on, w jaki sposób silnik bazy danych zamierza odczytać i połączyć dane. W planie można znaleźć skanowanie całej tabeli, wyszukiwanie po indeksie, sortowanie, agregację, łączenie tabel oraz operacje wymagające tymczasowego obszaru roboczego. Sam fakt wystąpienia konkretnej operacji nie przesądza jeszcze o błędzie. Pełny odczyt małej tabeli może być rozsądny, natomiast ten sam sposób dostępu do bardzo dużego zbioru może powodować wyraźne opóźnienia.

Porównanie planu szacowanego z planem rzeczywistym pomaga ustalić, czy optymalizator prawidłowo przewidział liczbę przetwarzanych wierszy. Duża rozbieżność między estymacją a wykonaniem może wskazywać na nieaktualne statystyki, nietypowy rozkład wartości, nieodpowiednią selektywność indeksu albo parametry wpływające na wybór planu. Analizuje się również różnicę między czasem CPU a czasem oczekiwania. Długi czas CPU sugeruje intensywne obliczenia, a długi czas oczekiwania może wiązać się z blokadą, odczytem z dysku lub rywalizacją o zasoby.

Przydatne bywa rozdzielenie kilku perspektyw. Zapytanie wolne jednostkowo nie zawsze jest najważniejszym problemem, jeśli uruchamia się sporadycznie. Z kolei krótka operacja wykonywana tysiące razy może generować większy koszt całkowity. Warto więc porównywać średni czas, łączny czas, liczbę wykonań i odsetek nieudanych lub przerywanych operacji. Takie zestawienie pozwala ustalić kolejność działań optymalizacyjnych.

Monitoring zapytań i typowe problemy diagnostyczne

Jednym z częstych problemów jest brak odpowiedniego indeksu dla kolumn używanych w warunkach filtrowania, łączenia lub sortowania. Nie oznacza to jednak, że należy tworzyć indeks dla każdej kolumny. Nadmiar indeksów zwiększa koszt zapisu, zajmuje miejsce i może utrudnić wybór właściwej ścieżki dostępu. Analizie powinno podlegać to, czy indeks odpowiada rzeczywistym wzorcom zapytań oraz czy jego kolejność kolumn wspiera najczęściej stosowane warunki.

Spowolnienie może wynikać także z pobierania większej liczby kolumn lub wierszy, niż jest potrzebna aplikacji. Konstrukcje zwracające cały rekord utrudniają ocenę zakresu danych i mogą zwiększać transfer. Problemem bywają też funkcje stosowane na kolumnach w warunkach wyszukiwania, niejawne konwersje typów, operacje na tekstach oraz filtry, które uniemożliwiają efektywne użycie indeksu. W każdym przypadku należy sprawdzić plan, dane wejściowe oraz wpływ zmiany na pozostałe zapytania.

Osobną grupę stanowią blokady i zakleszczenia. Zapytanie może być poprawnie zoptymalizowane pod względem odczytu, a mimo to długo czekać, ponieważ inna transakcja przetrzymuje zasób. Wtedy samo skrócenie instrukcji SQL nie musi rozwiązać problemu. Sprawdza się kolejność operacji, długość transakcji, moment zatwierdzania zmian i zakres blokowanych danych. Pomocne jest rejestrowanie sesji oczekujących oraz relacji między sesją blokującą i oczekującą.

Należy zachować ostrożność przy interpretowaniu pojedynczego skoku czasu wykonania. Opóźnienie może być związane z chwilowym obciążeniem, opróżnieniem pamięci podręcznej, konserwacją danych, zmianą parametrów albo nietypowym zakresem wartości. Wiarygodny wniosek wymaga porównania wielu wykonań i uwzględnienia okresu, w którym problem występuje.

Monitoring zapytań — praktyczne wskazówki wdrożeniowe

Prace warto rozpocząć od ustalenia celu obserwacji. Inne dane będą potrzebne przy analizie wolnego ekranu aplikacji, inne przy nadmiernym zużyciu procesora, a jeszcze inne przy opóźnionych operacjach zapisu. Następnie wybiera się zakres czasu i ustala punkt odniesienia, aby można było porównać stan przed zmianą oraz po niej. Bez takiego porównania łatwo uznać zmianę za korzystną tylko dlatego, że problem chwilowo nie wystąpił.

W zależności od używanego silnika można korzystać z historii zapytań, dzienników, widoków systemowych, rozszerzeń diagnostycznych oraz narzędzi zbierających statystyki. Mechanizmy w rodzaju Query Store mogą przechowywać informacje o tekstach poleceń, planach i parametrach wydajnościowych, dzięki czemu można przeanalizować również zdarzenia, które nie są aktualnie wykonywane. Wymaga to jednak kontroli zakresu gromadzonych danych, retencji oraz wpływu rejestrowania na środowisko.

Wyniki powinny być agregowane w sposób ułatwiający decyzję. Przydatne zestawienie obejmuje identyfikator lub skrót zapytania, liczbę wykonań, średni czas, łączny czas, czas CPU, odczyty logiczne, odczyty fizyczne, ostatnie wystąpienie i używany plan. Jeśli polecenie występuje w wielu wariantach, warto rozróżnić parametry, ale jednocześnie grupować zapytania o tej samej strukturze. Pozwala to uniknąć sytuacji, w której jeden problem zostaje sztucznie podzielony na wiele drobnych wpisów.

Po znalezieniu przyczyny zmiana powinna być wprowadzana etapami. Możliwe działania obejmują korektę zapytania, zmianę indeksu, aktualizację statystyk, ograniczenie zakresu danych, zmianę sposobu stronicowania albo poprawę logiki transakcji. Każda modyfikacja może wpłynąć na inne operacje, dlatego należy sprawdzić także zapis, raportowanie i zapytania wykonywane w tym samym czasie. Warto zachować plan sprzed zmiany oraz wyniki pomiarów, aby móc cofnąć niekorzystną modyfikację lub odtworzyć tok diagnozy.

Monitoring zapytań — kiedy zgłosić problem do serwisu

Zgłoszenie do serwisu komputerowego jest zasadne, gdy spowolnienie pojawia się cyklicznie, dotyczy wielu programów, towarzyszą mu zawieszenia albo nie da się ustalić, czy źródłem jest baza danych, aplikacja, system operacyjny czy sprzęt. Przed przekazaniem urządzenia warto zanotować przybliżony moment występowania problemu, nazwę używanej aplikacji, rodzaj wykonywanej czynności oraz informację, czy opóźnienie dotyczy odczytu, zapisu, logowania lub generowania raportu. Takie dane ułatwiają połączenie obserwacji z logami i wynikami monitoringu.

W przypadku komputera wykorzystywanego do pracy z bazami danych analiza może obejmować stan dysku, pamięci, procesora, połączeń sieciowych i usług uruchomionych w tle. Należy też sprawdzić, czy problemy występują lokalnie, czy dotyczą kilku stanowisk korzystających z tego samego źródła danych. Pozwala to rozdzielić usterkę pojedynczego urządzenia od problemu konfiguracji aplikacji lub samej bazy.

Warszawskie Pogotowie Komputerowe 24/7 przyjmuje zgłoszenia związane z diagnostyką komputerów i problemami z wydajnością, a naprawy z reguły realizowane są do 3 dni roboczych. Przed rozpoczęciem prac można przygotować kopię ważnych danych, informacje o ostatnich zmianach oraz przykładowe wyniki monitoringu zapytań. Nie należy usuwać logów ani wykonywać przypadkowych zmian indeksów bez zapisania stanu wyjściowego, ponieważ może to utrudnić ustalenie pierwotnej przyczyny.