Stima della cardinalità (SQL Server)

Si applica a:SQL Serverdatabase SQL di AzureAzure SQL Istanza gestitaDatabase SQL in Microsoft Fabric

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 di un piano di esecuzione della query, denominato 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 gli operatori e per le quali è vera una delle seguenti condizioni:
  • 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 può essere particolarmente vera se l'operatore è qualsiasi operatore diverso dall'operatore di uguaglianza (=).
  • Il predicato usa l'operatore di confronto non uguale a (!=) 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 l'unione di colonne tramite operatori aritmetici o di concatenazione di 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 CE per il proprio sistema. La maggior parte dei sistemi trae vantaggio dalla CE (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 applicativo potrebbe contenere una query importante il cui piano di esecuzione viene modificato in un piano più lento a causa di cambiamenti nella stima della cardinalità (CE) tra le varie versioni. 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 del CE

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 CE si basa su quattro presupposti di base:

• 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 distinti sono equidistanti 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.

• Contenimento (semplice): Gli utenti interrogano i dati esistenti. Ad esempio, per un join di uguaglianza tra due tabelle, tenere conto della selettività dei predicati ¹ in ciascun istogramma di input prima di unire gli istogrammi per stimare la selettività del join.

• Inclusione: per i predicati di filtro dove Column = Constant, si presuppone che la costante sia effettivamente esistente nella colonna associata. Se un passaggio corrispondente dell'istogramma non è vuoto, si presume che uno dei valori distinti del passaggio 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 incorporano ipotesi e algoritmi aggiornati che offrono buoni risultati negli ambienti moderni di data warehousing e nei carichi di lavoro OLTP. Rispetto alle ipotesi di CE 70, a partire da CE 120 sono state modificate le seguenti ipotesi del modello:

• Indipendenza diventa correlazione: la combinazione dei diversi valori di colonna non è necessariamente indipendente. Questo potrebbe assomigliare maggiormente all'interrogazione di dati reali.
• Il contenimento semplice diventa contenimento di base: gli utenti potrebbero eseguire query per i dati che non esistono. Ad esempio, per un join di uguaglianza tra due tabelle, utilizziamo gli istogrammi delle tabelle di base per stimare la selettività del join e poi teniamo conto della selettività dei predicati.

Utilizzare Query Store per valutare la versione dell'estimatore 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 individuare query con costi elevati o con prestazioni peggiorate. Per altre informazioni, vedere Monitorare le prestazioni usando Query Store.

Se si sta preparando un aggiornamento a SQL Server o si aumenta il livello di compatibilità di un database in qualsiasi piattaforma di SQL Server, prendere in considerazione Aggiornare i database usando l'Assistente all'ottimizzazione delle query, che può aiutare a confrontare le prestazioni delle query tra due diversi livelli di compatibilità.

Usare gli eventi estesi per valutare la versione del sistema di 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 di CE

Di seguito sono riportati i passaggi che è possibile seguire per valutare se una qualsiasi delle query più importanti ha prestazioni peggiori con la versione più recente di CE. 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. Assicurati che il tuo database abbia la configurazione LEGACY_CARDINALITY_ESTIMATION 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. Esegui la tua 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 grafico di query.

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 stimato e diverse proprietà Stimate simili che riguardano le prestazioni reali anziché le previsioni del conteggio delle righe.

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

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

9. Confrontare il numero stimato di righe con il numero effettivo di righe. La stima di cardinalità è imprecisa dell'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. Confronta i valori delle proprietà CE delle due esecuzioni.

    • La percentuale di inaccuratezza con il CE più recente è inferiore a quella con il CE precedente?

14. Infine, confronta i vari valori delle proprietà di prestazione ottenuti dalle due esecuzioni.

    • La query ha utilizzato un piano di esecuzione diverso con le due diverse stime CE?

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

    • A meno che la query non funzioni meglio e con un piano diverso con il vecchio stimatore di cardinalità, quasi certamente vorrai usare lo stimatore di cardinalità più recente.

    • Tuttavia, se la query viene eseguita con un piano più veloce con il CE precedente, valuta di forzare il sistema a usare il piano più veloce e a ignorare il CE. In questo modo puoi usare l’ultima versione dell’estimatore di cardinalità per tutto, mantenendo il piano più veloce in quell’unico caso particolare.

Come attivare il piano di query ottimale

Supponiamo che con CE 120 o versione successiva venga generato un piano di query meno efficiente per la tua 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à su 110 o inferiore, si attiva CE 70, ma tutte le query vengono assoggettate al modello CE 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.

• È possibile usare l'opzione di configurazione con ambito database LEGACY_CARDINALITY_ESTIMATION per fare in modo che l'intero database usi il CE precedente, mantenendo al tempo stesso gli altri miglioramenti dell'ottimizzatore di query.

• È possibile usare l'hint per la query LEGACY_CARDINALITY_ESTIMATION per fare in modo che una singola query utilizzi la versione precedente di CE, mantenendo al tempo stesso gli altri miglioramenti nell'ottimizzatore di query.

• È 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.

• Forzare un piano diverso con Query Store.

Livello di compatibilità del database

È possibile assicurarsi che il database sia a un determinato livello usando il codice di Transact-SQL seguente per ALTER DATABASE (Transact-SQL) livello di compatibilità.

SELECT ServerProperty('ProductVersion');
GO

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

Per i database preesistenti in esecuzione a livelli di compatibilità inferiori, purché l'applicazione non debba usare miglioramenti disponibili solo in un livello di compatibilità di database superiore, è un approccio valido per mantenere il livello di compatibilità del database precedente. Per il nuovo lavoro di sviluppo o quando un'applicazione esistente richiede l'uso di nuove funzionalità, ad esempio l'elaborazione di query intelligenti nei database SQL, nonché alcuni nuovi transact-SQL, pianificare l'aggiornamento del livello di compatibilità del database alla versione 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 su un livello di compatibilità pari o superiore a 120, l'attivazione del trace flag 9481 forza il sistema a usare la versione 70 dell'Utilità di stima della cardinalità.

Stimatore di cardinalità precedente

Per un database SQL Server impostato al livello di compatibilità 120 e superiori, lo stimatore di cardinalità legacy (CE versione 70) può essere attivato a livello di database utilizzando ALTER DATABASE SCOPED CONFIGURATION.

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 utilizzare l'hint

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

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

Imposta l’hint di Query Store

Le query possono essere forzate a utilizzare lo stimatore di cardinalità legacy senza modificare la query, tramite gli hint di Query Store.

1. Identificare la query nelle viste del catalogo di Query Store 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 controllo più preciso, è possibile forzare il sistema a usare il piano generato con CE 70 durante i test. Dopo aver aggiunto ai preferiti il piano che preferisci, puoi impostare l'intero database in modo che usi il livello di compatibilità e il CE 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), espandi il nodo Query Store, fai clic con il pulsante destro del mouse su Nodi con il maggiore consumo di risorse e quindi seleziona Visualizza i nodi con il maggiore consumo di risorse. Il display mostra i pulsanti denominati Forza piano e Rimuovi forzatura piano.

Per altre informazioni su Query Store, vedere Monitorare le prestazioni tramite 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 valore letterale Transact-SQL, come 3, 'ABC', '2005-12-31', 1.0e3 oppure 0x12345678. Per ulteriori informazioni, consultare Constant Folding.

Inoltre, alcune espressioni che non sono soggette al constant folding ma i cui argomenti sono noti in fase di compilazione, che gli argomenti siano parametri o costanti, vengono valutate durante l'ottimizzazione dallo stimatore della cardinalità (dimensione del set di risultati) che fa parte di Query Optimizer. Per altre informazioni, vedere Valutazione delle espressioni.

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 della cardinalità di Query Optimizer fornisca stime valide, assicurarsi innanzitutto che le AUTO_CREATE_STATISTICS opzioni di database AUTO_UPDATE_STATISTICS e SET siano ON (impostazione predefinita) o che siano state create manualmente statistiche su tutte le colonne a cui viene fatto riferimento in 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 incluse 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 costituite solo da costanti nella condizione della query siano costantizzabili oppure 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 CE

Questa sezione descrive query di esempio che traggono vantaggio dai miglioramenti apportati alla stima di cardinalità nelle versioni più recenti. Si tratta di informazioni in background che non richiedono un'azione specifica da parte dell'utente.

Esempio A. CE comprende che il valore massimo potrebbe essere superiore a quello registrato quando le statistiche sono state raccolte l'ultima volta

Si supponga che le statistiche siano state raccolte per OrderTable in data 2016-04-30, quando il valore massimo OrderAddedDate era 2016-04-30. CE 120 (e versioni successive) riconosce che le colonne in OrderTable, che contengono dati in ordine crescente, potrebbero avere valori superiori al massimo registrato nelle statistiche. Questa comprensione migliora il piano di query per le istruzioni Transact-SQL SELECT , ad esempio le seguenti.

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

Esempio B. CE comprende che i predicati filtrati nella stessa tabella sono spesso correlati

Nel seguente SELECT vediamo 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 da CE 120, SQL Server comprende che potrebbe esserci una correlazione tra le due colonne della stessa tabella, Model e ModelVariant. Il CE stima con maggiore precisione il numero di righe che verranno restituite dalla query e l'ottimizzatore di query genera un piano di esecuzione migliore.

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

Esempio C. CE non presuppone più alcuna correlazione tra i predicati di filtro provenienti 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, il CE presuppone che non vi sia alcuna correlazione tra s.type e r.date. Pertanto, il CE formula una stima inferiore 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';

Contenuti correlati

• Feedback sulla 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
• Indicazioni per query (Transact-SQL)
• Hint per le query USE HINT
• Aggiornare i database usando l’Assistente per l’ottimizzazione delle query
• Monitorare le prestazioni tramite Query Store
• Guida sull'architettura di elaborazione delle query
• Suggerimenti di Query Store
• Elaborazione di query intelligenti nei database SQL
• Impostare i flag di traccia con DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints