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
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.
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.
