Stima della cardinalità (SQL Server)Cardinality Estimation (SQL Server)

QUESTO ARGOMENTO SI APPLICA A: sìSQL ServersìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo articolo illustra come valutare e scegliere la migliore configurazione di stima della cardinalità per il sistema SQL.This article illustrates how you can assess and choose the best cardinality estimation (CE) configuration for your SQL system. Per la maggior parte dei sistemi è disponibile la stima della cardinalità più recente perché è la più accurata.Most systems benefit from the latest CE because it is the most accurate. La stima della cardinalità prevede il numero di righe che verranno probabilmente restituite dalla query.The CE predicts how many rows your query will likely return. La stima della cardinalità è usata da Query Optimizer per generare il piano di query ottimale.The cardinality prediction is used by the Query Optimizer to generate the optimal query plan. Con stime più accurate, Query Optimizer è in genere in grado di produrre un piano di query migliore.With more accurate estimations, the Query Optimizer can usually do a better job of producing a more optimal query plan.

Il sistema di applicazioni può includere una query importante il cui piano viene configurato su un piano più lento a causa della nuova stima di cardinalità.Your application system could possibly have an important query whose plan is changed to a slower plan due to the new CE. Una query di questo tipo potrebbe essere simile quanto segue:Such a query might be like one of the following:

  • Una query OLTP (elaborazione di transazioni online) che viene eseguita con una frequenza tale da determinare l'esecuzione simultanea di più istanze della query.An OLTP (online transaction processing) query that runs so frequently that multiple instance of it often run concurrently.
  • Un'istruzione SELECT con aggregazione sostanziale che viene eseguita durante l'orario lavorativo di OLTP.A SELECT with substantial aggregation that runs during your OLTP business hours.

Sono implementate tecniche per identificare una query che risulta più lenta con la nuova stima della cardinalità.You have techniques for identifying a query that performs slower with the new CE. Inoltre, sono disponibili opzioni per la risoluzione del problema di prestazioni.And you have options for how to address the performance issue.

Versioni della stima della cardinalitàVersions of the CE

Nel 1998 è stato incluso un aggiornamento importante della stima di cardinalità in SQL ServerSQL Server 7.0, per cui il livello di compatibilità era 70.In 1998, a major update of the CE was part of SQL ServerSQL Server 7.0, for which the compatibility level was 70. Questa versione del modello di stima di cardinalità si basa su quattro presupposti:This version of the CE model is set on four basic assumptions:

  • 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.Independence: Data distributions on different columns are assumed to be independent of each other, unless correlation information is available and usable.
  • Uniformità: i valori distinct sono divisi uniformemente e hanno tutti la stessa frequenza.Uniformity: Distinct values are evenly spaced and that they all have the same frequency. Più precisamente, all'interno di ogni intervallo dell'istogramma, i valori distinct sono distribuiti uniformemente e hanno tutti la stessa frequenza.More precisely, within each histogram step, distinct values are evenly spread and each value has same frequency.
  • Indipendenza (semplice): gli utenti eseguono una query per i dati esistenti.Containment (Simple): Users query for data that exists. Ad esempio, per un join di uguaglianza tra due tabelle, considerare la selettività di join 1 in ogni istogramma di input prima di creare un join di istogrammi e stimarne la selettività.For example, for an equality join between two tables, factor in the predicates selectivity1 in each input histogram, before joining histograms to estimate the join selectivity.
  • Inclusione: per i predicati di filtro dove Column = Constant, si presuppone che la costante sia effettivamente esistente nella colonna associata.Inclusion: For filter predicates where Column = Constant, the constant is assumed to actually exist for the associated column. Se un intervallo dell'istogramma corrispondente non è vuoto, si presuppone che uno dei valori distinct dell'intervallo corrisponda al valore del predicato.If a corresponding histogram step is non-empty, one of the step's distinct values is assumed to match the value from the predicate.

    1 Numero di righe che soddisfa il predicato.1 Row count that satisfies the predicate.

Gli aggiornamenti successivi sono iniziati con SQL Server 2014 (12.x)SQL Server 2014 (12.x), con livelli di compatibilità 120 e superiori.Subsequent updates started with SQL Server 2014 (12.x)SQL Server 2014 (12.x), meaning compatibility levels 120 and above. 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.The CE updates for levels 120 and above incorporate updated assumptions and algorithms that work well on modern data warehousing and on OLTP workloads. 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:From the CE 70 assumptions, the following model assumptions were changed starting with CE 120:

  • Indipendenza diventa correlazione: la combinazione dei diversi valori di colonna che non sono necessariamente indipendenti.Independence becomes Correlation: The combination of the different column values are not necessarily independent. Può assomigliare all'esecuzione di query sui dati più reali.This may resemble more real-life data querying.
  • Indipendenza semplice diventa contenimento di base: gli utenti possono eseguire la query per dati che non esistono.Simple Containment becomes Base Containment: Users might query for data that does not exist. 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.For example, for an equality join between two tables, we use the base tables histograms to estimate the join selectivity, and then factor in the predicates selectivity.

Livello di compatibilità: per assicurarsi che il database sia impostato su un determinato livello, è possibile usare il codice Transact-SQLTransact-SQL seguente per COMPATIBILITY_LEVEL.Compatibility level: You can ensure your database is at a particular level by using the following Transact-SQLTransact-SQL code for COMPATIBILITY_LEVEL.

SELECT ServerProperty('ProductVersion');  
GO  

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 130;  
GO  

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

Per un database di SQL ServerSQL 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à per i livelli 70.For a SQL ServerSQL Server database set at compatibility level 120 or above, activation of the trace flag 9481 forces the system to use the CE version 70.

Stima di cardinalità legacy: per un database di SQL ServerSQL Server impostato sul livello di compatibilità 120 o superiore, è possibile attivare la stima di cardinalità per i livelli 70 a livello di database usando l'istruzione ALTER DATABASE SCOPED CONFIGURATION.Legacy CE: For a SQL ServerSQL Server database set at compatibility level 120 and above, the CE version 70 can be can be activated by using the at the database level by using the 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

Oppure, a partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, l'hint per la query USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').Or starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1, the Query Hint USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

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

Query Store: a partire da SQL Server 2016 (13.x)SQL Server 2016 (13.x), Query Store è uno strumento utile per esaminare le prestazioni delle query.Query store: Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the query store is a handy tool for examining the performance of your queries. In Management StudioManagement Studio in Esplora oggetti nel nodo del database viene visualizzato un nodo Query Store quando Query Store è abilitato.In Management StudioManagement Studio, in the Object Explorer under your database node, a Query Store node is displayed when the query store is enabled.

ALTER DATABASE <yourDatabase>  
SET QUERY_STORE = ON;  
GO  

SELECT q.actual_state_desc AS [actual_state_desc_of_QueryStore],  
        q.desired_state_desc,  
        q.query_capture_mode_desc  
FROM sys.database_query_store_options AS q;  
GO  

ALTER DATABASE <yourDatabase>  
SET QUERY_STORE CLEAR;  

Suggerimento

Si consiglia di installare la versione più recente di Management Studio e di aggiornarlo spesso.We recommend that you install the latest release of Management Studio and update it often.

Un'altra opzione per tenere traccia del processo relativo alle stime di cardinalità consiste nell'usare l'evento esteso denominato query_optimizer_estimate_cardinality.Another option for tracking the cardinality estimation process is to use the extended event named query_optimizer_estimate_cardinality. Il codice di esempio SQL ServerSQL Server seguente viene eseguito su Transact-SQLTransact-SQL.The following Transact-SQLTransact-SQL code sample runs on SQL ServerSQL Server. Scrive un file con estensione xel in C:\Temp\. Il percorso è comunque modificabile.It writes a .xel file to C:\Temp\ (although you can change the path). Quando si apre il file con estensione xel in Management StudioManagement Studio, le informazioni dettagliate sono visualizzate in modo intuitivo.When you open the .xel file in Management StudioManagement Studio, its detailed information is displayed in a user friendly manner.

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 specifici per Database SQLSQL Database, vedere Eventi estesi nel database SQL.For information about extended events as tailored for Database SQLSQL Database, see Extended events in SQL Database.

Procedura per valutare la versione della stima di cardinalitàSteps to assess the CE version

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.Next are steps you can use to assess whether any of your most important queries perform less well under the latest CE. Alcuni dei passaggi vengono completati eseguendo un esempio di codice presentato in una sezione precedente.Some of the steps are performed by running a code sample presented in a preceding section.

  1. Aprire Management StudioManagement Studio.Open Management StudioManagement Studio. Assicurarsi che il database di SQL ServerSQL Server sia impostato sul massimo livello di compatibilità disponibile.Ensure your SQL ServerSQL Serverdatabase is set to the highest available compatibility level.

  2. Seguire questa procedura preliminare:Perform the following preliminary steps:

    1. Aprire Management StudioManagement Studio.Open Management StudioManagement Studio.

    2. Eseguire T-SQL per assicurarsi che il database di SQL ServerSQL Server sia impostato sul massimo livello di compatibilità disponibile.Run the T-SQL to ensure that your SQL ServerSQL Server database is set to the highest available compatibility level.

    3. Assicurarsi che la configurazione LEGACY_CARDINALITY_ESTIMATION del database sia impostata su OFF.Ensure that your database has its LEGACY_CARDINALITY_ESTIMATION configuration turned OFF.

    4. Eseguire un'istruzione CLEAR per cancellare il contenuto dell'archivio query.CLEAR your query store. Naturalmente, verificare che l'archivio query sia impostato su ON.Of course, ensure your query store is ON.

    5. Eseguire l'istruzione: SET NOCOUNT OFF;Run the statement: SET NOCOUNT OFF;

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

  4. Eseguire la query importante.Run your important query.

  5. Nel riquadro dei risultati, nella scheda Messaggi , notare il numero effettivo di righe interessate.In the results pane, on the Messages tab, note the actual number of rows affected.

  6. Nel riquadro dei risultati, nella scheda Risultati , fare doppio clic sulla cella che contiene le statistiche in formato XML.In the results pane on the Results tab, double-click the cell that contains the statistics in XML format. Viene visualizzato un piano di query grafico.A graphic query plan is displayed.

  7. Fare clic con il pulsante destro del mouse nella prima casella del piano di query grafico e scegliere Proprietà.Right-click the first box in the graphic query plan, and then click Properties.

  8. Per un successivo confronto con una configurazione diversa, notare i valori per le proprietà seguenti:For later comparison with a different configuration, note the values for the following properties:

    • CardinalityEstimationModelVersion.CardinalityEstimationModelVersion.

    • Numero stimato di righe.Estimated Number of Rows.

    • Costo I/O stimatoStimato simili che riguardano le prestazioni effettive anziché le previsioni del numero di righe.Estimated I/O Cost, and several similar Estimated properties that involve actual performance rather than row count predictions.

    • Operazione logica e Operazione fisica.Logical Operation and Physical Operation. Parallelismo è un valore adeguato.Parallelism is a good value.

    • Modalità di esecuzione effettiva.Actual Execution Mode. Batch è un valore adeguato, migliore di Riga.Batch is a good value, better than Row.

  9. Confrontare il numero stimato di righe con il numero effettivo di righe.Compare the estimated number of rows to the actual number of rows. La stima di cardinalità è imprecisa del 1% (per eccesso o per difetto) o del 10%?Is the CE inaccurate by 1% (high or low), or by 10%?

  10. Eseguire: SET STATISTICS XML OFF;Run: SET STATISTICS XML OFF;

  11. Eseguire l'istruzione T-SQL per abbassare il livello di compatibilità del database di un livello (ad esempio da 130 a 120).Run the T-SQL to decrease the compatibility level of your database by one level (such as from 130 down to 120).

  12. Eseguire di nuovo tutti i passaggi non preliminari.Rerun all the non-preliminary steps.

  13. Confrontare i valori delle proprietà della stima di cardinalità delle due esecuzioni.Compare the CE property values from the two runs.

    • La percentuale di imprecisione con la stima di cardinalità più recente è inferiore rispetto a quella meno recente?Is the inaccuracy percentage under the newest CE less than under the older CE?
  14. Infine, confrontare i vari valori delle proprietà delle prestazioni delle due esecuzioni.Finally, compare the various performance property values from the two runs.

    • La query ha usato un piano diverso nelle due stime di cardinalità?Did your query use a different plan under the two differing CE estimations?

    • La query è risultata più lenta con la stima di cardinalità più recente?Did your query run slower under the latest CE?

    • 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.Unless your query runs better and with a different plan under the older CE, you almost certainly want the latest CE.

    • 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à.However, if your query runs with a faster plan under the older CE, consider forcing the system to use the faster plan and to ignore the CE. In questo modo si può usare la stima di cardinalità più recente per tutto, mantenendo il piano più veloce nel caso particolare.This way you can have the latest CE on for everything, while keeping the faster plan in the one odd case.

Come attivare il piano di query ottimaleHow to activate the best query plan

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.Suppose that with CE 120 or above, a less efficient query plan is generated for your query. Si seguito sono riportate alcune opzioni per attivare il piano migliore:Here are some options you have to activate the better plan:

  1. È possibile impostare il livello di compatibilità su un valore inferiore rispetto a quello più recente, per l'intero database.You could set the compatibility level to a value lower than the latest available, for your whole 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.For example, setting the compatibility level 110 or lower activates CE 70, but it makes all queries subject to the previous CE model.

    • Impostando un livello di compatibilità inferiore viene mancare anche una serie di miglioramenti in Query Optimizer per le versioni più recenti.Further, setting a lower compatibility level also misses a number of improvements in the query optimizer for latest versions.

  2. 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 database LEGACY_CARDINALITY_ESTIMATION.You could use LEGACY_CARDINALITY_ESTIMATION database option, to have the whole database use the older CE, while retaining other improvements in the query optimizer.

  3. 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.You could use LEGACY_CARDINALITY_ESTIMATION query hint, to have a single query use the older CE, while retaining other improvements in the query optimizer.

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.For the finest control, you could force the system to use the plan that was generated with CE 70 during your testing. Dopo avere aggiunto il piano preferito, è possibile impostare l'intero database per l'uso della compatibilità e della stima di cardinalità più recenti.After you pin your preferred plan, you can set your whole database to use the latest compatibility level and CE. L'opzione viene elaborata successivamente.The option is elaborated next.

Come forzare un piano di query specificoHow to force a particular query plan

L'archivio query offre diversi modi per forzare l'uso di un determinato piano di query:The query store gives you different ways that you can force the system to use a particular query plan:

  • Eseguire sp_query_store_force_plan.Execute sp_query_store_force_plan.

  • In Management StudioManagement Studio espandere il nodo Query Store, fare clic con il pulsante destro del mouse su Top Resource Consuming Nodes (Primi nodi per consumo di risorse) e quindi su View Top Resource Consuming Nodes (Visualizza primi nodi per consumo di risorse).In Management StudioManagement Studio, expand your Query Store node, right-click Top Resource Consuming Nodes, and then click View Top Resource Consuming Nodes. Verranno visualizzati i pulsanti Forza piano e Annulla forzatura piano.The display shows buttons labeled Force Plan and Unforce Plan.

Per altre informazioni sull'archivio query, vedere Monitoraggio delle prestazioni con Archivio query.For more information about the query store, see Monitoring Performance By Using the Query Store.

Esempi di miglioramenti della stima di cardinalitàExamples of CE improvements

Questa sezione descrive query di esempio che usufruiscono dai miglioramenti implementati nella stima di cardinalità nelle versioni recenti.This section describes example queries that benefit from the enhancements implemented in the CE in recent releases. Si tratta di informazioni in background che non richiedono un'azione specifica da parte dell'utente.This is background information that does not call for specific action on your part.

Esempio A. La stima di cardinalità riconosce che il valore massimo potrebbe essere superiore rispetto al momento in cui sono state raccolte le statisticheExample A. CE understands maximum value might be higher than when statistics were last gathered

Si supponga che le statistiche siano state raccolte per OrderTable in data 2016-04-30, quando il valore massimo OrderAddedDate era 2016-04-30.Suppose statistics were last gathered for OrderTable on 2016-04-30, when the maximum OrderAddedDate was 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.The CE 120 (and above version) understands that columns in OrderTable which have ascending data might have values larger than the maximum recorded by the statistics. Questa consapevolezza migliora il piano di query per le istruzioni SELECT di Transact-SQLTransact-SQL come la seguente.This understanding improves the query plan for Transact-SQLTransact-SQL SELECT statements such as the following.

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 correlatiExample B. CE understands that filtered predicates on the same table are often correlated

Nell'istruzione SELECT seguente sono presenti predicati filtrati su Model e ModelVariant.In the following SELECT we see filtered predicates on Model and ModelVariant. Si comprende intuitivamente che quando Model è "Xbox" esiste la possibilità che ModelVariant sia "One" poiché Xbox ha una variante denominata One.We intuitively understand that when Model is 'Xbox' there is a chance the ModelVariant is 'One', given that Xbox has a variant called One.

A partire dalla stima di cardinalità per i livelli 120, SQL ServerSQL Server riconosce che potrebbe esserci una correlazione tra le due colonne nella stessa tabella, ovvero Model e ModelVariant.Starting with CE 120, SQL ServerSQL Server understands there might be a correlation between the two columns on the same table, Model and ModelVariant. La stima di cardinalità prevede in modo più preciso quante righe verranno restituite dalla query e Query Optimizer genera un piano più ottimale.The CE makes a more accurate estimation of how many rows will be returned by the query, and the query optimizer generates a more optimal plan.

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

Esempio C. La stima di cardinalità non presume più alcuna correlazione tra i predicati filtrati da tabelle diverseExample C. CE no longer assumes any correlation between filtered predicates from different tables

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.With extense new research on modern workloads and actual business data reveal that predicate filters from different tables usually do not correlate with each other. Nella query seguente la stima di cardinalità presuppone che non esista correlazione tra s.type e r.date.In the following query, the CE assumes there is no correlation between s.type and r.date. La stima di cardinalità riduce quindi la stima del numero di righe restituite.Therefore the CE makes a lower estimate of the number of rows returned.

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';  

Vedere ancheSee Also

Monitoraggio e ottimizzazione delle prestazioni Monitor and Tune for Performance
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator (Ottimizzazione dei piani di query con la stima di cardinalità di SQL Server 2014)Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
Query Hints (Hint per la query)Query Hints
Hint per le query USE HINT USE HINT Query Hints
Monitoraggio delle prestazioni tramite Archivio query Monitoring Performance By Using the Query Store
Guida sull'architettura di elaborazione delle queryQuery Processing Architecture Guide