Procedure consigliate per l'archivio queryBest Practice with the Query Store

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 argomento descrive le procedure consigliate per l'uso dell'archivio query con il carico di lavoro.This topic outlines the best practices for using the Query Store with your workload.

Usare la versione più recente di SQL Server Management Studio Use the latest SQL Server Management Studio

SQL Server Management StudioSQL Server Management Studio ha un set di interfacce utente progettate per la configurazione dell'archivio query nonché per l'utilizzo dei dati raccolti relativi al carico di lavoro. has set of user interfaces designed for configuring Query Store as well as for consuming collected data about your workload.
Scaricare la versione più recente di Management StudioManagement Studio qui.Download the latest version of Management StudioManagement Studio here.

Per una rapida descrizione dell'uso di Query Store in scenari di risoluzione dei problemi, vedere il post relativo a Query Store nei blog @Azure.For a quick description on how to use Query Store in troubleshooting scenarios refer to Query Store @Azure Blogs.

Usare Informazioni dettagliate prestazioni query nel database SQL di Azure Use Query Performance Insight in Azure SQL Database

Se si esegue l'archivio query in Database SQLSQL Database è possibile usare Informazioni dettagliate prestazioni query per analizzare il consumo di DTU nel tempo.If you run Query Store in Database SQLSQL Database you can use Query Performance Insight to analyze DTU consumption over time.
Anche se è possibile usare Management StudioManagement Studio per ottenere il consumo dettagliato delle risorse per tutte le query, in termini di CPU, memoria, I/O e così via, Informazioni dettagliate prestazioni query offre un metodo rapido ed efficace per determinare l'impatto delle query sul consumo di DTU complessivo per il database.While you can use Management StudioManagement Studio to get detailed resource consumption for all your queries (CPU, memory, IO, etc.), Query Performance Insight gives you a quick and efficient way to determine their impact on overall DTU consumption for your database.
Per altre informazioni, vedere l'articolo relativo a Informazioni dettagliate prestazioni query del database SQL di Azure.For more information, see Azure SQL Database Query Performance Insight.

Usare l'archivio query con pool di database elasticiUsing Query Store with Elastic Pool Databases

È possibile usare Archivio query in tutti i database, anche in pool molto compressi.You can use Query Store in all databases without concerns, in even densely packed pools. Tutti i problemi correlati all'utilizzo eccessivo delle risorse, che possono essersi verificati quando Archivio query era abilitato per un numero elevato di database in pool elastici, sono stati risolti.All issues related to excessive resource usage, that might have occurred when Query Store was enabled for the large number of databases in the Elastic Pools, have been resolved.

Adattare Query Store al proprio carico di lavoro Keep Query Store adjusted to your workload

Configurare l'archivio query in base al carico di lavoro e ai requisiti di risoluzione dei problemi di prestazioni.Configure Query Store based on your workload and performance troubleshooting requirements.
I parametri predefiniti offrono un modo rapido per iniziare, ma è opportuno monitorare il comportamento dell'archivio query nel tempo e regolare la configurazione di conseguenza:The default parameters are good for a quick start but you should monitor how Query Store behaves over time and adjust its configuration accordingly:

query-store-propertiesquery-store-properties

Di seguito sono riportate alcune linee guida per l'impostazione dei valori dei parametri:Here are guidelines to follow for setting parameter values:

Dimensioni massime (MB): specifica il limite per lo spazio dati che l'archivio query può occupare all'interno del database.Max Size (MB): Specifies the limit for the data space that Query Store will take inside your database. Si tratta dell'impostazione più importante, che influisce direttamente sulla modalità di funzionamento dell'archivio query.This is the most important setting that directly affects operation mode of the Query Store.

Mentre l'archivio query raccoglie query, piani di esecuzione e statistiche, le sue dimensioni nel database aumentano fino a quando non viene raggiunto questo limite.While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. A quel punto, l'archivio query passa automaticamente alla modalità operativa di sola lettura e smette di raccogliere nuovi dati. Questo si riflette negativamente sull'accuratezza dell'analisi delle prestazioni.When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new data, which means that your performance analysis is no longer accurate.

Il valore predefinito (100 MB) potrebbe non essere sufficiente se il carico di lavoro genera un numero elevato di query e piani diversi o se si vuole conservare la cronologia delle query per un periodo di tempo più lungo.The default value (100 MB) may not be sufficient if your workload generates large number of different queries and plans or if you want to keep query history for a longer period of time. Tenere traccia dell'utilizzo dello spazio e aumentare le Dimensioni massime (MB) per impedire che l'archivio query passi alla modalità di sola lettura.Keep track of current space usage and increase the Max Size (MB) to prevent Query Store from transitioning to read-only mode. Usare Management StudioManagement Studio o eseguire lo script seguente per ottenere informazioni aggiornate sulle dimensioni dell'archivio query:Use Management StudioManagement Studio or execute the following script to get the latest information about Query Store size:

USE [QueryStoreDB];  
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason  
FROM sys.database_query_store_options;  

Lo script seguente imposta un nuovo valore di Dimensioni massime (MB):The following script sets a new Max Size (MB):

ALTER DATABASE [QueryStoreDB]  
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);  

Intervallo di raccolta statistiche: definisce il livello di granularità delle statistiche di runtime raccolte. Il valore predefinito è 1 ora.Statistics Collection Interval: Defines level of granularity for the collected runtime statistic (the default is 1 hour). È possibile usare un valore inferiore se è necessaria una maggiore granularità o maggiore rapidità nel rilevare e limitare i problemi, ma questo influisce direttamente sulle dimensioni dei dati dell'archivio query.Consider using lower value if you require finer granularity or less time to detect and mitigate issues but keep in mind that it will directly affect the size of Query Store data. Usare SSMS o Transact-SQL per impostare un valore diverso per Intervallo di raccolta statistiche:Use SSMS or Transact-SQL to set different value for Statistics Collection Interval:

ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);  

Soglia per query non aggiornate (giorni): criteri di pulizia basati sul tempo che controllano il periodo di conservazione di statistiche di runtime persistenti e query inattive.Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
Per impostazione predefinita, l'archivio query è configurato in modo da conservare i dati per 30 giorni, che per alcuni scenari potrebbe essere un periodo eccessivamente lungo.By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.

Evitare di conservare i dati cronologici che non si intende usare.Avoid keeping historical data that you do not plan to use. In questo modo è possibile ridurre il ricorso allo stato di sola lettura.This will reduce changes to read-only status. Le dimensioni dei dati dell'archivio query e il tempo necessario per rilevare e limitare il problema saranno più prevedibili.The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Usare Management StudioManagement Studio oppure lo script seguente per configurare i criteri di pulizia basati sul tempo:Use Management StudioManagement Studio or the following script to configure time-based cleanup policy:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));  

Modalità di pulizia basata sulle dimensioni: specifica se viene eseguita la pulizia automatica dei dati quando le dimensioni dei dati dell'archivio query si avvicinano al limite.Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

È consigliabile attivare la pulizia basata sulle dimensioni per assicurarsi che l'archivio query venga sempre eseguito in modalità lettura/scrittura e possa raccoglie i dati più recenti.It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);  

Modalità di acquisizione archivio query: specifica i criteri di acquisizione delle query per l'archivio query.Query Store Capture Mode: Specifies the query capture policy for the Query Store.

  • All : vengono acquisite tutte le query.All – Captures all queries. Si tratta dell'opzione predefinita.This is the default option.

  • Auto : le query poco frequenti e le query con durata di compilazione ed esecuzione non significativa vengono ignorate.Auto – Infrequent queries and queries with insignificant compile and execution duration are ignored. Le soglie per la durata della fase di esecuzione, la compilazione e il conteggio esecuzioni vengono determinate internamente.Thresholds for execution count, compile and runtime duration are internally determined.

  • None l'archivio query smette di acquisire nuove query.None – Query Store stops capturing new queries.

    Lo script seguente imposta la modalità di acquisizione query su Auto:The following script sets the Query Capture mode to Auto:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);  

Iniziare a risolvere i problemi di prestazioni relativi alle queryHow to start with query performance troubleshooting

Il flusso di lavoro di risoluzione dei problemi relativi all'archivio query è semplice, come illustra il diagramma seguente:Troubleshooting workflow with Query Store is simple, as shown on the following diagram:

query-store-troubleshootingquery-store-troubleshooting

Abilitare l'archivio query usando Management StudioManagement Studio come descritto nella sezione precedente o eseguire l'istruzione Transact-SQLTransact-SQL seguente:Enable Query Store by using Management StudioManagement Studio as described in the previous section, or execute the following Transact-SQLTransact-SQL statement:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;  

La raccolta di un set di dati che rappresenti in modo accurato il carico di lavoro da parte dell'archivio query può richiedere del tempo.It will take some time until Query Store collects the data set that accurately represents your workload. In genere, un giorno è sufficiente anche per carichi di lavoro molto complessi.Usually, one day is enough even for very complex workloads. Tuttavia, dopo aver abilitato la funzionalità è possibile iniziare a esplorare i dati e identificare le query che richiedono attenzione immediata.However, you can start exploring the data and identify queries that need your attention immediately after you enabled the feature.
Passare alla sottocartella dell'archivio query nel nodo database in Esplora oggetti di Management StudioManagement Studio per aprire le viste di risoluzione dei problemi per scenari specifici.Navigate to the Query Store sub-folder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.
Le viste dell'archivio query di Management StudioManagement Studio possono essere usate con un set di metriche di esecuzione, espresse come una delle funzioni statistiche seguenti: Management StudioManagement Studio Query Store views operate with the set of execution metrics, each expressed as any of the following statistic functions:

Metrica di esecuzioneExecution metric Funzione statisticaStatistic function
Tempo CPU, Durata, Conteggio esecuzioni, Letture logiche, Scritture logiche, Utilizzo memoria e Letture fisicheCPU time, Duration, Execution Count, Logical Reads, Logical writes, Memory consumption, and Physical Reads Media, Massimo, Minimo, Deviazione standard, TotaleAverage, Maximum, Minimum, Standard Deviation, Total

L'immagine seguente mostra come trovare le viste dell'archivio query:The following graphic shows how to locate Query Store views:

query-store-viewsquery-store-views

La tabella seguente illustra quando usare ognuna delle viste dell'archivio query:The following table explains when to use each of the Query Store views:

Vista di SSMSSSMS view ScenarioScenario
Query regrediteRegressed Queries Trovare le query per cui le metriche di esecuzione sono recentemente peggiorate.Pinpoint queries for which execution metrics have recently regressed (i.e. changed to worse).
Usare questa vista per correlare i problemi di prestazioni osservati nell'applicazione con le query effettive da correggere o migliorare.Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.
Consumo complessivo risorseOverall Resource Consumption Analizzare il consumo totale delle risorse per il database per una delle metriche di esecuzione.Analyze the total resource consumption for the database for any of the execution metrics.
Usare questa vista per identificare gli schemi di consumo delle risorse, ad esempio nei carichi di lavoro diurni e notturni, e ottimizzare il consumo complessivo per il database.Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.
Prime query per consumo risorseTop Resource Consuming Queries Scegliere una metrica di esecuzione di interesse e trovare le query con i valori più estremi in un intervallo di tempo specificato.Choose an execution metric of interest and identify queries that had the most extreme values for a provided time interval.
Usare questa vista per concentrare l'attenzione sulle query più rilevanti che hanno l'impatto maggiore sul consumo delle risorse di database.Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.
Query con piani forzatiQueries With Forced Plans Elenca i piani forzati in precedenza tramite Query Store.Lists previously forced plans using Query Store.
Usare questa vista per accedere rapidamente a tutti i piani attualmente forzati.Use this view to quickly access all currently forced plans.
Query con variazione elevataQueries With High Variation Analizzare le query con variazione di esecuzione elevata in relazione alle dimensioni disponibili, ad esempio Durata, Tempo CPU, I/O e Utilizzo memoria nell'intervallo di tempo desiderato.Analyze queries with high execution variation as it relates to any of the available dimensions, such as Duration, CPU time, IO, and Memory usage in the desired time interval.
Usare questa vista per identificare le query con prestazioni molto variabili che possono influire negativamente sull'esperienza utente in tutte le applicazioni.Use this view to identify queries with widely variant performance that can be impacting user experience across your applications.
Query rilevateTracked Queries Tenere traccia dell'esecuzione delle query più importanti in tempo reale.Track the execution of the most important queries in real-time. In genere, questa vista viene usata in presenza di query con piani forzati per garantire la stabilità delle prestazioni delle query.Typically, you use this view when you have queries with forced plans and you want to make sure that query performance is stable.
Suggerimento

Per informazioni dettagliate sull'uso di Management StudioManagement Studio per identificare le prime query per consumo risorse e correggere le query regredite a causa della modifica di una scelta del piano, vedere i post relativi all'archivio query nei blog di @Azure.For a detailed description how to use Management StudioManagement Studio to identify the top resource consuming queries and fix those that regressed due to the change of a plan choice, see Query Store @Azure Blogs.

Quando si identifica una query con prestazioni non ottimali, l'azione correttiva dipende dalla natura del problema.When you identify a query with sub-optimal performance, your action depends on the nature of the problem.

  • Se la query è stata eseguita con più piani e l'ultimo piano è notevolmente peggiore rispetto al piano precedente, è possibile ricorrere al meccanismo di uso forzato del piano per fare in modo che SQL ServerSQL Server usi sempre il piano ottimale per le esecuzioni future.If the query was executed with multiple plans and the last plan is significantly worse than previous plan, you can use the plan forcing mechanism to force SQL ServerSQL Server to always use the optimal plan for future executions.

    query-store-force-planquery-store-force-plan

Nota

Nella figura sopra riportata sono illustrate forme diverse per piani di query specifici, con i significati seguenti per ogni stato possibile:The above graphic may feature different shapes for specific query plans, with the following meanings for each possible status:

Con formeShape SignificatoMeaning
CircleCircle Query completata (esecuzione normale correttamente completata)Query Completed (Regular Execution successfully finished)
SquareSquare Annullata (esecuzione inizializzata sul lato client interrotta)Cancelled (Client initiated aborted execution)
TriangleTriangle Non riuscita (esecuzione interrotta da un'eccezione)Failed (Exception aborted execution)

Le dimensioni della forma riflettono inoltre il numero di esecuzioni di query nell'intervallo di tempo specificato e aumentano in base al numero di esecuzioni.Also, the size of the shape reflects query execution count within the specified time interval, increasing in size with a higher number of executions.

  • Si può concludere che la query sia priva di un indice per l'esecuzione ottimale.You may conclude that your query is missing an index for optimal execution. Queste informazioni vengono rese disponibili all'interno del piano di esecuzione query.This information is surfaced within the query execution plan. Creare l'indice mancante e verificare le prestazioni della query usando l'archivio query.Create the missing index and check the query performance using the Query Store.

    query-store-show-planquery-store-show-plan

    Se si esegue il carico di lavoro in Database SQLSQL Database, iscriversi a Index Advisor per Database SQLSQL Database per ricevere automaticamente indicazioni relative agli indici.If you run your workload on Database SQLSQL Database, sign up for Database SQLSQL Database Index Advisor to automatically receive index recommendations.

  • In alcuni casi si può applicare la ricompilazione delle statistiche, se c'è una notevole differenza tra il numero stimato di righe nel piano di esecuzione e quello effettivo.In some cases you may enforce statistic recompilation if you see that the difference between the estimated and the actual number of rows in the execution plan is significant.

  • Riscrivere le query problematiche.Rewrite problematic queries. Ad esempio, per sfruttare i vantaggi della parametrizzazione delle query o per implementare la logica ottimale.For example to take advantages of query parameterization or to implement more optimal logic.

Verificare che l'archivio query raccolga i dati delle query in modo continuativo Verify Query Store is Collecting Query Data Continuously

L'archivio query può cambiare automaticamente la modalità operativa.Query Store can silently change operations mode. È necessario monitorare regolarmente lo stato dell'archivio query per assicurarsi che sia in funzione e per prevenire errori dovuti a cause evitabili.You should regularly monitor the state of the Query Store to ensure that the Query Store is operating, and to take action to avoid failures due to preventable causes. Eseguire questa query per determinare la modalità operativa e visualizzare i parametri più importanti:Execute the following query to determine the operation mode and view the most relevant parameters:

USE [QueryStoreDB];  
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

La differenza tra actual_state_desc e desired_state_desc indica che si è verificata una modifica automatica della modalità operativa.The difference between the actual_state_desc and desired_state_desc indicates that a change of operations mode occurred automatically. La modifica più comune è il passaggio automatico dell'archivio query alla modalità di sola lettura.The most common change is for the Query Store to silently switch to read-only mode. Eccezionalmente, l'archivio query può passare a uno stato di errore a causa di errori interni.In extremely rarely circumstances, Query Store can end up in the ERROR state because of internal errors.

Quando lo stato effettivo è di sola lettura, usare la colonna readonly_reason per determinare la causa radice.When the actual state is read-only, use the readonly_reason column to determine the root cause. In genere, l'archivio query passa alla modalità di sola lettura quando viene superato il limite delle dimensioni.Typically you will find that Query Store transitioned to read-only mode because the size quota was exceeded. In questo caso readonly_reason è impostato su 65536.In that case the readonly_reason is set to 65536. Per altre situazioni, vedere sys.database_query_store_options (Transact-SQL).For other reasons, see sys.database_query_store_options (Transact-SQL).

Per riportare l'archivio query in modalità lettura/scrittura e attivare la raccolta dei dati, prendere in considerazione i passaggi seguenti:Consider the following steps to switch Query Store to read-write mode and activate data collection:

  • Aumentare le dimensioni massime dello spazio di archiviazione usando l'opzione MAX_STORAGE_SIZE_MB di ALTER DATABASE.Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE.

  • Eseguire la pulizia dei dati dell'archivio query usando l'istruzione seguente:Clean up Query Store data by using the following statement:

    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;  
    

    È possibile applicare uno o entrambi questi passaggi eseguendo questa istruzione, che ripristina in modo esplicito la modalità lettura/scrittura:You can apply one or both of these steps by the executing the following statement that explicitly changes operation mode back to read-write:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);  

Seguire questa procedura:Take the following steps to be proactive:

  • È possibile prevenire le modifiche automatiche della modalità operativa applicando le procedure consigliate.You can prevent silent changes of operation mode by applying best practices. Facendo in modo che le dimensioni dell'archivio query siano sempre inferiori al valore massimo consentito è possibile ridurre drasticamente il rischio di passaggio alla modalità di sola lettura.If you ensure that Query Store size is always below the maximally allowed value that will dramatically reduce a chance of transitioning to read-only mode. Attivare criteri basati sulle dimensioni, come descritto nella sezione Adattare l'archivio query al carico di lavoro , in modo che l'archivio query esegua automaticamente la pulizia dei dati quando le dimensioni si avvicinano al limite.Activate size based policy as described in the Configure Query Store section, so that the Query Store automatically cleans data when the size approaches the limit.

  • Per fare in modo che vengano conservati i dati più recenti, configurare criteri basati sul tempo per rimuovere regolarmente le informazioni non aggiornate.In order to make sure that most recent data is retained, configure time-based policy to remove stale information regularly.

  • Infine, è consigliabile impostare la modalità di acquisizione query su Auto per escludere le query generalmente meno rilevanti per il carico di lavoro.Finally, you should consider setting Query Capture Mode to Auto as it filters out queries that are usually less relevant for your workload.

Stato di erroreError State

Per recuperare l'archivio query, provare a impostare in modo esplicito la modalità lettura/scrittura e ricontrollare lo stato effettivo.To recover Query Store try explicitly setting the read-write mode and check actual state again.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

Se il problema persiste, il danneggiamento dei dati di Query Store è persistente sul disco.If the problem persists, it indicates corruption of the Query Store data is persisted on the disk.

È possibile recuperare Query Store eseguendo la stored procedure sp_query_store_consistency_check all'interno del database interessato.Query Store could be recovered by executing sp_query_store_consistency_check stored procedure within the affected database.

Se questa soluzione non funziona, si può provare a cancellare il contenuto di Query Store prima di richiedere la modalità lettura/scrittura.If that didn't help, you can try to clear Query Store before requesting read-write mode.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE CLEAR;  
GO  

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;  

Impostare la modalità di acquisizione query ottimaleSet the Optimal Query Capture Mode

Mantenere i dati più rilevanti nell'archivio query.Keep the most relevant data in Query Store. La tabella seguente descrive gli scenari tipici per ogni modalità di acquisizione query:The following table describes typical scenarios for each Query Capture Mode:

Modalità di acquisizione queryQuery Capture Mode ScenarioScenario
AllAll Analizzare accuratamente il carico di lavoro in termini di forme di query, frequenza di esecuzione e altre statistiche.Analyze your workload thoroughly in terms of all queries shapes and their execution frequencies and other statistics.

Identificare le nuove query nel carico di lavoro.Identify new queries in your workload.

Rilevare l'eventuale uso di query ad hoc per identificare le possibilità di parametrizzazione automatica o dell'utente.Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.
AutoAuto Concentrare l'attenzione su query rilevanti e da correggere, ovvero le query eseguite regolarmente o che hanno un consumo di risorse elevato.Focus your attention on relevant and actionable queries; those queries that execute regularly or that have significant resource consumption.
NoneNone Il set di query da monitorare è stato già acquisito in fase di esecuzione e si vuole eliminare qualsiasi distrazione introdotta da altre query.You have already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries may introduce.

È adatta ad ambienti di testing e di benchmarking.None is suitable for testing and bench-marking environments.

È adatta ai fornitori di software che forniscono l'archivio query configurato per il monitoraggio del carico di lavoro della relativa applicazione.None is also appropriate for software vendors who ship Query Store configuration configured to monitor their application workload.

Deve essere usata con cautela perché può precludere la possibilità di rilevare e ottimizzare nuove query importanti.None should be used with caution as you might miss the opportunity to track and optimize important new queries. Evitare di usare questa modalità a meno che non sia richiesta da uno scenario specifico.Avoid using None unless you have a specific scenario that requires it.

Mantenere i dati più rilevanti nell'archivio queryKeep the Most Relevant Data in Query Store

Configurare l'archivio query in modo che contenga solo i dati rilevanti per garantire l'esecuzione ininterrotta e la risoluzione ottimale dei problemi con un impatto minimo sul normale carico di lavoro.Configure the Query Store to contain only the relevant data and it will run continuously providing great troubleshooting experience with a minimal impact on your regular workload.
La tabella seguente riporta le procedure consigliate:The following table provides best practices:

Procedura consigliataBest Practice ImpostazioneSetting
Limitare i dati cronologici conservati.Limit retained historical data. Configurare criteri basati sul tempo per attivare la pulizia automatica.Configure time-based policy to activate auto-cleanup.
Escludere le query non rilevanti.Filter out non-relevant queries. Configurare la modalità di acquisizione query su Auto.Configure Query Capture Mode to Auto.
Eliminare le query meno rilevanti quando vengono raggiunte le dimensioni massime.Delete less relevant queries when maximum size is reached. Attivare criteri di pulizia basati sulle dimensioni.Activate size-based cleanup policy.

Evitare l'uso di query senza parametri Avoid using non-parameterized queries

L'uso di query senza parametri quando non è assolutamente necessario, ad esempio nel caso di analisi ad hoc, non è consigliato.Using non-parameterized queries when that is not absolutely necessary (for example in case of ad-hoc analysis) is not a best practice. Non è possibile riutilizzare i piani memorizzati nella cache e questo impone a Query Optimizer di compilare query per ogni testo query univoco.Cached plans cannot be reused which forces Query Optimizer to compile queries for every unique query text. Per altre informazioni su questo argomento, vedere le linee guida per l'utilizzo della parametrizzazione forzata.For more information on this topic, see Guidelines for Using Forced Parameterization.
L'archivio query può superare rapidamente il limite di dimensioni a causa del numero potenzialmente elevato di testi query diversi e del conseguente numero elevato di piani di esecuzione diversi con forma simile.Also, Query Store can rapidly exceed the size quota because of potentially a large number of different query texts and consequently a large number of different execution plans with similar shape.
Questo influisce negativamente sulle prestazioni del carico di lavoro e l'archivio query potrebbe passare alla modalità di sola lettura o potrebbe eliminare dati continuamente nel tentativo di gestire le query in ingresso.As a result, performance of your workload will be sub-optimal and Query Store might switch to read-only mode or might be constantly deleting the data trying to keep up with the incoming queries.

Valutare le opzioni seguenti:Consider following options:

  • Parametrizzare le query, se applicabile. Ad esempio, eseguire il wrapping delle query all'interno di una stored procedure o sp_executesql.Parameterize queries where applicable, for example wrap queries inside a stored procedure or sp_executesql. Per altre informazioni su questo argomento, vedere Parametri e riutilizzo del piano di esecuzione.For more information on this topic, see Parameters and Execution Plan Reuse.
  • Usare l'opzione Ottimizza per carichi di lavoro ad hoc se il carico di lavoro contiene molti batch ad hoc monouso con piani di query diversi.Use the Optimize for Ad Hoc Workloads option if your workload contains many single use ad-hoc batches with different query plans.

    • Confrontare il numero di valori query_hash distinti con il numero totale di voci in sys.query_store_query.Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. Se il rapporto è vicino a 1, il carico di lavoro ad hoc genera query diverse.If the ratio is close to 1 your ad-hoc workload generates different queries.
  • Applicare la parametrizzazione forzata per il database o per un subset di query, se il numero di piani di query diversi non è elevato.Apply forced parameterization, for the database or for a subset of queries if the number of different query plans is not large.

    • Usare la guida di piano per forzare la parametrizzazione solo per la query selezionata.Use plan guide to force parameterization only for the selected query.

    • Configurare la parametrizzazione forzata in modo che venga usata l'opzione di database Parameterization, se il carico di lavoro contiene un numero ridotto di piani di query diversi: quando il rapporto tra il conteggio di valori query_hash distinti e il numero totale di voci in sys.query_store_query è molto inferiore a 1.Configure forced parameterization as using the Parameterization database option command, if there are a small number of different query plans in your workload: when the ratio between the count of distinct query_hash and the total number of entries in sys.query_store_query is much less than 1.

  • Impostare la Modalità di acquisizione query su Auto per filtrare automaticamente le query ad hoc con un consumo di risorse ridotto.Set the Query Capture Mode to AUTO to automatically filter out ad-hoc queries with small resource consumption.

Evitare il modello DROP e CREATE nella gestione degli oggetti contenitore per le query Avoid a DROP and CREATE pattern when maintaining containing objects for the queries

L'archivio query associa ogni voce query a un oggetto contenitore, ad esempio una stored procedure, una funzione o un trigger.Query Store associates query entry with a containing object (stored procedure, function, and trigger). Quando si ricrea un oggetto contenitore, viene generata una nuova voce query per lo stesso testo query.When you recreate a containing object, a new query entry will be generated for the same query text. Questo impedisce di monitorare le statistiche sulle prestazioni relative a tale query nel tempo e di ricorrere al meccanismo di uso forzato del piano.This will prevent you from tracking performance statistics for that query over time and use plan forcing mechanism. Per evitare questo problema, usare il processo ALTER <object> per modificare la definizione dell'oggetto contenitore, quando è possibile.To avoid this, use the ALTER <object> process to change a containing object definition whenever it is possible.

Verificare regolarmente lo stato dei piani forzati Check the status of Forced Plans regularly

L'uso forzato del piano è un meccanismo efficace per risolvere i problemi di prestazioni delle query critiche e renderle più prevedibili.Plan forcing is a convenient mechanism to fix performance for the critical queries and make them more predictable. Tuttavia, come accade con gli hint di piano e le guide di piano, forzare un piano non garantisce che poi venga usato nelle esecuzioni successive.However, as with plan hints and plan guides, forcing a plan is not a guarantee that it will be used in future executions. In genere, quando lo schema del database viene modificato in modo che gli oggetti a cui fa riferimento il piano di esecuzione vengono modificati o eliminati, l'uso forzato del piano ha esito negativo.Typically, when database schema changes in a way that objects referenced by the execution plan are altered or dropped, plan forcing will start failing. In questo caso SQL ServerSQL Server opta per la ricompilazione delle query, mentre il motivo effettivo dell'errore viene esposto in sys.query_store_plan.In that case SQL ServerSQL Server falls back to query recompilation while the actual forcing failure reason is surfaced in sys.query_store_plan. La query seguente restituisce informazioni sui piani forzati:The following query returns information about forced plans:

USE [QueryStoreDB];  
GO  

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,  
    force_failure_count, last_force_failure_reason_desc  
FROM sys.query_store_plan AS p  
JOIN sys.query_store_query AS q on p.query_id = q.query_id  
WHERE is_forced_plan = 1;  

Per un elenco completo dei motivi, vedere sys.query_store_plan.For full list of reasons, refer to sys.query_store_plan. È anche possibile usare l'oggetto XEvent query_store_plan_forcing_failed per tenere traccia degli errori di uso forzato del piano di risoluzione dei problemi.You can also use the query_store_plan_forcing_failed XEvent to track troubleshoot plan forcing failures.

Evitare di rinominare i database se sono presenti query con piani forzati Avoid renaming databases if you have queries with Forced Plans

I piani di esecuzione fanno riferimento agli oggetti con nomi in tre parti database.schema.object.Execution plans reference objects using three-part names database.schema.object.

Se si rinomina un database, l'uso forzato del piano avrà esito negativo e questo provoca la ricompilazione in tutte le esecuzioni di query successive.If you rename a database, plan forcing will fail which will cause recompilation in all subsequent query executions.

Vedere ancheSee Also

Viste del catalogo di Archivio query (Transact-SQL) Query Store Catalog Views (Transact-SQL)
Stored procedure di Archivio query (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
Uso di Archivio query con OLTP in-memoria Using the Query Store with In-Memory OLTP
Monitoraggio delle prestazioni tramite Archivio query Monitoring Performance By Using the Query Store
Guida sull'architettura di elaborazione delle queryQuery Processing Architecture Guide