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 TabeliLiczebność tabeli (n)Próg rekompilacji (# modyfikacje)
Temporary (Tymczasowa)n < 66
Temporary (Tymczasowa)6 <= n <= 500500
Permanent (Stałą)n <= 500500
Temporary or permanent
(Tymczasowa lub stała)
n > 500Do 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.


Dariusz Brejnak

Od prawie trzydziestu lat jest pasjonatem informatyki, a zwłaszcza dziedzin dotyczących baz danych, hurtowni danych oraz ogólnie rozumianej tematyki BI. Jego druga pasja to fotografia http://dariuszbrejnak.pl