Ottimizzare le query con Query Tuning Assistant (QTA)

  • 5 minuti

Si decide di usare Query Store per monitorare le prestazioni del database prima della migrazione e confrontare questi dati con le prestazioni dopo l'aggiornamento. Si prevede di usare Query Tuning Assistant (QTA) per trovare query regredite e suggerire miglioramenti. Questa unità descrive i passaggi per usare Query Store e QTA per mantenere le prestazioni delle query.

Poiché le applicazioni di database sono sistemi critici che supportano le operazioni dell'azienda, è necessario avere un piano d'azione e un piano di emergenza per l'aggiornamento. Aggiornare i database di produzione di base uno alla volta. Determinare un piano di test per i database aggiornati per assicurarsi che ogni database sia di nuovo in produzione senza problemi prima di aggiornarne altri.

È possibile usare Query Store per monitorare continuamente le prestazioni delle query e per il test A/B per misurare gli effetti di una modifica, ad esempio un aggiornamento del database. L'Assistente ottimizzazione query fornisce supporto automatico dopo un aggiornamento, per l'individuazione e la correzione delle query regredite sulla base dei dati acquisiti in Query Store.

Per garantire il funzionamento corretto dell'Assistente ottimizzazione query è necessario eseguire i passaggi seguenti nell'ordine indicato.

Importante

Assicurarsi di usare il database dell'applicazione di produzione o uno con un carico di lavoro dell'applicazione che corrisponda strettamente al carico di lavoro del database di produzione, in modo che Query Store possa raccogliere metriche realistiche sulle query.

  1. Eseguire la migrazione del database a SQL Server 2022.
  2. Lasciare invariato il livello di compatibilità alla versione di SQL Server precedente.
  3. Abilitare Query Store nel database.
  4. Consentire a Query Store di raccogliere le metriche di base sulle query in base a un'attività utente realistica sufficiente.
  5. Aggiornare il livello di compatibilità a SQL Server 2022 (160).
  6. Anche in questo caso, consentire a Query Store di raccogliere i dati sulle query in base a un'attività utente realistica sufficiente.
  7. Usare l'assistente ottimizzazione query per confrontare le prestazioni delle query prima e dopo la modifica del livello di compatibilità del database. Se vengono trovate query regredite, identificare le correzioni.

Eseguire la migrazione del database

Quando si è pronti per la transizione a SQL Server 2022, iniziare eseguendo la migrazione del database alla nuova istanza. La migrazione può essere eseguita in vari modi. Ad esempio è possibile usare una funzione di backup e ripristino semplice, il mirroring del database o un'operazione bulkload. La scelta più appropriata dipende dalla configurazione dell'ambiente corrente e dalla versione di SQL Server da cui si esegue la migrazione. Servizio Migrazione del database di Azure è una buona soluzione, perché supporta i database da SQL Server 2005 in poi.

Nota

Servizio Migrazione del database di Azure supporta anche le migrazioni di database a Istanza gestita di SQL di Azure. Per iniziare, usare l'estensione Migrazione SQL di Azure per Azure Data Studio.

Lasciare invariato il livello di compatibilità

Dopo la migrazione del database, lasciare invariato il livello di compatibilità. Questo passaggio è fondamentale, visto che la baseline deve essere misurata usando la configurazione del database corrente. Fino a quando non si sposta il livello di compatibilità a SQL Server 2014 (120) o versione successiva, SQL Server usa lo strumento di stima della cardinalità legacy. SQL Server 2014 ha introdotto uno strumento di stima della cardinalità aggiornato che offre vantaggi alla maggior parte delle query, ma raramente può avere un impatto negativo sulle prestazioni.

Abilitare Query Store

Sebbene il livello di compatibilità del database rimanga quello della versione precedente, è possibile abilitare Query Store nel database, poiché è una funzionalità a livello di server. Per abilitare Query Store:

  1. In SQL Server Management Studio (SSMS), fare clic con il pulsante destro del mouse sul database e selezionare Proprietà.
  2. Nella finestra Proprietà database selezionare Query Store nel riquadro sinistro.
  3. Impostare Modalità operativa (richiesta) su Sola lettura o Lettura/Scrittura.
  4. Seleziona OK.

In alternativa, è possibile eseguire l'istruzione seguente per abilitare Query Store nella modalità predefinitaREAD WRITE:

ALTER DATABASE <database-name> SET QUERY_STORE = ON

Consentire a Query Store di raccogliere dati

Riportare il database in produzione e passare tutte le connessioni di database da applicazioni o report. Il database inizia a ricevere query dalle applicazioni di produzione. Consentire l'esecuzione di Query Store per un tempo sufficiente alla raccolta di un carico di lavoro realistico nel database.

Query Store deve acquisire un ciclo tipico di attività aziendali, tra cui orario di ufficio, elaborazione notturna, finestre di manutenzione e altre attività. Per molte aziende, l'attività di una settimana è sufficiente, ma per alcune aziende questo periodo può essere più breve o più lungo.

Molte aziende hanno cicli aziendali importanti, e quindi attività univoche, per l'elaborazione delle buste paga bisettimanali o di fine mese. È consigliabile tenere presente la tempistica dei cicli aziendali dell'esperienza dei database. Per un negozio di alimentari, l'arrivo settimanale dell'inventario e i cicli di rifornimento coprono la maggior parte delle attività del database.

È possibile visualizzare i dati raccolti esplorando le schede query store. Per visualizzare le schede, in Esplora oggetti di SSMSespandere l'albero del database per visualizzare Archivio query. Se si ritiene che la quantità di dati raccolti sia sufficiente, è possibile pianificare l'aggiornamento.

Aggiornare il livello di compatibilità

Prima di apportare qualsiasi modifica a un database, è consigliabile eseguire un backup del database, al di fuori dell'orario lavorativo, se possibile. Dopo aver eseguito il backup, aggiornare il livello di compatibilità come segue:

  1. Fare clic con il pulsante destro del mouse sul database in SSMS Esplora oggetti e scegliere Proprietà.
  2. Nella finestra Proprietà database selezionare la scheda Opzioni.
  3. Cambiare il livello di compatibilità a SQL Server 2022 (160) e selezionare OK.

In alternativa, è possibile eseguire la seguente istruzione:

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

Consentire a Query Store di continuare a raccogliere dati

Dopo l'aggiornamento del database e la ripresa dell'esecuzione delle applicazioni, Query Store continua a essere eseguito in background per raccogliere le metriche per le query. Le query vengono ora esposte a potenziali problemi con l'uso del nuovo strumento di stima della cardinalità usato da Query Optimizer.

Continuare a eseguire Query Store e consentire la raccolta dei dati per la stessa durata di prima dell'aggiornamento. Tuttavia, la regressione delle query potrebbe essere immediatamente visualizzata in modo da poter intervenire per correggere immediatamente eventuali problemi di prestazioni.

Eseguire l’assistente ottimizzazione query

Eseguire l'assistente ottimizzazione query per risolvere eventuali query regredite. Per configurare l'assistente ottimizzazione query:

  1. Fare clic con il pulsante destro del mouse sul database in Esplora oggetti di SSMS e selezionare Attività>Aggiornamento del database>Nuova sessione di aggiornamento del database.
  2. Nella schermata Setup dell'Assistente ottimizzazione guidata query immettere la durata del carico di lavoro (giorni) per acquisire e il livello di compatibilità del database di destinazione.
  3. Selezionare Successivo per configurare le schermate Impostazioni e Ottimizzazione.
  4. Selezionare Fine.

Per monitorare l'assistente ottimizzazione query, fare clic con il pulsante destro del mouse sul nome del database, selezionare Attività>Aggiornamento database>Monitora sessioni. L'Assistente ottimizzazione query restituisce il report di riepilogo delle query regredite più importanti, confrontando i dati osservati con i dati di base. È quindi possibile visualizzare le modifiche consigliate dall'Assistente ottimizzazione query per ottimizzare le query che registrano prestazioni ridotte.

Riepilogo

Usare l'Assistente ottimizzazione query dopo l'aggiornamento del database per individuare e correggere le query in regressione (con riduzione delle prestazioni) in seguito all'aggiornamento. Per consentire all'Assistente ottimizzazione query di individuare le query regredite, è prima necessario creare una linea di base usando Query Store per misurare le query in base al livello di compatibilità precedente.

Query Store raccoglie quindi le metriche dopo l'aggiornamento, che possono essere usate con l'Assistente ottimizzazione query per confrontare le nuove prestazioni con la linea di base. Per le attività dell'Assistente ottimizzazione query è fondamentale che Query Store raccolga dati prima e dopo l'aggiornamento.

Quando l'Assistente ottimizzazione query rileva query regredite, esamina varie soluzioni alla ricerca delle azioni più efficaci per migliorare le prestazioni. È quindi possibile applicare queste azioni.