Monitoring blokad z XE

Monitoring blokad z Extended Events to lekki sposób rejestrowania zdarzeń blokowania bez obciążenia typowego dla Profilera.

Rola Extended Events w obserwowaniu blokad

Monitoring blokad z wykorzystaniem Extended Events, czyli rozszerzonych zdarzeń SQL Server, służy do rejestrowania sytuacji, w których jedna sesja musi czekać na zasób zajęty przez inną sesję. Najczęściej chodzi o blokady związane z wierszami, stronami, obiektami lub innymi zasobami używanymi podczas wykonywania instrukcji Transact-SQL. W typowym łańcuchu blokowania występuje sesja blokująca, która posiada potrzebną blokadę, oraz jedna albo więcej sesji oczekujących na jej zwolnienie. Jeżeli oczekujących jest kilka, pojedyncza transakcja może powodować opóźnienia w wielu połączeniach.

Podstawowym zdarzeniem przeznaczonym do wykrywania dłuższego blokowania jest sqlserver.blocked_process_report. SQL Server generuje raport tego typu dopiero po przekroczeniu skonfigurowanego progu blokady, dlatego mechanizm nie rejestruje każdej krótkiej kolizji. Próg ustawia się na poziomie instancji za pomocą opcji blocked process threshold (s) konfigurowanej przez sp_configure. Wartość zero oznacza wyłączenie raportowania blokad przez ten mechanizm. Raport zawiera między innymi informacje o sesji oczekującej, instrukcji, zasobie oraz sesji, która utrzymuje konfliktującą blokadę.

Konfiguracja sesji i dobór rejestrowanych zdarzeń

Sesja Extended Events powinna określać zdarzenie, interesujące pola, opcjonalne predykaty oraz miejsce zapisu. Do trwałego monitoringu najczęściej stosuje się cel event_file, który zapisuje dane do pliku odczytywanego później przez narzędzia SQL Server. Cel ring_buffer przechowuje zdarzenia w pamięci i jest przydatny podczas krótkiej obserwacji, ale po restarcie instancji lub przepełnieniu bufora starsze wpisy mogą zostać utracone. Należy również uwzględnić rozmiar plików, liczbę plików rotowanych oraz uprawnienia konta usługi SQL Server do katalogu docelowego.

W sesji można rejestrować także sqlserver.login, sqlserver.logout lub zdarzenia związane z wykonywaniem instrukcji, lecz dodawanie wielu zdarzeń bez celu diagnostycznego zwiększa ilość danych. Do analizy zakleszczeń używa się między innymi sqlserver.xml_deadlock_report. Zakleszczenie jest innym zjawiskiem niż zwykłe blokowanie: w jego przypadku sesje wzajemnie oczekują na zasoby, a aparat SQL Server wybiera jedną transakcję jako ofiarę zakleszczenia. Raport blokad powinien więc być interpretowany oddzielnie od raportów deadlocków.

Przykładowa konfiguracja wymaga wcześniejszego ustawienia progu, utworzenia sesji oraz jej uruchomienia. Zmiana progu może wpływać na zachowanie całej instancji, dlatego powinna odpowiadać celowi obserwacji. Bardzo niski próg może ujawnić krótkie, normalne oczekiwania, ale jednocześnie wygenerować dużo raportów. Wyższy próg ogranicza szum i skupia uwagę na blokadach odczuwalnych przez aplikację. Samo utworzenie sesji nie oznacza jeszcze rozpoczęcia zbierania danych; sesję trzeba uruchomić, a po zakończeniu diagnostyki można ją zatrzymać.

Odczyt raportu blokady i odtworzenie łańcucha oczekiwania

Raport blocked_process_report jest dokumentem XML zawierającym dane o procesie zablokowanym. Podczas analizy należy sprawdzić identyfikator sesji, identyfikator transakcji, czas oczekiwania, typ zasobu i instrukcję wykonywaną przez sesję. Istotne są również informacje o bazie danych, obiekcie, trybie blokady oraz poziomie izolacji transakcji. Sam identyfikator sesji nie wyjaśnia przyczyny problemu, ponieważ ta sama sesja może wykonywać różne instrukcje w kolejnych chwilach. Warto porównać raport z innymi zdarzeniami z tego samego przedziału czasu.

Najważniejszym krokiem jest ustalenie, która sesja utrzymuje blokadę oraz dlaczego nie zwolniła zasobu. Przyczyną może być długa transakcja, oczekiwanie na operację wejścia-wyjścia, niezamknięta transakcja aplikacyjna, nieefektywne zapytanie albo zbyt szeroki zakres blokady. Raport blokady nie powinien być automatycznie traktowany jako dowód błędu w samym silniku. Często wskazuje jedynie skutek decyzji aplikacji, na przykład rozpoczęcia transakcji przed wykonaniem wielu niezależnych operacji i zakończenia jej dopiero po długim czasie.

Podczas korelacji danych trzeba zachować zgodność czasu i identyfikować wpisy należące do tej samej sesji. Przydatne są nazwa aplikacji, nazwa hosta, login, identyfikator sesji oraz identyfikator żądania. Jeżeli monitoring obejmuje także plan wykonania lub statystyki oczekiwań, można sprawdzić, czy blokada wynika z braku odpowiedniego indeksu, skanowania dużego zakresu danych albo nieoczekiwanej kolejności operacji. Extended Events rejestruje zdarzenia, ale nie zastępuje analizy schematu, transakcji i kodu aplikacji.

Wykorzystanie danych z XE podczas diagnostyki

Wyniki monitoringu warto analizować jako serię powtarzalnych zdarzeń, a nie jako pojedynczy wpis. Jednorazowa blokada trwająca krótko może być normalnym elementem współbieżnego działania. Powtarzające się raporty dotyczące tego samego zasobu, tej samej sesji blokującej lub tej samej instrukcji wskazują natomiast na problem wymagający dalszego sprawdzenia. Należy porównać czas występowania blokad z harmonogramem zadań, okresami zwiększonego ruchu i działaniem procesów wykonujących modyfikacje danych.

Extended Events obciąża środowisko w sposób kontrolowany, ponieważ sesja może rejestrować tylko wybrane zdarzenia i pola. Nie oznacza to jednak, że każda konfiguracja jest bezkosztowa. Zbyt szeroki zakres zdarzeń, rejestrowanie dużej liczby instrukcji lub intensywne zapisywanie do celu może zwiększyć zużycie procesora, pamięci i operacji wejścia-wyjścia. Z tego powodu sesję należy projektować pod konkretne pytanie diagnostyczne, ograniczać predykatami oraz obserwować jej wpływ na instancję SQL Server.

Po zebraniu materiału można ustalić, czy problem wymaga zmiany zapytania, indeksu, zakresu transakcji, kolejności operacji albo sposobu obsługi połączenia. W niektórych przypadkach właściwym działaniem jest skrócenie transakcji, w innych zmiana strategii dostępu do danych lub korekta kodu, który pozostawia transakcję otwartą. Analiza plików Extended Events powinna być wykonywana na kopii danych diagnostycznych albo w kontrolowanym narzędziu odczytującym pliki XE, z zachowaniem oryginalnych plików do dalszej weryfikacji.