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

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure 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. In genere, maggiore è l'accuratezza della stima della cardinalità, più è ottimale il piano di query.The more accurate the CE, the more optimal the query plan, usually.

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 che viene eseguita con una frequenza tale da determinare l'esecuzione simultanea di più istanze della query.An OLTP 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 della cardinalità nell'ambito di Microsoft SQL Server 7.0, per cui il livello di compatibilità era 70.In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which the compatibility level was 70. Gli aggiornamenti successivi sono stati eseguiti con SQL Server 2014SQL Server 2014 e SQL ServerSQL Server 2016, con livelli di compatibilità 120 e 130.Subsequent updates came with SQL Server 2014SQL Server 2014 and SQL ServerSQL Server 2016, meaning compatibility levels 120 and 130. Gli aggiornamenti della stima di cardinalità per i livelli 120 e 130 integrano presupposti e algoritmi che funzionano bene in carichi di lavoro di data warehouse moderni e in OLTP (elaborazione di transazioni online).The CE updates for levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP (online transaction processing).

Livello di compatibilità: per assicurarsi che il database sia impostato su un determinato livello, è possibile usare il codice Transact-SQL seguente per COMPATIBILITY_LEVEL.Compatibility level: You can ensure your database is at a particular level by using the following Transact-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 Server impostato sul livello di compatibilità 120, l'attivazione del flag di traccia 9481 impone al sistema di usare la stima della cardinalità per il livello 70.For a SQL Server database set at compatibility level 120, activation of the trace flag 9481 forces the system to use the CE for level 70.

Stima di cardinalità legacy: per un database di SQL Server impostato sul livello di compatibilità 130, è possibile attivare la stima della cardinalità di livello 70 usando l'istruzione Transact-SQL seguente relativa a SCOPED CONFIGURATION .Legacy CE: For a SQL Server database set at compatibility level 130, the level 70 CE can be can be activated by using the following Transact-SQL statement about 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';  

Archivio query:a partire da SQL ServerSQL Server 2016 l'archivio query è uno strumento utile per esaminare le prestazioni delle query.Query store:Starting with SQL ServerSQL Server 2016 the query store is a handy tool for examining the performance of your queries. In SQL Server Management Studio (SSMS.exe), in Esplora oggetti nel nodo del database, viene visualizzato un nodo Archivio query quando l'archivio query è impostato su ON.In SQL Server Management Studio (SSMS.exe), in the Object Explorer under your database node, a Query Store node is displayed when the query store is ON.

ALTER DATABASE <yourDatabase>  
    SET QUERY_STORE = ON;  
go  

SELECT  
        q.actual_state_desc    AS [actual_state_desc-ofQueryStore],  
        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: è consigliabile installare la versione più recente di (SSMS.exe)ogni mese.Tip: We recommend that each month you install the latest release of (SSMS.exe).

Un'altra opzione per tenere traccia delle stime di cardinalità consiste nell'usare l'evento esteso denominato query_optimizer_estimate_cardinality.Another option for tracking the cardinality predictions of the CE is to use the extended event named query_optimizer_estimate_cardinality. Il seguente codice T-SQL di esempio viene eseguito su SQL ServerSQL Server.The following T-SQL code sample runs on SQL ServerSQL Server. Scrive un file con estensione xel in C:\Temp\ (percorso modificabile).It writes a .xel file to C:\Temp\ (although you can change the path). Quando si apre il file con estensione xel in SQL Server Management Studio, i relativi dettagli vengono visualizzati in modo intuitivo.When you open the .xel file in SSMS, 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 appositi per il database SQL di Azure, vedere Eventi estesi nel database SQL.For information about extended events as tailored for Azure SQL 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 SSMS.Open SSMS. Assicurarsi che il database di SQL ServerSQL Serversia 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 SSMS.Open SSMS.

    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;</span><span class="sxs-lookup"><span data-stu-id="3cc01-140">Run the statement: \SET NoCount OFF;`

  3. Eseguire l'istruzione: `SET STATISTICS XML ON;</span><span class="sxs-lookup"><span data-stu-id="3cc01-141">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;</span><span class="sxs-lookup"><span data-stu-id="3cc01-157">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 nuova stima di cardinalità venga generato un piano di query più lento per la query.Suppose that with the new CE a slower query plan is generated for your query. Ecco alcune opzioni per attivare il piano più veloce.Here are some options you have to activate the faster plan.

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

  • In questo modo viene attivata la stima di cardinalità precedente, ma per tutte le query viene usata la stima di cardinalità precedente e meno accurata.This activates the older CE, but it makes all queries subject to the older and less accurate CE.

  • Inoltre, il livello precedente non può usufruire degli importanti miglioramenti apportati a Query Optimizer.Further the older level also loses excellent improvements in the query optimizer.

Per fare in modo che l'intero database usi la stima di cardinalità precedente, mantenendo i miglioramenti in Query Optimizer, è possibile usare LEGACY_CARDINALITY_ESTIMATION.You could use LEGACY_CARDINALITY_ESTIMATION to have the whole database use the older CE, while retaining the improvements in the query optimizer.

Per il massimo controllo, è possibile forzare il sistema SQL in modo da usare il piano generato con la stima di cardinalità precedente durante i test.The the finest control, you could force the SQL system to use the plan that was generated with the older CE 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 SSMS espandere Archivio Query , fare clic con il pulsante destro del mouse su Top Resource Consuming Nodes(Primi nodi per consumo risorse) e scegliere View Top Resource Consuming Nodes(Visualizza primi nodi per consumo risorse).In the SSMS, 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.

Descrizioni delle stime di cardinalità avanzateDescriptions of advance CE

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 per OrderTable siano state raccolte per l'ultima volta il 30-04-2016, quando il valore massimo per OrderAddedDate era 30-04-2016.Suppose statistics were last gathered for OrderTable on 2016-04-30, when the maximum OrderAddedDate was 2016-04-30. La stima di cardinalità per il livello di compatibilità 120 (e livelli più elevati) comprende che le colonne in OrderTable con dati in ordine crescente potrebbero contenere valori maggiori rispetto al valore massimo registrato dalle statistiche.The CE for compatibility level 120 (and for higher levels) 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 SQL SELECT come la seguente.This understanding improves the query plan for SQL SELECTs 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 Make e Model.In the following SELECT we see filtered predicates on Make and Model. Intuitivamente, si comprende che quando la marca è "Honda" è probabile che il modello sia "Civic", dato che la Civic è prodotta da Honda.We intuitively understand that when Make is 'Honda' there is a chance the Model is 'Civic', given that Honda makes the Civic.

Il livello 120 della stima di cardinalità riconosce che potrebbe esserci una correlazione tra le due colonne per marca e modello nella stessa tabella, ovvero Make e Model.The level 120 CE understands there might be a correlation between the two columns on the same table, Make and Model. 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_Year, Purchase_Price  
    FROM dbo.Cars  
    WHERE  
        Make  = 'Honda'  AND  
        Model = 'Civic';  

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 alcuna 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 prestazioniMonitor and Tune for Performance