In questo articolo, esploreremo l’utilizzo dell’hint OPTIMIZE FOR e analizzeremo come questo impatta sulla lettura delle statistiche da parte dell’optimizer.

Quando una stored procedure viene eseguita la prima volta, i parametri con cui viene richiamata vengono salvati nella cache insieme al piano di esecuzione e utilizzati per il calcolo della cardinalità.

Questo comportamento è denominato “parameter sniffing”.

Nel caso in cui la distribuzione dei valori all’interno di una statistica sia poco uniforme, una stima errata della cardinalità, dovuta al parameter sniffing, può impattare negativamente sulle prestazioni in fase di esecuzione.

Facciamo un esempio:

USE tempdb
GO

SET NOCOUNT ON

CREATE TABLE t1 (IdRow int identity(1,1) PRIMARY KEY, Valore char(3))

DECLARE @i int

-- Inserisco 1000 righe con il valore AAA
SET @i = 0
WHILE @i < 1000
BEGIN
 INSERT INTO t1 (Valore) VALUES ('AAA')
 SET @i = @i + 1
END

-- Inserisco 500 righe con il valore BBB
SET @i = 0
WHILE @i < 500
BEGIN
 INSERT INTO t1 (Valore) VALUES ('BBB')
 SET @i = @i + 1
END

-- Inserisco 1 riga con il valore CCC
INSERT INTO t1 (Valore) VALUES ('CCC')

CREATE INDEX IX1 ON t1 (Valore)
GO

-- Analizziamo le statistiche
DBCC SHOW_STATISTICS (t1, IX1)
GO

 

Analizzando i piani di esecuzione possiamo constatare come le righe stimate vengano calcolate sulla base della cardinalità del parametro lanciato per primo dopo la compilazione (o ricompilazione) della stored procedure.

La figura evidenzia, ad esempio, la discrepanza tra righe stimate e quelle reali nel caso in cui la stored procedure venga richiamata con il parametro “AAA” essendo stata richiamata la prima volta con il parametro “CCC”.

Se guardiamo il piano di esecuzione in formato XML troveremo il dettaglio del parametro salvato:

<ParameterList>

<ColumnReference Column="@Valore" ParameterCompiledValue="'CCC'"
ParameterRuntimeValue="'CCC'" />

</ParameterList>

In casi come quello appena descritto, possiamo utilizzare l’hint “OPTIMIZE FOR” per controllare la modalità con cui i parametri vengono salvati nel piano di esecuzione, in modo da migliorare la stima della cardinalità.

Ci sono due modi per utilizzare “OPTIMIZE FOR”:

  1. OPTIMIZE FOR Parametro: a prescindere dal parametro con cui la stored procedure viene chiamata per prima, il piano di esecuzione verrà ottimizzato e salvato nella cache con il valore specificato e, di conseguenza, le statistiche verranno sempre calcolate in base alla cardinalità di quel parametro
  2. OPTIMIZE FOR UNKNOWN: non verrà salvato alcun parametro nel piano di esecuzione in cache e le statistiche verranno calcolate in base alla densità

Esempio caso 1:

ALTER PROCEDURE p1
 @Valore char(3)
AS

 

SELECT IdRow, Valore
FROM t1
WHERE Valore = @Valore
OPTION(OPTIMIZE FOR (@Valore = 'AAA'))

GO

-- Eseguiamola passandogli prima 'CCC' e poi 'BBB'
EXEC p1 'CCC' --> 1 riga, stimate 1000
EXEC p1 'BBB' --> 500 righe, stimate 1000

 

Come si vede, in entrambi i casi l’optimizer stima 1000 righe che è appunto la cardinalità del valore “AAA”

Esempio caso 2:

ALTER PROCEDURE p1
 @Valore char(3)
AS

 

SELECT IdRow, Valore
FROM t1
WHERE Valore = @Valore
OPTION(OPTIMIZE FOR UNKNOWN)

GO

-- Eseguiamola passandogli i vari valori
EXEC p1 'CCC' --> 1 riga, stimate 500,333
EXEC p1 'BBB' --> 500 righe, stimate 500,333
EXEC p1 'AAA' --> 1000 righe, stimate 500,333

Come si vede, l’optimizer stima le righe in base alla densità, ovvero:

righe stimate = densità x numero righe totali = 0,3333333 x 1501 = 500,333

 Ora vi faccio notare qualcosa di interessante :-)

 Proviamo ad aggiungere un po’ di valori NULL per il campo “Valore”:

DECLARE @i int
SET @i = 0
WHILE @i < 2000
BEGIN
 INSERT INTO t1 (Valore) VALUES (NULL)
 SET @i = @i + 1
END

-- Analizziamo le statistiche
UPDATE STATISTICS t1 WITH FULLSCAN;
DBCC SHOW_STATISTICS (t1, IX1)
GO

  

Dalle statistiche possiamo notare la presenza di 2000 valori NULL per il campo “Valore”. La densità è leggermente diminuita, passando da 0,333 a 0,25.

Ricompiliamo la stored e lanciamola con il parametro CCC:

EXEC sp_recompile p1

EXEC p1 'CCC'

 Secondo i calcoli fatti in precedenza ci aspetteremmo la seguente stima:

densità x numero righe totali = 0,25 x 3501 = 875,25

Se guardiamo però il piano di esecuzione notiamo che la stima non è cambiata:

Come mai ha ignorato i valori NULL?

La risposta è insita nel predicato della query: 

WHERE Valore = @Valore

Nel caso in cui @Valore sia NULL, la query non restituirà alcuna riga nonostante vi siano 2000 righe a NULL, perché il risultato del confronto "Valore = @Valore" è valutato come UNKNOWN.

È interessante il fatto che l’optimizer, conscio di questo fatto, non consideri la cardinalità dei valori NULL, migliorando la stima.

Il calcolo effettuato è

[densità dei valori NOT NULL] x [numero righe totali NOT NULL]

dove densità dei valori NOT NULL si ottiene facendo l’inverso della somma di tutti i valori della colonna DISTINCT_RANGE_ROWS con l’aggiunta del numero di step – 1 (escludo la riga dei NULL).

Nel nostro caso il calcolo è [1/(0+0+0+0+4-1)] x 1501

 

Alla prossima!

Giuseppe Zagarrio