Posts tagged DMV

W komentarzach do pierwszego wpisu z serii SQL Server dla DBA Robert Kubalski przypomniał o istnieniu widoku sys.dm_os_performance_counters, który może być źródłem cennych informacji o instancji SQL Server i bazach danych istniejących w ramach owej instancji. Pomyślałem, że warto by było napisać, w jaki sposób DBA może interpretować wartości zwracane w wyniku zapytania do wspomnianego widoku.

Pierwsze podejście – typy liczników

Widok dynamiczny sys.dm_os_performance_counters umożliwia podejrzenie wartości liczników monitora wydajności (perfmon.exe) dedykowanych konkretnej instancji SQL Server (tej, z której zadajemy zapytanie do widoku). Jednak nie wszystkie wartości liczbowe można bezpośrednio interpretować. Aby dowiedzieć, w jaki sposób należy interpretować wartości zwracane dla poszczególnych liczników, trzeba poznać typy liczników.

Istnieje kilka typów liczników (są to typy liczników WMI), a listę ich kodów można uzyskać w prosty sposób:

SELECT DISTINCT cntr_type
FROM sys.dm_os_performance_counters
ORDER BY cntr_type;

Oto owe typy liczników i ich interpretacja:

• 65792 - PERF_COUNTER_LARGE_RAWCOUNT – obrazuje ostatnio przechwyconą wartość licznika; tego typu liczniki interpretujemy bezpośrednio – odczytana wartość może być od razu interpretowana (bez przeliczania)
• 272696576 - PERF_COUNTER_BULK_COUNT – oznacza licznik, którego wartości są liczone per zadany interwał czasowy; do wyliczenia sensownej wartości takiego potrzebne są dwa pomiary (a następnie różnicę między pomiarami należy podzielić przez ilość jednostek czasu, np. ilość sekund)
• 537003264 – PERF_LARGE_RAW_FRACTION – taki licznik używany jest do obliczenia ilorazu reprezentującego wskaźnik procentowy (jest podstawiany w liczniku dzielenia, a więc do pełni szczęścia potrzebujemy mianownika – patrz typ PERF_LARGE_RAW_BASE poniżej); niektóre liczniki (np. Buffer Cache Hit Ratio) są reprezentowane jako wartości procentowe i powstają jako wynik dzielenia licznika typu 537003264 przez licznik typu 1073939712
• 1073874176 - PERF_AVERAGE_BULK – ten licznik jest używany do obliczenia ilorazu reprezentującego średnią wartość(podstawiany w liczniku dzielenia – znów potrzebny jest mianownik w postaci licznika typu PERF_LARGE_RAW_BASE); niektóre liczniki (np. Average Wait Time (ms)) reprezentują wartości średnie i powstają jako wynik dzielenia wartości licznika typu 1073874176 przez wartości odpowiedniego licznika typu 1073939712
• 1073939712 - PERF_LARGE_RAW_BASE – licznik używany do obliczenia ilorazu (podstawiany w mianowniku dzielenia); stanowi parę z licznikiem typu 537003264 lub 1073874176.

A zatem, jeśli zobaczysz takie dwa liczniki:

znając typy liczników możesz wywnioskować, że należy podzielić wartość z kolumny cntr_value dla licznika Buffer cache hit ratio przez wartość z tej samej kolumny dla licznika Buffer cache hit ratio base (w tym konkretnym przypadku otrzymujemy wartość 1, co oznacza, że instancja notuje idealnie 100% trafień w bufor danych).

Drugie podejście – licznik, obiekt, instancja

Liczniki są podzielone pomiędzy obiekty. Obiekt można traktować jak kategorię. Oczywiście, są to te same obiekty, które instancja SQL Server dodaje do listy obiektów do użycia w monitorze wydajności. I jednocześnie trzeba pamiętać, że widok sys.dm_os_performance_counters zwraca wyłącznie wartości tych liczników, które pochodzą od danej instancji, na której wykonujemy zapytanie. Dla niektórych liczników, tak jak w perfmon.exe istnieje wiele instancji. Przykładem mogą być liczniki mówiące o rozmiarze dzienników transakcji w bazach danych oraz ilości miejsca zajętego w każdym z dzienników transakcji:

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%Log File%';

Trzecie podejście – przykładowe zapytania i liczniki

O ile zapytanie o wszystkie liczniki nie obciąża praktycznie serwera (liczba odczytów i zużycie procesora są niemal zerowe, a sam widok zwraca co najwyżej kilka tysięcy wierszy – liczba wierszy zależy od ilości baz danych na instancji), o tyle zazwyczaj interesuje nas konkretny licznik lub grupa liczników.

Page Life Expectancy

Page Life Expectancy z obiektu Buffer Manager czyli czas życia stron w pamięci mierzony w sekundach. Ten licznik przydaje się, by zweryfikować, czy przypadkiem strony danych z pamięci nie są zrzucane do pliku stronicowania. Gwałtowny spadek tego licznika zazwyczaj mówi o opróżnieniu bufora danych – czy to celowym, czy wymuszonym przez system (prosty sposób na stwierdzenie braku ustawionego uprawnienia Lock pages in memory).

SELECT
  object_name,
  counter_name,
  cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND counter_name = 'Page Life Expectancy';

Przykładowy wynik (niski, bo zmierzony tuż po restarcie usługi):

Przy okazji warto zauważyć, że kolumny tekstowe widoku sys,dm_os_performance_counters są typu nchar(128), a więc nazwy obiektów, liczników oraz instancji są dopełniane do 128 znaków spacjami. Jest to istotna sprawa przy pisaniu zapytań, zwłaszcza z użyciem operatora LIKE (warto dokładać znak % na końcu wzorca, by nie nadziać się na pułapkę związaną z dodatkowymi spacjami w nazwach).

Buffer cache hit ratio

Buffer cache hit ratio z obiektu Buffer Manager czyli procent trafień zapytań w strony znajdujące się w buforze danych. Im bliżej wartości 1 (oznaczającej 100%), tym lepiej.

SELECT
  c.object_name,
  c.counter_name,
  CASE
    WHEN b.cntr_value = 0 THEN 0
    ELSE CONVERT(numeric(38,2), c.cntr_value * 1.0 / b.cntr_value)
  END AS value
FROM sys.dm_os_performance_counters AS c
INNER JOIN sys.dm_os_performance_counters AS b
ON c.object_name = b.object_name
AND b.counter_name LIKE RTRIM(c.counter_name) + '%'
AND c.instance_name = b.instance_name
AND b.cntr_type = 1073939712
WHERE c.[object_name] LIKE '%Buffer Manager%'
AND c.counter_name = 'Buffer cache hit ratio';

Przykładowy wynik:

Powyższą technikę odszukiwania licznika i mianownika do obliczania ilorazu można stosować do większości liczników, których wartości wyliczamy z ilorazu. Są jednak przypadki, kiedy nazwa licznika i nazwa współczynnika używanego w mianowniku ilorazu różnią się nie tylko słowem “Base”. Przykładem może być licznik Avg. Time Between Batches (ms) z obiektu Broker TO Statistics:

Połączenia i aktywne transakcje

Liczniki Active Transactions z obiektu Databases oraz User Connections z obiektu General statistics pozwalają zorientować się, jaka jest aktualna liczba połączeń do instancji SQL Server oraz ile aktywnych transakcji jest otwartych w kontekście każdej bazy danych.

SELECT
  [object_name],
  counter_name,
  instance_name,
  cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Active Transactions',
'User Connections'
);

Przykładowy wynik:

Podsumowanie

Widok sys.dm_os_performance_counters daje DBA możliwość śledzenie pokaźnej ilości liczników mówiących wiele o kondycji instancji SQL Server. Co ważne, administrator nie musi korzystać z żadnych zewnętrznych narzędzi. Może wykorzystać widok do zbierania danych wydajnościowych do celów analizy, budowania tzw. baseline, klasycznego monitorowania, itd.

Widok ten jest szeroko wykorzystywany przez różne narzędzia firm trzecich (np. Quest Spotlight), jak również przez narzędzia dostępne w SQL Server (np. Performance Studio). Warto się nim zainteresować, zwłaszcza, jeśli naszym celem jest zbudowanie własnego rozwiązania do monitorowania serwera (patrz także wpis pt. SQL Server – Interaktywny performance dashboard dla ubogich).