Condividi tramite

  • Articolo

Risoluzione dei problemi relativi alle prestazioni delle query: elaborazione delle costanti in fase di compilazione durante la stima della cardinalità

In SQL Server alcune espressioni costanti vengono valutate in una fase preliminare per migliorare le prestazioni delle query. Questo comportamento viene denominato elaborazione delle costanti in fase di compilazione. Una costante è un valore letterale Transact-SQL, ad esempio 3, 'ABC', '2005-12-31', 1.0e3 o 0x12345678.

Espressioni per cui è possibile eseguire l'elaborazione delle costanti in fase di compilazione

In SQL Server viene utilizzata l'elaborazione delle costanti in fase di compilazione per i tipi di espressioni seguenti:

  • Espressioni aritmetiche, ad esempio 1+1, 5/3*2, che includono solo costanti.

  • Espressioni logiche, ad esempio 1=1 e 1>2 AND 3>4, che includono solo costanti.

  • Funzioni predefinite per cui si ritiene possibile eseguire l'elaborazione delle costanti in fase di compilazione, incluse CAST e CONVERT. In genere, per una funzione è possibile eseguire l'elaborazione delle costanti in fase di compilazione se si tratta di una funzione solo dei relativi input e non di altre informazioni contestuali, ad esempio opzioni SET, impostazioni della lingua, opzioni di database e chiavi di crittografia. Per le funzioni non deterministiche non è possibile eseguire l'elaborazione delle costanti in fase di compilazione. Per le funzioni predefinite deterministiche, tranne alcune eccezioni, è possibile eseguire l'elaborazione delle costanti in fase di compilazione.

Si applica un'eccezione ai tipi LOB. Se l'output del processo di elaborazione delle costanti in fasi di esecuzione è di tipo LOB (text, image, nvarchar(max), varchar(max) o varbinary(max)), in SQL Server non viene eseguita l'elaborazione delle costanti in fase di compilazione per le espressioni.

Espressioni per cui non è possibile eseguire l'elaborazione delle costanti in fase di compilazione

Per tutti gli altri tipi di espressione non è possibile eseguire l'elaborazione delle costanti in fase di compilazione. In particolare, non è possibile eseguire l'elaborazione delle costanti in fase di compilazione per i tipi di espressioni seguenti:

  • Espressioni non costanti, ad esempio un'espressione il cui risultato dipende dal valore di una colonna.

  • Espressioni il cui risultato dipende da una variabile o un parametro locale, ad esempio @x.

  • Funzioni non deterministiche.

  • Funzioni definite dall'utente (Transact-SQL e CLR).

  • Espressioni il cui risultato dipende dalle impostazioni della lingua.

  • Espressioni il cui risultato dipende dalle opzioni SET.

  • Espressioni il cui risultato dipende dalle opzioni di configurazione del server.

Esempi di espressioni per le quali è possibile eseguire l'elaborazione delle costanti in fase di compilazione e di espressioni per le quali tale elaborazione non è possibile

Si consideri la query seguente:

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Se l'opzione di database PARAMETERIZATION non è impostata su FORCED per la query (il valore predefinito è SIMPLE), l'espressione 117.00 + 1000.00 viene valutata e sostituita dal relativo risultato, 1117.00, prima che la query sia compilata. Tra i vantaggi dell'elaborazione delle costanti in fase di compilazione sono inclusi i seguenti:

  • L'espressione non deve essere valutata più volte in fase di esecuzione.

  • Il valore dell'espressione in seguito alla valutazione viene utilizzato da Query Optimizer per stimare le dimensioni del set di risultati della parte di query TotalDue > 117.00 + 1000.00.

Se dbo.f è invece una funzione scalare definita dall'utente, per l'espressione dbo.f(100) non viene eseguita l'elaborazione delle costanti in fase di compilazione, in quanto in SQL Server questa operazione non è possibile per le espressioni che implicano funzioni definite dall'utente, anche se deterministiche.

Valutazione delle espressioni in fase di compilazione per la stima della cardinalità

Alcune espressioni per cui non viene eseguita l'elaborazione delle costanti ma i cui argomenti sono noti in fase di compilazione, sia che si tratti di parametri o di costanti, vengono valutate tramite lo strumento per la stima delle dimensioni del set di risultati (cardinalità) incluso in Query Optimizer durante l'ottimizzazione.

In particolare, le funzioni predefinite e gli operatori speciali seguenti vengono valutati in fase di compilazione se tutti gli input sono noti: UPPER, LOWER, RTRIM, DATEPART( YY only ), GetDate, CAST e CONVERT.

Anche gli operatori seguenti vengono valutati in fase di compilazione se tutti i relativi input sono noti:

  • Operatori aritmetici: +, -, *, /, unario -

  • Operatori logici: AND, OR, NOT

  • Operatori di confronto: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Gli altri operatori o funzioni non vengono valutati da Query Optimizer durante la stima della cardinalità.

Esempi di valutazione delle espressioni in fase di compilazione

Si consideri la seguente stored procedure Transact-SQL:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

Durante l'ottimizzazione dell'istruzione SELECT nella procedura, Query Optimizer tenta di valutare la cardinalità prevista del set di risultati per la condizione OrderDate > @d+1. Per l'espressione @d+1 non viene eseguita l'elaborazione delle costanti in fase di compilazione, in quanto @d è un parametro. Durante l'ottimizzazione, tuttavia, il valore del parametro è noto. Ciò consente una stima precisa delle dimensioni del set di risultati e, di conseguenza, la definizione di un piano della query appropriato.

Si consideri quindi un esempio simile al precedente, ad eccezione del fatto che una variabile locale @d2 sostituisce @d+1 nella query e che l'espressione viene valutata in un'istruzione SET anziché nella query.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

Quando l'istruzione SELECT in MyProc2 viene ottimizzata in SQL Server, il valore di @d2 non è noto. In Query Optimizer, pertanto, viene utilizzata una stima predefinita per la selettività di OrderDate > @d2, in questo caso 30%.

Procedure consigliate: Utilizzo dell'elaborazione delle costanti in fase di compilazione e della valutazione delle espressioni in fase di compilazione per generare piani della query ottimali

Per garantire la generazione di piani della query ottimali, è consigliabile progettare query, stored procedure e batch affinché Query Optimizer possa stimare in modo preciso la selettività delle condizioni nella query, in base a statistiche sulla distribuzione dei dati. In caso contrario, verrà utilizzata una stima predefinita per determinare la selettività, come il valore 30% dell'esempio precedente.

Per garantire che la stima della cardinalità sia corretta, è innanzitutto consigliabile accertarsi che le opzioni SET di database AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS siano impostate su ON (impostazione predefinita) o che siano state create manualmente statistiche su tutte le colonne a cui si fa riferimento in una condizione della query. Quindi, durante la progettazione delle condizioni nelle query, attenersi, se possibile, alle indicazioni seguenti:

  • Evitare l'utilizzo di variabili locali nelle query. Utilizzare invece parametri, valori letterali o espressioni.

  • Limitare l'utilizzo di funzioni e operatori incorporati in una query contenente un parametro a quelli indicati nella sezione Compile-Time Expression Evaluation for Cardinality Estimation.

  • Assicurarsi che per le espressioni solo costanti nella condizione della query sia possibile eseguire l'elaborazione delle costanti o la valutazione in fase di compilazione.

  • Se è necessario utilizzare una variabile locale per valutare un'espressione da utilizzare in una query, considerare l'opportunità di valutarla in un ambito diverso rispetto a quello delle query. Potrebbe essere utile, ad esempio, eseguire una delle operazioni seguenti:

    • Passare il valore della variabile a una stored procedure contenente la query che si desidera valutare e quindi fare in modo che la query utilizzi il parametro di procedura anziché una variabile locale.

    • Costruire una stringa contenente una query basata in parte sul valore della variabile locale e quindi eseguire la stringa utilizzando linguaggio SQL dinamico (EXEC o sp_executesql).

    • Parametrizzare la query ed eseguirla utilizzando sp_executesql, quindi passare il valore della variabile come parametro alla query.