Scenari di utilizzo di Query Store
Si applica a:
SQL Server 2016 (13.x) e versioni successiveAzure SQL DatabaseIstanza gestita di SQL di AzureAzure Synapse Analytics (solo pool SQL dedicato)database SQL in Microsoft Fabric
Query Store può essere utilizzato in un ampio set di scenari dove è cruciale monitorare e garantire prestazioni prevedibili del carico di lavoro. Di seguito sono riportati alcuni esempi:
Identificare e risolvere le query con regressioni di scelta del piano
Identificare e ottimizzare le query che hanno il maggior consumo di risorse
Test A/B
Mantenere la stabilità delle prestazioni durante l'aggiornamento alla versione successiva di SQL Server
Identificare e migliorare i carichi di lavoro ad hoc
Per altre informazioni su configurazione e gestione con Query Store, vedere Monitorare le prestazioni con Query Store.
Per informazioni sull'individuazione di informazioni utilizzabili e sull'ottimizzazione delle prestazioni con Query Store, vedere Ottimizzazione delle prestazioni con Query Store.
Per informazioni sul funzionamento dell'archivio query nel database SQL di Azure, vedere Uso dell'archivio query nel database SQL di Azure.
Individuare e risolvere le query con regressioni nella scelta del piano
Durante l'esecuzione di query normali, Query Optimizer può scegliere 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. Generalmente, il nuovo piano è migliore o quasi uguale a quello usato in precedenza. Tuttavia, a volte il nuovo piano risulta decisamente peggiore. In questi casi si parla di regressione nella scelta del piano. Prima di Query Store, identificare e risolvere questo problema risultava difficile perché SQL Server non offriva un archivio dati predefinito che gli utenti potessero esaminare per i piani di esecuzione usati nel corso del tempo.
Con Query Store, è possibile eseguire rapidamente le operazioni seguenti:
Identificare tutte le query le cui metriche di esecuzione siano peggiorate nel periodo di tempo di interesse (ultima ora, giorno, settimana e così via). Usare Query regredite in SQL Server Management Studio per velocizzare l'analisi.
Tra le query regredite è semplice trovare quelle query con più piani e che hanno subito un peggioramento a causa di una scelta errata di un piano. Usare il pannello Riepilogo piano in Query regredite per visualizzare tutti i piani di una query regredita e le loro prestazioni nel tempo.
Forzare il piano precedente dalla storia se ha dimostrato di essere migliore. Usare il pulsante Forza piano in Query regredite per applicare il piano selezionato alla query.
Per una descrizione dettagliata dello scenario, vedere il blog su Query Store: un registratore di dati di volo per il database.
Identificare e ottimizzare le query che hanno il maggior consumo di risorse
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. Tra le prime query per consumo di risorse si rilevano in genere quelle regredite o quelle che possono essere migliorate con un'ottimizzazione aggiuntiva.
Il modo più semplice per iniziare a esplorare consiste nell'aprire Prime query per consumo di risorse in Management 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). Selezionare il pulsante Configura per controllare il numero di query da analizzare e l'intervallo di tempo di interesse. È 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).
Esaminare il riepilogo del piano a destra per analizzare la cronologia di esecuzione e avere informazioni sui diversi piani e sulle statistiche di runtime. Utilizzare il riquadro inferiore per esaminare i diversi piani o per confrontarli visivamente, visualizzati affiancati (utilizzare il pulsante Confronta).
Quando si identifica una query con prestazioni non ottimali, l'azione correttiva dipende dalla natura del problema.
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 Server userà il piano ottimale per le esecuzioni successive.
Verificare se Query Optimizer rileva indici mancanti nel piano XML. In caso affermativo, creare l'indice mancante e usare l'archivio query per valutare le prestazioni delle query dopo la creazione dell'indice.
Assicurarsi che le statistiche siano aggiornate per le tabelle sottostanti usate dalla query.
Verificare che gli indici usati dalla query siano deframmentati.
Valutare l'opportunità di riscrivere la query con costo elevato. Ad esempio, sfruttare i vantaggi associati alla parametrizzazione della query e ridurre l'utilizzo di SQL dinamico. Implementare la logica ottimale durante la lettura dei dati (applicare il filtro dei dati sul lato database, non sul lato applicazione).
Test A/B
Usare Query Store per confrontare le prestazioni del carico di lavoro prima e dopo le modifica dell'applicazione.
Il seguente elenco contiene alcuni esempi in cui è possibile utilizzare Query Store per valutare l'impatto delle modifiche apportate all'ambiente o all'applicazione sulle prestazioni del carico di lavoro:
Implementazione della nuova versione dell'applicazione.
Aggiunta di nuovo hardware al server.
Creazione di indici mancanti nelle tabelle a cui fanno riferimento query costose.
Applicazione di un criterio di filtro per la sicurezza a livello di riga. Per altre informazioni, vedere Optimizing Row Level Security with Query Store (Ottimizzazione della sicurezza a livello di riga con Query Store).
Aggiunta del controllo temporale delle versioni di sistema alle tabelle che vengono modificate di frequente dalle applicazioni OLTP.
In qualsiasi scenario, applicare il flusso di lavoro seguente:
Esegui il carico di lavoro con Query Store prima della modifica pianificata per generare la baseline di prestazioni.
Applicare la modifica dell'applicazione in un determinato momento controllato.
Continuare l'esecuzione del carico di lavoro per il tempo necessario a generare un'immagine delle prestazioni del sistema dopo la modifica
Confrontare i risultati di #1 e #3.
Aprire Overall Database Consumption (Consumo database globale) per determinare l'impatto sull'intero database.
Aprire Prime query per consumo di risorse (oppure eseguire un'analisi con Transact-SQL) per analizzare l'impatto delle modifiche sulle query più importanti.
Decidere se mantenere la modifica o eseguire operazioni di rollback se le nuove prestazioni sono inaccettabili.
La figura seguente mostra l'analisi di Query Store (passaggio 4) in caso di mancanza di un indice. Aprire il riquadro Principali query per consumo di risorse / Riepilogo piano per ottenere la visualizzazione per la query che dovrebbe essere interessata dalla creazione dell'indice:
È anche possibile confrontare i piani prima e dopo la creazione dell'indice affiancandoli. 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.
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).
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).
In base all'analisi è consigliabile mantenere l'indice visto che le prestazioni delle query sono state migliorate.
Mantenere la stabilità delle prestazioni durante l'aggiornamento alla versione successiva di SQL Server
Prima di SQL Server 2014 (12.x), gli utenti erano esposti al rischio di regressione delle prestazioni durante l'aggiornamento alla versione più recente della piattaforma. Il motivo era che la versione più recente di Query Optimizer si attivava subito dopo l'installazione dei nuovi bit.
A partire da SQL Server 2014 (12.x), 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. Questa capacità, insieme a Query Store, offre un elevato livello di controllo sulle prestazioni delle query durante il processo di aggiornamento. Il flusso di lavoro di aggiornamento consigliato è illustrato nella figura seguente:
Aggiornare SQL Server senza modificare il livello di compatibilità del database. In questo modo non si espongono le modifiche più recenti di Query Optimizer, ma è possibile usare le funzionalità più recenti di SQL Server, tra cui Query Store.
Abilitare il Query Store. Per altre informazioni, vedere Adattare Query Store al proprio carico di lavoro.
Consentire a Query Store di acquisire query e piani e di stabilire una baseline delle prestazioni con il livello di compatibilità del database sorgente/precedente. Rimani in questo passaggio abbastanza a lungo per catturare tutti i piani e ottenere una baseline stabile. Tale operazione può avere la durata di un ciclo aziendale normale per un carico di lavoro di produzione.
Passare al livello di compatibilità più recente: esporre il carico di lavoro alla versione più recente di Query Optimizer per consentire la possibilità di creare nuovi piani.
Usare Query Store per le correzioni di analisi e regressioni: di solito i nuovi miglioramenti Query Optimizer dovrebbero produrre piani migliori. Query Store offre comunque un modo semplice per identificare le regressioni di scelta del piano e risolverle usando un meccanismo che forza il piano. A partire da SQL Server 2017 (14.x),quando si usa la funzionalità Correzione automatica del piano questo passaggio è automatico.
a. Per i casi in cui sono presenti regressioni, forzare il piano considerato migliore in precedenza nel Query Store.
b. Se non è possibile forzare i piani di query o se le prestazioni non sono ancora sufficienti, è consigliabile ripristinare l'impostazione precedente del livello di compatibilità del database e contattare il supporto tecnico Microsoft.
Suggerimento
In SQL Server Management Studio Usare l'attività Aggiorna database per aggiornare il livello di compatibilità del database del database. Per informazioni dettagliate, vedere Aggiornamento di database mediante l'Assistente ottimizzazione query.
Identificare e migliorare i carichi di lavoro ad hoc
Alcuni carichi di lavoro non includono query dominanti che è possibile ottimizzare per migliorare le prestazioni complessive dell'applicazione. 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. 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. 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. Questa situazione non è ideale per Query Store, poiché un numero elevato di query e piani invade lo spazio riservato, il che significa che Query Store potrebbe passare molto rapidamente alla modalità di sola lettura. Se hai attivato la Modalità di pulizia basata sulle dimensioni (fortemente consigliata per mantenere il Query Store sempre attivo e funzionante), il processo in background eseguirà la pulizia delle strutture del Query Store nella maggior parte del tempo, spesso assorbendo notevoli risorse di sistema.
La visualizzazione Prime query per consumo di risorse fornisce una prima indicazione della natura ad hoc del tuo carico di lavoro.
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). Dal diagramma sopra, puoi vedere che il 90% delle tue Prime query per consumo di risorse vengono eseguite una sola volta.
In alternativa, è possibile eseguire uno script Transact-SQL per ottenere il numero totale di testi di query, query e piani nel sistema e determinare quanto siano differenti confrontando query_hash e query_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:
Il risultato della query mostra che, nonostante il numero elevato di query e piani in Query Store, query_hash e query_plan_hash in realtà non sono diversi. Un rapporto molto maggiore di 1 tra i testi delle query univoci e gli hash delle query univoci 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.
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.
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. 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.
L'approccio che prevede l'uso di modelli di query individuali richiede la creazione di una guida del piano.
--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.
Se tutte o la maggior parte delle query sono idonee alla parametrizzazione automatica, considerare la configurazione di PARAMETERIZATION = FORCED per l'intero database: Per altre informazioni, vedere Linee guida per l'utilizzo della parametrizzazione forzata.
--Apply forced parameterization for entire database
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;
Dopo aver applicato uno qualsiasi di questi passaggi, in Prime query per consumo di risorse viene visualizzata una visione diversa del carico di lavoro.
In alcuni casi, l'applicazione può generare un numero elevato di query diverse, che non sono adatte alla parametrizzazione automatica. 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 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. Per impedire l'acquisizione di tali query nel Query Store, impostare QUERY_CAPTURE_MODE su AUTO.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);