Archiv pro štítek: optimalizace

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.

Nalezení problémových dotazů

SELECT 
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text
, (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms] 
, max_elapsed_time/1000 AS [MaxExecTime in ms]
, min_elapsed_time/1000 AS [MinExecTime in ms]
, (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, qs.execution_count AS NumberOfExecs
, qs.total_rows
, qs.last_rows
, qs.min_rows 
, qs.max_rows
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.dbid = db_id()
ORDER BY (total_elapsed_time/execution_count) DESC;