Esplorare Query Store

Completato

SQL Server Query Store è una funzionalità di un singolo database che acquisisce automaticamente una cronologia delle query, dei piani e delle statistiche di runtime, semplificando la risoluzione dei problemi di prestazioni e l'ottimizzazione delle query. Fornisce anche informazioni dettagliate sui modelli di utilizzo del database e sull'utilizzo delle risorse.

Query Store è costituito da tre archivi:

• Archivio piani: archivia le informazioni stimate del piano di esecuzione.
• Archivio statistiche di runtime: archivia le informazioni sulle statistiche di esecuzione.
• Archivio delle statistiche di attesa: Conserva le informazioni sulle statistiche di attesa.

Abilitare Query Store

Query Store è abilitato per impostazione predefinita nei database SQL di Azure. Se si vuole usarlo con SQL Server e Azure Synapse Analytics, è necessario abilitarlo. Per abilitare la funzionalità Query Store, usare la query seguente valida per il proprio ambiente:

-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

Modalità di raccolta dei dati di Query Store

Query Store si integra con la pipeline di elaborazione delle query in più fasi. A ogni punto di integrazione, i dati vengono raccolti in memoria e scritti su disco in modo asincrono per ridurre al minimo l'overhead di I/O. I punti di integrazione sono i seguenti:

1. Quando una query viene eseguita per la prima volta, il testo e il piano di esecuzione stimato iniziale della query vengono inviati a Query Store e salvati in modo permanente.

2. Il piano viene aggiornato in Query Store quando una query viene ricompilata. Se la ricompilazione genera un nuovo piano di esecuzione, anche il piano viene salvato in modo permanente in Query Store per aggiungersi ai piani precedenti. Inoltre, Query Store tiene traccia delle statistiche di esecuzione per ogni piano di query a scopo di confronto.

3. Durante la compilazione e il controllo delle fasi di ricompilazione, Query Store identifica se è presente un piano forzato per l'esecuzione della query. La query viene ricompilata se Query Store fornisce un piano forzato diverso dal piano nella cache delle procedure.

4. Quando viene eseguita una query, le statistiche di runtime vengono salvate in modo permanente in Query Store. Query Store aggrega questi dati per garantire una rappresentazione accurata di ogni piano di query.

Per altre informazioni su come Query Store raccoglie i dati, vedere Come Query Store raccoglie i dati.

Scenari comuni

Query Store di SQL Server fornisce informazioni dettagliate preziose sulle prestazioni delle operazioni del database. Gli scenari comuni includono:

• Identificazione e correzione delle regressioni delle prestazioni causata dalla selezione del piano di esecuzione delle query inferiore.
• Identificazione e ottimizzazione delle query sull'utilizzo delle risorse più elevato.
• Test A/B per valutare l'impatto delle modifiche del database e dell'applicazione.
• Garantire la stabilità delle prestazioni dopo gli aggiornamenti di SQL Server.
• Individuazione delle query usate più di frequente.
• Controllo della cronologia dei piani di query per una query.
• Identificazione e miglioramento dei carichi di lavoro non pianificati.
• Comprendere le categorie di attesa prevalenti di un database e le query e i piani che influiscono sui tempi di attesa.
• Analisi dei modelli di utilizzo del database nel tempo in termini di utilizzo delle risorse (CPU, I/O, memoria).

Individuare le visualizzazioni di Query Store

Dopo aver abilitato Query Store in un database, la cartella Query Store è visibile per il database in Esplora oggetti. Per Azure Synapse Analytics, le visualizzazioni di Query Store sono elencate in Visualizzazioni sistema. Le visualizzazioni di Query Store offrono informazioni rapide e aggregate sugli aspetti delle prestazioni del database di SQL Server.

Query regredite

Una query regredita presenta una riduzione delle prestazioni nel tempo a causa delle modifiche apportate al piano di esecuzione. I piani di esecuzione stimati possono cambiare a causa di vari fattori, tra cui modifiche dello schema, modifiche alle statistiche e modifiche all'indice. L'analisi della cache delle procedure potrebbe essere il primo istinto, ma archivia solo il piano di esecuzione più recente per una query, e i piani possono essere rimossi in base alle richieste di memoria del sistema. Query Store, tuttavia, rende persistenti più piani di esecuzione per ogni query, consentendo la flessibilità di scegliere un piano specifico tramite l'uso forzato del piano per risolvere la regressione delle prestazioni delle query dovuta alle modifiche del piano.

La visualizzazione Query regredite può individuare le query le cui metriche di esecuzione stanno regredindo a causa di modifiche del piano di esecuzione in un intervallo di tempo specificato. Questa visualizzazione consente di filtrare in base a una metrica selezionata, ad esempio durata, tempo CPU, conteggio righe e altro ancora, e una statistica (totale, media, min, max o deviazione standard). Elenca quindi le prime 25 query regredite in base al filtro fornito. Per impostazione predefinita, viene visualizzata una visualizzazione grafico a barre delle query, ma facoltativamente è possibile visualizzare le query in un formato griglia.

Dopo aver selezionato una query nel riquadro delle query in alto a sinistra, nel riquadro di riepilogo del piano vengono visualizzati i piani di query persistenti associati alla query nel corso del tempo. La selezione di un piano di query nel riquadro Riepilogo piano mostra un piano di query grafico nel riquadro inferiore. I pulsanti della barra degli strumenti nel riquadro di riepilogo del piano e nel riquadro del piano di query grafico consentono di forzare il piano selezionato per la query selezionata. Questa struttura e comportamento del riquadro vengono usati in modo coerente in tutte le viste query SQL.

In alternativa, è possibile usare la stored procedure sp_query_store_force_plan per l'utilizzo forzato del piano.

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

Consumo complessivo risorse

La visualizzazione Utilizzo complessivo risorse consente di analizzare il consumo totale di risorse per più metriche di esecuzione(ad esempio conteggio esecuzioni, durata, tempo di attesa e altro) per un intervallo di tempo specificato. I grafici sottoposti a rendering sono interattivi; quando si seleziona una misura da uno dei grafici, in una nuova scheda viene visualizzata una visualizzazione drill-through con le query associate alla misura scelta.

La visualizzazione dei dettagli fornisce le prime 25 query per consumo di risorse che hanno contribuito alla metrica selezionata. Questa visualizzazione dei dettagli usa l'interfaccia coerente che consente l'ispezione delle query associate e dei relativi dettagli, la valutazione dei piani di query stimati salvati e, facoltativamente, l'utilizzo forzato del piano per migliorare le prestazioni. Questa visualizzazione è utile quando la contesa delle risorse di sistema diventa un problema, ad esempio quando l'utilizzo della CPU raggiunge la capacità.

Prime query per consumo di risorse

La visualizzazione Prime query per consumo di risorse è simile al drill-down dei dettagli della visualizzazione Consumo complessivo risorse. Consente anche di selezionare una metrica e una statistica come filtro. Tuttavia, le query visualizzate sono le prime 25 query con maggiore impatto in base al filtro e all'intervallo di tempo scelto.

La visualizzazione Prime query per consumo di risorse fornisce la prima indicazione della natura non pianificata del carico di lavoro durante l'identificazione e il miglioramento dei carichi di lavoro non pianificati. Nell'immagine seguente, ad esempio, la metrica Conteggio esecuzioni e la statistica Totale vengono selezionate per rivelare che circa 90% delle prime query che utilizzano le risorse vengono eseguite una sola volta.

Query con piani forzati

La visualizzazione Query con piani forzati offre un'analisi rapida delle query con piani di query forzati. Questa visualizzazione diventa rilevante se un piano forzato non viene più eseguito come previsto e deve essere rivalutato. Questa visualizzazione offre la possibilità di esaminare con facilità tutti i piani di esecuzione stimati salvati in modo permanente per una query selezionata e di determinare se un altro piano è ora più adatto per le prestazioni. In tal caso, i pulsanti della barra degli strumenti sono disponibili per annullare l'utilizzo forzato di un piano in base alle esigenze.

Screenshot delle query con piani forzati.

Query con variazione elevata

Le prestazioni delle query possono variare da un'esecuzione all'altra. La visualizzazione Query con variazione elevata contiene un'analisi delle query con la variazione o la deviazione standard più elevata per una metrica selezionata. L'interfaccia è coerente con la maggior parte delle visualizzazioni di Query Store e consente l'ispezione dettagliata delle query, la valutazione del piano di esecuzione e, facoltativamente, l'utilizzo forzato di un piano specifico. Usare questa visualizzazione per ottimizzare le query imprevedibili in un modello di prestazioni più coerente.

Statistiche di attesa query

La visualizzazione Statistiche di attesa query analizza le categorie di attesa più attive per il database ed esegue il rendering di un grafico. Questo grafico è interattivo: la selezione di una categoria di attesa visualizza i dettagli delle query che contribuiscono alla statistica del tempo di attesa.

L'interfaccia della visualizzazione dei dettagli è coerente anche con la maggior parte delle visualizzazioni di Query Store e consente l'ispezione dettagliata delle query, la valutazione del piano di esecuzione e, facoltativamente, l'utilizzo forzato di un piano specifico. Questa visualizzazione consente di identificare le query che influiscono sull'esperienza utente nelle applicazioni.

Rilevamento di query

La visualizzazione Query di rilevamento consente di analizzare una query specifica in base a un valore ID query immesso. Dopo l'esecuzione, la visualizzazione fornisce la cronologia di esecuzione completa della query. Un segno di spunta per un'esecuzione indica che è stato usato un piano forzato. Questa visualizzazione può fornire informazioni dettagliate sulle query, ad esempio quelle con piani forzati, per verificare che le prestazioni delle query rimangano stabili.

Uso di Query Store per trovare le attese delle query

Quando le prestazioni di un sistema iniziano a peggiorare, è opportuno consultare le statistiche di attesa delle query per identificare potenzialmente una causa. Oltre a identificare le query che devono essere ottimizzate, può anche far luce sui potenziali aggiornamenti dell'infrastruttura che sarebbero utili.

L'archivio query SQL offre la visualizzazione Statistiche di attesa delle query per fornire informazioni dettagliate sulle principali categorie di attesa per il database. Attualmente sono presenti 23 categorie di attesa.

Un grafico a barre visualizza le categorie di attesa con maggiore impatto per il database quando si apre la visualizzazione Statistiche di attesa query. Inoltre, un filtro posizionato sulla barra degli strumenti del riquadro delle categorie di attesa consente di calcolare le statistiche di attesa in base al tempo di attesa totale (impostazione predefinita), al tempo di attesa medio, al tempo di attesa minimo, al tempo di attesa massimo o al tempo di attesa della deviazione standard.

La selezione di una categoria di attesa esegue il drill-through dei dettagli delle query che contribuiscono a tale categoria di attesa. Da questa visualizzazione è possibile analizzare le singole query con maggiore impatto. È possibile accedere ai piani di esecuzione stimati salvati in modo permanente visualizzati nel riquadro Riepilogo piano selezionando una query nel riquadro delle query. Se si seleziona un piano di query nel riquadro Riepilogo piano, viene visualizzato il piano di query grafico nel riquadro inferiore. Da questa visualizzazione è possibile forzare o annullare l'utilizzo forzato di un piano di query per la query per migliorare le prestazioni.

Correzione automatica dei piani

In SQL Server 2017 e nel database SQL di Azure è stato introdotto il concetto di correzione automatica dei piani tramite l'analisi dei dati in Query Store. Quando si abilita Query Store con un database in SQL Server 2017 (o versione successiva) e nel database SQL di Azure, il motore di SQL Server cerca le regressioni dei piani di query e fornisce raccomandazioni. È possibile visualizzare queste raccomandazioni nella DMV sys.dm_db_tuning_recommendations. Queste raccomandazioni includono istruzioni T-SQL per forzare manualmente un piano di query quando le prestazioni sono in buono stato.

Se si acquisisce fiducia in questi consigli, è possibile abilitare SQL Server per forzare automaticamente i piani quando vengono rilevate regressioni. Abilitare la correzione automatica dei piani usando ALTER DATABASE e l'argomento AUTOMATIC_TUNING.

Per il database SQL di Azure, è anche possibile abilitare la correzione automatica dei piani tramite le opzioni di ottimizzazione automatica nel portale di Azure o le API REST. Le raccomandazioni per la correzione automatica dei piani sono sempre abilitate per qualsiasi database in cui è abilitato Query Store (che è l'impostazione predefinita per il database SQL di Azure e Istanza gestita di SQL di Azure). La correzione automatica dei piani (FORCE_PLAN) è abilitata per impostazione predefinita per i nuovi database per il database SQL di Azure.