Monitoraggio delle prestazioni con Query StoreMonitoring performance by using 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

La funzionalità Archivio query di SQL ServerSQL Server mostra informazioni dettagliate sulle prestazioni e sulla scelta del piano di query.The SQL ServerSQL Server Query Store feature provides you with insight on query plan choice and performance. Semplifica la risoluzione dei problemi di prestazioni in quanto consente di individuare rapidamente le variazioni delle prestazioni causate da modifiche nei piani di query.It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Archivio query acquisisce automaticamente una cronologia delle query, dei piani e delle statistiche di runtime e li conserva per la consultazione.Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. I dati vengono separati in base a intervalli di tempo, consentendo di visualizzare i modelli di utilizzo del database e capire quando sono state apportate modifiche al piano di query nel server.It separates data by time windows so you can see database usage patterns and understand when query plan changes happened on the server. Per configurare l'archivio query, è possibile usare l'opzione ALTER DATABASE SET .You can configure query store using the ALTER DATABASE SET option.

Per informazioni sul funzionamento dell'archivio query nel database SQL di Azure, vedere Uso dell'archivio query nel database SQL di Azure.For information about operating the Query Store in Azure SQL Database, see Operating the Query Store in Azure SQL Database.

Abilitazione di Archivio query Enabling the Query Store

Per impostazione predefinita, la funzionalità Archivio query non è attiva per i nuovi database.Query Store is not active for new databases by default.

Usare la pagina Archivio query in Management StudioUse the Query Store Page in Management Studio

  1. In Esplora oggetti fare clic con il pulsante destro del mouse su un database e quindi scegliere Proprietà.In Object Explorer, right-click a database, and then click Properties.

    Nota

    È necessaria almeno la versione SQL Server 2016SQL Server 2016 di Management StudioManagement Studio.Requires at least SQL Server 2016SQL Server 2016 version of Management StudioManagement Studio.

  2. Nella finestra di dialogo Proprietà database selezionare la pagina Archivio query .In the Database Properties dialog box, select the Query Store page.

  3. Nella casella Modalità operativa (richiesta) selezionare Attivato.In the Operation Mode (Requested) box, select On.

Usare istruzioni Transact-SQLUse Transact-SQL Statements

  1. Per abilitare l'archivio query, usare l'istruzione ALTER DATABASE .Use the ALTER DATABASE statement to enable the query store. Esempio:For example:

    ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;  
    

    Per altre opzioni della sintassi correlate all'archivio query, vedere Opzioni ALTER DATABASE SET (Transact-SQL).For more syntax options related to the query store, see ALTER DATABASE SET Options (Transact-SQL).

Nota

Non è possibile abilitare l'archivio query per il database master o tempdb .You cannot enable the query store for the master or tempdb database.

Informazioni presenti in Archivio query Information in the Query Store

I piani di esecuzione per query specifiche in SQL ServerSQL Server in genere cambiano nel tempo per motivi diversi, quali modifiche delle statistiche, modifiche dello schema, creazione/eliminazione di indici e così via. Nella cache delle procedure, dove sono archiviati i piani di query memorizzati nella cache, viene archiviato solo il piano di esecuzione più recente.Execution plans for any specific query in SQL ServerSQL Server typically evolve over time due to a number of different reasons such as statistics changes, schema changes, creation/deletion of indexes, etc. The procedure cache (where cached query plans are stored) only stores the latest execution plan. La rimozione dei piani dalla cache dei piani può dipendere anche da problemi di memoria.Plans also get evicted from the plan cache due to memory pressure. Di conseguenza, le regressioni delle prestazioni di esecuzione delle query causate da modifiche del piano di esecuzione possono essere rilevanti e richiedere tempo per la risoluzione.As a result, query performance regressions caused by execution plan changes can be non-trivial and time consuming to resolve.

Dal momento che nell'archivio query vengono mantenuti più piani di esecuzione per ogni query, è possibile applicare i criteri in modo che il processore di query usi un piano di esecuzione specifico per una query.Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query. Questo processo viene chiamato utilizzo forzato del piano.This is referred to as plan forcing. Per applicare l'utilizzo forzato del piano in Archivio query, viene usato un meccanismo simile all'hint per la query USE PLAN , che però non richiede modifiche nelle applicazioni utente.Plan forcing in Query Store is provided by using a mechanism similar to the USE PLAN query hint, but it does not require any change in user applications. Grazie all'utilizzo forzato del piano è possibile risolvere molto rapidamente una regressione delle prestazioni di esecuzione delle query causata da una modifica del piano.Plan forcing can resolve a query performance regression caused by a plan change in a very short period of time.

Le statistiche di attesa sono un'altra fonte di informazione con le quali è possibile risolvere i problemi di prestazioni in SQL Server.Wait stats are another source of information that helps to troubleshoot performance in SQL Server. Per molto tempo le statistiche di attesa sono state disponibili solo a livello di istanza, il che rendeva difficile l'esecuzione del backtracking per la query effettiva.For a long time, wait statistics were available only on instance level, which made it hard to backtrack it to the actual query. In SQL Server 2017 e nel database SQL di Azure è stata aggiunta un'altra dimensione in Query Store che tiene traccia delle statistiche di attesa.In SQL Server 2017 and Azure SQL Database we added another dimension in Query Store that tracks wait stats.

La funzionalità Archivio query viene usata in genere negli scenari seguenti:Common scenarios for using the Query Store feature are:

  • Individuare e correggere rapidamente una regressione delle prestazioni di esecuzione delle query forzando un piano di query precedente.Quickly find and fix a plan performance regression by forcing the previous query plan. Correggere le query in cui si è verificata di recente una regressione delle prestazioni a causa di modifiche del piano di esecuzione.Fix queries that have recently regressed in performance due to execution plan changes.

  • Determinare il numero di volte in cui una query è stata eseguita in un determinato intervallo di tempo, in modo da assistere un amministratore di database nella risoluzione dei problemi relativi alle prestazioni delle risorse.Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.

  • Identificare le prime n query (in base al tempo di esecuzione, al consumo della memoria e così via) nelle ultime x ore.Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

  • Controllare la cronologia dei piani di query per una determinata query.Audit the history of query plans for a given query.

  • Analizzare i modelli di utilizzo delle risorse (CPU, I/O e memoria) per un determinato database.Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

  • Identificare le prime query n in attesa su risorse.Identify top n queries that are waiting on resources.
  • Comprendere la natura di attesa per una query o un piano in particolare.Understand wait nature for a particular query or plan.

In Query Store sono contenuti tre archivi:The query store contains three stores:

  • a archivio piani: per il salvataggio in modo permanente delle informazioni sul piano di esecuzionea plan store for persisting the execution plan information
  • a archivio statistiche runtime: per il salvataggio in modo permanente delle informazioni sulle statistiche di esecuzione.a runtime stats store for persisting the execution statistics information.
  • a archivio statistiche di attesa: per il salvataggio in modo permanente delle informazioni sulle statistiche di attesa.a wait stats store for persisting wait statistics information.

    Il numero di piani univoci che è possibile archiviare per una query nell'archivio piani è limitato dall'opzione di configurazione max_plans_per_query .The number of unique plans that can be stored for a query in the plan store is limited by the max_plans_per_query configuration option. Per migliorare le prestazioni, le informazioni vengono scritte nei due archivi in modo asincrono.To enhance performance, the information is written to the two stores asynchronously. Per ridurre al minimo l'utilizzo dello spazio, le statistiche di esecuzione di runtime nell'archivio delle statistiche di runtime vengono aggregate in un intervallo di tempo fisso.To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed time window. Per visualizzare le informazioni contenute in questi archivi, è possibile eseguire una query sulle viste del catalogo dell'archivio query.The information in these stores is visible by querying the query store catalog views.

    La query seguente restituisce le informazioni sulle query e sui piani inclusi nell'archivio query.The following query returns information about queries and plans in the query store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*  
FROM sys.query_store_plan AS Pl  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id ;  

Usare la funzionalità Query regredite Use the Regressed Queries Feature

Dopo aver abilitato Archivio query, aggiornare la parte del database del riquadro Esplora oggetti per aggiungere la sezione Archivio query .After enabling the query store, refresh the database portion of the Object Explorer pane to add the Query Store section.

Struttura ad albero di Archivio query in Esplora oggettiQuery store tree in Object Explorer

Selezionare Query regredite per aprire il riquadro Query regredite in Management StudioManagement Studio.Select Regressed Queries to open the Regressed Queries pane in Management StudioManagement Studio. Nel riquadro Query regredite sono visualizzati i piani e le query presenti nell'archivio query.The Regressed Queries pane shows you the queries and plans in the query store. Usare le caselle a discesa nella parte superiore per selezionare le query in base a diversi criteri.Use the drop down boxes at the top to select queries based on various criteria. Selezionare un piano per visualizzare il piano di query con interfaccia grafica.Select a plan to see the graphical query plan. Sono disponibili pulsanti per visualizzare la query di origine, forzare e annullar e la forzatura di un piano di query e aggiornare la visualizzazione.Buttons are available to view the source query, force, and unforce a query plan, and refresh the display.

Query regredite in Esplora oggettiRegressed queries in object explorer

Per forzare un piano, selezionare una query e un piano, quindi fare clic su Forza piano.To force a plan, select a query and plan, and then click Force Plan. È possibile forzare solo piani che sono stati salvati dalla funzionalità del piano di query e che sono ancora presenti nella relativa cache.You can only force plans that were saved by the query plan feature and are still retained in the query plan cache.

Ricerca di query di attesa Finding wait queries

A partire da SQL Server 2017 CTP 2.0 e nel database SQL di Azure sono a disposizione degli utenti di Query Store statistiche di attesa per query.Starting from SQL Server 2017 CTP 2.0 and on Azure SQL Database wait statistics per query over time are available for Query Store customers. In Query Store i tipi di attesa sono raggruppati in categorie di attesa.In Query Store wait types are combined into wait categories. Il mapping completo è disponibile in sys.query_store_wait_stats (Transact-SQL)Full mapping is available here sys.query_store_wait_stats (Transact-SQL)

Le categorie di attesa raggruppano tipi di attesa diversi in bucket simili per natura.Wait categories are combining different wait types into buckets similar by nature. Per le varie categorie di attesa è necessario un'analisi di completamento diversa per risolvere il problema. Per i tipi di attesa della stessa categoria la risoluzione dei problemi è invece molto simile. Specificando la query interessata come prima nelle attese, si indica la parte mancante necessaria a completare le analisi in modo corretto.Different wait categories require a different follow up analysis to resolve the issue, but wait types from the same category lead to very similar troubleshooting experiences, and providing the affected query on top of waits would be the missing piece to complete the majority of such investigations successfully.

Di seguito sono descritti alcuni esempi su come ottenere informazioni dettagliate riguardanti il carico di lavoro prima e dopo aver introdotto le categorie di attesa in Query Store:Here are some examples how you can get more insights into your workload before and after introducing wait categories in Query Store:

Esperienza precedentePrevious experience Esperienza successivaNew experience AzioneAction
Attese di RESOURCE_SEMAPHORE elevate per databaseHigh RESOURCE_SEMAPHORE waits per database Attese di memoria elevate in Query Store per query specificheHigh Memory waits in Query Store for specific queries Individuare le prime query per utilizzo della memoria in Query Store.Find the top memory consuming queries in Query Store. È probabile che queste query ritardino il successivo l'avanzamento delle query interessate.These queries are probably delaying further progress of the affected queries. È consigliabile usare l'hint per la query MAX_GRANT_PERCENT per queste query o per le query interessate.Consider using MAX_GRANT_PERCENT query hint for these queries, or for the affected queries.
Attese di LCK_M_X elevate per databaseHigh LCK_M_X waits per database Attese di blocco elevate in Query Store per query specificheHigh Lock waits in Query Store for specific queries Controllare i testi di query per le query interessate e identificare le entità di destinazione.Check the query texts for the affected queries and identify the target entities. In Query Store cercare altre query che modificano la stessa entità, che vengono eseguite frequentemente e/o hanno una durata elevata.Look in Query Store for other queries modifying the same entity, which are executed frequently and/or have high duration. Dopo aver individuato queste query, provare a modificare la logica dell'applicazione per migliorare la concorrenza, oppure usare un livello di isolamento meno restrittivo.After identifying these queries, consider changing the application logic to improve concurrency, or use a less restrictive isolation level.
Attese di PAGEIOLATCH_SH elevate per databaseHigh PAGEIOLATCH_SH waits per database Attese di I/O del buffer elevate in Query Store per query specificheHigh Buffer IO waits in Query Store for specific queries Individuare le query con un numero elevato di letture fisiche in Query Store.Find the queries with a high number of physical reads in Query Store. Se corrispondono alle query con attese di I/O elevate, provare a introdurre un indice nell'entità sottostante, in modo da eseguire ricerche anziché analisi e ridurre così al minimo il sovraccarico di I/O delle query.If they match the queries with high IO waits, consider introducing an index on the underlying entity, in order to do seeks instead of scans, and thus minimize the IO overhead of the queries.
Attese di SOS_SCHEDULER_YIELD elevate per databaseHigh SOS_SCHEDULER_YIELD waits per database Attese di CPU elevate in Query Store per query specificheHigh CPU waits in Query Store for specific queries Individuare la prime query per utilizzo CPU in Query Store.Find the top CPU consuming queries in Query Store. Tra queste query identificare quelle in cui la tendenza di utilizzo CPU elevato è correlata ad attese di CPU elevate per le query interessate.Among them, identify the queries for which high CPU trend correlates with high CPU waits for the affected queries. Concentrarsi sull'ottimizzazione di queste query: considerare la possibilità di una regressione del piano o la mancanza di un indice.Focus on optimizing those queries – there could be a plan regression, or perhaps a missing index.

Opzioni di configurazione Configuration Options

Le opzioni seguenti sono disponibili per la configurazione dei parametri dell'archivio query.The following options are available to configure query store parameters.

OPERATION_MODE
Può essere READ_WRITE (impostazione predefinita) o READ_ONLY.Can be READ_WRITE (default) or READ_ONLY.

CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
Configurare l'argomento STALE_QUERY_THRESHOLD_DAYS per specificare il numero di giorni per la conservazione dei dati nell'archivio query.Configure the STALE_QUERY_THRESHOLD_DAYS argument to specify the number of days to retain data in the query store. Il valore predefinito è 30.The default value is 30. Per l'edizione Database SQLSQL Database Basic, l'impostazione predefinita è 7 giorni.For Database SQLSQL Database Basic edition, default is 7 days.

DATA_FLUSH_INTERVAL_SECONDS
Determina la frequenza con cui i dati scritti nell'archivio query vengono mantenuti su disco.Determines the frequency at which data written to the query store is persisted to disk. Per ottimizzare le prestazioni, i dati raccolti dall'archivio query vengono scritti in modo asincrono sul disco.To optimize for performance, data collected by the query store is asynchronously written to the disk. La frequenza con cui si verifica questo trasferimento asincrono viene configurata tramite DATA_FLUSH_INTERVAL_SECONDS.The frequency at which this asynchronous transfer occurs is configured via DATA_FLUSH_INTERVAL_SECONDS. Il valore predefinito 900 (15 min).The default value is 900 (15 min).

MAX_STORAGE_SIZE_MB
Consente di configurare le dimensioni massime dell'archivio query.Configures the maximum size of the query store. Se i dati nell'archivio query raggiungono il limite impostato in MAX_STORAGE_SIZE_MB, lo stato cambia da lettura/scrittura a sola lettura e la raccolta di nuovi dati viene interrotta.If the data in the query store hits the MAX_STORAGE_SIZE_MB limit, the query store automatically changes the state from read-write to read-only and stops collecting new data. Il valore predefinito è 100Mb.The default value is 100Mb. Per Database SQLSQL Database Premium Edition il valore predefinito è 1Gb, mentre per Database SQLSQL Database Basic Edition il valore predefinito è 10Mb.For Database SQLSQL Database Premium edition, default is 1Gb and for Database SQLSQL Database Basic edition, default is 10Mb.

INTERVAL_LENGTH_MINUTES
Determina l'intervallo di tempo in cui vengono aggregati i dati delle statistiche di esecuzione di runtime nell'archivio query.Determines the time interval at which runtime execution statistics data is aggregated into the query store. Per ottimizzare l'utilizzo dello spazio, le statistiche di esecuzione di runtime nell'archivio delle statistiche di runtime vengono aggregate in un intervallo di tempo fisso.To optimize for space usage, the runtime execution statistics in the Runtime Stats Store are aggregated over a fixed time window. L'intervallo di tempo predefinito viene configurato tramite INTERVAL_LENGTH_MINUTES.This fixed time window is configured via INTERVAL_LENGTH_MINUTES. Il valore predefinito è 60.The default value is 60.

SIZE_BASED_CLEANUP_MODE
Determina se il processo di pulizia viene attivato automaticamente quando la quantità totale di dati sta per raggiungere le dimensioni massime.Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. Può essere AUTO (impostazione predefinita) o OFF.Can be AUTO (default) or OFF.

QUERY_CAPTURE_MODE
Determina se Archivio dati acquisisce tutte le query oppure le query pertinenti in base a conteggio esecuzioni e consumo delle risorse oppure se interrompe l'aggiunta delle nuove query e tiene semplicemente traccia delle query correnti.Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. Può essere ALL (acquisire tutte le query), SUTO (ignorare le query poco frequenti e quelle con una durata di compilazione e di esecuzione non significativa) o NONE (arrestare l'acquisizione di nuove query).Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). Il valore predefinito in SQL Server 2016 è ALL, mentre nel database SQL di Azure è AUTO.The default value on SQL Server 2016 is ALL, while on Azure SQL Database is AUTO.

MAX_PLANS_PER_QUERY
Intero che rappresenta il numero massimo di piani mantenuti per ogni query.An integer representing the maximum number of plans maintained for each query. Il valore predefinito è 200.The default value is 200.

WAIT_STATS_CAPTURE_MODE
Controlla se Query Store acquisisce informazioni sulle statistiche relative all'attesa.Controls if Query Store captures wait statistics information. Può essere OFF = 0 o ON = 1 (impostazione predefinita)Can be OFF = 0 or ON = 1 (default)

Per determinare le opzioni correnti dell'archivio query, eseguire una query sulla vista sys.database_query_store_options .Query the sys.database_query_store_options view to determine the current options of the query store. Per altre informazioni sui valori, vedere sys.database_query_store_options.For more information about the values, see sys.database_query_store_options.

Per altre informazioni sull'impostazione di opzioni con istruzioni Transact-SQLTransact-SQL , vedere Gestione delle opzioni.For more information about setting options by using Transact-SQLTransact-SQL statements, see Option Management.

È possibile visualizzare e gestire Archivio query con Management StudioManagement Studio oppure usando le viste e le procedure seguenti.View and manage Query Storethrough Management StudioManagement Studio or by using the following views and procedures.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Viste del catalogo di Archivio queryQuery Store Catalog Views

Le informazioni su Query Store vengono presentate nelle viste del catalogo.Catalog views present information about the Query Store.

sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL) sys.query_context_settings (Transact-SQL)sys.query_context_settings (Transact-SQL)
sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL) sys.query_store_query (Transact-SQL)sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)sys.query_store_query_text (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL)sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL) sys.query_store_runtime_stats_interval (Transact-SQL)sys.query_store_runtime_stats_interval (Transact-SQL)

Stored procedure di Archivio queryQuery Store Stored Procedures

Per configurare Query Store vengono usate le stored procedure.Stored procedures configure the Query Store.

sp_query_store_flush_db (Transact-SQL)sp_query_store_flush_db (Transact-SQL) sp_query_store_reset_exec_stats (Transact-SQL)sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_force_plan (Transact-SQL)sp_query_store_force_plan (Transact-SQL) sp_query_store_unforce_plan (Transact-SQL)sp_query_store_unforce_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL)sp_query_store_remove_plan (Transct-SQL) sp_query_store_remove_query (Transact-SQL)sp_query_store_remove_query (Transact-SQL)

Principali scenari di utilizzo Key Usage Scenarios

Gestione delle opzioni Option Management

Questa sezione fornisce alcune linee guida per la gestione della funzionalità Archivio query.This section provides some guidelines on managing Query Store feature itself.

Come sapere se la funzionalità Archivio query è attualmente attivaIs Query Store currently active?

I dati della funzionalità Query Store vengono archiviati nel database utente ed è quindi previsto un limite per le dimensioni, che viene configurato con MAX_STORAGE_SIZE_MB.Query Store stores its data inside the user database and that is why it has size limit (configured with MAX_STORAGE_SIZE_MB). Se i dati in Archivio query raggiungono tale limite, lo stato cambia automaticamente da lettura/scrittura a sola lettura e la raccolta di nuovi dati viene interrotta.If data in Query Store hits that limit Query Store will automatically change state from read-write to read-only and stop collecting new data.

Eseguire una query su sys.database_query_store_options per determinare se la funzionalità Query Store è attualmente attiva e se è in corso la raccolta delle statistiche di runtime.Query sys.database_query_store_options to determine if Query Store is currently active, and whether it is currently collects runtime stats or not.

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

Lo stato di Archivio query è determinato dalla colonna actual_state.Query Store status is determined by actual_state column. Se è diverso dallo stato previsto, la colonna readonly_reason contiene maggiori informazioni.If it’s different than the desired status, the readonly_reason column can give you more information.
Quando le dimensioni di Archivio query superano la quota, la funzionalità passa alla modalità readon_only.When Query Store size exceeds the quota, the feature will switch to readon_only mode.

Ottenere le opzioni di Archivio queryGet Query Store options

Per informazioni dettagliate sullo stato di Archivio query, eseguire l'istruzione seguente in un database utente.To find out detailed information about Query Store status, execute following in a user database.

SELECT * FROM sys.database_query_store_options;  

Impostazione dell'intervallo di Archivio querySetting Query Store interval

È possibile ignorare l'intervallo per l'aggregazione delle statistiche di runtime delle query (impostazione predefinita: 60 minuti).You can override interval for aggregating query runtime statistics (default is 60 minutes).

ALTER DATABASE <database_name>   
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);  

Nota

I valori arbitrari non sono consentiti per INTERVAL_LENGTH_MINUTES.Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES. Usare una di queste opzioni: 1, 5, 10, 15, 30, 60 o 1440 minuti.Use one of the following: 1, 5, 10, 15, 30, 60, or 1440 minutes.

Il nuovo valore per l'intervallo viene esposto mediante la visualizzazione sys.database_query_store_options .New value for interval is exposed through sys.database_query_store_options view.

Utilizzo dello spazio di Archivio queryQuery Store space usage

Per controllare le dimensioni e i limiti correnti di Archivio query, eseguire l'istruzione seguente nel database utente.To check current the Query Store size and limit execute the following statement in the user database.

SELECT current_storage_size_mb, max_storage_size_mb   
FROM sys.database_query_store_options;  

Se lo spazio di archiviazione di Archivio query è esaurito, usare l'istruzione seguente per estenderlo.If the Query Store storage is full use the following statement to extend the storage.

ALTER DATABASE <database_name>   
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);  

Impostare tutte le opzioni di Archivio querySet all Query Store options

È possibile impostare più opzioni di Archivio query contemporaneamente con un'unica istruzione ALTER DATABASE.You can set multiple Query Store options at once with a single ALTER DATABASE statement.

ALTER DATABASE <database name>   
SET QUERY_STORE (  
    OPERATION_MODE = READ_WRITE,  
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),  
    DATA_FLUSH_INTERVAL_SECONDS = 3000,  
    MAX_STORAGE_SIZE_MB = 500,  
    INTERVAL_LENGTH_MINUTES = 15,  
    SIZE_BASED_CLEANUP_MODE = AUTO,  
    QUERY_CAPTURE_MODE = AUTO,  
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON 
);  

Pulizia dello spazioCleaning up the space

Le tabelle interne di Archivio query vengono create nel filegroup PRIMARY durante la creazione del database e tale configurazione non è modificabile in un secondo momento.Query Store internal tables are created in the PRIMARY filegroup during database creation and that configuration cannot be changed later. Se si esaurisce lo spazio, è possibile cancellare dati di Archivio query meno recenti usando l'istruzione seguente.If you are running out of space you might want to clear older Query Store data by using the following statement.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;  

In alternativa, è possibile cancellare solo dati di query ad hoc perché sono meno rilevanti per le ottimizzazioni query e l'analisi del piano, ma occupano molto spazio.Alternatively, you might want to clear up only ad-hoc query data, since it is less relevant for query optimizations and plan analysis but takes up just as much space.

Eliminare query ad-hoc È possibile eliminare le query che sono state eseguite solo una volta e che risalgono a più di 24 ore prima.Delete ad-hoc queries This deletes the queries that were only executed only once and that are more than 24 hours old.

DECLARE @id int  
DECLARE adhoc_queries_cursor CURSOR   
FOR   
SELECT q.query_id  
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q   
    ON q.query_text_id = qt.query_text_id  
JOIN sys.query_store_plan AS p   
    ON p.query_id = q.query_id  
JOIN sys.query_store_runtime_stats AS rs   
    ON rs.plan_id = p.plan_id  
GROUP BY q.query_id  
HAVING SUM(rs.count_executions) < 2   
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  
ORDER BY q.query_id ;  

OPEN adhoc_queries_cursor ;  
FETCH NEXT FROM adhoc_queries_cursor INTO @id;  
WHILE @@fetch_status = 0  
    BEGIN   
        PRINT @id  
        EXEC sp_query_store_remove_query @id  
        FETCH NEXT FROM adhoc_queries_cursor INTO @id  
    END   
CLOSE adhoc_queries_cursor ;  
DEALLOCATE adhoc_queries_cursor;  

Per la cancellazione dei dati non più necessari, è possibile definire procedure personalizzate con logiche diverse.You can define your own procedure with different logic for clearing up data you no longer want.

Per rimuovere i dati non necessari, nell'esempio precedente viene usata la stored procedure estesa sp_query_store_remove_query .The example above uses the sp_query_store_remove_query extended stored procedure for removing unnecessary data. È anche possibile usare:You can also use:

  • sp_query_store_reset_exec_stats : consente di cancellare le statistiche di runtime per un piano specifico.sp_query_store_reset_exec_stats – to clear runtime statistics for a given plan.

  • sp_query_store_remove_plan : consente di rimuovere un singolo piano.sp_query_store_remove_plan – to remove a single plan.

Controllo delle prestazioni e risoluzione dei problemi Performance Auditing and Troubleshooting

Archivio query conserva la cronologia delle metriche relative a compilazione e runtime per tutte le esecuzioni delle query e questo consente di ottenere facilmente informazioni sul carico di lavoro.Query Store keeps a history of compilation and runtime metrics throughout query executions, allowing you to ask questions about your workload.

Ultime n query eseguite sul database?Last n queries executed on the database?

SELECT TOP 10 qt.query_sql_text, q.query_id,   
    qt.query_text_id, p.plan_id, rs.last_execution_time  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
ORDER BY rs.last_execution_time DESC;  

Numero di esecuzioni per ogni query.Number of executions for each query?

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,   
    SUM(rs.count_executions) AS total_execution_count  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text  
ORDER BY total_execution_count DESC;  

Numero di query con il tempo medio di esecuzione maggiore nell'ultima ora.The number of queries with the longest average execution time within last hour?

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,  
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,   
    rs.last_execution_time   
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id  
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())  
ORDER BY rs.avg_duration DESC;  

Numero di query con il maggior numero medio di letture I/O fisiche nelle ultime 24 ore, con il numero medio di righe e di esecuzioni corrispondente.The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count?

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,   
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,   
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p   
    ON q.query_id = p.query_id   
JOIN sys.query_store_runtime_stats AS rs   
    ON p.plan_id = rs.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi   
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id  
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())   
ORDER BY rs.avg_physical_io_reads DESC;  

Query con più piani.Queries with multiple plans? Queste query sono particolarmente interessanti perché sono candidati a regressioni in seguito alla modifica del piano selezionato.These queries are especially interesting because they are candidates for regressions due to plan choice change. La query seguente consente di identificare queste query unitamente a tutti i piani:The following query identifies these queries along with all plans:

WITH Query_MultPlans  
AS  
(  
SELECT COUNT(*) AS cnt, q.query_id   
FROM sys.query_store_query_text AS qt  
JOIN sys.query_store_query AS q  
    ON qt.query_text_id = q.query_text_id  
JOIN sys.query_store_plan AS p  
    ON p.query_id = q.query_id  
GROUP BY q.query_id  
HAVING COUNT(distinct plan_id) > 1  
)  

SELECT q.query_id, object_name(object_id) AS ContainingObject,   
    query_sql_text, plan_id, p.query_plan AS plan_xml,  
    p.last_compile_start_time, p.last_execution_time  
FROM Query_MultPlans AS qm  
JOIN sys.query_store_query AS q  
    ON qm.query_id = q.query_id  
JOIN sys.query_store_plan AS p  
    ON q.query_id = p.query_id  
JOIN sys.query_store_query_text qt   
    ON qt.query_text_id = q.query_text_id  
ORDER BY query_id, plan_id;  

Query in cui si è verificata di recente una regressione delle prestazioni (confrontando momenti diversi).Queries that recently regressed in performance (comparing different point in time)? L'esempio di query seguente restituisce tutte le query in cui il tempo di esecuzione è raddoppiato nelle ultime 48 ore in seguito alla modifica del piano selezionato.The following query example returns all queries for which execution time doubled in last 48 hours due to a plan choice change. La query confronta tutti gli intervalli delle statistiche di runtime affiancandoli.Query compares all runtime stat intervals side by side.

SELECT   
    qt.query_sql_text,   
    q.query_id,   
    qt.query_text_id,   
    rs1.runtime_stats_id AS runtime_stats_id_1,  
    rsi1.start_time AS interval_1,   
    p1.plan_id AS plan_1,   
    rs1.avg_duration AS avg_duration_1,   
    rs2.avg_duration AS avg_duration_2,  
    p2.plan_id AS plan_2,   
    rsi2.start_time AS interval_2,   
    rs2.runtime_stats_id AS runtime_stats_id_2  
FROM sys.query_store_query_text AS qt   
JOIN sys.query_store_query AS q   
    ON qt.query_text_id = q.query_text_id   
JOIN sys.query_store_plan AS p1   
    ON q.query_id = p1.query_id   
JOIN sys.query_store_runtime_stats AS rs1   
    ON p1.plan_id = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi1   
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan AS p2   
    ON q.query_id = p2.query_id   
JOIN sys.query_store_runtime_stats AS rs2   
    ON p2.plan_id = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval AS rsi2   
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id  
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())   
    AND rsi2.start_time > rsi1.start_time   
    AND p1.plan_id <> p2.plan_id  
    AND rs2.avg_duration > 2*rs1.avg_duration  
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;  

Per visualizzare tutte le regressioni delle prestazioni, non solo quelle correlate alla modifica del piano selezionato, è sufficiente rimuovere la condizione AND p1.plan_id <> p2.plan_id dalla query precedente.If you want to see performance all regressions (not only those related to plan choice change) than just remove condition AND p1.plan_id <> p2.plan_id from the previous query.

Query che rimangono più a lungo in attesaQueries that are waiting the most? Questa query restituirà le prime 10 query che rimangono più a lungo in attesa.This query will return top 10 queries that wait the most.

 SELECT TOP 10
   qt.query_text_id,
   q.query_id,
   p.plan_id,
   sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC

Query in cui si è verificata di recente una regressione delle prestazioni (confrontando esecuzioni recenti e della cronologia).Queries that recently regressed in performance (comparing recent vs. history execution)? La query successiva confronta le esecuzioni di query in base ai periodi di esecuzione.The next query compares query execution based periods of execution. In questo specifico esempio la query confronta le esecuzioni nel periodo recente (1 ora) con il periodo della cronologia (ultimo giorno) e identifica quelle che hanno introdotto additional_duration_workload.In this particular example the query compares execution in recent period (1 hour) vs. history period (last day) and identifies those that introduced additional_duration_workload. Questa metrica viene ottenuta moltiplicando la differenza tra l'esecuzione media recente e quella media della cronologia e il numero delle esecuzioni recenti.This metrics is calculated as a difference between recent average execution and history average execution multiplied by the number of recent executions. Rappresenta in effetti la quantità di esecuzioni recenti con durata aggiuntiva introdotte rispetto alla cronologia:It actually represents how much of additional duration recent executions introduced compared to history:

--- "Recent" workload - last 1 hour  
DECLARE @recent_start_time datetimeoffset;  
DECLARE @recent_end_time datetimeoffset;  
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  
SET @recent_end_time = SYSUTCDATETIME();  

--- "History" workload  
DECLARE @history_start_time datetimeoffset;  
DECLARE @history_end_time datetimeoffset;  
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());  
SET @history_end_time = SYSUTCDATETIME();  

WITH  
hist AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
     FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @history_start_time   
               AND rs.last_execution_time < @history_end_time)  
        OR (rs.first_execution_time \<= @history_start_time   
               AND rs.last_execution_time > @history_start_time)  
        OR (rs.first_execution_time \<= @history_end_time   
               AND rs.last_execution_time > @history_end_time)  
    GROUP BY p.query_id  
),  
recent AS  
(  
    SELECT   
        p.query_id query_id,   
        CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration,   
        SUM(rs.count_executions) count_executions,  
        COUNT(distinct p.plan_id) num_plans   
    FROM sys.query_store_runtime_stats AS rs  
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
    WHERE  (rs.first_execution_time >= @recent_start_time   
               AND rs.last_execution_time < @recent_end_time)  
        OR (rs.first_execution_time \<= @recent_start_time   
               AND rs.last_execution_time > @recent_start_time)  
        OR (rs.first_execution_time \<= @recent_end_time   
               AND rs.last_execution_time > @recent_end_time)  
    GROUP BY p.query_id  
)  
SELECT   
    results.query_id query_id,  
    results.query_text query_text,  
    results.additional_duration_workload additional_duration_workload,  
    results.total_duration_recent total_duration_recent,  
    results.total_duration_hist total_duration_hist,  
    ISNULL(results.count_executions_recent, 0) count_executions_recent,  
    ISNULL(results.count_executions_hist, 0) count_executions_hist   
FROM  
(  
    SELECT  
        hist.query_id query_id,  
        qt.query_sql_text query_text,  
        ROUND(CONVERT(float, recent.total_duration/  
                   recent.count_executions-hist.total_duration/hist.count_executions)  
               *(recent.count_executions), 2) AS additional_duration_workload,  
        ROUND(recent.total_duration, 2) total_duration_recent,   
        ROUND(hist.total_duration, 2) total_duration_hist,  
        recent.count_executions count_executions_recent,  
        hist.count_executions count_executions_hist     
    FROM hist   
        JOIN recent   
            ON hist.query_id = recent.query_id   
        JOIN sys.query_store_query AS q   
            ON q.query_id = hist.query_id  
        JOIN sys.query_store_query_text AS qt   
            ON q.query_text_id = qt.query_text_id      
) AS results  
WHERE additional_duration_workload > 0  
ORDER BY additional_duration_workload DESC  
OPTION (MERGE JOIN);  

Misure per garantire la stabilità delle prestazioni di esecuzione delle query Maintaining Query Performance Stability

Per le query eseguite più volte è possibile notare che SQL ServerSQL Server usa piani diversi che comportano durate e utilizzi diversi delle risorse.For queries executed multiple times you may notice that SQL ServerSQL Server uses different plans, resulting in different resource utilization and duration. Archivio query consente di rilevare il momento in cui si verifica una regressione delle prestazioni di esecuzione delle query e di determinare il piano ottimale in un periodo di interesse.With Query Store you can detect when query performance regressed and determine the optimal plan within a period of interest. È quindi possibile forzare il piano ottimale per le future esecuzioni delle query.You can then force that optimal plan for future query execution.

È anche possibile identificare incoerenze nelle prestazioni di una query con parametri (impostati sia automaticamente che manualmente).You can also identify inconsistent query performance for a query with parameters (either auto- parameterized or manually parameterized). Tra i diversi piani è possibile identificare quello più rapido e adatto per tutti o per la maggior parte dei valori di parametro e forzarne l'uso in modo da garantire prestazioni prevedibili per un ampio numero di scenari utente.Among different plans you can identify the plan which is fast and optimal enough for all or most of the parameter values and force that plan, keeping predictable performance for the wider set of user scenarios.

Forzare un piano per una query (applicando criteri di utilizzo forzato).Force or a plan for a query (apply forcing policy). Quando si forza un piano per una determinata query, la query viene sempre eseguita con il piano di cui è stato forzato l'utilizzo.When a plan is forced for a certain query, every time a query comes to execution it will be executed with the plan that is forced.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;  

Se si usa sp_query_store_force_plan , è possibile forzare solo piani che sono stati registrati da Archivio query come piani per tale query.When using sp_query_store_force_plan you can only force plans that were recorded by Query Store as a plan for that query. In altre parole, gli unici piani disponibili per una query sono quelli già usati per eseguire tale query mentre Archivio query era attivo.In other words, the only plans available for a query are those that were already used to execute that query while Query Store was active.

Rimuovere l'utilizzo forzato del piano per una query.Remove plan forcing for a query. Per impiegare di nuovo Query Optimizer di SQL ServerSQL Server per calcolare il piano di query ottimale, usare sp_query_store_unforce_plan per annullare l'utilizzo forzato del piano selezionato per la query.To rely again on the SQL ServerSQL Server query optimizer to calculate the optimal query plan, use sp_query_store_unforce_plan to unforce the plan that was selected for the query.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;  

Vedere ancheSee Also

Procedure consigliate per l'archivio query Best Practice with the Query Store
Uso di Archivio query con OLTP in-memoria Using the Query Store with In-Memory OLTP
Scenari di utilizzo dell'Archivio query Query Store Usage Scenarios
Come Archivio query raccoglie i dati How Query Store Collects Data
Stored procedure di Archivio query (Transact-SQL) Query Store Stored Procedures (Transact-SQL)
Viste del catalogo di Archivio query (Transact-SQL) Query Store Catalog Views (Transact-SQL)
Monitoraggio e ottimizzazione delle prestazioni Monitor and Tune for Performance
Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni Performance Monitoring and Tuning Tools
Aprire Monitoraggio attività (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
Statistiche sulle query dinamiche Live Query Statistics
Monitoraggio attività Activity Monitor
sys.database_query_store_options (Transact-SQL)sys.database_query_store_options (Transact-SQL)
Uso dell'archivio query nel database SQL di AzureOperating the Query Store in Azure SQL Database