Questo post vuole essere un reminder di un comportamento che il Query Processor di Sql Server ha regolarmente quando si parla di variabili locali e costanti (literal).Come sappiamo, SQL Server colleziona un elenco di statistiche che poi serviranno al motore per stabilire quale piano di esecuzione (il migliore secondo la sua analisi interna) applicare nell'esecuzione di ogni query.
Queste statistiche inoltre vengono aggiornate di default (a meno che non si cambi il comportamento standard) qualora SQL Server lo ritenga opportuno.
Grazie a tale procedimento, ogni esecuzione di lettura o scrittura sui nostri database è ottimizzata in base ai contatori salvati.
Può succedere comunque che il motore non mantenga perfettamente allineate le statistiche, ragion per cui, una delle best practices consigliate è quella di effettuare
UPDATE STATISTICS a periodicità che le nostre applicazioni richiedono (basandosi ad esempio sulle quantità di update che l'applicazione stessa effettua, se è alto, un update statistics fullscan è consigliato, magari anche ogni notte).
C'è una particolare pratica, da seguire il meno possibile, che è quella di utilizzare variabili locali nelle condizioni di WHERE, con scopo di ridurre l'utilizzo dei literal. Ad esempio:
1 - Utilizzo delle variabili locali (direttamente da
documentazione - Northwind)
declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h,Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate
1 - Utilizzo delle costanti (direttamente da
documentazione - Northwind)
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= '20040731'
Ognuno dei precedenti approcci avrà un piano di esecuzione differente. Nel primo dei casi il numero delle righe della SalesOrderHeader sarà il 30% dell'intera tabella, mentre nel secondo una percentuale intorno all'1% che è un valore vicino all'effettivo numero di righe che soddisfano la condizione. Come mai due query equivalenti hanno però due analisi differenti da parte del motore? La differenza sta proprio nell'interpretazione del literal rispetto ad una variabile (non un parametro, una variabile locale). La documentazione indica:
"Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, then a selectivity estimate of 1/(number of unique values in column) will be used, which is accurate."
Quindi, utilizzando local variables è possibile che la stima della query non sia quella sperata e quindi il piano stesso non dia le prestazioni desiderate.
Se però si vuole reinterpretare ad ogni esecuzione il valore della variabile per calcolare il piano effettivo è sempre possibile utilizzare l'opzione RECOMPILE:
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate
OPTION(RECOMPILE)
Questa opzione forza l'ottimizzazione della query ad ogni esecuzione, perdendo quindi tempo nella ricreazione dei piani di esecuzione, ma dando l'effettivo costo della query stessa.
Stiamo parlando di query, ma lo stesso si presenta anche a livello di stored procedure.
E' quindi buona norma evitare l'utilizzo di local variables anche in questo contesto, per le medesime ragioni, considerando poi che con le stored procedure i piani sono salvati e persistiti in cache by design.
Stay Tuned!
