Widok sys.dm_db_stats_histogram

sys.dm_db_stats_histogram zwraca histogram rozkładu wartości bazowy dla wybranej statystyki.

Widok sys.dm_db_stats_histogram — czym jest i do czego służy

Widok sys.dm_db_stats_histogram w Microsoft SQL Server udostępnia szczegółowe informacje o histogramie zapisanym dla wybranej statystyki kolumnowej lub statystyki obejmującej kolumny. Histogram opisuje rozkład wartości, który został zbudowany podczas zbierania albo aktualizowania statystyk. Dzięki temu optymalizator zapytań może szacować, ile wierszy spełni warunek zapisany w zapytaniu. Sam widok nie tworzy nowej statystyki i nie uruchamia aktualizacji. Odczytuje dane dotyczące istniejącego obiektu statystyk, dlatego przed jego użyciem trzeba znać identyfikator obiektu oraz identyfikator statystyki.

Wynik można traktować jako rozwinięcie informacji widocznych w katalogowych widokach statystyk. Widok zawiera między innymi numer kroku histogramu, wartość graniczną przedziału, liczbę zmian wartości, liczbę wierszy należących do przedziału oraz częstotliwość występowania wartości granicznej. Poszczególne kolumny pozwalają ocenić, czy dane są równomiernie rozłożone, czy występują wartości dominujące, a także czy rozkład jest silnie skupiony w określonych zakresach. Ma to znaczenie przy analizie planów wykonania, szczególnie wtedy, gdy plan wydaje się nieproporcjonalny do rzeczywistej liczby zwracanych wierszy.

Widok sys.dm_db_stats_histogram — kontekst techniczny i porównania

Histogram jest budowany na podstawie próby albo pełnego odczytu danych, zależnie od sposobu aktualizacji statystyki i ustawień środowiska. Nie przechowuje osobnego wpisu dla każdej wartości. SQL Server grupuje informacje w ograniczoną liczbę przedziałów, a następnie zapisuje podsumowanie rozkładu. Z tego powodu histogram stanowi przybliżenie, a nie kompletny spis danych. Przy kolumnie o bardzo dużej liczbie różnych wartości nie da się oczekiwać, że każdy element będzie widoczny jako oddzielny krok.

Warto odróżnić kilka pojęć. RANGE_HI_KEY oznacza górną granicę danego kroku. DISTINCT_RANGE_ROWS opisuje przybliżoną liczbę różnych wartości występujących w zakresie, natomiast EQ_ROWS wskazuje szacowaną liczbę wierszy równą wartości granicznej. AVG_RANGE_ROWS przedstawia średnią liczbę wierszy przypadającą na jedną wartość w zakresie, a RANGE_ROWS odnosi się do wierszy mieszczących się pomiędzy poprzednią a bieżącą granicą. Taki podział jest ważny, ponieważ warunek równości, na przykład WHERE StatusId = 4, jest oceniany inaczej niż warunek zakresowy, taki jak WHERE DataUtworzenia >= ....

W porównaniu z ogólnym widokiem sys.stats omawiany widok pokazuje znacznie bardziej szczegółową strukturę rozkładu. sys.stats pomaga ustalić, jakie statystyki istnieją, do jakiego obiektu należą i kiedy były aktualizowane. sys.stats_columns wskazuje kolumny objęte daną statystyką. Z kolei sys.dm_db_stats_properties dostarcza informacji o liczbie wierszy, liczbie zmian oraz czasie ostatniej aktualizacji, ale nie prezentuje poszczególnych kroków histogramu. sys.dm_db_stats_histogram jest więc użyteczny wtedy, gdy sama data aktualizacji albo liczba zmian nie wyjaśnia problemu z estymacją.

Widok sys.dm_db_stats_histogram — typowe problemy interpretacyjne

Najczęstszym problemem jest analizowanie histogramu bez sprawdzenia, czy statystyka dotyczy właściwej kolumny. Statystyka może mieć nazwę utworzoną automatycznie, nazwę nadaną przez użytkownika albo być powiązana z indeksem. Najpierw należy ustalić identyfikatory za pomocą sys.stats i sys.stats_columns, a dopiero później pobierać szczegóły z widoku histogramu. Błędny identyfikator może prowadzić do pustego wyniku lub do analizy innej statystyki niż ta, która wpływa na oceniany plan.

Drugim problemem jest utożsamianie starego histogramu z aktualnym obrazem tabeli. Jeżeli po ostatnim zebraniu statystyk nastąpiło wiele zmian, rozkład danych mógł się zmienić. Nowe wartości mogły pojawić się poza zakresem zapisanym w histogramie, a wartości wcześniej rzadkie mogły stać się dominujące. W takiej sytuacji estymacja liczby wierszy może odbiegać od rzeczywistego wyniku. Należy wtedy porównać czas aktualizacji, liczbę zmian oraz aktualny plan wykonania, zamiast wyciągać wniosek wyłącznie z jednego kroku histogramu.

Nie powinno się także zakładać, że każda różnica między estymowaną i rzeczywistą liczbą wierszy wynika wyłącznie ze statystyk. Znaczenie mogą mieć predykaty złożone, funkcje użyte na kolumnach, konwersje typów, korelacja kilku kolumn oraz sposób łączenia tabel. Statystyka jedno- lub wielokolumnowa opisuje określony zakres informacji i nie zawsze odzwierciedla zależności między wszystkimi warunkami zapytania. Histogram jest dowodem pomocniczym w diagnozie, a nie samodzielnym wyjaśnieniem każdego nieoptymalnego planu.

Uwaga wymaga również wartości NULL. Nie są one reprezentowane w histogramie tak samo jak zwykłe wartości klucza. Jeżeli kolumna zawiera dużo braków, a zapytanie filtruje właśnie po wartości NULL albo wyklucza ją, należy uwzględnić dodatkowe metadane statystyki i rzeczywistą selektywność warunku. Podobnie ostrożnie należy traktować dane tekstowe, daty o nierównomiernym rozkładzie oraz kolumny, w których występują okresowe skoki liczby rekordów.

Widok sys.dm_db_stats_histogram — praktyczne wskazówki diagnostyczne

Analizę najlepiej rozpocząć od zapisania identyfikatora obiektu, nazwy statystyki i kolumny, której dotyczy. Następnie warto odczytać właściwości statystyki, sprawdzić ostatnią aktualizację i dopiero potem obejrzeć kroki histogramu. Przydatne jest zestawienie histogramu z planem wykonania oraz z pomiarem rzeczywistej liczby zwróconych wierszy. Pozwala to rozdzielić problem błędnej estymacji od problemu kosztownego operatora, nieodpowiedniego indeksu albo niekorzystnego kształtu zapytania.

Przykładowe zapytanie można ograniczyć do jednej statystyki:

SELECT
    object_id,
    stats_id,
    step_number,
    range_high_key,
    equal_rows,
    distinct_range_rows,
    average_range_rows,
    range_rows
FROM sys.dm_db_stats_histogram
WHERE object_id = OBJECT_ID(N'dbo.Zamowienia')
  AND stats_id = 3
ORDER BY step_number;

Przed uruchomieniem takiego zapytania trzeba zastąpić nazwę obiektu i numer statystyki wartościami odpowiadającymi badanemu przypadkowi. W praktyce nie należy zgadywać numeru stats_id. Powinien zostać odczytany z katalogu systemowego. Warto też pamiętać o kontekście bazy danych, ponieważ widok odnosi się do bieżącej bazy. Samo zapytanie odczytujące histogram nie zmienia danych ani statystyk.

Jeżeli histogram jest nieaktualny, można rozważyć odświeżenie statystyki zgodnie z przyjętą polityką utrzymania. Po odświeżeniu należy ponownie ocenić plan i sprawdzić, czy poprawiła się zgodność estymacji z wykonaniem. Nie zaleca się mechanicznego wymuszania aktualizacji przy każdym problemie, ponieważ częste operacje tego rodzaju również zużywają zasoby. Trafniejsza jest obserwacja zmian danych, częstotliwości modyfikacji i znaczenia konkretnej statystyki dla najważniejszych zapytań.

Widok sys.dm_db_stats_histogram — kiedy zgłosić się do serwisu

Pomoc specjalistyczna może być potrzebna, gdy analiza histogramu wskazuje na powtarzające się rozbieżności, a problem dotyczy nie tylko jednego zapytania. Szczególnie istotne są sytuacje, w których czas wykonania zmienia się po kolejnych aktualizacjach statystyk, plan wybiera nieadekwatny sposób dostępu do danych albo diagnostyka wymaga bezpiecznego sprawdzenia konfiguracji i kopii zapasowych. W takich przypadkach przydatne jest przygotowanie nazwy bazy, identyfikatora obiektu, nazwy statystyki, planu wykonania oraz informacji o tym, kiedy nastąpiło pogorszenie.

Warszawskie Pogotowie Komputerowe 24/7 zajmuje się diagnostyką sprzętu komputerowego, a naprawy z reguły realizowane są do 3 dni roboczych. Przy zgłoszeniu dotyczącym problemów z oprogramowaniem lub działaniem komputera warto przekazać opis objawów, komunikaty i kolejność wykonywanych czynności. Uporządkowane dane ułatwiają rozdzielenie awarii sprzętowej od błędu konfiguracji, nieaktualnych statystyk albo niewłaściwego planu zapytania.

Warto zgłosić problem również wtedy, gdy nie ma pewności, czy przyczyną jest baza danych, system operacyjny, nośnik danych czy uszkodzenie podzespołu. Przed przekazaniem sprzętu należy zabezpieczyć ważne pliki i zanotować objawy. Przy analizie sys.dm_db_stats_histogram pomocne pozostają konkretne dane: nazwa statystyki, identyfikator obiektu, czas ostatniej aktualizacji, fragment zapytania oraz porównanie liczby estymowanej z rzeczywistą. Taki zestaw informacji zwiększa szansę na sprawne ustalenie źródła problemu.