Stima della cardinalità (SQL Server)

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Query Optimizer di SQL Server è un ottimizzatore basato sui costi. Questo significa che vengono selezionati i piani di query con il minor costo di elaborazione stimato per l'esecuzione. Query Optimizer determina il costo di esecuzione di un piano di query in base a due fattori principali:

• Numero totale di righe elaborate a ogni livello del piano di query, detto cardinalità del piano.
• Modello di costo dell'algoritmo determinato dagli operatori utilizzati nella query.

Il primo fattore, ovvero la cardinalità, viene utilizzato come parametro di input del secondo fattore, ovvero il modello di costo. Una migliore cardinalità comporta pertanto costi stimati migliori e, di conseguenza, piani di esecuzione più rapidi.

In SQL Server la stima di cardinalità (CE) deriva principalmente da istogrammi creati al momento della creazione di indici o statistiche, in modo manuale o automatico. In alcuni casi, per determinare la cardinalità in SQL Server vengono inoltre utilizzate le informazioni sui vincoli e le riscritture logiche delle query.

Nei casi seguenti, in SQL Server non è possibile eseguire un controllo accurato delle cardinalità. Questo comporta calcoli dei costi non accurati che potrebbero portare a piani di query non ottimali. Evitando questi costrutti nelle query, è possibile migliorare le prestazioni. In alcuni casi, è possibile ricorrere a formulazioni di query alternative o ad altre misure, indicate di seguito:

• Query con predicati che utilizzano operatori di confronto tra colonne della stessa tabella.
• Query con predicati che utilizzano operatori e una delle seguenti condizioni è vera:
  • Non vi sono statistiche nelle colonne coinvolte a destra o a sinistra degli operatori.
  • La distribuzione dei valori nelle statistiche non è uniforme, ma la query cerca un set di valori estremamente selettivo. Questa situazione si verifica in particolare se l'operatore è diverso dall'operatore di uguaglianza (=).
  • Il predicato usa l'operatore di confronto diverso da (!=) o l'operatore logico NOT.
• Query che usano qualsiasi funzione predefinita di SQL Server o una funzione definita dall'utente a valori scalari il cui argomento non è un valore costante.
• Query che implicano il join di colonne attraverso operatori aritmetici o di concatenazione delle stringhe.
• Query che confrontano variabili i cui valori non sono noti al momento della compilazione e dell'ottimizzazione della query.

Questo articolo illustra come valutare e scegliere la migliore configurazione di stima della cardinalità per il sistema. Per la maggior parte dei sistemi è disponibile la stima della cardinalità più recente perché è la più accurata. La stima della cardinalità prevede il numero di righe che verranno probabilmente restituite dalla query. La stima della cardinalità è usata da Query Optimizer per generare il piano di query ottimale. Con stime più accurate, Query Optimizer è in genere in grado di produrre un piano di query migliore.

Il sistema di applicazioni può includere una query importante il cui piano viene sostituito con un piano più lento a causa di modifiche della stima della cardinalità tra versioni diverse. Sono disponibili tecniche e strumenti per l'identificazione di una query che risulta più lenta a causa di problemi di stima della cardinalità. Inoltre, sono disponibili opzioni per la risoluzione del problema di prestazioni conseguente.

Versioni della stima della cardinalità

Nel 1998 è stato incluso un aggiornamento importante della stima della cardinalità nell'ambito di SQL Server 7.0, per cui il livello di compatibilità era 70. Questa versione del modello di stima di cardinalità si basa su quattro presupposti:

• Indipendenza: si presuppone che le distribuzioni dei dati in colonne diverse siano indipendenti una dall'altro, a meno che siano disponibili e utilizzabili informazioni di correlazione.

• Uniformità: i valori distinct sono divisi uniformemente e hanno tutti la stessa frequenza. Più precisamente, all'interno di ogni intervallo dell'istogramma, i valori distinct sono distribuiti uniformemente e hanno tutti la stessa frequenza.

• Indipendenza (semplice): gli utenti eseguono una query per i dati esistenti. Ad esempio, per un join di uguaglianza tra due tabelle, considerare la selettività di join ¹ in ogni istogramma di input prima di creare un join di istogrammi e stimarne la selettività.

• Inclusione: per i predicati di filtro dove Column = Constant, si presuppone che la costante sia effettivamente esistente nella colonna associata. Se un intervallo dell'istogramma corrispondente non è vuoto, si presuppone che uno dei valori distinct dell'intervallo corrisponda al valore del predicato.

  ¹ Numero di righe che soddisfa il predicato.

Gli aggiornamenti successivi sono iniziati con SQL Server 2014 (12.x), con livelli di compatibilità 120 e superiori. Gli aggiornamenti della stima di cardinalità per i livelli 120 e superiori integrano presupposti e algoritmi aggiornati che funzionano bene nel data warehousing moderno e nei carichi di lavoro OLTP. Dopo i presupporti della stima di cardinalità per i livelli 70, con la stima di cardinalità per i livelli 120 sono stati modificati i presupposti del modello seguenti:

• Indipendenza diventa correlazione: la combinazione dei diversi valori di colonna che non sono necessariamente indipendenti. Può assomigliare all'esecuzione di query sui dati più reali.
• Indipendenza semplice diventa contenimento di base: gli utenti possono eseguire la query per dati che non esistono. Ad esempio, per un join di uguaglianza tra due tabelle, si usano gli istogrammi delle tabelle di base per stimare la selettività di join e considerare la selettività dei predicati.

Usare Query Store per valutare la versione di stima di cardinalità

A partire da SQL Server 2016 (13.x), Query Store è uno strumento utile per esaminare le prestazioni delle query. Una volta abilitato, Query Store inizierà a tenere traccia delle prestazioni delle query nel tempo, anche se i piani di esecuzione cambiano. Monitorare Query Store per prestazioni di query a costi elevati o regredite. Per altre informazioni, vedere Monitoraggio delle prestazioni con Query Store.

Per preparare un aggiornamento a SQL Server o promuovere un livello di compatibilità del database in qualsiasi piattaforma SQL Server, è consigliabile aggiornare i database con Assistente ottimizzazione query, che consente di confrontare le prestazioni delle query in due livelli di compatibilità diversi.

Usare gli eventi estesi per valutare la versione stima della cardinalità

Un'altra opzione per tenere traccia del processo relativo alle stime di cardinalità consiste nell'usare l'evento esteso denominato query_optimizer_estimate_cardinality. Il seguente codice Transact-SQL di esempio viene eseguito su SQL Server. Scrive un file con estensione xel in C:\Temp\. Il percorso è comunque modificabile. Quando si apre il file con estensione xel in Management Studio, le informazioni dettagliate sono visualizzate in modo intuitivo.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Per informazioni sugli eventi estesi appositi per il database SQL, vedere Eventi estesi nel database SQL.

Procedura per valutare la versione della stima di cardinalità

Di seguito sono indicate alcune operazioni da eseguire per stabilire se una delle query più importanti presenta prestazioni inferiori con la stima di cardinalità più recente. Alcuni dei passaggi vengono completati eseguendo un esempio di codice presentato in una sezione precedente.

1. Aprire SQL Server Management Studio (SSMS). Assicurarsi che il database di SQL Server sia impostato sul massimo livello di compatibilità disponibile.

2. Seguire questa procedura preliminare:

   1. Aprire SQL Server Management Studio (SSMS).

   2. Eseguire Transact-SQL per assicurarsi che il database di SQL Server sia impostato sul massimo livello di compatibilità disponibile.

   3. Assicurarsi che la configurazione LEGACY_CARDINALITY_ESTIMATION del database sia impostata su OFF.

   4. Cancellare il contenuto di Query Store. Nel database verificare che Query Store sia impostato su ON.

   5. Eseguire l'istruzione: SET NOCOUNT OFF;

3. Eseguire l'istruzione: SET STATISTICS XML ON;

4. Eseguire la query importante.

5. Nel riquadro dei risultati, nella scheda Messaggi , notare il numero effettivo di righe interessate.

6. Nel riquadro dei risultati, nella scheda Risultati , fare doppio clic sulla cella che contiene le statistiche in formato XML. Viene visualizzato un piano di query grafico.

7. Fare clic con il pulsante destro del mouse nella prima casella del piano di query grafico e scegliere Proprietà.

8. Per un successivo confronto con una configurazione diversa, notare i valori per le proprietà seguenti:

   • CardinalityEstimationModelVersion.

   • Numero stimato di righe.

   • Costo I/O stimatoStimato simili che riguardano le prestazioni effettive anziché le previsioni del numero di righe.

   • Operazione logica e Operazione fisica. Parallelismo è un valore adeguato.

   • Modalità di esecuzione effettiva. Batch è un valore adeguato, migliore di Riga.

9. Confrontare il numero stimato di righe con il numero effettivo di righe. La stima di cardinalità è imprecisa del 1% (per eccesso o per difetto) o del 10%?

10. Eseguire: SET STATISTICS XML OFF;

11. Eseguire l'istruzione Transact-SQL per abbassare il livello di compatibilità del database di un livello (ad esempio da 130 a 120).

12. Eseguire di nuovo tutti i passaggi non preliminari.

13. Confrontare i valori delle proprietà della stima di cardinalità delle due esecuzioni.

    • La percentuale di imprecisione con la stima di cardinalità più recente è inferiore rispetto a quella meno recente?

14. Infine, confrontare i vari valori delle proprietà delle prestazioni delle due esecuzioni.

    • La query ha usato un piano diverso nelle due stime di cardinalità?

    • La query è risultata più lenta con la stima di cardinalità più recente?

    • A meno che la query non venga eseguita con prestazioni migliori e con un piano diverso con la stima di cardinalità precedente, quasi certamente è preferibile usare la stima di cardinalità più recente.

    • Se però la query viene eseguita con un piano più veloce con la stima di cardinalità precedente, è possibile forzare il sistema affinché usi il piano più veloce e ignori la stima di cardinalità. In questo modo si può usare la stima di cardinalità più recente per tutto, mantenendo il piano più veloce nel caso particolare.

Come attivare il piano di query ottimale

Si supponga che con la stima di cardinalità per i livelli 120 o superiori, venga generato un piano di query meno efficiente per la query. Ecco alcune opzioni per attivare il piano migliore, ordinate dall'ambito più grande al più piccolo:

• È possibile impostare il livello di compatibilità del database su un valore inferiore rispetto a quello più recente, per l'intero database.

  • Ad esempio, impostando il livello di compatibilità per i livelli 110 o inferiori, si attiva la stima di cardinalità per i livelli 70, ma si rendono tutte le query soggette al modello di stima di cardinalità precedente.

  • Impostando un livello di compatibilità inferiore viene a mancare anche una serie di miglioramenti in Query Optimizer per le versioni più recenti e incide su tutte le query del database.

• Per fare in modo che l'intero database usi la stima di cardinalità precedente, mantenendo gli altri miglioramenti in Query Optimizer, è possibile usare l'opzione di configurazione di scopo del database LEGACY_CARDINALITY_ESTIMATION.

• Per fare in modo che una sola query usi la stima di cardinalità precedente, mantenendo gli altri miglioramenti in Query Optimizer, è possibile usare l'hint per la query LEGACY_CARDINALITY_ESTIMATION.

• È possibile imporre LEGACY_CARDINALITY_ESTIMATION con la funzionalità hint di Query Store per consentire a una singola query di usare la stima di cardinalità precedente senza modificare la query.

• Imporre un piano diverso con Query Store.

Livello di compatibilità del database

Per assicurarsi che il database sia impostato su un determinato livello, è possibile usare il codice Transact-SQL seguente per COMPATIBILITY_LEVEL.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

Per i database esistenti in esecuzione in livelli di compatibilità inferiori, se l'applicazione non richiede l'uso dei miglioramenti disponibili solo in un livello di compatibilità superiore, un approccio valido prevede il mantenimento del livello di compatibilità del database precedente. Per i nuovi progetti di sviluppo o quando un'applicazione esistente richiede l'uso di nuove funzionalità come Elaborazione di query intelligenti oltre a nuovi elementi di Transact-SQL, pianificare l'aggiornamento del livello di compatibilità del database a quello più recente disponibile. Per altre informazioni, vedere Livelli di compatibilità e aggiornamenti del motore di database.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

Per un database di SQL Server impostato sul livello di compatibilità 120 o su un livello superiore, l'attivazione del flag di traccia 9481 impone al sistema di usare la stima di cardinalità di livello 70.

Stima di cardinalità legacy

Per un database di SQL Server impostato sul livello di compatibilità 120 o superiore, è possibile attivare la stima di cardinalità legacy di livello 70 a livello di database usando MODIFICARE LA CONFIGURAZIONE CON AMBITO DATABASE.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Modificare la query per usare l'hint

A partire da SQL Server 2016 (13.x) SP1, modificare la query per usare l'hint per la query USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Impostare l’hint di Query Store

Le query possono essere forzate a usare lo strumento di stima di cardinalità legacy senza modificare la query, usando gli hint di Query Store.

1. Identificare la query nelle viste del catalogo di Query Stores sys.query_store_query_text e sys.query_store_query. Ad esempio, cercare una query eseguita per frammento di testo:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. Nell'esempio seguente viene applicato l'hint Query Store per forzare lo strumento di stima della cardinalità legacy a query_id 39, senza modificare la query:

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Come forzare un piano di query specifico

Per il massimo controllo, è possibile imporre al sistema di usare il piano generato con la stima di cardinalità per i livelli 70 durante i test. Dopo avere aggiunto il piano preferito, è possibile impostare l'intero database per l'uso della compatibilità e della stima di cardinalità più recenti. L'opzione viene elaborata successivamente.

Query Store offre diversi modi per forzare l'uso di un determinato piano di query:

• Eseguire sys.sp_query_store_force_plan.

• In SQL Server Management Studio (SSMS) espandere Query Store, fare clic con il pulsante destro del mouse su Primi nodi per consumo risorse e scegliere Visualizza primi nodi per consumo risorse. Verranno visualizzati i pulsanti Forza piano e Annulla forzatura piano.

Per altre informazioni su Query Store, vedere Monitoraggio delle prestazioni con Query Store.

Riduzione costante e valutazione delle espressioni durante la stima di cardinalità

Il motore di database valuta alcune espressioni costanti in una fase preliminare per migliorare le prestazioni delle query. Questo comportamento viene denominato elaborazione delle costanti in fase di compilazione. Una costante è un Transact-SQL letterale come 3, 'ABC', '2005-12-31', 1.0e3, o 0x12345678. Per altre informazioni, vedere Riduzione costante.

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. Per altre informazioni, vedere Valutazione espressione.

Procedure consigliate: usare la riduzione costante e la valutazione delle espressioni in fase di compilazione per generare di piani di query ottimali

Per assicurarsi di generare piani di query ottimali, è consigliabile progettare query, stored procedure e batch in modo che Query Optimizer possa stimare accuratamente la selettività delle condizioni nella query, in base alle statistiche sulla distribuzione dei dati. In caso contrario, Query Optimizer deve usare un valore predefinito durante la stima della selettività.

Per assicurarsi che lo strumento di stima di cardinalità di Query Optimizer fornisca stime corrette, le opzioni database AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS devono essere impostate su ON (impostazione predefinita) o devono essere create manualmente statistiche su tutte le colonne relative a una condizione di query. Quindi, quando si progettano le condizioni nelle query, eseguire le operazioni seguenti quando è possibile:

• Evitare l'uso di variabili locali nelle query, in alternativa usare parametri, valori letterali o espressioni.

• Limitare l'uso di operatori e funzioni incorporate in una query che contiene un parametro a quelli elencati in Valutazione delle espressioni in fase di compilazione per la stima di cardinalità.

• Assicurarsi che le espressioni di sola costante nella condizione della query siano riducibili in modo costante o che possano essere valutate in fase di compilazione.

• Se è necessario usare una variabile locale per valutare un'espressione da usare in una query, valutarla in un ambito diverso rispetto alla query. Ad esempio, potrebbe essere utile eseguire una delle seguenti opzioni:

  • Passare il valore della variabile a una stored procedure contenente la query da valutare e fare in modo che la query usi il parametro procedure anziché una variabile locale.

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

  • Parametrizzare la query ed eseguirla usando sp_executesql e passare il valore della variabile come parametro alla query.

Esempi di miglioramenti della stima di cardinalità

Questa sezione descrive query di esempio che usufruiscono dai miglioramenti implementati nella stima di cardinalità nelle versioni recenti. Si tratta di informazioni in background che non richiedono un'azione specifica da parte dell'utente.

Esempio A. La stima di cardinalità riconosce che il valore massimo potrebbe essere superiore rispetto al momento in cui sono state raccolte le statistiche

Si supponga che le statistiche siano state raccolte per OrderTable in data 2016-04-30, quando il valore massimo OrderAddedDate era 2016-04-30. La stima di cardinalità per i livelli 120 (e successivi) comprende che le colonne in OrderTable con dati in ordine crescente potrebbero contenere valori maggiori rispetto al valore massimo registrato dalle statistiche. Questa consapevolezza migliora il piano di query per le istruzioni SELECT di Transact-SQL come la seguente.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

Esempio B. La stima di cardinalità riconosce che i predicati filtrati nella stessa tabella sono spesso correlati

Nell'istruzione SELECT seguente sono presenti predicati filtrati su Model e ModelVariant. Si comprende intuitivamente che quando Model è "Xbox" esiste la possibilità che ModelVariant sia "One" poiché Xbox ha una variante denominata One.

A partire dalla stima di cardinalità per i livelli 120, SQL Server riconosce che potrebbe esserci una correlazione tra le due colonne nella stessa tabella, ovvero Model e ModelVariant. La stima di cardinalità prevede in modo più preciso quante righe verranno restituite dalla query e Query Optimizer genera un piano più ottimale.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

Esempio C. La stima di cardinalità non presume più alcuna correlazione tra i predicati filtrati da tabelle diverse

Una nuova ricerca estesa su carichi di lavoro moderni e dati di business effettivi rivelano che i filtri del predicato da tabelle diverse in genere non sono correlati tra loro. Nella query seguente la stima di cardinalità presuppone che non esista correlazione tra s.type e r.date. La stima di cardinalità riduce quindi la stima del numero di righe restituite.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';  

Contenuto correlato

• Feedback stima di cardinalità (CE)
• Monitoraggio e ottimizzazione delle prestazioni
• Ottimizzazione dei piani di query con la funzionalità di stima di cardinalità di SQL Server 2014
• Hints (Transact-SQL) - Query
• Hint per le query USE HINT
• Aggiornare i database tramite l’Assistente ottimizzazione query
• Monitorare le prestazioni tramite Query Store
• Guida sull'architettura di elaborazione delle query
• Hint di Query Store
• Elaborazione di query intelligenti nei database SQL
• DBCC TRACEON - Flag di traccia (Transact-SQL)
• sys.query_store_query_hints