Archiv pro štítek: statistiky

Odhady počtu řádků a proměnné

Pojďme se podívat, co dělá query optimizer, když mu zabráníme používat statistiky.

Nejdříve si připravme tabulku pro testy a naplňme jí daty.

CREATE TABLE A(a INT, b CHAR(500))
GO

SET NOCOUNT ON
DECLARE @o INT = 1
DECLARE @p INT = 1

WHILE @o <= 50
BEGIN

	WHILE @p <= 200
	BEGIN
		INSERT INTO A(a,b) VALUES(@p,'x')
		SET @p += 1
	END
	
	SET @p = 1
	SET @o += 1
	
END
GO

Vytvoříme index, přičemž se nám vytvoří také statistika na základě všech hodnot ve sloupci a.

CREATE INDEX i_A ON A(a)
GO

Nyní vyzkoušíme jak SQL server pracuje, když může statistiky využít. U následujících dotazů na základě statistik server přesně odhadne počet vrácených řádků, což si můžeme ověřit v exekučním plánu.

--odhaduje 50 řádků, vrací 50 řádků
SELECT * FROM A WHERE a = 50

--odhaduje 500 řádků, vrací 500 řádků
SELECT * FROM A WHERE a <= 10

--odhaduje 1000 řádků, vrací 1000 řádků
SELECT * FROM A WHERE a <= 20

--odhaduje 3000 řádků, vrací 3000 řádků
SELECT * FROM A WHERE a <= 60

--odhaduje 150 řádků, vrací 150 řádků
SELECT * FROM A WHERE a BETWEEN 1 AND 3

sestatistikou

Pokud však místo konstanty v podmínce výběru použijeme proměnné, pak server v době kompilace dotazu nezná jejích hodnotu a nemůže podle statistiky tedy odvodit počet vrácených řádků. Server v tomto případě počty vrácených řádků odhaduje na základě použitého operátoru v podmínce a to nezávisle na hodnotě proměnné.

--odhaduje 50 řádků, vrací 50 řádků
DECLARE @a INT = 50
SELECT * FROM A WHERE a = @a
GO

--odhaduje 50 řádků, vrací 0 řádků
DECLARE @a INT = 500
SELECT * FROM A WHERE a = @a
GO

--odhaduje 3000 řádků, vrací 500 řádků
DECLARE @a INT = 10
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 1000 řádků
DECLARE @a INT = 20
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 3000 řádků
DECLARE @a INT = 60
SELECT * FROM A WHERE a <= @a
GO

--odhaduje 3000 řádků, vrací 0 řádků
DECLARE @a INT = 200
SELECT * FROM A WHERE a > @a
GO

--odhaduje 900 řádků, vrací 150 řádků
DECLARE @a INT = 1
DECLARE @b INT = 3
SELECT * FROM A WHERE a BETWEEN @a AND @b

Pojďme si vysvětlit jak k takovým odhadům server dojde. Sloupec a obsahuje 10 000 hodnot a 200 jedinečných hodnot. Statistika i_A obsahuje hodnotu All density, která se vypočte jako 1 / počet jedinečných hodnot, tedy v našem případě 1 / 200 = 0.005.

statistika

Pro operátor rovnosti server odhaduje počet vrácených řádků jako All density * počet řádků. V našem případě 0.005 * 10 000 = 50.
Pro operátory >, <, >=, <= odhaduje počet vrácených řádků jako 30% z celkového počtu řádků, tedy 0.3 * 10 000 = 3000.
Pro operátor BETWEEN odhaduje server počet vrácených řádků jako 9% z celkového počtu řádků, tedy 0.09 * 10 000 = 900.

Přesně takto kvalitní odhady server používá, pokud vnější proměnné z procedury předáme do vnitřních proměnných a snažíme se tak zamezit parametr sniffingu.

Statistiky v MSSQL serveru

Podrobnosti o existující statistice je možné zjistit v

  • SSMS – Object Explorer – statistics – properties – Details.
  • systémových pohledech sys.stats a sys.stats_columns
  • DBCC SHOW_STATISTICS(<table_name>, <stats_name>) [WITH HISTOGRAM]

Co statistika obsahuje?

All Density – 1 / (počet jedinečných hodnot)

Statistika má maximálně 200 řádků.

Řádek statistiky obsahuje:

  • RANGE_HI_KEY – Horní mez intervalu kroku histogramu.
  • RANGE_ROWS – počet řádků v intervalu, mimo horní mez
  • EQ_ROWS – počet řádků odpovídající horní mezi intervalu
  • DISTINCT_RANGE_ROWS – počet jedinečných hodnot v intervalu, mimo horní mez
  • AVG_RANGE_ROWS – průměrná hodnota počtu jedinečných hodnot, mimo horní mez. Tedy RANGE_ROWS / DISTINCT_RANGE_ROWS

Pokud v exekučním plánu vidíme velmi rozdílnou hodnotu v údaji Estimated Number of Rows a Actual Number of Rows, pak máme problém se statistikami.

Pokud je na úrovni SQL serveru zapnuto Auto Create Statistics a Auto Update Statistics (a tyto hodnoty by měly být zapnuty), pak pokud server při vytváření exekučního plánu zjistí chybějící statistiky respektive zastaralé statistiky, tak dojde k vytvoření potřebné statistiky respektive k občerstvení zastaralé statistiky. To může zdržovat při vykonávání dotazu. Odchytit update statistik při vykonání dotazu je možné Profilerem – událost Performance/Auto Stat.

Při rebuildu indexu dojde k aktualizaci statistik s ním spojených. V tomto případě se provede fullscan, tedy statistiky se vypočtou ze všech řádků a dostaneme tak nejpřesnější možné statistiky. Po tomto není vhodné aktualizovat statistiky s implicitním nastavením, výsledek by byl horší než při použití fullscan.

Server automaticky (pokud je zapnuto Auto Create Statistics) vyrábí statistiky, které potřebuje pro vytvoření plánu vykonávání dotazu. Statistiky vyrábí jen pro jednotlivé sloupce. Automaticky není schopen vyrobit statistiky pro kombinací sloupců. V určitých případech může být vhodné takovéto statistiky vytvořit manuálně, nebo za použití Database Tuning Advisor.

Filtrované statistiky

S vytvořením filtrovaného indexu se vytvoří i filtrovaná statistika.
Filtrované statistiky je možné vytvářet i manuálně.
Výhodou je možnost obejít limit 200 řádků statistiky tak, že vytvoříme dvě statistiky v různých intervalech hodnot. Například jednu pro 90% historických dat a jednu podrobnější pro 10% aktuálních a často používaných dat.
Nevýhodou je, že pokud používáme filtrované statistiky nad sloupcem a pokud server zjistí, že chybí nefiltrovaná statistika, pak nefiltrovanou statistiku automaticky vytvoří. S tím souvisí režie s udržováním dvojích statistik. Doporučuje se ponechat zapnuté automatické tvoření statistik a u nefiltrované statistiky vypnout automatický přepočet (nastavit NORECOMPUTE) a nechat ji zestárnout, server pak bude používat aktuální filtrované statistiky.

Automatický update statistik

Řídí se nastavením serveru:
Auto Update Statistics – zapíná automatickou aktualizaci
Auto Update Statistics Asynchronously – zapíná asynchronní aktualizaci statistik, implicitně vypnuto

Synchronní aktualizace – Pokud se při sestavování plánu zjistí zastaralá statistika, pak se okamžitě provede její aktualizace. Výhodou je plán na základě vždy aktuálních statistik. Nevýhodou je, že aktualizace statistik zdržuje sestavení plánu. Toto nastavení se doporučuje.
Asynchronní aktualizace – Plán se sestaví na základě dostupných statistik. Pokud se zjistí zastaralá statistika, pak je asynchronně odpálena její aktualizace. Výhodou je, že sestavení plánu není zdržováno výpočtem statistik. Nevýhodou je, že plán může být postaven na základě neaktuálních statistik.

Statistiky jsou zastaralé když

  • tabule má více než 500 řádků a je modifikováno více než 20% řádků + další 500 řádků
  • tabule má méně nebo rovno 500 řádků a je modifikováno 500 řádků
  • pokud se počet řádků tabule změní z 0 na nenulový počet
  • u temporarních tabulí po každých změněných 6 záznamech

Filtrované statistky se vyhodnocují bohužel podle počtu modifikací všech řádků a ne jen těch řádků zahrnutých do filtru. Z toho vyplývá nutnost manuální aktualizace filtrovaných statistik.

Zda jsou statistiky zastaralé se vyhodnocuje při sestavování plánu vykonání dotazu.

Chybějící statistiky

Je možné odhalit profilerem zachycením události Errors and Warnings/Missing Column Statistics
Příčinou může být vypnutá volba serveru Auto Create Statistics.
Druhou příčinou může být používání tabulkových proměnných pro které server statistiky nevyrábí (odhadovaný počet řádků je vždy 1 respektive 0 při nesplnitelné podmínce). Mělo by být pravidlem, že tabulkové proměnné se používají jen pro tabulky do 100 řádků. Pro větší tabulky je lépe využívat temporární tabuli (s ‚#‘ na začátku názvu).

Nemožnost použít statistiky

Při použití proměnných v podmínkách výběru nemůže server vyžít statistiky, protože nezná hodnotu proměnné v době sestavování plánu. Pro odhadovaný počet řádků tak server použije průměrnou hodnotu z celé tabulky, která se může diametrálně lišit od hodnoty odpovídající hodnotě proměnné, můžeme tak dostat chybný plán vykonání.

Příliš hrubé statistiky

Pro 4 000 000 záznamů dostaneme 200 řádků statistiky po 20 000 záznamech. Z toho může být 90% historických dat, pro které se statistiky nemění. Řešením je udělat dvě statistiky. Jednu pro historická data, tu není potřeba často udržovat. A jednu pro aktuální živá data. Statistika pro 10% živých dat tak bude jemněji dělena a bude muset být manuálně častěji udržována aktuální.

Nedostatečné automatické aktualizace

20% modifikovaných řádků je málo pro odpálení automatické aktualizace u tabulí s velkým počtem řádků. Nejhorší je to u primárních klíčů, které přibývají na konci řady, tedy mimo statistku a server je tak nemůže vyhodnotit. Zde je nutná častější manuální aktualizace statistiky.

Statistiky a paměť

Statistiky jsou používány pro odhad velikosti potřebné paměti pro některé druhy joinu. Pokud je odhad podhodnocen, pak je místo chybějící paměti využívána tempdb a dochází k cca 10 násobnému zpomalení. Tento problém je možné zachytit profilerem jako události Errors and Warnings/Sort Warnings a Errors and Warnings/Hash Warnings

Zdroje

SQL Server Statistics