Scenari di utilizzo dell'Archivio queryQuery Store Usage Scenarios

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

Archivio query può essere usato in diversi scenari in cui è fondamentale rilevare e garantire prestazioni prevedibili del carico di lavoro.Query Store can be used in wide set of scenarios when tracking and ensuring predictable workload performance is critical. Di seguito sono riportati alcuni esempi:Here are some examples you can consider:

  • Individuare e risolvere le query con regressioni nella scelta del pianoPinpoint and fix queries with plan choice regressions

  • Identificare e ottimizzare le prime query per consumo di risorseIdentify and tune top resource consuming queries

  • Test A/BA/B testing

  • Mantenere la stabilità delle prestazioni durante l'aggiornamento alla nuova versione di SQL ServerSQL ServerKeep performance stability during the upgrade to newer SQL ServerSQL Server

  • Identificare e migliorare i carichi di lavoro ad hocIdentify and improve ad-hoc workloads

Individuare e risolvere le query con regressioni nella scelta del pianoPinpoint and fix queries with plan choice regressions

Durante l'esecuzione di query normali, Query Optimizer può selezionare un piano diverso se alcuni input importanti vengono modificati, ad esempio se viene modificata la cardinalità dei dati, se vengono creati, modificati o eliminati indici, se vengono aggiornate le statistiche e così via. In generale, il nuovo piano selezionato è migliore o uguale a quello usato in precedenza.During the regular query execution Query Optimizer may decide to take a different plan because important inputs became different: data cardinality has changed, indexes have been created, altered or dropped, statistics have been updated, etc. For the most part new plan it picks is better or about the same than one was used previously. Tuttavia, a volte il nuovo piano risulta decisamente peggiore. In questi casi si parla di regressione nella scelta del piano.However, there are cases when new plan is significantly worse - we refer to that situation as plan choice change regression. Prima di Archivio query, identificare e risolvere questo problema risultava molto difficile perché SQL ServerSQL Server non forniva un archivio dati predefinito che gli utenti potessero esaminare per i piani di esecuzione usati nel corso del tempo.Prior to Query Store, it was an issue very difficult to identify and fix as SQL ServerSQL Server didn’t provide built-in data store for users to look at for execution plans that were used over time.

Con Query Store, è possibile eseguire rapidamente le operazioni seguenti:With the Query Store you can quickly:

  • Identificare tutte le query le cui metriche di esecuzione siano peggiorate nel periodo di tempo di interesse (ultima ora, giorno, settimana e così via).Identify all queries which execution metrics have been degraded in the period of time of interest (last hour, day, week, etc.). Usare Query regredite in SQL Server Management StudioSQL Server Management Studio per velocizzare l'analisi.Use Regressed Queries in SQL Server Management StudioSQL Server Management Studio to speed up your analysis.

  • Tra le query regredite è molto semplice individuare quelle con più piani e che hanno subito un peggioramento a causa di una scelta errata di un piano.Among the regressed queries it’s very easy to find those that had multiple plans and which degraded because of the bad plan choice. Usare il riquadro Riepilogo piano in Query regredite per visualizzare tutti i piani per una query regredita e le relative prestazioni della query nel tempo.Use Plan Summary pane in Regressed Queries to visualize all plans for a regressed query and their query performance over time.

  • Forzare il piano precedente dalla cronologia se ha dimostrato di essere più efficace.Force the previous plan from the history if it proved to be better. Usare il pulsante Forza piano in Query regredite per forzare un piano selezionato per la query.Use Force Plan button in Regressed Queries to force selected plan for the query.

    query-store-usage-1query-store-usage-1

    Per una descrizione dettagliata dello scenario, vedere il blog sull' Archivio query: un'utilità di traccia eventi dei dati per il database .For detailed description of the scenario refer to Query Store: A flight data recorder for your database blog.

Identificare e ottimizzare le prime query per consumo di risorseIdentify and tune top resource consuming queries

Anche se il carico di lavoro può generare migliaia di query, nella pratica la gran parte delle risorse di sistema viene usata solo da poche query che, di conseguenza, richiedono attenzione.Although your workload may generate thousands of queries, typically only a handful of them actually use the most of the system resources and therefore require your attention. Tra le prime query per consumo di risorse si rilevano in genere quelle regredite o quelle che possono essere migliorate con un'ottimizzazione aggiuntiva.Among top resource consuming queries you will typically find those that are either regressed or those that can be improved with additional tuning.

Il modo più facile per iniziare l'esplorazione consiste nell'aprire Prime query per consumo di risorse in Management StudioManagement Studio.The easiest way to start exploration is to open Top Resource Consuming Queries in Management StudioManagement Studio. L'interfaccia utente è suddivisa in tre riquadri: un istogramma che rappresenta le prime query per consumo di risorse (sinistra), un riepilogo del piano per la query selezionata (destra) e un piano di query visivo per il piano selezionato (in basso).User interface is separated into three panes: A histogram representing top resource consuming queries (left), a plan summary for selected query (right) and visual query plan for selected plan (bottom). Fare clic sul pulsante Configura per controllare il numero di query da analizzare e l'intervallo di tempo di interesse.Click the Configure button to control how many queries you want to analyze and the time interval of interest. È anche possibile scegliere tra diverse dimensioni di consumo delle risorse (durata, CPU, memoria, operazioni I/O, numero di esecuzione) e la baseline (Media, Min, Max, Totale, Deviazione standard).Additionally, you can choose between different resource consumption dimensions (duration, CPU, memory, IO, number of executions) and the baseline (Average, Min, Max, Total, Standard Deviation).

query-store-usage-2query-store-usage-2

Esaminare il riepilogo del piano a destra per analizzare la cronologia di esecuzione e avere informazioni sui diversi piani e sulle statistiche di runtime.Look at the plan summary on the right to analyze the execution history and learn about the different plans and their runtime statistics. Usare il riquadro inferiore per esaminare i diversi piani o per eseguire un confronto visivo con il rendering in modalità affiancata (usare il pulsante Confronta).Use the bottom pane to examine the different plans or to compare them visually, rendered side by side (use the Compare button).

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

  1. Se la query è stata eseguita con più piani e l'ultimo piano è significativamente peggiore del piano precedente, è possibile usare il meccanismo di utilizzo forzato del piano per essere certi che SQL ServerSQL Server userà il piano ottimale per le esecuzioni successiveIf the query was executed with multiple plans and the last plan is significantly worse than previous plan, you can use the plan forcing mechanism to ensure SQL ServerSQL Server will use the optimal plan for future executions

  2. Verificare se Query Optimizer rileva indici mancanti nel piano XML.Check if the optimizer is suggesting any missing indexes in XML plan. In caso affermativo, creare l'indice mancante e usare Archivio query per valutare le prestazioni delle query dopo la creazione dell'indiceIf yes, create the missing index and use the Query Store to evaluate query performance after the index creation

  3. Verificare che le statistiche siano aggiornate per le tabelle sottostanti usate dalla query.Make sure that the statistics are up-to-date for the underlying tables used by the query.

  4. Verificare che gli indici usati dalla query siano deframmentati.Make sure that indexes used by the query are defragmented.

  5. Valutare l'opportunità di riscrivere la query con costo elevato.Consider rewriting expensive query. Ad esempio, sfruttare i vantaggi associati alla parametrizzazione della query e ridurre l'utilizzo di SQL dinamico.For example, take advantages of query parameterization and reduce usage of dynamic SQL. Implementare la logica ottimale durante la lettura dei dati (applicare il filtro dei dati sul lato database, non sul lato applicazione).Implement optimal logic when read the data (apply data filtering on database side, not on application side).

Test A/BA/B testing

Usare Archivio query per confrontare le prestazioni del carico di lavoro prima e dopo la modifica dell'applicazione che si intende introdurre.Use Query Store to compare workload performance before and after the application change you plan to introduce. L'elenco seguente contiene alcuni esempi in cui è possibile usare Archivio query per valutare l'impatto della modifica dell'ambiente o dell'applicazione sulle prestazioni del carico di lavoro:The following list contains several examples where you can use Query Store to assess impact of the environment or application change to the workload performance:

  • Implementazione della nuova versione dell'applicazione.Rolling out new application version.

  • Aggiunta di nuovo hardware al server.Adding new hardware to the server.

  • Creazione degli indici mancanti nelle tabelle a cui fanno riferimento le query con costo elevato.Creating missing indexes on tables referenced by expensive queries.

  • Applicazione di un criterio di filtro per la sicurezza a livello di riga.Applying filtering policy for row-level security. Per altre informazioni, vedere il blog sull' ottimizzazione della sicurezza a livello di riga con Archivio query.For more details see Optimizing Row Level Security with Query Store.

  • Aggiunta del controllo temporale delle versioni di sistema alle tabelle che vengono modificate di frequente dalle applicazioni OLTP.Adding temporal system-versioning to tables that are frequently modified by your OLTP applications.

    In qualsiasi scenario, applicare il flusso di lavoro seguente:In any of these scenarios apply the following workflow:

  1. Eseguire il carico di lavoro con Archivio query prima della modifica pianificata per generare dati di base delle prestazioni.Run your workload with the Query Store before the planned change to generate performance baseline.

  2. Applicare la modifica dell'applicazione in un determinato momento controllato.Apply application change at the controlled moment in time.

  3. Continuare l'esecuzione del carico di lavoro per il tempo necessario a generare un'immagine delle prestazioni del sistema dopo la modificaContinue running the workload long enough to generate performance image of the system after the change

  4. Confrontare i risultati di 1 e 3.Compare results from #1 and #3.

    1. Aprire Overall Database Consumption (Consumo database globale) per determinare l'impatto sull'intero database.Open Overall Database Consumption to determine impact to the entire database.

    2. Aprire Prime query per consumo di risorse (o eseguire un'analisi con Transact-SQLTransact-SQL) per analizzare l'impatto della modifica nelle query più importanti.Open Top Resource Consuming Queries (or run your own analysis using Transact-SQLTransact-SQL) to analyze impact of the change to the most important queries.

  5. Decidere se mantenere la modifica o eseguire operazioni di rollback se le nuove prestazioni sono inaccettabili.Decide whether to keep the change or perform roll back in case when new performance is unacceptable.

    La figura seguente mostra l'analisi di Archivio query (passaggio 4) in caso di creazione dell'indice mancante.The following illustration shows Query Store analysis (step 4) in case of missing index creation. Aprire il riquadro Prime query per consumo di risorse /Riepilogo piano per ottenere la visualizzazione per la query che dovrebbe essere interessata dalla creazione dell'indice:Open Top Resource Consuming Queries / Plan summary pane to get this view for the query that should be impacted by the index creation:

    query-store-usage-3query-store-usage-3

    È anche possibile confrontare i piani prima e dopo la creazione dell'indice con il rendering in modalità affiancata,Additionally, you can compare plans before and after index creation by rendering them side by side. usando l'opzione della barra degli strumenti Confronta i piani per la query selezionata in una finestra separata, contrassegnata con un quadrato rosso nella barra degli strumenti.(“Compare the plans for the selected query in a separate window” toolbar option which is marked with red square on the toolbar.)

    query-store-usage-4query-store-usage-4

    Nel piano prima della creazione dell'indice (plan_id = 1, sopra) manca l'hint per l'indice e si può vedere che Clustered Index Scan è l'operatore con il costo più elevato nella query (rettangolo rosso).Plan before index creation (plan_id = 1, above) has missing index hint and you can inspect that Clustered Index Scan was the most expensive operator in the query (red rectangle).

    Nel piano dopo la creazione dell'indice mancante (plan_id = 15, sotto) ora è presente Index Seek (Nonclustered) che consente di ridurre il costo complessivo della query e di migliorare le prestazioni (rettangolo verde).Plan after missing index creation (plan_id = 15, below) now has Index Seek (Nonclustered) which reduces the overall cost of the query and improves it performance (green rectangle).

    In base all'analisi è consigliabile mantenere l'indice visto che le prestazioni delle query sono state migliorate.Based on analysis you would likely keep the index as query performance has been improved.

Mantenere la stabilità delle prestazioni durante l'aggiornamento alla nuova versione di SQL ServerSQL Server Keep performance stability during the upgrade to newer SQL ServerSQL Server

Prima di SQL Server 2014SQL Server 2014, gli utenti erano esposti al rischio di regressione delle prestazioni durante l'aggiornamento alla versione più recente della piattaforma.Prior to SQL Server 2014SQL Server 2014, users were exposed to the risk of performance regression during the upgrade to the latest platform version. Il motivo era che la versione più recente di Query Optimizer si attivava subito dopo l'installazione dei nuovi bit.The reason for that was the fact that latest version of Query Optimizer became active immediately once new bits are installed.

A partire da SQL Server 2014SQL Server 2014 tutte le modifiche di Query Optimizer sono associate al livello di compatibilità del database più recente, quindi i piani non vengono modificati esattamente al momento di aggiornamento, ma quando un utente modifica COMPATIBILITY_LEVEL in un livello più recente.Starting with SQL Server 2014SQL Server 2014 all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL to the latest one. Questa funzionalità, in combinazione con Archivio query, offre un alto livello di controllo sulle prestazioni delle query nel processo di aggiornamento.This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process. Il flusso di lavoro di aggiornamento consigliato è illustrato nella figura seguente:Recommended upgrade workflow is shown in the following picture:

query-store-usage-5query-store-usage-5

  1. Aggiornare SQL ServerSQL Server senza modificare il livello di compatibilità del database.Upgrade SQL ServerSQL Server without changing the database compatibility level. In questo modo non si espongono le modifiche più recenti di Query Optimizer, ma è possibile usare le funzionalità più recenti di SQL ServerSQL Server, tra cui Query Store.It doesn’t expose the latest Query Optimizer changes but still provides newer SQL ServerSQL Server features including Query Store.

  2. Abilitare Query Store.Enable Query Store. Per altre informazioni su questo argomento, vedere Adattare Query Store al proprio carico di lavoro.For more information on this topic, see Keep Query Store adjusted to your workload.

  3. Consentire a Query Store di acquisire query e piani e stabilire una baseline delle prestazioni del processo con il livello di compatibilità del database sorgente/precedente.Allow Query Store to capture queries and plans, and establishes a performance baseline with the source/previous database compatibility level. Non proseguire con i passaggi successivi finché tutti i piani sono stati acquisiti e la baseline è diventata stabile.Stay at this step long enough to capture all plans and get a stable baseline. Tale operazione può avere la durata di un ciclo aziendale normale per un carico di lavoro di produzione.This can be the duration of an usual business cycle for a production workload.

  4. Passare al livello di compatibilità più recente: esporre il carico di lavoro alle modifiche più recenti di Query Optimizer e consentire la possibilità di creare nuovi piani.Move to latest database compatibility level: get your workload exposed to the latest Query Optimizer changes and let it potentially create new plans.

  5. Usare Query Store per le correzioni di analisi e regressioni: nella maggior parte dei casi, le nuove modifiche di Query Optimizer genereranno piani migliori.Use Query Store for analysis and regression fixes: for the most part, the new Query Optimizer changes should produce better plans. Query Store offre comunque un modo semplice per identificare le regressioni di scelta del piano e risolverle usando un meccanismo che forza il piano.However, Query Store will provide an easy way to identify plan choice regressions and fix them using a plan forcing mechanism.

Identificare e migliorare i carichi di lavoro ad hocIdentify and improve ad-hoc workloads

Alcuni carichi di lavoro non includono query dominanti che è possibile ottimizzare per migliorare le prestazioni complessive dell'applicazione.Some workloads do not have dominant queries that you can tune to improve overall application performance. In genere, questi carichi di lavoro sono caratterizzati da un numero relativamente elevato di query diverse, ognuna delle quali utilizza una parte delle risorse di sistema.Those workloads are typically characterized with relatively large number of different queries each of them consuming portion of system resources. Essendo univoche, queste query vengono eseguite raramente, (di solito una sola volta, quindi si consiglia di assegnare un nome ad hoc), di conseguenza il consumo di runtime non è critico.Being unique, those queries are executed very rarely (usually only once, thus name ad hoc), so their runtime consumption is not critical. D'altra parte, dato che l'applicazione genera continuamente nuove query, una parte significativa delle risorse di sistema viene impiegata per la compilazione di query, il che non rappresenta uno scenario ottimale.On the other hand, given that application is generating net new queries all the time, significant portion of system resources is spent on query compilation which is not optimal. Questa situazione non è ideale per Archivio query perché un numero elevato di query e piani ne occupa lo spazio riservato e causa probabilmente il passaggio in tempi molto rapidi alla modalità di sola lettura di Archivio query.This is not ideal situation for Query Store either given that large number of queries and plans flood the space you have reserved which means that Query Store will likely end up in the read-only mode very quickly. Se è attivato Modalità di pulizia basata sulle dimensioni , un'opzionefortemente consigliata per mantenere Archivio query sempre attivo e in esecuzione, il processo in background esegue costantemente la pulizia delle strutture di Archivio query assorbendo molto spesso notevoli risorse di sistema.If you activated Size Based Cleanup Policy (highly recommended to keep Query Store always up and running), then background process will be cleaning Query Store structures most of the time also taking significant system resources.

La visualizzazionePrime query per consumo di risorse fornisce la prima indicazione relativa alla natura ad hoc del carico di lavoro:Top Resource Consuming Queries view will give you first indication of the ad-hoc nature of your workload:

query-store-usage-6query-store-usage-6

Usare la metrica Conteggio esecuzioni per verificare se le prime query sono ad hoc (questa operazione richiede l'esecuzione di Archivio query con QUERY_CAPTURE_MODE = ALL).Use Execution Count metric to analyze whether your top queries are ad hoc (this requires you to run Query Store with QUERY_CAPTURE_MODE = ALL). Dal diagramma precedente è possibile vedere che il 90% delle Prime query per consumo di risorse viene eseguito una sola volta.From diagram above you can see that 90% of your Top Resource Consuming Queries are executed only once.

In alternativa, è possibile eseguire script Transact-SQLTransact-SQL per ottenere il numero totale di testi della query, query e piani nel sistema e determinarne le differenze confrontando query_hash e plan_hash:Alternatively, you can run Transact-SQLTransact-SQL script to get total number of query texts, queries and plans in the system and determine how different they are by comparing their query_hash and plan_hash:

/*Do cardinality analysis when suspect on ad-hoc workloads*/  
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Si tratta di un potenziale risultato che può essere prodotto nel caso di carichi di lavoro con query ad hoc:This is one potential result you can get in case of workload with ad-hoc queries:

query-store-usage-7query-store-usage-7

Il risultato della query mostra che, nonostante il numero elevato di query e piani in Archivio query, query_hash e plan_hash in realtà non sono diversi.Query result shows that despite the large number of queries and plans in the Query Store their query_hash and plan_hash are actually not different. Un rapporto molto maggiore di 1 tra i testi della query univoci e il valore query_hash univoco indica che il carico di lavoro è un buon candidato per la parametrizzazione perché l'unica differenza tra le query è la costante letterale (parametro) fornita come parte del testo della query.Ratio between unique query texts and unique query_hash which is much bigger than 1 is an indication that workload is a good candidate for parameterization as the only difference between the queries is literal constant (parameter) provided as part of the query text.

In genere, questa situazione si verifica quando l'applicazione genera query, invece di richiamare stored procedure o query con parametri, oppure quando si basa su framework di mapping relazionale a oggetti che generano query per impostazione predefinita.Usually, this situation happens if your application generates queries (instead of invoking stored procedures or parameterized queries) or if it relies on object-relational mapping frameworks that generate queries by default.

Se si ha il controllo del codice dell'applicazione è possibile valutare l'opportunità di riscrivere il livello di accesso ai dati per usare stored procedure o query con parametri.If you are in control of the application code you may consider rewriting of the data access layer to utilize stored procedures or parameterized queries. Tuttavia, questa situazione può essere notevolmente migliorata anche senza modificare l'applicazione, forzando la parametrizzazione delle query per l'intero database, ovvero per tutte le query, oppure per i singoli modelli di query con lo stesso valore query_hash.However, this situation can be also significantly improved without application changes by forcing query parameterization for the entire database (all queries) or for the individual query templates with the same query_hash.

L'approccio che prevede l'uso di singoli modelli di query richiede la creazione di guide di piano:Approach with individual query templates requires plan guide creation:

/*Apply plan guide for the selected query template*/  
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  

EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

La soluzione che include le guide di piano è più precisa, ma richiede più lavoro.Solution with plan guides is more precise but it requires more work.

Se tutte o la maggior parte delle query sono idonee alla parametrizzazione automatica, valutare la modifica di FORCED PARAMETERIZATION per l'intero database:If all your queries (or majority of them) are candidates for auto-parameterization than changing FORCED PARAMETERIZATION for the entire database may be a better option:

/*Apply forced parameterization for entire database*/  
ALTER DATABASE <database name> SET PARAMETERIZATION  FORCED;  

Nota

Per altre informazioni su questo argomento, vedere le linee guida per l'utilizzo della parametrizzazione forzata.For more information on this topic, see Guidelines for Using Forced Parameterization.

Dopo aver applicato uno di questi passaggi, in Prime query per consumo di risorse viene visualizzata un'altra immagine del carico di lavoro.After you apply any of these steps, Top Resource Consuming Queries will show you different picture of your workload.

query-store-usage-8query-store-usage-8

In alcuni casi l'applicazione può generare un numero elevato di query diverse, che non sono adatte alla parametrizzazione automatica.In some cases your application may generate lots of different queries which are not good candidates for auto-parameterization. In questo caso viene visualizzato un numero elevato di query nel sistema, ma il rapporto tra le query univoche e il valore univoco query_hash è probabilmente prossimo a 1.In that case you will see large number of queries in the system but the ratio between unique queries and unique query_hash is likely close to 1.

In questo caso è opportuno abilitare l'opzione server Ottimizza per carichi di lavoro ad hoc per evitare di usare in modo non efficiente cache per query che probabilmente non verranno più eseguite.In that case you may want to enable the Optimize for Ad Hoc Workloads server option to prevent wasting cache memory on queries that won’t likely be executed again. Per impedire l'acquisizione di tali query in Archivio query, impostare QUERY_CAPTURE_MODE su AUTO.To prevent capture of those queries in the Query Store, set QUERY_CAPTURE_MODE to AUTO.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  

sp_configure 'optimize for ad hoc workloads', 1;  
GO  
RECONFIGURE;  
GO  

ALTER DATABASE  [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE  [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Vedere ancheSee Also

Monitoraggio delle prestazioni con Archivio query Monitoring Performance By Using the Query Store
Procedure consigliate per l'archivio queryBest Practice with the Query Store