Introduzione all'Assistente ottimizzazione query (QTA)
Si prevede di eseguire la migrazione di due database di produzione da SQL Server 2012 a una nuova istanza di SQL Server 2022. Poiché i database sono in uso, si vogliono inizialmente lasciare i livelli di compatibilità alle impostazioni originali e ripointare le applicazioni. Lo strumento di stima della cardinalità è cambiato da SQL Server 2014 e si vuole misurare le prestazioni delle query prima di modificare il livello di compatibilità dei database in SQL Server 2022 (160).
È necessario trovare e correggere le query regredite quando si sposta il livello di compatibilità e si applica il nuovo algoritmo di stima della cardinalità. L'uso di questo approccio misura una linea di base per confrontare le prestazioni dopo l'aggiornamento del livello di compatibilità.
L'abilitazione di Query Store raccoglie le metriche necessarie per trovare le query regredite e l'uso dell'Assistente ottimizzazione query consente di correggere le query regredite. Questa unità offre una panoramica di Query Store e QTA.
Panoramica di Query Store
La funzionalità Query Store è stata introdotta in SQL Server 2016 per raccogliere in modo continuo informazioni sull'esecuzione e sulle prestazioni delle query all'interno di un database. Query Store funziona come un registratore di dati in anteprima per raccogliere informazioni di runtime su query e piani. Se si salvano questi dati di runtime, è possibile tenere traccia delle prestazioni nel tempo. Se si verifica un errore, è presente una cronologia delle informazioni per individuare la causa del problema.
A partire da SQL Server 2022 e in database SQL di Azure e Istanza gestita di SQL, Query Store è abilitato per i nuovi database per impostazione predefinita. In SQL Server 2016, SQL Server 2017 e SQL Server 2019, Query Store non è abilitato per impostazione predefinita, ma qualsiasi database in un'istanza di SQL Server 2016 o versione successiva può abilitare, disabilitare e configurare Query Store. Vedere l'unità successiva per istruzioni su come abilitare Query Store o confermarne la configurazione in un database.
Query Store può funzionare sui database con livelli di compatibilità precedenti rispetto all'istanza di SQL Server. Ad esempio, se si esegue la migrazione di un database da SQL Server 2012 a SQL Server 2022 e si lascia il livello di compatibilità 110, Query Store può comunque funzionare sul database.
Tuttavia, molte funzionalità di Elaborazione query intelligente e altri miglioramenti automatici delle prestazioni sono abilitati solo per i livelli di compatibilità del database più recenti. È pertanto consigliabile provare a testare le prestazioni dell'applicazione nel livello di compatibilità del database DI SQL Server più recente. Query Store e QTA possono essere utili per questo test delle prestazioni.
Se abilitata in un database, Query Store raccoglie e segnala le statistiche seguenti per le query:
- Query regredite
- Consumo complessivo risorse
- Prime query per consumo di risorse
- Query con piani forzati
- Query con variazione elevata
- Statistiche di attesa query
- Query rilevate
Una query regredita si verifica quando Query Optimizer usa un nuovo piano di query che causa una riduzione delle prestazioni. La regressione può verificarsi dopo importanti modifiche, ad esempio l'aggiunta, l'eliminazione o la modifica di un indice, l'aggiornamento delle statistiche o la modifica della cardinalità dei dati.
Prima di Query Store, SQL Server non ha fornito informazioni dettagliate sulla causa delle regressioni e l'identificazione dei problemi era un problema per gli sviluppatori e gli amministratori del database. È ora possibile usare Query Store per trovare query regredite e forzare l'ottimizzazione a usare un piano specifico dalla cronologia.
È comune che alcune delle migliaia di query usino la maggior parte delle risorse di sistema. Query Store identifica le query con il consumo più elevato, causato dalla regressione o da un'ottimizzazione non adeguata. A seconda della configurazione, è possibile filtrare i risultati per durata, CPU, memoria, I/O o numero di esecuzioni.
È possibile usare Query Store per monitorare le prestazioni in corso e per i test A/B per confrontare le prestazioni prima e dopo l'applicazione di una singola modifica. Ad esempio, è possibile ottimizzare le prestazioni di una query aggiungendo un indice a una tabella a cui fa riferimento la query, in modo che la ricerca di join sia più veloce. Il confronto delle statistiche in Query Store prima e dopo l'aggiunta dell'indice indica se l'indice influisce sulle prestazioni. È anche possibile confrontare le statistiche dopo l'aggiunta di nuovo hardware o l'aggiornamento di un'applicazione.
Panoramica di Query Tuning Assistant
Query Tuning Assistant (QTA) usa i dati di Query Store per trovare le query che iniziano a regredire. L'assistente ottimizzazione query sperimenta automaticamente per trovare una soluzione che velocizza la query, prima che la query sia sottoperforma fino al punto di influire sugli utenti.
È possibile usare Query Store e QTA per monitorare e ottimizzare le prestazioni del database dopo l'aggiornamento. Dopo la migrazione di un database a SQL Server 2016 o versione successiva, lasciare invariato il livello di compatibilità del database e abilitare Query Store per raccogliere le statistiche sulle prestazioni delle query di base.
Modificare quindi il livello di compatibilità e continuare a usare i dati di Query Store per misurare le statistiche sulle prestazioni delle query. È possibile confrontare le statistiche per scoprire se ogni query sta eseguendo prestazioni migliori, uguali o peggiori rispetto a prima dell'aggiornamento.
Quando si modifica il livello di compatibilità per aggiornare il database, SQL Server modifica la versione dello strumento di stima della cardinalità usato. L'assistente ottimizzazione query trova i possibili modelli di regressione delle query a causa della modifica nello strumento di stima della cardinalità e esperimenti per trovare miglioramenti delle prestazioni. È quindi possibile creare guide di piano per le query che mostrano miglioramenti.
Riepilogo
Query Store misura continuamente le statistiche sulle prestazioni delle query, così come il registratore dei dati di volo di un aereo acquisisce dati di attività durante il volo. È possibile abilitare Query Store in qualsiasi database in SQL Server 2016 o versione successiva, indipendentemente dal livello di compatibilità. Usare Query Store per monitorare continuamente le prestazioni delle query e per il test A/B per misurare gli effetti di una singola modifica.
Quando si aggiorna un database a SQL Server 2014 o versione successiva, le modifiche allo strumento di stima della cardinalità potrebbero rallentare le query veloci nella versione precedente di SQL Server. Idealmente, è consigliabile trovare e correggere eventuali regressioni prima che influiscano sugli utenti. Abilitando Query Store nei database, è possibile raccogliere statistiche sulle query in modo continuativo. È quindi possibile usare l'assistente ottimizzazione query per identificare e correggere le query regredite prima che diventino un problema.