Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server – sesja system_health

Jednym z tematów często pojawiających się na konferencji SQLDay 2014 (na sesjach i w kuluarach), była systemowa sesja Extended Events o nazwie system_health. Wspomniał o niej m.in. Bob Ward przy okazji prezentacji na temat stanów oczekiwania. Pomyślałem, że dobrze byłoby napisać parę słów o tym często niedocenianym źródle informacji dla DBA.

O sesji słów kilka

Gdzie można znaleźć sesję system_health? Oczywiście najprościej jest odszukać węzeł Management – Extended Events w oknie Object Explorer aplikacji SQL Server Management Studio.

image

 

 

 

 

 

 

 

Co takiego śledzi ta sesja? Wystarczy ją zeskryptować, by się tego dowiedzieć. Ale można też odpytać metadane:

SELECT name, object_type, description 
FROM sys.dm_xe_objects 
WHERE name IN (
  SELECT name FROM sys.server_event_session_events 
  WHERE event_session_id = (
    SELECT event_session_id 
    FROM sys.server_event_sessions 
    WHERE name = 'system_health'
  )
)
ORDER BY name;
GO

Wynik:

image

Siedemnaście zdarzeń, niektóre od razu rzucają się w oczy. Mi przupadły do gustu zwłaszcza informacje o stanach oczekiwań i raporty o zakleszczeniach – dlatego o tym będzie w dalszej części tego wpisu.

Sesja system_health zapisuje dane do dwóch magazynów – do plików (w katalogu logów instancji SQL Server) oraz do bufora (ring buffer). Zaletą plików jest większa trwałość informacji (po jakimś czasie, gdy zdarzeń będzie wystarczająco wiele, zawartość plików jest nadpisywana, ale i tak informacje w plikach są w stanie przetrwać np. restart usługi SQL Server).

Zakleszczenia

Jeśli szukasz informacji o zakleszczeniach, jakie ostatnio miały miejsce na serwerze, możesz spróbować poszukać raportów o nich w plikach nagranych przez sesję system_health:

SELECT
  events.event.value('@timestamp', 'datetime') AS timestamp,
  events.event.query('(/event/data/value/deadlock)[1]') AS deadlock_report
FROM (
  SELECT convert(xml, event_data) AS event_data
  FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
  WHERE object_name = 'xml_deadlock_report'
) AS system_health
CROSS APPLY system_health.event_data.nodes('/event') AS events(event)
ORDER BY timestamp DESC;
GO

Przykładowy wynik:

image

I teraz, jeśli masz wprawę w czytaniu raportów o zakleszczeniach w formacie XML, kliknij na odnośniku, by otworzyć raport. A jeśli wolisz postać graficzną, otwórz raport XML i zapisz go do pliku z rozszerzeniem .xdl. Po otwarciu pliku w SQL Server Management Studio zobaczysz znajomo wyglądający graf (analogia do nagranego zdarzenia deadlock graph w SQL Server Profiler).

image

Statystyki stanów oczekiwania

Jedną z powszechnie stosowanych metod wykrywania przyczyn problemów wydajnościowych w SQL Server jest wykorzystanie statystyk stanów oczekiwania. Każda chwila oczekiwania instancji jest kategoryzowana i rejestrowana w określonej kategorii stanu oczekiwania. Swego czasu ukazał się nawet dokument opisujący najistotniejsze stany oczekiwania (przydałoby się być może odświeżyć ten dokument, jako że ukazał się on jeszcze za czasów SQL Server 2005, ale większość stanów oczekiwania tam opisanych do dzisiaj jest często obserwowana na serwerach).

Sesja system_health nagrywa z częstotliwością 5 minut wyniki wykonania procedury sp_server_diagnostics (tej samej, która jest wykorzystywana m.in. do wykrywania problemów po stronie repliki primary w klastrach AlwaysOn). Jednym z rekordów zwracanych przez tę procedurę jest raport dotyczący statystyk stanów oczekiwania. Co dzięki temu możemy uzyskać? Ano, możemy mieć dostęp do statystyk stanów oczekiwania, które już dawno zostały “zapomniane” przez DMV sys.dm_os_wait_stats (jego zawartość jest czyszczona przy okazji restartu instancji). Jak zobaczyć zestawy statystyk trzymanych w plikach sesji system_health?

SELECT
  events.event.value('@timestamp', 'datetime') AS timestamp,
  events.event.query('(/event/data/value/queryProcessing/topWaits)[1]') 
    AS top_waits_xml
FROM (
  SELECT convert(xml, event_data) AS event_data, object_name
  FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
) AS system_health
CROSS APPLY system_health.event_data.nodes('/event') AS events(event)
WHERE system_health.object_name = 'sp_server_diagnostics_component_result'
AND events.event.exist('/event/data/text[text()[1] eq "QUERY_PROCESSING"]') = 1
ORDER BY timestamp DESC;
GO

Przykładowy wynik:

image

Klikając na odnośnikach można podejrzeć statystyki z konkretnej godziny. Oczywiście, porządne statystyki to takie, które były zbierane przez dłuższy czas (wówczas są miarodajne). Którą próbkę statystyk zatem wybrać z powyższego wyniku? Tu z pomocą może przyjść chociażby widok sys.dm_os_sys_info, który w kolumnie sqlserver_start_time zwraca datę i godzinę ostatniego startu usługi SQL Server. Jeśli instancja działa nieprzerwanie zbyt krótko, by statystyki były wiarygodne, bierzemy pod uwagę ostatnie zestawienie nagrane przed ostatnim restartem.

Fragment przykładowego wyniku prezentującego statystyki stanów oczekiwań:

image

Oczywiście, można parsować taki XML aż do uzyskania postaci tabelarycznej.

That’s (not) all folks :-)

Podsumowując, sesja system_health to, obok chociażby śladu default trace, ciekawe źródło informacji dla administratora. Niniejszy wpis prezentuje zaledwie skrawek możliwości, jakie daje system_health. Oprócz tego, co opisałem, kusząco wygląda na przykład przechwytywanie informacji o wybranych błędach czy raportowanie zapytań zbyt długo oczekujących w określonych stanach oczekiwania.

Ale to już materiał na (być może) kolejny wpis na blogu :-) Stay tuned!

One thought on “SQL Server – sesja system_health

  1. Pingback: dotnetomaniak.pl

Leave a Reply