Jeśli chcemy mieć stale aktualne statystyki możemy je aktualizować ręcznie lub włączyć opcje automatycznej aktualizacji statystyk AUTO_UPDATE_STATISTICS. Po włączeniu tej opcji to właśnie optymalizator zapytań określa, kiedy statystyki mogą być nieaktualne, a następnie aktualizuje je, gdy są używane przez zapytanie. Ta czynność jest nazywana rekompilacją statystyk.
Kiedy statystyki stają się nieaktualne?
Po wszelkich modyfikacjach na danych czyli operacjach: INSERT, UPDATE, DELETE, MERGE, które zmieniają rozkład danych w tabeli lub poindeksowanym widoku. Query Optimizer określa, kiedy statystyki mogą być nieaktualne, zliczając liczbę modyfikacji wierszy od ostatniej aktualizacji statystyk i porównując liczbę modyfikacji wierszy z wartością progową. Próg jest oparty na liczności tabeli, którą można zdefiniować jako liczbę wierszy w tabeli lub widoku indeksowanym. Progi te zależą od wersji SQL Server. Z biegiem lat ilość danych przetwarzanych w SQL Server ciągle się zmieniała. Progi te dla dużej ilości danych zostały zmienione od wersji SQL Server 2016.
Poniżej przedstawiona jest tabela z progami zmieniającymi się w zależności od ilości modyfikowanych rekordów i rodzaju tabel.
| Rodzaj Tabeli | Liczebność tabeli (n) | Próg rekompilacji (# modyfikacje) |
|---|---|---|
| Temporary (Tymczasowa) | n < 6 | 6 |
| Temporary (Tymczasowa) | 6 <= n <= 500 | 500 |
| Permanent (Stałą) | n <= 500 | 500 |
| Temporary or permanent (Tymczasowa lub stała) | n > 500 | Do wersji SQL Server 2014 500 + (0.20 * n) Od wersji SQL Server 2016 MIN ( 500 + (0.20 * n), SQRT(1,000 * n) ) |
Jak widać zmiana dotyczy tabel i liczebności powyżej 500 rekordów. Dotychczas mówiło się, że aktualizacja statystyk następuje po dokonaniu zmian na 20 procentach liczebności. (Dokładnie 500 + 20%). Od wersji SQL Server 2016 ten próg został zmieniony. Wprowadzona została druga wartość czyli pierwiastek kwadratowy z liczebności tabeli pomnożonej przez 1000. Wynik ten porównywany jest z wcześniejszym sposobem wyliczenia progu i wybierana jest mniejsza wartość.
- Sprawdźmy to :
- n = 1000; 500+0,2*1000= 700 ==> SQRT(1000*1000) = 1000 ==>wygrywa stary sposób
- n=19685; 500+0,2* 19685= 4437==> SQRT(1000* 19685) = 4437==>punkt zmiany sposobu liczenia
- n=20000; 500+0,2*20000=4500 ==> SQRT(1000*20000) = 4473 ==> Przy liczebności ok. 20000 zaczyna już wygrywać nowszy sposób liczenia progu.
- n=1000000; 500+0,2*1000000= 200500 ==>SQRT(1000*1000000) = 31 622 ==>wygrywa drugi sposób
Jak widać przy liczebności 19685 rekordów zaczyna SQL Server stosować nowy sposób wyliczania progu do rekompilacji statystyk.
Ale dlaczego dopiero w wersji SQL Server 2016, a co z wcześniejszymi wersjami?
Wcześniej była możliwość włączenia TraceFlag TF2371, lecz było to dostępne w najwyższych wersjach SQL czyli powyżej wersji Standard. Flaga ta została wprowadzona od wersji SQL Server 2008R2 z SP1. Flaga ta przełączała się na dynamiczny sposób wyliczania progu rekompilacji.
