Aggiornare i database tramite l’Assistente ottimizzazione query

Articolo
03/22/2024

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.

Recommended database upgrade workflow using 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. L'Assistente ottimizzazione query, tuttavia, non esegue il rollback a un piano valido noto in precedenza, come illustrato nell'ultimo passaggio del flusso di lavoro consigliato. L'Assistente ottimizzazione query terrà invece traccia di eventuali regressioni trovate nella vista Query regredite di Query Store ed eseguirà l'iterazione delle possibili permutazioni delle varianti del modello di ottimizzazione applicabili in modo che possa essere generato un piano nuovo e migliore.

Importante

L'Assistente ottimizzazione query non genera carichi di lavoro utente. Se si esegue l'Assistente ottimizzazione query in un ambiente non usato dalle applicazioni, verificare che sia comunque possibile eseguire un carico di lavoro di test rappresentativo nel motore di database SQL Server di destinazione in altro modo.

Flusso di lavoro dell'Assistente ottimizzazione 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. L'Assistente ottimizzazione query aiuta nell'esecuzione dei passaggi seguenti:

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.
Richiedere la raccolta di una seconda estrazione di dati del 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 report sui miglioramenti misurati e facoltativamente consentire che le modifiche vengano rese persistenti usando guide di piano.

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

Vedere di seguito come l'Assistente ottimizzazione query cambia solo gli ultimi passaggi del flusso di lavoro consigliato per aggiornare il livello di compatibilità mediante Query Store illustrato sopra. Invece di offrire la possibilità di scegliere tra il piano di esecuzione che attualmente risulta inefficiente e l'ultimo piano di esecuzione ottimale, l'Assistente ottimizzazione query offre opzioni di regolazione specifiche per le query regredite selezionate, al fine di creare un nuovo stato migliorato con i piani di esecuzione ottimizzati.

Recommended database upgrade workflow using QTA

Spazio di ricerca interno per l'ottimizzazione dell'Assistente ottimizzazione query

L'Assistente ottimizzazione query 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 rileva possibili criteri noti di regressioni delle query derivanti da modifiche alle versioni di Stima della 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 regredire, perché sono state progettate specificamente per funzionare con la versione di Stima della cardinalità presente in SQL Server 2012 (11.x) (Stima della cardinalità 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 criteri di Stima della cardinalità cercati dall'Assistente ottimizzazione query 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 Hint per le query USE HINT e Versioni di 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 altre informazioni, vedere Hint per le query USE HINT e Versioni di Stima della cardinalità.
Funzione con valore di tabella a più istruzioni (MSTVF) a cardinalità fissadi 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 Query Optimizer Stima della cardinalità di SQL Server 2008 R2 (10.50.x) e versioni precedenti), l'hint per la query QUERYTRACEON 9488 viene usato per generare un piano di esecuzione. Per altre informazioni sulle funzioni con valori di tabella con istruzioni multiple (MSTVF), vedere Creazione di funzioni definite dall'utente (Motore di database).

Nota

Come ultima risorsa, se gli hint con ambito limitato non restituiscono risultati soddisfacenti per i modelli di query idonei, viene preso in considerazione anche l'uso completo di Stima della cardinalità 70, usando l'hint per la query USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') usato 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.

Avviare l'Assistente ottimizzazione Query per gli aggiornamenti del database

L'Assistente ottimizzazione Query è una funzionalità basata sulla sessione che archivia lo stato della sessione nello schema msqta del database utente in cui 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 di Assistente ottimizzazione query guidata 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 al termine il flusso di lavoro l'Assistente ottimizzazione query. 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 uno scenario del genere immettere un valore più elevato, adatto alle esigenze contingenti.
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 usare l'Assistente ottimizzazione query 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 dell'Assistente ottimizzazione query 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 dai valori Metrica (Durata o CpuTime) e Aggregazione (il valore predefinito è Media). Selezionare anche un valore per Query da 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 espansa selezionando il pulsante ....
  - Esecuzioni: numero di esecuzioni della 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 la selezione delle query per la sperimentazione e il rilevamento di 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 Ottimizzabile = False non possono essere selezionate per la sperimentazione.

Importante

Un messaggio informa che quando l'Assistente ottimizzazione query passa alla fase di sperimentazione, non sarà possibile tornare alla pagina Visualizzazione 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 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 di 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 è diverso da quello della pagina precedente, perché l'opzione È possibile distribuire qui è È possibile eseguire il rollback. Questa colonna può essere True o False a seconda che sia possibile o meno eseguire il rollback dell'ottimizzazione delle query distribuite e rimuovere la guida di piano.

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 è possibile usare l'Assistente ottimizzazione query 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.