Silniki bazodanowe wykorzystują statystyki rozkładu danych do utworzenia optymalnego planu wykonania zapytania. Decyzja w jaki sposób wykonywane będzie zapytanie, w głównej mierze opiera się właśnie o informacje na temat liczebności przetwarzanego / zwracanego zbioru. Dotyczy to nie tylko sposobu dostępu do danych – czyli czy wykonać skan tabeli a być może przeszukać właściwy indeks. Na podstawie wielkości szukanego zbioru podejmowane są decyzje o kolejności działań, wyborze algorytmu łączenia tabel czy alokacji pamięci. W artykule tym przedstawię problem aktualności statystyk i ich znaczenie w procesie optymalizacji zapytań.
Po co są statystyki (histogramy rozkładu danych)
Wysyłając nowe zapytanie do serwera bazodanowego, wykonywana jest jego analiza pod kątem składniowym i logicznym. Następnie uruchamiany jest proces tworzenia optymalnego planu wykonania zapytania. Proces ten jest dość rozbudowany i może być czasochłonny. Dlatego raz utworzone plany przechowywane są w pamięci serwera celem ponownego wykorzystania w przyszłości.
Ogólnie rzecz biorąc, tworzenie planu rozpoczyna się od identyfikacji właściwych struktur do odpytania i wyboru metody dostępu do danych. Pomijając plany trywialne, dla których statystyki w ogóle nie są analizowane, histogramy rozkładu pomagają oszacować przydatność poszczególnych indeksów oraz sposób ich wykorzystania.
Weźmy na przykład proste zapytanie odwołujące się do jednej tabeli :
--#START przygotowanie tabeli testowej
IF OBJECT_ID('dbo.test') is not null
DROP TABLE dbo.test
CREATE TABLE dbo.test
(
id INT IDENTITY(1,1),
high_selective_col INT,
descr VARCHAR(100)
CONSTRAINT [CLX_PK_Test] PRIMARY KEY CLUSTERED
(id ASC)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.test(high_selective_col, descr)
VALUES( IDENT_CURRENT('dbo.test'), REPLICATE('x',100))
GO 100000
CREATE NONCLUSTERED INDEX NCLX_TEST_HSC ON dbo.test(high_selective_col)
GO
--#END przygotowanie tabeli
-- nasze zapytanie
SELECT * FROM dbo.test
WHERE high_selective_col = 123
Pierwsze pytanie na jakie optymalizator musi znaleźć odpowiedź to, do jakich struktur (tabel, indeksów) trzeba sięgnąć aby wykonać to zapytanie w sposób optymalny. Optymalny czyli minimalizujący zasoby systemowe niezbędne do jego realizacji. Chodzi głównie o ilość operacji I/O czyli po prostu zasobów dyskowych i pamięci RAM. Ponieważ mamy tu zapytanie do jednej tabeli, pod uwagę bierzemy tylko struktury bezpośrednio z nią związane.
Nasza tabela testowa zorganizowana jest w strukturze indeksu klastrowego i dodatkowo mamy utworzony indeks nieklastrowy na kolumnie high_selective_col który jak łatwo się domyśleć, będzie tutaj wykorzystany. Przeanalizujmy jednak dlaczego i w jakich sytuacjach optymalizator wybiera konkretny indeks. Struktura, wysokość drzew indeksów (index level) wyglądają następująco :
SELECT i.index_id, i.NAME
,index_type_desc
,index_level
,page_count
,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.test'), NULL, NULL, 'DETAILED') x
INNER JOIN sys.indexes i ON x.index_id = i.index_id
AND x.object_id = i.object_id
order by i.index_id, x.index_level desc
W zapytaniu testowym, jedynym predykatem czyli wyrażeniem opisującym cechy szukanych obiektów (rekordów) jest warunek high_selective_col = 123. Stanie się on naszym predykatem wyszukiwania (SEEK PREDICATE) bo mamy utworzony na tej kolumnie indeks NCLX_TEST_HSC i optymalizator na podstawie statystyk rozkładu danych wie, że warto go użyć. Jest to indeks nieklastrowy, zbudowany na tabeli z indeksem klastrowym. Oznacza to, że na poziomie liści tego indeksu, znajdują się klucze indeksu klastrowego (wartość kolumny – ID ).
Nasze zapytanie jest wysoce selektywne – zwraca jeden rekord. Jeśli zdecydujemy się na użycie tego indeksu, aby dostać się wszystkich kolumn jednego rekordu (w końcu mamy select *) będziemy musieli przejść przez dwie struktury B-drzewa. Znając liczbę poziomów indeksów, łatwo policzyć że potrzeba minimum 5 odczytów (widać to w statystykach odczytów – logical reads 5). Jest to dużo mniej niż cała tabela, która ma 1516 stron na poziomie liści indeksu klastrowego, więc zysk jest oczywisty. Optymalizator jak widać wybrał właściwy plan :
Dzięki aktualnym statystykom rozkładu danych, optymalizator przed wykonaniem samego zapytania jest w stanie oszacować dla ilu rekordów nasz warunek wyszukiwania będzie spełniony. Łatwo więc obliczyć koszty (tutaj głównie liczbę odczytów) wykonania zapytania dla różnych wariantów planów. Statystyki dla kolumny high_selective_col zostały stworzone automatycznie w momencie zakładania indeksu. Są więc w 100% aktualne i pełne. Widać to dobrze we właściwościach operatora przeszukiwania indeksu :
Estymacja kardynalności w tym przypadku jest = 1 (estimated number of rows) co pokrywa się z rzeczywistą liczbą zwróconych rekordów (actual number of rows = 1). Powyższy plan będzie optymalny dla zapytań o wysokiej selektywności czyli zwracających niewiele rekordów.
Łatwo sobie wyobrazić, że dla zapytań mało selektywnych, zwracających już nawet kilkaset rekordów, plan ten nie będzie tak efektywny (przeszukiwanie w pętli dwóch struktur vs skan tabeli). Widać to w kolejnym przykładzie, gdy wyszukiwać będziemy 5000 rekordów. Pierwsze zapytanie jest zoptymalizowane przez silnik SQL Server, w drugim wymuszam użycie indeksu nieklastrowego. Plany i statystyki odczytów poniżej :
-- zapytanie optymalizowane przez SQL Server :
SELECT * FROM dbo.test
WHERE high_selective_col <= 5000
-- wymuszony, poprzedni plan wykonania zapytania
SELECT * FROM dbo.test WITH ( INDEX(NCLX_TEST_HSC) )
WHERE high_selective_col <= 5000
Widać że stary plan wykonania (Query 2) jest 10x gorszy pod kątem liczby odczytów niż skan całej tabeli. Optymalizator zapytań wykonał dobrą robotę, pokazując przy okazji jak ważną rolę w procesie optymalizacji spełniają aktualne statystyki. Dzięki nim, potrafił oszacować liczbę przetwarzanych rekordów i wybrał przeczytanie całej tabeli zamiast 5000 iteracji po strukturach indeksów.
Problem nieaktualnych statystyk
Do tej pory analizowaliśmy sytuację idealną, gdy nasze statystyki były aktualne. Sprawdźmy co się stanie, gdy zmienimy nieco rozkład naszych danych wykonując szereg typowych działań (DML) dla systemu transakcyjnego.
-- dodajmy 5000 rekordów z wartością 123
INSERT INTO dbo.test(high_selective_col,descr)
VALUES (123, REPLICATE('x',100))
GO 5000
-- powtórzmy nasze zapytanie testowe
SELECT * FROM dbo.test
WHERE high_selective_col = 123
Jak widać powyżej, plan wykonania zapytania nie zmienił się, ale drastycznie wzrosła liczba odczytów dyskowych (z 5 na 15015). Dodaliśmy raptem 5000 rekordów które z pewnością nie zajęły 15000 stron. Błąd w wyborze nieoptymalnego planu widać we właściwościach operatorów indeksów.
Optymalizator wciąż oczekuje że zapytanie to zwróci jeden rekord i jego estymata kosztów pozostała na poziomie 5 odczytów. Stąd wybrany został stary plan, a dużo efektywniej byłoby wykonać teraz skan tabeli. Tym razem rzeczywista liczba operacji „doczytania” brakujących danych z indeksu klastrowego to 5001 – tyle mamy rekordów . Ta rozbieżność pomiędzy wartościami „Estimated / Actual number of rows” to typowy symptom nieaktualnych statystyk. Co więcej wprowadza również błąd w porównaniu procentowym kosztów planów wykonania zapytań, obserwowanym w Management Studio (które również oparte jest o tę samą wiedzę z której korzysta optymalizator).
Wykonajmy teraz obydwa zapytania jako jeden batch i porównajmy plany wykonań oraz ich koszty :
-- zapytanie optymalizowane w oparciu o nieaktualne statystyki
SELECT * FROM dbo.test
WHERE high_selective_col = 123
-- zapytanie z wymuszonym skanowaniem indeksu
SELECT * FROM dbo.test WITH (INDEX(0))
WHERE high_selective_col = 123
Jak widać, informacja o efektywności jest mocno przekłamana. Pierwsze zapytanie uruchomione w oparciu o nieaktualne statystyki i pozostawione optymalizatorowi zajmuje teoretycznie 1% całego batcha czyli wydaje się bardzo efektywne. Drugie zapytanie z wymuszonym skanem indeksu (WITH(INDEX(0)) zabrało niby 99% czasu/kosztu całego batcha, co oczywiście jest nie prawdą. Widać to w statystykach odczytów. Jest to kolejny przykład (po funkcjach skalarnych), że nie należy porównywać wydajności dwóch zapytań w oparciu o informacje z graficznego interfejsu Management Studio.
Informacje o statystykach
Do obejrzenia szczegółowych informacji o statystykach najlepiej sięgnąć po procedurę DBCC SHOW_STATISTICS, mamy też do dyspozycji kilka widoków/funkcji systemowych. Zobaczmy więc jak wyglądają statystyki dla indeksu NCLX_TEST_HSC :
DBCC SHOW_STATISTICS('dbo.test','NCLX_TEST_HSC')
Procedura DBCC SHOW_STATISTICS domyślnie zwraca trzy recordset’y. W pierwszym widzimy podstawowe informacje na temat statystyki. Kiedy i dla jakiej próbki rekordów została utworzona. Widać też od razu że nie jest w pełni aktualna, bo po ostatnim insercie, mamy w tabeli 105000 rekordów a statystyka została zrobiona dla 100000.
SQL Server może automatycznie aktualizować statystyki, ale algorytm jest dość oszczędny i autoupdate (dla SQL Server 2014 i wcześniej) wykonywany jest po przekroczeniu progu (20%+500) modyfikacji rekordów, czyli w naszym przypadku nastąpiłby po zmodyfikowaniu 20501 rekordów. Ma to swoje uzasadnienie, bo utworzenie/aktualizacja statystyk pociąga za sobą koszty związane choćby z przeczytaniem tabeli lub jej próbki. Dlatego traktujemy mechanizm automatyczny raczej jako element wspierający niż coś co może zastąpić świadomy plan utrzymania.
Warto wspomnieć, że W SQL Server 2016 algorytm ten został zmodyfikowany i dla dużych tabel automatyczny update wykonywany jest częściej. W SQL Server 2008R2 SP1 została też wprowadzona flaga 2371 wymuszająca częstsze auto-aktualizacje.
Drugi recordset zawiera informacje o wektorze gęstości a trzeci – pełne informacje o przyjętych przedziałach histogramu. Kolumna high_selective_col ma bardzo prostą statystykę, którą interpretujemy następująco. Została utworzona w oparciu o próbkę 100% rekordów z tabeli testowej (Rows Sampled/Rows). Histogram jest podzielony tylko na dwa przedziały :
-
- pierwszy przedział to rekordy których wartość maksymalna (RANGE_HI_KEY) = 1. W tym przedziale nie ma żadnych rekordów poniżej wartości maksymalnej ( DISTINCT oraz RANGE_ROWS = 0) i jest tylko jeden rekord równy górnej granicy przedziału (EQ_ROWS = 1). Wartość informacyjna tego przedziału jest taka, że jeśli będziemy szukać rekordów dla których wartość kolumny =1 to powinniśmy otrzymać tylko jeden taki rekord i nie ma w tej tabeli rekordów, dla których ta kolumna ma wartości < 1 .
- drugi przedział jest bardzo podobny jeśli chodzi o wartość informacyjną. Obejmuje rekordy dla których wartość kolumny high_selective_col jest pomiędzy (1, 100000>. Statystyka tego przedziału mówi, że znajduje się w nich 99998 (RANGE_ROWS) rekordów z wartościami poniżej górnej granicy przedziału, z czego unikalnych (DISTINCT_RANGE_ROWS) jest 99998. Jest też jeden (EQ_ROWS=1) rekord o wartości górnej granicy przedziału (RANGE_HI_KEY). Wynika z tego wprost unikalność wartości w tej kolumnie.
Powyższa informacja ma kluczowe znaczenia w wyborze algorytmu dostępu do danych. Dzięki niej łatwo oszacować liczbę koniecznych odczytów czy powtórzeń pętli przechodzenia przez drzewo indeksu klastrowego.
Jeśli chodzi o identyfikację nieaktualnych statystyk, najlepiej sięgnąć do funkcji tabelarycznej sys.dm_db_stats_properties dzięki której możemy dowiedzieć się kiedy nastąpiła ostatnia aktualizacja statystyk i ile modyfikacji od tego momentu miało miejsce.
SELECT
s.name AS "Statistic name",
sp.last_updated AS "Last Updated",
sp.rows,
sp.rows_sampled,
sp.unfiltered_rows,
sp.modification_counter AS "Modifications",
(sp.rows*0.2)+500-sp.modification_counter as updates_to_auto_refresh_stats
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties (s.object_id,s.stats_id) AS sp
WHERE s.name = 'NCLX_TEST_HSC'
Statystyka dla indeksu NCLX_TEST_HSC została utworzona w momencie jego utworzenia, w oparciu o pełną próbkę 100000 rekordów. Widać że jest w pewnym stopniu nieaktualna, ponieważ wykonanych zostało 5000 modyfikacji (INSERT). Statystyka będzie traktowana jako nieaktualna i zostanie przeliczona na nowo, dopiero po przekroczeniu progu określonego algorytmem 20%+500 zmian (czyli brakuje jeszcze 15500 operacji) oraz po kolejnym odwołaniu się do tego indeksu.
Aktualizacja statystyk
Aktualizację statystyk możemy wykonać na kilka sposobów. Pierwszy, już wspominany to pozostawienie tego zadania mechanizmom automatycznym. W zdecydowanej większości przypadków są one jednak nieefektywne i dla dużych tabel mogą występować po prostu zbyt rzadko. Lepiej jest identyfikować nieaktualne struktury i dokonywać aktualizacji w ramach planu utrzymania bazy danych. Możemy aktualizować pojedyncze statystyki, lub np. wszystkie dla danej tabeli lub bazy danych (sp_updatestats). Do tego celu najlepiej użyć polecenia UPDATE STATISTICS :
-- pełna aktualizacja wszystkich statystyk dla tabeli dbo.test
UPDATE STATISTICS dbo.test WITH FULLSCAN
-- pełna aktualizacja statystyki dla wybranego indeksu
UPDATE STATISTICS dbo.test NCLX_TEST_HSC WITH FULLSCAN
Aktualizację statystyk, jeśli jest konieczna i rozkład danych w kolumnie nie jest jednorodny, najlepiej wykonać w trybie FULLSCAN. Oczywiście nie zawsze możemy sobie na to pozwolić z uwagi na ograniczenia czasowe.
Warto wiedzieć, że przebudowanie indeksu (REBUILD) zawsze aktualizuje statystyki w trybie pełnym (WITH FULLSCAN). Nie ma więc potrzeby powtarzać tej operacji dla indeksów które są przebudowywane w planie utrzymania.
Sprawdźmy jeszcze raz naszą statystykę po aktualizacji i wykonajmy raz jeszcze zapytanie testowe.
DBCC SHOW_STATISTICS('dbo.test','NCLX_TEST_HSC') WITH HISTOGRAM
SELECT * FROM dbo.test
WHERE high_selective_col = 123
Histogram został podzielony po aktualizacji na cztery przedziały (maksymalnie może ich być 200). Użytkownik nie ma wpływu na to w jaki sposób ten podział zostanie wykonany, co czasem bywa problemem (rozkład nierównomierny).
Najciekawszy jest teraz drugi przedział histogramu. Widzimy że w zakresie od (1,123> mamy w teraz 121 (RANGE_ROWS) + 5001 (EQ_ROWS) = 5122 rekordów. Przejdźmy do planu zapytania :
Tym razem plan jest zupełnie inny. Został utworzony w oparciu o nowe informacje – dla 5001 rekordów nie ma sensu iterować po strukturach indeksu, łatwiej jest wykonać skan tabeli co też zostało uczynione. Optymalizator utworzył optymalny plan bo działał w oparciu o aktualne statystyki.
Podsumowanie
Aktualność statystyk jest bez wątpienia jednym z najważniejszych czynników wpływających na tworzenie optymalnego planu zapytania. Nieaktualne informacje o rozkładzie wpływają również na efektywność wykonań planów równoległych. Źle oszacowana liczebność przetwarzanego zbioru, może powodować nierówny podział pracy dzielonej na wątki co później objawia się zwiększonym czasem oczekiwania (wait’s CXPACKET). Jak widać skutków ubocznych nieaktualnych statystyk jest sporo dlatego warto o nich pamiętać i mieć je na uwadze w planach utrzymania bazy danych.
Źródła :
SQL Server Statistics, MSDN
SQL Server Statistics, Holger Schmeling
Dobry kurs. Czekamy na więcej.
Znalazłem informację, że dla małych tabel polecenie bez dodatkowych opcji, czyli bez (WITH FULLSCAN) i bez (WITH SAMPLE xx PERCENT), a więc coś takiego jak poniżej
UPDATE STATISTICS dbo.test;
działa tak samo jak z opcją (WITH FULLSCAN). Tylko co to znaczy „mała tabela”? Ile wierszy zawiera „mała tabela”? Na stronach MSDN w dokumentacji tworzenia statystyk, przy opcji FULLSCAN jest taka uwaga: „When omitted, SQL Server uses sampling to create the statistics, and determines the sample size that is required to create a high quality query plan”. Czy wiadomo coś więcej o tym próbkowaniu? Na przykład, ile procent wierszy jest pobieranych aby utworzyć próbkę wykorzystywaną do utworzenia planu zapytania?
Optymalizator kiedy tworzy lub aktualizuję statystykę to minimalną próbką jest 8 MB (1024 stron) lub rozmiar tabeli jeśli jest mniejsza niż minimalna próbka. Rozmiar próbki rośnie wraz z wielkością tabel ale w jaki sposób to tego nie wiem.
Można wywnioskować z tego że mała tabela w kontekście statystyk to tabela nieprzekraczająca 8MB.
Warto spojrzeć:
https://blogs.technet.microsoft.com/rob/2008/05/16/sql-server-statistics/
Super, dzięki za wytłumaczenie.