Ręczna aktualizacja statystyk

Ręczna aktualizacja statystyk poleceniem UPDATE STATISTICS bywa potrzebna po masowych zmianach danych.

Rola statystyk w optymalizatorze SQL Server

W SQL Server statystyki opisują rozkład wartości w jednej lub kilku kolumnach tabeli albo widoku indeksowanego. Optymalizator zapytań wykorzystuje je podczas tworzenia planu wykonania, między innymi do oszacowania liczby wierszy spełniających warunki w klauzuli WHERE, łączących się przez JOIN lub ograniczanych przez predykaty zakresowe. Na tej podstawie wybiera między innymi skanowaniem tabeli, skanowaniem indeksu i wyszukiwaniem po indeksie, a także ustala kolejność złączeń oraz sposób agregacji danych.

Obiekt statystyk zawiera informacje o liczbie wierszy i stron, czasie aktualizacji oraz próbie danych użytej do obliczeń. Jego histogram przedstawia rozkład wartości dla pierwszej kolumny objętej statystyką. Histogram SQL Server może mieć maksymalnie 200 kroków, dlatego nie odwzorowuje każdej wartości osobno w dużej tabeli. Po masowych zmianach danych wcześniejszy histogram może przestać odpowiadać rzeczywistemu rozkładowi, nawet jeśli sama tabela i jej indeksy nadal są dostępne oraz poprawne.

Kiedy polecenie UPDATE STATISTICS ma uzasadnienie

Ręczna aktualizacja statystyk jest przydatna po operacjach, które znacząco zmieniły liczność lub rozkład danych. Dotyczy to między innymi masowego INSERT, usunięcia dużej części rekordów przez DELETE, modyfikacji kluczowych wartości za pomocą UPDATE, załadowania nowej partii danych oraz przebudowy zawartości tabeli po imporcie. Szczególne znaczenie ma sytuacja, w której zmienione wartości są nierównomiernie rozmieszczone, na przykład większość nowych rekordów ma tę samą datę, status albo identyfikator klienta.

SQL Server ma mechanizmy automatycznej aktualizacji statystyk, ale ich uruchomienie zależy od ustawień bazy danych i wykrycia odpowiedniej liczby zmian. Automatyczna aktualizacja może również użyć próbkowania, które nie zawsze dokładnie odwzoruje rzadkie wartości albo nowy, skoncentrowany zakres danych. Ręczne wykonanie polecenia pozwala wskazać konkretną tabelę, indeks lub nazwany obiekt statystyk oraz dobrać sposób próbkowania do rozmiaru i charakteru danych. Nie oznacza to jednak, że każdą statystykę należy odświeżać po każdej zmianie.

Składnia, zakres i koszt odświeżenia

Podstawowa składnia w SQL Server ma postać UPDATE STATISTICS schema_name.table_name. Można wskazać konkretny obiekt statystyk lub indeks, na przykład UPDATE STATISTICS dbo.Zamowienia IX_Zamowienia_Data. Opcja WITH FULLSCAN powoduje przeanalizowanie wszystkich wierszy zamiast losowej próby, co może zwiększyć dokładność histogramu, lecz także wymagać odczytu dużej ilości danych i zużycia zasobów serwera. Alternatywnie można określić próbkowanie procentowe, używając składni WITH SAMPLE 30 PERCENT.

W przypadku tabeli partycjonowanej standardowa aktualizacja statystyk może nie zapewnić lokalnego odświeżenia każdego zmienionego fragmentu w taki sposób, jakiego wymaga dany proces ładowania danych. Zakres operacji trzeba więc dopasować do modelu tabeli, używanych indeksów i wersji SQL Server. Odświeżenie statystyk nie przebudowuje indeksu, nie naprawia fragmentacji i nie zmienia danych. Jest operacją analityczną, która aktualizuje metadane wykorzystywane przez optymalizator. Polecenie może także wywołać kompilację nowych planów dla zapytań korzystających z odświeżonych informacji, dlatego należy uwzględnić możliwe przejściowe obciążenie CPU oraz wejścia-wyjścia.

Weryfikacja efektu po masowej zmianie danych

Samo wykonanie UPDATE STATISTICS nie potwierdza, że problem z wydajnością został rozwiązany. Przed operacją warto ustalić, które zapytania wykazują rozbieżność między szacowaną a rzeczywistą liczbą wierszy. W SQL Server można sprawdzić metadane statystyk za pomocą sys.stats oraz datę ostatniej aktualizacji przez funkcję STATS_DATE. Przy analizie należy uwzględnić statystyki utworzone automatycznie, statystyki związane z indeksami oraz statystyki kolumnowe zdefiniowane ręcznie.

Po aktualizacji należy ponownie wykonać reprezentatywne zapytanie i porównać plan wykonania, szacowania kardynalności oraz rzeczywistą liczbę odczytanych wierszy. Jeżeli plan nadal jest nieefektywny, przyczyną może być nie tylko nieaktualna statystyka, lecz także nieadekwatny indeks, niejednoznaczny predykat, konwersja typów danych, funkcja zastosowana na kolumnie albo nieaktualne założenia dotyczące zapytania. W środowisku produkcyjnym ręczne odświeżanie powinno być zaplanowane po zakończeniu ładowania danych i monitorowane pod kątem czasu wykonania, blokad oraz wpływu na równoległe zapytania.