Aggiorna i database utilizzando l'Assistente per l'Ottimizzazione delle Query

Articolo
05.03.2025

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Quando si esegue la migrazione da una versione precedente di SQL Server a SQL Server 2014 (12.x) o versioni successive e si aggiorna il livello di compatibilità del database alla versione più recente disponibile, un carico di lavoro può essere esposto al rischio di regressione delle prestazioni. Questo è anche possibile in misura minore durante l'aggiornamento da SQL Server 2014 (12.x) a una nuova versione.

A partire da SQL Server 2014 (12.x) e per tutte le nuove versioni, tutte le modifiche di Query Optimizer sono associate al livello di compatibilità del database più recente, quindi i piani non vengono modificati esattamente nel punto di aggiornamento, ma quando un utente passa dall'opzione di database COMPATIBILITY_LEVEL alla versione più recente disponibile. Per altre informazioni sulle modifiche a Query Optimizer introdotte in SQL Server 2014 (12.x), vedere Stima della cardinalità. Per altre informazioni sui livelli di compatibilità e sul loro possibile effetto sugli aggiornamenti, vedere Livelli di compatibilità e aggiornamenti del motore di database.

Questa funzionalità di collegamento offerta dal livello di compatibilità del database in combinazione con Query Store offre un livello ottimale di controllo delle prestazioni delle query nel processo di aggiornamento, se l'aggiornamento segue il flusso di lavoro consigliato illustrato di seguito. Per altre informazioni sul flusso di lavoro consigliato per l'aggiornamento del livello di compatibilità, vedere Modificare la modalità di compatibilità del database e usare il Query Store.

Flusso di lavoro del processo di aggiornamento del database consigliato con Query Store

Questo controllo sugli aggiornamenti è stato migliorato ulteriormente in SQL Server 2017 (14.x) con l'introduzione di Ottimizzazione automatica e consente di automatizzare l'ultimo passaggio del flusso di lavoro consigliato visualizzato sopra.

A partire da SQL Server Management Studio v18 la nuova funzionalità Assistente ottimizzazione query (QTA, Query Tuning Assistant) assiste gli utenti nel flusso di lavoro consigliato per garantire la stabilità delle prestazioni durante l'aggiornamento alle versioni più recenti di SQL Server, come documentato nella sezione Mantenere la stabilità delle prestazioni durante l'aggiornamento alla nuova versione di SQL Server di Scenari di utilizzo di Query Store. Tuttavia, l'Assistente ottimizzazione query (QTA) non esegue il rollback a un piano valido noto in precedenza, come visto nell'ultimo passaggio del flusso di lavoro consigliato. QTA terrà invece traccia di eventuali regressioni trovate nella vista Query con regresso del Query Store e esplorerà le possibili permutazioni delle varianti del modello di ottimizzazione applicabili così che possa essere creato un piano nuovo e migliore.

Importante

QTA non genera carichi di lavoro utente. Se esegui QTA in un ambiente non utilizzato dalle tue applicazioni, assicurati che sia comunque possibile eseguire un carico di lavoro di test rappresentativo sul motore di database SQL Server di destinazione con altri metodi.

Flusso di lavoro dell'Assistente di ottimizzazione delle query

Il punto iniziale di QTA presuppone che un database di una versione precedente di SQL Server venga spostato (tramite CREATE DATABASE ... FOR ATTACH o RESTORE) a una versione più recente del motore di database SQL Server e il livello di compatibilità del database non viene modificato immediatamente prima dell'aggiornamento. QTA guiderà attraverso i seguenti passaggi:

Configurare Query Store in base alle impostazioni consigliate per la durata del carico di lavoro (in giorni) impostata dall'utente. Considerare la durata del carico di lavoro corrispondente al ciclo operativo tipico.
Richiedere l'avvio del carico di lavoro obbligatorio, in modo che Query Store possa raccogliere una baseline di dati del carico di lavoro (se non sono ancora disponibili).
Eseguire l'aggiornamento al livello di compatibilità del database di destinazione scelto dall'utente.
Si richiede la raccolta di una seconda serie di dati sul carico di lavoro, per il confronto e il rilevamento della regressione.
Eseguire l'iterazione su eventuali regressioni in base alla vista Query Store Query regredite, sperimentare raccogliendo statistiche di runtime sulle possibili permutazioni di variazioni del modello di ottimizzazione applicabile e misurare i risultati.
Creare un report sui miglioramenti misurati e, facoltativamente, consentire di rendere permanenti tali modifiche usando le guide di piano.

Per altre informazioni sul collegamento di un database, vedere Collegamento e scollegamento di database.

Vedi di seguito come l'Assistente ottimizzazione query modifica solo gli ultimi passaggi del flusso di lavoro consigliato per aggiornare, utilizzando Query Store, il livello di compatibilità illustrato sopra. Invece di offrire la possibilità di scegliere tra il piano di esecuzione attualmente inefficiente e l'ultimo piano di esecuzione noto come buono, l'Assistente ottimizzazione query offre opzioni di ottimizzazione specifiche per le query regredite selezionate, affinché si crei un nuovo stato migliorato con piani di esecuzione ottimizzati.

Flusso di lavoro consigliato per l'aggiornamento del database utilizzando QTA

Spazio di ricerca interno per l'ottimizzazione di QTA

QTA prende in esame solo le query SELECT che possono essere eseguite da Query Store. Le query con parametri sono idonee se il parametro compilato è noto. Le query che dipendono da costrutti di runtime, ad esempio tabelle temporanee o variabili di tabella, non sono idonee in questa fase.

L'Assistente ottimizzazione query si concentra sui modelli noti di regressione delle query derivanti da modifiche alle versioni del Calcolatore di Cardinalità (CE, Cardinality Estimator). Ad esempio, quando si aggiorna un database da SQL Server 2012 (11.x) e livello di compatibilità 110 a SQL Server 2017 (14.x) e livello di compatibilità 140, alcune query potrebbero subire una regressione, poiché sono state progettate specificamente per funzionare con la versione CE (Cardinality Estimation) presente in SQL Server 2012 (11.x) (CE 70). Ciò non significa che il ripristino da Stima della cardinalità 140 a Stima della cardinalità 70 sia l'unica opzione. Se la regressione è originata da una sola modifica specifica nella versione più recente, è possibile suggerire che la query usi solo la parte rilevante della versione di Stima della cardinalità precedente (che funzionava meglio per la query specifica) pur utilizzando tutti gli altri miglioramenti delle versioni di Stima della cardinalità più recenti. In questo modo sarà possibile consentire alle altre query del carico di lavoro che non hanno regredito di sfruttare i miglioramenti delle versioni più recenti di Stima della cardinalità.

I modelli di stima della cardinalità cercati dal QTA sono i seguenti:

Independenza vs correlazione: se il presupposto di indipendenza offre stime migliori per la query specifica, l'hint per la query USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') fa sì che SQL Server generi un piano di esecuzione usando una selettività minima quando stima predicati AND per i filtri per rendere conto della correlazione. Per altre informazioni, vedere Suggerimenti della query USE HINT e Versioni della Stima della Cardinalità.
Indipendenza semplice vs. Indipendenza di base: se un'indipendenza di join diversa offre stime migliori per la query specifica, l'hint per la query USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') fa sì che SQL Server generi un piano di esecuzione mediante il presupposto Simple Containment (Indipendenza semplice) anziché mediante il presupposto predefinito Base Containment (Indipendenza di base). Per ulteriori informazioni, consultare Suggerimenti per le query con USE HINT e Versioni del Calcolatore di Cardinalità.
Funzione con valore di tabella a più istruzioni (MSTVF) a cardinalità fissa di 100 righe vs. 1 riga: se la stima fissa predefinita per le funzioni con valori di tabella di 100 righe non restituisce un piano più efficiente di quello ottenuto usando la stima fissa predefinita per le funzioni con valori di tabella di 1 riga (corrispondente all'impostazione predefinita con il modello Ottimizzatore di query di SQL Server 2008 R2 (10.50.x) e versioni precedenti), il suggerimento per la query QUERYTRACEON 9488 viene usato per generare un piano di esecuzione. Per ulteriori informazioni sulle MSTVF, consultare Creare funzioni definite dall'utente (Motore di database).

Nota

Come ultima risorsa, se gli hint a scopo limitato non restituiscono risultati soddisfacenti per i modelli di query idonei, viene preso in considerazione anche l'uso completo del CE 70, utilizzando l'hint USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') per generare un piano di esecuzione.

Importante

Qualsiasi hint impone determinati comportamenti che potrebbero essere presi in considerazione per gli aggiornamenti futuri di SQL Server. È consigliabile applicare gli hint solo quando non è disponibile nessun'altra opzione e si prevede di rivedere il codice con hint a ogni nuovo aggiornamento. Imponendo i comportamenti è possibile che si impedisca al carico di lavoro di sfruttare i vantaggi dei miglioramenti introdotti nelle versioni più recenti di SQL Server.

Avvia l'Assistente per l'ottimizzazione delle query per gli aggiornamenti del database

QTA è una funzionalità basata sulla sessione che archivia lo stato della sessione nello schema msqta del database utente dove una sessione viene creata per la prima volta. Nel tempo è possibile creare più sessioni di ottimizzazione per un singolo database, ma per un database specifico può essere presente una sola sessione attiva.

Creare una sessione di aggiornamento del database

In SQL Server Management Studio aprire Esplora Oggetti e connettersi al Motore di Database.
Per il database di cui si vuole aggiornare il livello di compatibilità, fare clic con il pulsante destro del mouse sul nome del database, selezionare Attività, selezionare Aggiornamento database e selezionare Nuova sessione di aggiornamento database.
Nella finestra della QTA Wizard, sono necessari due passaggi per configurare una sessione:
1. Nella finestra Configura configurare Query Store per acquisire l'equivalente di un ciclo aziendale completo di dati del carico di lavoro da analizzare e ottimizzare.
  - Immettere la durata prevista del carico di lavoro in giorni (il valore minimo è 1 giorno). Questo valore verrà usato per proporre impostazioni consigliate di Query Store e provare a consentire la raccolta dell'intera baseline. L'acquisizione di una baseline ottimale è importante per garantire che sia possibile esaminare le query regredite dopo la modifica del livello di compatibilità del database.
  - Impostare il livello di compatibilità del database di destinazione previsto per il database utente, dopo il completamento del flusso di lavoro dell'Assistente di Ottimizzazione Query (QTA). Al termine, selezionare Avanti.
2. Nella finestra Impostazioni due colonne indicano lo stato Corrente di Query Store nel database di destinazione, nonché le impostazioni Consigliate.
  - Le impostazioni consigliate sono selezionate per impostazione predefinita, ma, se si seleziona il pulsante di opzione sopra la colonna Corrente, si accettano le impostazioni correnti ed è anche possibile ottimizzare la configurazione corrente di Query Store.
  - L'impostazione Soglia per query non aggiornate è pari al doppio della durata in giorni prevista per il carico di lavoro. Questo dipende dal fatto che Query Store dovrà conservare informazioni sul carico di lavoro baseline e sul carico di lavoro successivo all'aggiornamento del database. Al termine, selezionare Avanti.
  Importante
  
  Il valore Dimensioni massime proposto è un valore arbitrario, che può essere adatto a un carico di lavoro di breve durata. Tenere tuttavia presente che questo valore può essere insufficiente per contenere informazioni sui carichi di lavoro baseline e successivi all'aggiornamento del database in caso di carichi di lavoro particolarmente intensi, ad esempio quando è necessario generare più piani diversi. Se si prevede che questo sarà il caso, immettere un valore superiore appropriato.
La finestra Regolazione completa la configurazione della sessione e indica i passaggi successivi per aprire e svolgere la sessione. Al termine, fare clic su Fine.

Eseguire il flusso di lavoro di aggiornamento del database

Per il database di cui si vuole aggiornare il livello di compatibilità, fare clic con il pulsante destro del mouse sul nome del database, selezionare Attività, selezionare Aggiornamento database e selezionare Monitora sessioni.
La pagina Gestione delle sessioni elenca la sessione corrente e le sessioni correnti e precedenti per il database nell'ambito. Selezionare la sessione desiderata e selezionare Dettagli.

Nota

Se la sessione corrente non è presente, selezionare il pulsante Aggiorna.

L'elenco contiene le informazioni seguenti:
- ID Sessione
- Nome sessione: nome generato dal sistema e costituito dal nome del database e dalla data e ora di creazione della sessione.
- Stato: stato della sessione (Attiva o Chiusa).
- Descrizione: elemento generato dal sistema che include il livello di compatibilità del database di destinazione selezionato dall'utente e il numero di giorni per il carico di lavoro del ciclo aziendale.
- Ora avvio: data e ora di creazione della sessione.
Nota

Elimina sessione: elimina tutti i dati archiviati per la sessione selezionata. Tuttavia l'eliminazione di una sessione chiusa non elimina le guide di piano distribuite in precedenza. Se si elimina una sessione che aveva distribuito guide di piano, non è possibile utilizzare QTA per eseguire il rollback. Cercare invece le guide di piano mediante la tabella di sistema sys.plan_guides ed eliminarle manualmente con sp_control_plan_guide.
Il punto di ingresso per una nuova sessione è il passaggio Raccolta dati.

Nota

Il pulsante Sessioni torna ad aprire la pagina Gestione delle sessioni, lasciando invariata la sessione attiva.

Questo passaggio include tre passaggi secondari:
1. Raccolta di dati baseline richiede all'utente di eseguire il ciclo del carico di lavoro rappresentativo, in modo che Query Store possa raccogliere una baseline. Dopo il completamento del carico di lavoro, selezionare Esecuzione del carico di lavoro completata e selezionare Avanti.
  
  Nota
  
  La finestra QTA può essere chiusa mentre il carico di lavoro è in esecuzione. Se in un secondo momento si torna alla sessione che rimane in stato attivo, questa verrà ripresa dallo stesso passaggio in cui è stata interrotta.
2. Aggiornamento del database richiederà l'autorizzazione per aggiornare il livello di compatibilità del database al livello desiderato. Per procedere con il passaggio secondario successivo, selezionare Sì.
  
  La pagina seguente conferma che il livello di compatibilità del database è stato aggiornato correttamente.
3. Raccolta dati osservati richiede all'utente di eseguire nuovamente il ciclo del carico di lavoro rappresentativo, per consentire a Query Store di acquisire una baseline di confronto che verrà usata per la ricerca di opportunità di ottimizzazione. Durante l'esecuzione del carico di lavoro, usare il pulsante Aggiorna per aggiornare l'elenco delle query regredite, se presenti. Modificare il valore Query da mostrare per limitare il numero di query visualizzate. L'ordine dell'elenco è influenzato dalla Metrica (Durata o CpuTime) e dall'Aggregazione (Media è predefinito). Selezionare anche quante Query mostrare. Dopo il completamento del carico di lavoro, selezionare Esecuzione del carico di lavoro completata e selezionare Avanti.
  
  L'elenco contiene le informazioni seguenti:
  - ID query
  - Testo query: istruzione Transact-SQL che può essere ampliata selezionando il pulsante ....
  - Esecuzioni: Mostra il numero di esecuzioni di quella query per l'intera raccolta del carico di lavoro.
  - Metrica baseline: metrica selezionata (Durata o CpuTime) in millisecondi per la raccolta di dati baseline prima dell'aggiornamento di compatibilità del database.
  - Metrica osservata: metrica selezionata (Durata o CpuTime) in ms per la raccolta di dati dopo l'aggiornamento di compatibilità del database.
  - % di modifica: percentuale di variazione della metrica selezionata tra lo stato prima e dopo l'aggiornamento di compatibilità del database. Un numero negativo rappresenta la quantità di regressione misurata per la query.
  - Ottimizzabile: True o False a seconda che la query sia o meno idonea per la sperimentazione.
Visualizzazione analisi consente di selezionare le query per sperimentare e individuare opportunità di ottimizzazione. Il valore Query da mostrare diventa l'ambito delle query idonee per la sperimentazione. Dopo la verifica delle query desiderate, selezionare Avanti per iniziare la sperimentazione.

Nota

Le query con l'impostazione Regolabile = False non possono essere selezionate per la sperimentazione.

Importante

Un messaggio informa che quando QTA passa alla fase di sperimentazione, non sarà possibile tornare alla pagina di Analisi.
Se non si selezionano tutte le query idonee prima di passare alla fase di sperimentazione, sarà necessario creare una nuova sessione in un secondo momento e ripetere il flusso di lavoro. Questo richiede la reimpostazione del livello di compatibilità del database sul valore precedente.
Visualizzazione risultati consente di selezionare le query a cui distribuire l'ottimizzazione proposta come guida di piano.

L'elenco contiene le informazioni seguenti:
- ID query
- Testo della query: istruzione Transact-SQL che può essere espansa selezionando il pulsante ....
- Stato: visualizza lo stato di sperimentazione corrente per la query.
- Metrica baseline: la metrica selezionata (Durata o CpuTime) in ms per le query eseguita nel Passaggio 2 passaggio secondario 3, che rappresenta la query regredita dopo l'aggiornamento di compatibilità del database.
- Metrica osservata: la metrica selezionata (durata o CpuTime) in ms per la query dopo la sperimentazione, per un'ottimizzazione proposta con risultati soddisfacenti.
- % di modifica: percentuale di modifica per la metrica selezionata tra lo stato prima e dopo la sperimentazione, che rappresenta la quantità di miglioramento misurata per la query con l'ottimizzazione proposta.
- Opzione di query: collegamento all'hint proposto che migliora la metrica di esecuzione della query.
- È possibile distribuire: True o False a seconda che l'ottimizzazione della query proposta possa essere o meno distribuita come guida di piano.
Verifica Mostra lo stato di distribuzione delle query selezionate in precedenza per questa sessione. L'elenco in questa pagina differisce da quello nella pagina precedente poiché la colonna Può distribuire è stata modificata in Può eseguire il rollback. Questa colonna può essere True o False a seconda che l'ottimizzazione delle query distribuite possa essere annullata e la guida del piano rimossa.

Se in un secondo momento è necessario eseguire il rollback su un'ottimizzazione proposta, selezionare la query corrispondente e selezionare Rollback. La guida di piano della query viene rimossa e l'elenco viene aggiornato per rimuovere la query di cui è stato eseguito il rollback. Si noti che nell'immagine seguente è stata rimossa la query 8.

Nota

L'eliminazione di una sessione chiusa non elimina le guide di piano distribuite in precedenza. Se si elimina una sessione che aveva distribuito guide di piano, non si può usare QTA per eseguire il rollback. Invece, cerca le guide di piano utilizzando la tabella di sistema sys.plan_guides ed eliminale manualmente usando sp_control_plan_guide.