Procedure consigliate per Query StoreBest practices with Query Store

Si applica a:Applies to: sìSQL ServerSQL Server (tutte le versioni supportate) yesSQL ServerSQL Server (all supported versions) Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL Database SìIstanza gestita di SQL di AzureAzure SQL Managed InstanceYesIstanza gestita di SQL di AzureAzure SQL Managed Instance sìAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsSi applica a:Applies to: sìSQL ServerSQL Server (tutte le versioni supportate) yesSQL ServerSQL Server (all supported versions) Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL Database SìIstanza gestita di SQL di AzureAzure SQL Managed InstanceYesIstanza gestita di SQL di AzureAzure SQL Managed Instance sìAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

Questo articolo descrive le procedure consigliate per l'uso di SQL Server Query Store con un carico di lavoro.This article outlines the best practices for using SQL Server Query Store with your workload.

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

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

Per una rapida descrizione di come usare Query Store in scenari di risoluzione dei problemi, vedere i post relativi a Query Store nei blog di Azure.For a quick description on how to use Query Store in troubleshooting scenarios, see Query Store Azure blogs.

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

Se si esegue Query Store nel database SQL di AzureAzure SQL Database, è possibile usare Informazioni dettagliate prestazioni query per analizzare il consumo delle risorse nel tempo.If you run Query Store in database SQL di AzureAzure SQL Database, you can use Query Performance Insight to analyze resource consumption over time. Anche se è possibile usare Management StudioManagement Studio e Azure Data Studio per ottenere il consumo dettagliato delle risorse per tutte le query, ad esempio CPU, memoria e I/O, 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 and Azure Data Studio to get detailed resource consumption for all your queries, such as CPU, memory, and I/O, 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.

Questa sezione descrive impostazioni di configurazione predefinite ottimali progettate per garantire un funzionamento affidabile di Query Store e delle funzionalità dipendenti.This section describes optimal configuration defaults that are designed to ensure reliable operation of the Query Store and dependent features. La configurazione predefinita è ottimizzata per la raccolta di dati continua, ossia per un tempo minimo di OFF/READ_ONLY.Default configuration is optimized for continuous data collection, that is minimal time spent in OFF/READ_ONLY states. Per altre informazioni su tutte le opzioni di Query Store disponibili, vedere Opzioni ALTER DATABASE SET (Transact-SQL).For more information about all available Query Store options, see ALTER DATABASE SET options (Transact-SQL).

ConfigurazioneConfiguration DescrizioneDescription PredefinitoDefault CommentComment
MAX_STORAGE_SIZE_MBMAX_STORAGE_SIZE_MB Specifica il limite per lo spazio dati che Query Store occupa all'interno del database del clienteSpecifies the limit for the data space that Query Store can take inside the customer database 100100 Applicato per i nuovi databaseEnforced for new databases
INTERVAL_LENGTH_MINUTESINTERVAL_LENGTH_MINUTES Definisce la dimensione dell'intervallo di tempo durante il quale le statistiche di runtime raccolte per i piani di query vengono aggregate e rese persistenti.Defines size of time window during which collected runtime statistics for query plans are aggregated and persisted. Tutti i piani di query attivi hanno al massimo una riga per un periodo di tempo definito con questa configurazioneEvery active query plan has at most one row for a period of time defined with this configuration 6060 Applicato per i nuovi databaseEnforced for new databases
STALE_QUERY_THRESHOLD_DAYSSTALE_QUERY_THRESHOLD_DAYS Criterio di pulizia basato sul tempo che controlla il periodo di memorizzazione delle statistiche di runtime persistenti e delle query inattiveTime-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries 3030 Applicato per i nuovi database e i database con un'impostazione predefinita precedente (367)Enforced for new databases and databases with previous default (367)
SIZE_BASED_CLEANUP_MODESIZE_BASED_CLEANUP_MODE Specifica se la pulizia automatica dei dati viene eseguita quando la dimensione dati dell'archivio query si avvicina al limiteSpecifies whether automatic data cleanup takes place when Query Store data size approaches the limit AUTOAUTO Applicato per tutti i databaseEnforced for all databases
QUERY_CAPTURE_MODEQUERY_CAPTURE_MODE Specifica se vengono monitorate tutte le query o solo un sottoinsieme di esseSpecifies whether all queries or only a subset of queries are tracked AUTOAUTO Applicato per tutti i databaseEnforced for all databases
FLUSH_INTERVAL_SECONDSFLUSH_INTERVAL_SECONDS Specifica il periodo massimo durante il quale le statistiche di runtime acquisite vengono mantenute in memoria prima di essere scaricate su discoSpecifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk 900900 Applicato per i nuovi databaseEnforced for new databases

Importante

I valori predefiniti vengono applicati automaticamente nella fase finale dell'attivazione di Query Store in tutti i database SQL di AzureAzure SQL Database.These defaults are automatically applied in the final stage of Query Store activation in all database SQL di AzureAzure SQL Database. Dopo l'abilitazione, il database SQL di AzureAzure SQL Database non modificherà i valori di configurazione impostati dai clienti, a meno che non abbiano un impatto negativo sul carico di lavoro primario o sulle operazioni affidabili di Query Store.After it's enabled, database SQL di AzureAzure SQL Database won't change configuration values that are set by customers, unless they negatively impact primary workload or reliable operations of the Query Store.

Nota

Non è possibile disabilitare Query Store in database singolo database SQL di AzureAzure SQL Database e in un pool elastico.Query Store cannot be disabled in database SQL di AzureAzure SQL Database single database and Elastic Pool. L'esecuzione di ALTER DATABASE [database] SET QUERY_STORE = OFF restituirà l'avviso 'QUERY_STORE=OFF' is not supported in this version of SQL Server..Executing ALTER DATABASE [database] SET QUERY_STORE = OFF will return the warning 'QUERY_STORE=OFF' is not supported in this version of SQL Server..

Se si desidera mantenere le impostazioni personalizzate, usare ALTER DATABASE con le opzioni dell'archivio query per riportare la configurazione allo stato precedente.If you want to stay with your custom settings, use ALTER DATABASE with Query Store options to revert configuration to the previous state. Vedere Procedure consigliate per Query Store per informazioni su come scegliere i parametri di configurazione ottimali.Check out Best Practices with the Query Store in order to learn how to choose optimal configuration parameters.

Usare Query Store con database di pool elasticiUse 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 Query Store era abilitato per un numero elevato di database nei 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 lavoroKeep 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 sono sufficienti per iniziare, ma è opportuno monitorare il comportamento di Query Store nel tempo e adattare la configurazione di conseguenza.The default parameters are good enough to start, but you should monitor how Query Store behaves over time and adjust its configuration accordingly.

Proprietà di Query StoreQuery 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 Query Store occupa all'interno del database.Max Size (MB): Specifies the limit for the data space that Query Store takes inside your database. Si tratta dell'impostazione più importante, che influisce direttamente sulla modalità di funzionamento di Query Store.This is the most important setting that directly affects the operation mode of Query Store.

Mentre Query Store 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 in SQL Server 2016 (13.x)SQL Server 2016 (13.x) e SQL Server 2017 (14.x)SQL Server 2017 (14.x) è 100 MB.The default value in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x) is 100 MB. Queste dimensioni potrebbero non essere sufficienti 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.This size might not be sufficient if your workload generates a large number of different queries and plans or if you want to keep query history for a longer period of time. A partire da SQL Server 2019 (15.x)SQL Server 2019 (15.x), il valore predefinito è 1 GB.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the default value is 1 GB. Tenere traccia dell'utilizzo dello spazio e aumentare le Dimensioni massime (MB) per impedire che Query Store passi alla modalità di sola lettura.Keep track of current space usage and increase the Max Size (MB) value to prevent Query Store from transitioning to read-only mode.

Importante

Il limite Dimensioni massime (MB) non è necessariamente applicato.The Max Size (MB) limit isn't strictly enforced. Le dimensioni di archiviazione vengono controllate solo quando Query Store scrive i dati su disco.Storage size is checked only when Query Store writes data to disk. Questo intervallo viene impostato dall'opzione Intervallo di scaricamento dati (minuti) .This interval is set by the Data Flush Interval (Minutes) option. Se Query Store ha violato il limite di dimensioni massime tra i controlli delle dimensioni di archiviazione, passa alla modalità di sola lettura.If Query Store has breached the maximum size limit between storage size checks, it transitions to read-only mode. Se è abilitata la Modalità di pulizia basata sulle dimensioni, viene attivato anche il meccanismo di pulizia per applicare il limite di dimensioni massime.If Size Based Cleanup Mode is enabled, the cleanup mechanism to enforce the maximum size limit is also triggered.

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 per Dimensioni massime (MB) :The following script sets a new value for Max Size (MB):

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

Intervallo di scaricamento dati (minuti) : definisce la frequenza per salvare in modo permanente le statistiche di runtime raccolte su disco.Data Flush Interval (Minutes): It defines the frequency to persist collected runtime statistics to disk. È espresso in minuti nell'interfaccia utente grafica (GUI), ma in Transact-SQLTransact-SQL è espresso in secondi.It's expressed in minutes in the graphical user interface (GUI), but in Transact-SQLTransact-SQL it's expressed in seconds. Il valore predefinito è 900 secondi, ovvero 15 minuti nell'interfaccia utente grafica.The default is 900 seconds, which is 15 minutes in the graphical user interface. Valutare la possibilità di usare un valore più elevato se il carico di lavoro non genera un numero elevato di query e piani diversi o se è possibile attendere più tempo per salvare i dati in modo permanente prima dell'arresto di un database.Consider using a higher value if your workload doesn't generate a large number of different queries and plans, or if you can withstand longer time to persist data before a database shutdown.

Nota

L'uso del flag di traccia 7745 impedisce la scrittura su disco dei dati di Query Store nel caso di un comando di failover o arresto.Using trace flag 7745 prevents Query Store data from being written to disk in case of a failover or shutdown command. Per altre informazioni, vedere la sezione Usare i flag di traccia nei server cruciali.For more information, see the Use trace flags on mission-critical servers section.

Usare SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL per impostare un valore diverso per Intervallo di scaricamento dati:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Data Flush Interval:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Intervallo di raccolta statistiche: definisce il livello di granularità delle statistiche di runtime raccolte, espresso in minuti.Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in minutes. Il valore predefinito è 60 minuti.The default is 60 minutes. È possibile usare un valore inferiore se è necessaria una maggiore granularità o maggiore rapidità nel rilevare e limitare i problemi.Consider using a lower value if you require finer granularity or less time to detect and mitigate issues. Tenere presente che il valore influisce direttamente sulle dimensioni dei dati di Query Store.Keep in mind that the value directly affects the size of Query Store data. Usare SQL Server Management StudioSQL Server Management Studio o Transact-SQLTransact-SQL per impostare un valore diverso per Intervallo di raccolta statistiche:Use SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL to set a different value for Statistics Collection Interval:

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

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, espresso in giorni.Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. Per impostazione predefinita, Query Store è 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 might be unnecessarily long for your scenario.

Evitare di conservare i dati cronologici che non si intende usare.Avoid keeping historical data that you don't plan to use. Questo accorgimento riduce il ricorso allo stato di sola lettura.This practice reduces changes to read-only status. Le dimensioni dei dati di Query Store e il tempo necessario per rilevare e limitare il problema saranno più prevedibili.The size of Query Store data and 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 = 90));

Modalità di pulizia basata sulle dimensioni: specifica se viene eseguita la pulizia automatica dei dati quando le dimensioni dei dati di Query Store si avvicinano al limite.Size Based Cleanup Mode: Specifies whether automatic data cleanup takes place when Query Store data size approaches the limit. Attivare la pulizia basata sulle dimensioni per assicurarsi che Query Store venga sempre eseguito in modalità lettura/scrittura e possa raccoglie i dati più recenti.Activate size-based cleanup to make sure that Query Store always runs in read-write mode and collects the latest data. Si noti che non esiste alcuna garanzia in presenza di carichi di lavoro gravosi che la pulizia di Query Store manterrà costantemente le dimensioni dei dati al di sotto del limite.Note that there is no guarantee under heavy workloads that Query Store cleanup will consistently maintain the data size under the limit. È possibile che la pulizia automatica dei dati superi tale limite ed attivi (temporaneamente) la modalità di sola lettura.It is possible for the automatic data cleanup to fall behind and to switch (temporarily) into read-only mode.

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

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

  • All (Tutto): Consente di acquisire tutte le query.All: Captures all queries. Si tratta dell'opzione predefinita in SQL Server 2016 (13.x)SQL Server 2016 (13.x) e SQL Server 2017 (14.x)SQL Server 2017 (14.x).This option is the default in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
  • 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 del runtime, della compilazione e del conteggio esecuzioni vengono determinate internamente.Thresholds for execution count, compile, and runtime duration are internally determined. A partire da SQL Server 2019 (15.x)SQL Server 2019 (15.x), si tratta dell'opzione predefinita.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default option.
  • Nessuna: Query Store smette di acquisire nuove query.None: Query Store stops capturing new queries.
  • Custom: consente un maggiore controllo e la capacità di ottimizzare i criteri di raccolta dati.Custom: Allows additional control and the capability to fine-tune the data collection policy. Le nuove impostazioni personalizzate definiscono che cosa accade entro la soglia di tempo per i criteri di acquisizione interni.The new custom settings define what happens during the internal capture policy time threshold. Si tratta di un limite di tempo durante il quale vengono valutate le condizioni configurabili e, se si verifica una di tali condizioni, la query è idonea per l'acquisizione da parte di Query Store.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store.

Importante

I cursori, le query all'interno delle stored procedure e le query compilate in modo nativo vengono sempre acquisiti quando la modalità di acquisizione di Query Store è impostata su All, Auto o Custom.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Per acquisire le query compilate in modo nativo, abilitare la raccolta delle statistiche per query usando sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

Lo script seguente imposta QUERY_CAPTURE_MODE su AUTO:The following script sets QUERY_CAPTURE_MODE to AUTO:

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

EsempiExamples

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO e imposta le altre opzioni consigliate in SQL Server 2016 (13.x)SQL Server 2016 (13.x):The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2016 (13.x)SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO e imposta le altre opzioni consigliate in SQL Server 2017 (14.x)SQL Server 2017 (14.x) in modo da includere le statistiche di attesa:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2017 (14.x)SQL Server 2017 (14.x) to include wait statistics:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

L'esempio seguente imposta QUERY_CAPTURE_MODE su AUTO, definisce le altre opzioni consigliate in SQL Server 2019 (15.x)SQL Server 2019 (15.x) e configura facoltativamente i criteri di acquisizione CUSTOM con i valori predefiniti, in alternativa alla nuova modalità di acquisizione AUTO predefinita:The following example sets QUERY_CAPTURE_MODE to AUTO and sets other recommended options in SQL Server 2019 (15.x)SQL Server 2019 (15.x), and optionally sets the CUSTOM capture policy with its defaults, instead of the new default AUTO capture mode:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Iniziare a risolvere i problemi di prestazioni relativi alle queryStart with query performance troubleshooting

Il flusso di lavoro di risoluzione dei problemi relativi a Query Store è semplice, come illustra il diagramma seguente:The troubleshooting workflow with Query Store is simple, as shown in the following diagram:

Risoluzione dei problemi di Query StoreQuery Store troubleshooting

Abilitare Query Store 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 di Query Store può richiedere del tempo.It takes 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 enable the feature. Andare alla sottocartella di Query Store nel nodo database in Esplora oggetti di Management StudioManagement Studio per aprire le viste di risoluzione dei problemi per scenari specifici.Go to the Query Store subfolder under the database node in Object Explorer of Management StudioManagement Studio to open troubleshooting views for specific scenarios.

Le viste dell'archivio query diManagement 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:

Versione di SQL ServerSQL ServerSQL ServerSQL Server version Metrica di esecuzioneExecution metric Funzione statisticaStatistic function
SQL Server 2016 (13.x)SQL Server 2016 (13.x) Tempo CPU, Durata, Conteggio esecuzioni, Letture logiche, Scritture logiche, Utilizzo memoria, Letture fisiche, Tempo CLR, Grado di parallelismo e Conteggio righeCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism (DOP), and Row count Media, Massimo, Minimo, Deviazione standard, TotaleAverage, Maximum, Minimum, Standard Deviation, Total
SQL Server 2017 (14.x)SQL Server 2017 (14.x) Tempo CPU, Durata, Conteggio esecuzioni, Letture logiche, Scritture logiche, Utilizzo memoria, Letture fisiche, Tempo CLR, Grado di parallelismo, Conteggio righe, Memoria log, Memoria TempDB e Tempi di attesaCPU time, Duration, Execution count, Logical reads, Logical writes, Memory consumption, Physical reads, CLR time, Degree of parallelism, Row count, Log memory, TempDB memory, and Wait times 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:

Viste di Query StoreQuery 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 SQL Server Management StudioSQL Server Management Studio view ScenarioScenario
Query regrediteRegressed Queries Trovare le query per cui le metriche di esecuzione sono recentemente regredite (ad esempio, peggiorate).Pinpoint queries for which execution metrics have recently regressed (for example, 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 need 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 di 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 that 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 affecting user experience across your applications.
Statistiche di attesa queryQuery Wait Statistics Analizzare le categorie di attesa più attive in un database e le query che contribuiscono maggiormente alla categoria di attesa selezionata.Analyze wait categories that are most active in a database and which queries contribute most to the selected wait category.
Usare questa vista per analizzare le statistiche di attesa e identificare le query che possono influire negativamente sull'esperienza utente in tutte le applicazioni.Use this view to analyze wait statistics and identify queries that might be affecting user experience across your applications.

Si applica a: a partire da SQL Server Management StudioSQL Server Management Studio v18.0 e SQL Server 2017 (14.x)SQL Server 2017 (14.x).Applies to: Starting with SQL Server Management StudioSQL Server Management Studio v18.0 and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
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 di risorse e correggere le query regredite a causa della modifica di una scelta del piano, vedere i post relativi a Query Store nei blog di Azure.For a detailed description of 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 suboptimal 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.If the query was executed with multiple plans and the last plan is significantly worse than the previous plan, you can use the plan forcing mechanism to force it. SQL ServerSQL Server prova a forzare il piano in query optimizer.tries to force the plan in the optimizer. Se l'uso forzato del piano ha esito negativo, viene generato un XEvent e a query optimizer viene richiesto di ottimizzare in modo normale.If plan forcing fails, an XEvent is fired and the optimizer is instructed to optimize in the normal way.

    Forzatura del piano di Query StoreQuery Store force plan

    Nota

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

    Con formeShape SignificatoMeaning
    CircleCircle Query completata, che significa che una normale esecuzione è stata completata correttamente.Query completed, which means that a regular execution successfully finished.
    SquareSquare Annullata, che significa che l'esecuzione inizializzata sul lato client è stata interrotta.Cancelled, which means that a client-initiated aborted execution.
    TriangleTriangle Non riuscita, che significa che l'esecuzione è stata interrotta da un'eccezione.Failed, which means that an exception aborted execution.

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

  • Si può concludere che la query sia priva di un indice per l'esecuzione ottimale.You might 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 Query Store.Create the missing index, and check the query performance by usingQuery Store.

    Visualizzare il piano di Query StoreQuery 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 potrebbe 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 might 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, ad esempio per sfruttare i vantaggi della parametrizzazione delle query o per implementare la logica ottimale.Rewrite problematic queries, for example, to take advantage of query parameterization or to implement more optimal logic.

Verificare che Query Store raccolga i dati delle query in modo continuativoVerify that Query Store collects query data continuously

Query Store può cambiare automaticamente la modalità operativa.Query Store can silently change the operation mode. Monitorare regolarmente lo stato di Query Store per assicurarsi che sia in funzione e per prevenire errori dovuti a cause evitabili.Regularly monitor the state of Query Store to ensure that 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 the operation mode occurred automatically. La modifica più comune è il passaggio automatico di Query Store alla modalità di sola lettura.The most common change is for Query Store to silently switch to read-only mode. Eccezionalmente, Query Store può passare a uno stato di errore a causa di errori interni.In extremely rare 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, Query Store passa alla modalità di sola lettura quando viene superato il limite delle dimensioni.Typically, you 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 executing the following statement that explicitly changes the 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. Assicurarsi che le dimensioni di Query Store siano sempre inferiori al valore massimo consentito per ridurre drasticamente il rischio di passaggio alla modalità di sola lettura.Ensure that Query Store size is always below the maximally allowed value to dramatically reduce a chance of transitioning to read-only mode. Attivare criteri basati sulle dimensioni, come descritto nella sezione Configurare Query Store, in modo che Query Store 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 Query Store automatically cleans data when the size approaches the limit.
  • Per assicurarsi che vengano conservati i dati più recenti, configurare criteri basati sul tempo per rimuovere regolarmente le informazioni non aggiornate.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 di Query Store su Auto per escludere le query generalmente meno rilevanti per il carico di lavoro.Finally, consider setting Query Store Capture Mode to Auto because it filters out queries that are usually less relevant for your workload.

Stato di erroreERROR state

Per recuperare Query Store, 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 the 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 that corruption of Query Store data is persisted on the disk.

A partire da SQL Server 2017 (14.x)SQL Server 2017 (14.x), è possibile recuperare Query Store eseguendo la stored procedure sp_query_store_consistency_check all'interno del database interessato.Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), Query Store can be recovered by executing the sp_query_store_consistency_check stored procedure within the affected database. Prima di provare a eseguire l'operazione di ripristino, è necessario disabilitare Query Store.Query Store must be disabled before you attempt the recovery operation. Per SQL Server 2016 (13.x)SQL Server 2016 (13.x), è necessario cancellare i dati da Query Store come illustrato.For SQL Server 2016 (13.x)SQL Server 2016 (13.x), you need to clear the data from Query Store as shown.

Se il ripristino non riesce, è possibile provare a cancellare Query Store prima di impostare la modalità lettura/scrittura.If the recovery was unsuccessful, you can try clearing Query Store before you set the 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 di Query Store ottimaleSet the optimal Query Store 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 di Query Store:The following table describes typical scenarios for each Query Store Capture Mode:

Modalità di acquisizione dell'archivio queryQuery Store Capture Mode ScenarioScenario
TuttoAll 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.

Stabilire se vengono usate query ad hoc per identificare le opportunità di parametrizzazione automatica o da parte dell'utente.Detect if ad-hoc queries are used to identify opportunities for user or auto parameterization.

Nota: si tratta della modalità di acquisizione predefinita in SQL Server 2016 (13.x)SQL Server 2016 (13.x) e SQL Server 2017 (14.x)SQL Server 2017 (14.x).Note: This is the default capture mode in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).
AutoAuto Concentrare l'attenzione su query rilevanti e da correggere.Focus your attention on relevant and actionable queries. Un esempio sono le query eseguite regolarmente o che hanno un consumo di risorse elevato.An example is those queries that execute regularly or that have significant resource consumption.

Nota: a partire da SQL Server 2019 (15.x)SQL Server 2019 (15.x), si tratta della modalità di acquisizione predefinita.Note: Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this is the default capture mode.
NessunoNone Il set di query da monitorare è stato già acquisito in fase di esecuzione e si vuole eliminare qualsiasi distrazione introdotta da altre query.You've already captured the query set that you want to monitor in runtime and you want to eliminate the distractions that other queries might introduce.

È adatta ad ambienti di testing e di benchmarking.None is suitable for testing and benchmarking 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 because 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.
Impostazione personalizzataCustom SQL Server 2019 (15.x)SQL Server 2019 (15.x) introduce una modalità di acquisizione Custom nel comando ALTER DATABASE SET QUERY_STORE.introduces a Custom capture mode under the ALTER DATABASE SET QUERY_STORE command. Una volta abilitate, le configurazioni aggiuntive di Query Store sono disponibili in una nuova impostazione di criteri di acquisizione di Query Store per ottimizzare la raccolta dati in un server specifico.When enabled, additional Query Store configurations are available under a new Query Store capture policy setting to fine-tune data collection in a specific server.

Le nuove impostazioni personalizzate definiscono che cosa accade entro la soglia di tempo per i criteri di acquisizione interni.The new custom settings define what happens during the internal capture policy time threshold. Si tratta di un limite di tempo durante il quale vengono valutate le condizioni configurabili e, se si verifica una di tali condizioni, la query è idonea per l'acquisizione da parte di Query Store.This is a time boundary during which the configurable conditions are evaluated and, if any are true, the query is eligible to be captured by Query Store. Per altre informazioni, vedere Opzioni ALTER DATABASE SET (Transact-SQL).For more information, see ALTER DATABASE SET Options (Transact-SQL).

Nota

I cursori, le query all'interno delle stored procedure e le query compilate in modo nativo vengono sempre acquisiti quando la modalità di acquisizione di Query Store è impostata su All, Auto o Custom.Cursors, queries inside stored procedures, and natively compiled queries are always captured when Query Store Capture Mode is set to All, Auto, or Custom. Per acquisire le query compilate in modo nativo, abilitare la raccolta delle statistiche per query usando sys.sp_xtp_control_query_exec_stats.To capture natively compiled queries, enable collection of per-query statistics by using sys.sp_xtp_control_query_exec_stats.

Mantenere i dati più rilevanti in Query StoreKeep the most relevant data in Query Store

Configurare Query Store 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 Query Store to contain only the relevant data so that it runs continuously and provides a 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 autocleanup.
Escludere le query non rilevanti.Filter out nonrelevant queries. Configurare la modalità di acquisizione di Query Store su Auto.Configure Query Store Capture Mode to Auto.
Eliminare le query meno rilevanti quando vengono raggiunte le dimensioni massime.Delete less relevant queries when the maximum size is reached. Attivare criteri di pulizia basati sulle dimensioni.Activate size-based cleanup policy.

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

L'uso di query senza parametri quando non è strettamente necessario non è una procedura consigliata.Using non-parameterized queries when that isn't necessary isn't a best practice. Un esempio è in caso di analisi ad hoc.An example is in the case of ad-hoc analysis. Non è possibile usare nuovamente i piani memorizzati nella cache e questo impone a Query Optimizer di compilare query per ogni testo della query univoco.Cached plans can't be reused, which forces Query Optimizer to compile queries for every unique query text. Per altre informazioni, vedere Linee guida per l'utilizzo della parametrizzazione forzata.For more information, see Guidelines for using forced parameterization.

Inoltre, Query Store può superare rapidamente il limite di dimensioni a causa del numero potenzialmente elevato di testi delle 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 a potentially 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 Query Store potrebbe passare alla modalità di sola lettura o eliminare dati continuamente per tentare di gestire le query in ingresso.As a result, performance of your workload is suboptimal, and Query Store might switch to read-only mode or constantly delete data to try to keep up with the incoming queries.

Valutare le opzioni seguenti:Consider the following options:

  • Parametrizzare le query, se applicabile.Parameterize queries where applicable. Ad esempio, eseguire il wrapping delle query all'interno di una stored procedure o sp_executesql.For example, wrap queries inside a stored procedure or sp_executesql. Per altre informazioni, vedere Parametri e riutilizzo del piano di esecuzione.For more information, 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 isn't large.
    • Usare una guida di piano per forzare la parametrizzazione solo per la query selezionata.Use a plan guide to force parameterization only for the selected query.
    • Configurare la parametrizzazione forzata usando il comando dell'opzione di database Parameterization, se nel carico di lavoro è presente un numero ridotto di piani di query diversi.Configure forced parameterization by using the parameterization database option command, if there are a small number of different query plans in your workload. Un esempio è quando il rapporto tra il numero di valori query_hash distinti e il numero totale di voci in sys.query_store_query è molto inferiore a 1.An example is 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 QUERY_CAPTURE_MODE su AUTO per filtrare automaticamente le query ad hoc con un consumo di risorse ridotto.Set QUERY_CAPTURE_MODE to AUTO to automatically filter out ad-hoc queries with small resource consumption.

Evitare il modello DROP e CREATE per gli oggetti contenitoreAvoid a DROP and CREATE pattern for containing objects

Query Store associa ogni voce di query a un oggetto contenitore, come una stored procedure, una funzione o un trigger.Query Store associates query entry with a containing object, such as stored procedure, function, and trigger. Quando si ricrea un oggetto contenitore, viene generata una nuova voce di query per lo stesso testo della query.When you re-create a containing object, a new query entry is 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 prevents you from tracking performance statistics for that query over time and using a plan forcing mechanism. Per evitare questa situazione, usare il processo ALTER <object> per modificare la definizione dell'oggetto contenitore, quando è possibile.To avoid this situation, use the ALTER <object> process to change a containing object definition whenever it's possible.

Verificare regolarmente lo stato dei piani forzatiCheck 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. Come accade con gli hint di piano e le guide di piano, forzare un piano non garantisce che poi venga usato nelle esecuzioni successive.As with plan hints and plan guides, forcing a plan isn't 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 vengano 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 starts 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 a full list of reasons, see sys.query_store_plan. È anche possibile usare l'oggetto XEvent query_store_plan_forcing_failed per monitorare e risolvere gli errori di uso forzato del piano.You can also use the query_store_plan_forcing_failed XEvent to track and troubleshoot plan forcing failures.

Evitare di rinominare i database per query con piani forzatiAvoid renaming databases for queries with forced plans

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

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

Uso di Query nei server mission-criticalUsing Query Store in mission-critical servers

I flag di traccia globali 7745 e 7752 possono essere usati per migliorare la disponibilità dei database tramite Query Store.The global trace flags 7745 and 7752 can be used to improve availability of databases by using Query Store. Per altre informazioni, vedere Flag di traccia.For more information, see Trace flags.

  • Il flag di traccia 7745 previene il comportamento predefinito quando Query Store scrive i dati su disco prima dell'arresto di SQL ServerSQL Server.Trace flag 7745 prevents the default behavior where Query Store writes data to disk before SQL ServerSQL Server can be shut down. Questo significa che i dati di Query Store che sono stati raccolti ma non sono ancora stati salvati su disco andranno persi, per l'intervallo di tempo definito con DATA_FLUSH_INTERVAL_SECONDS.This means that Query Store data that has been collected but not yet persisted to disk will be lost, up to the time window defined with DATA_FLUSH_INTERVAL_SECONDS.
  • Il flag di traccia 7752 abilita il caricamento asincrono di Query Store.Trace flag 7752 enables asynchronous load of Query Store. Questo consente di portare online un database ed eseguire query prima del completamento del ripristino di Query Store.This allows a database to become online and queries to be executed before Query Store has been fully recovered. Il comportamento predefinito consiste nell'eseguire un caricamento sincrono di Query Store.The default behavior is to do a synchronous load of Query Store. Il comportamento predefinito impedisce l'esecuzione delle query prima del completamento del ripristino di Query Store, ma evita anche l'esclusione di query nella raccolta di dati.The default behavior prevents queries from executing before Query Store has been recovered but also prevents any queries from being missed in the data collection.

Nota

A partire da SQL Server 2019 (15.x)SQL Server 2019 (15.x), questo comportamento è controllato dal motore e il flag di traccia 7752 non ha alcun effetto.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), this behavior is controlled by the engine, and trace flag 7752 has no effect.

Importante

Se si usa Query Store per informazioni dettagliate sui carichi di lavoro just-in-time in SQL Server 2016 (13.x)SQL Server 2016 (13.x), prevedere l'installazione dei miglioramenti della scalabilità delle prestazioni in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) appena possibile.If you're using Query Store for just-in-time workload insights in SQL Server 2016 (13.x)SQL Server 2016 (13.x), plan to install the performance scalability improvements in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) as soon as possible. Senza questi miglioramenti, quando il database è sottoposto a carichi di lavoro intensivi, può verificarsi una contesa di spinlock e le prestazioni del server possono risultare rallentate.Without these improvements, when the database is under heavy workloads, spinlock contention may occur and server performance may become slow. In particolare, è possibile che si verifichi una contesa significativa sullo spinlock QUERY_STORE_ASYNC_PERSIST o SPL_QUERY_STORE_STATS_COOKIE_CACHE.In particular, you may see heavy contention on the QUERY_STORE_ASYNC_PERSIST spinlock or SPL_QUERY_STORE_STATS_COOKIE_CACHE spinlock. Applicato questo miglioramento, Query Store non provocherà più contese di spinlock.After this improvement is applied, Query Store will no longer cause spinlock contention.

Importante

Se si usa Query Store per informazioni dettagliate sul carico di lavoro JIT in (da a SQL ServerSQL Server SQL Server 2016 (13.x)SQL Server 2016 (13.x) SQL Server 2017 (14.x)SQL Server 2017 (14.x) ), pianificare l'installazione del miglioramento della scalabilità delle prestazioni in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU15, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU23 e SQL Server 2019 (15.x)SQL Server 2019 (15.x) CU9 il prima possibile.If you're using Query Store for just-in-time workload insights in SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017 (14.x)SQL Server 2017 (14.x)), plan to install the performance scalability improvement in SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU15, SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU23, and SQL Server 2019 (15.x)SQL Server 2019 (15.x) CU9 as soon as possible. Senza questo miglioramento, quando il database è sottoposto a carichi di lavoro ad hoc pesanti, Query Store può usare una quantità elevata di memoria e le prestazioni del server possono risultare rallentate.Without this improvement, when the database is under heavy ad-hoc workloads, the Query Store may use a large amount of memory and server performance may become slow. Applicato questo miglioramento, Query Store impone limiti interni alla quantità di memoria che può essere usata dai vari componenti e può modificare automaticamente la modalità di operazione in sola lettura finché non viene restituita memoria sufficiente a Motore di databaseDatabase Engine.After this improvement is applied, Query Store imposes internal limits to the amount of memory its various components can use, and can automatically change the operation mode to read-only until enough memory has been returned to the Motore di databaseDatabase Engine. Si noti che i limiti di memoria interni di Query Store non sono documentati perché sono soggetti a modifiche.Note that Query Store internal memory limits are not documented because they are subject to change.

Uso di Query Store nella replica geografica attiva del database SQL di AzureUsing Query Store in Azure SQL Database active geo-replication

Query Store in una replica geografica attiva secondaria del database SQL di Azure sarà una copia di sola lettura dell'attività nella replica primaria.Query Store on a secondary active geo-replica of Azure SQL Database will be a read-only copy of the activity on the primary replica.

Evitare la partecipazione alla replica geografica di livelli di database SQL di Azure non corrispondenti.Avoid mismatched tiers of Azure SQL Databases participating in geo-replication. Un database secondario deve avere dimensioni di calcolo uguali o molto simili al database primario ed essere incluso nello stesso livello di servizio del database primario.A secondary database should be at or near the same compute size of the primary database, and in the same service tier of the primary database. Cercare il tipo di attesa HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO in sys.dm_db_wait_stats, che indica la limitazione della frequenza del log delle transazioni nella replica primaria a causa del ritardo del database secondario.Look for the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type in sys.dm_db_wait_stats which indicates transaction log rate throttling on the primary replica due to secondary lag.

Per altre informazioni sulla stima e la configurazione delle dimensioni del database SQL di Azure secondario della replica geografica attiva, vedere Configurazione del database secondario.For more on estimating and configuring the size of the secondary Azure SQL database of active geo-replication, see Configuring secondary database.

Vedere ancheSee also