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

Si applica a: SQL Server 2016 (13.x) e versioni successive

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 potrebbe 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.

In SQL Server 2014 (12.x) e versioni successive, tutte le modifiche di Query Optimizer vengono gestite al livello di compatibilità del database più recente, quindi i piani di esecuzione non vengono modificati direttamente al momento dell'aggiornamento, ma piuttosto quando un utente modifica l'opzione COMPATIBILITY_LEVEL di database alla versione più recente disponibile. Per altre informazioni sulle modifiche di Query Optimizer introdotte in SQL Server 2014 (12.x), vedere Stima della cardinalità (SQL Server). 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 controllo fornita dal livello di compatibilità del database, in combinazione con Query Store, offre un elevato livello di controllo sulle prestazioni delle query nel processo di aggiornamento se l'aggiornamento segue il flusso di lavoro consigliato visualizzato nel diagramma successivo. Per altre informazioni sul flusso di lavoro consigliato per l'aggiornamento del livello di compatibilità, vedere Modificare il livello di compatibilità del database e usare Query Store.

Questo controllo sugli aggiornamenti è stato ulteriormente migliorato con SQL Server 2017 (14.x) in cui è stata introdotta l'ottimizzazione automatica e consente di automatizzare l'ultimo passaggio del flusso di lavoro consigliato.

A partire da SQL Server Management Studio v18, la funzionalità Assistente ottimizzazione query (QTA) guida gli utenti attraverso il flusso di lavoro consigliato per mantenere la stabilità delle prestazioni durante gli aggiornamenti alle versioni più recenti di SQL Server, come documentato nella sezione Mantenere la stabilità delle prestazioni durante l'aggiornamento a scenari di utilizzo di Query Store più recenti. 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. Invece, l'assistente ottimizzatore tiene traccia di eventuali regressioni nella vista Query Store Query Reggredite e itera attraverso possibili permutazioni delle varianti del modello di ottimizzatore applicabili, affinché possa essere prodotto un nuovo piano 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 Collega un database o istruzioni RESTORE) a una versione più recente del motore di database di SQL Server e il livello di compatibilità del database prima dell'aggiornamento non viene modificato immediatamente. L'assistente QTA guida 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.

Il diagramma seguente mostra come QTA modifica solo le fasi finali del flusso di lavoro consigliato per aggiornare il livello di compatibilità utilizzando il Query Store illustrato in precedenza. Anziché scegliere tra il piano di esecuzione attualmente inefficiente e l'ultimo piano di esecuzione valido noto, l'assistente di ottimizzazione delle query (QTA) presenta opzioni di ottimizzazione specifiche per le query che hanno subito regressione, per creare un nuovo stato migliorato con piani di esecuzione più efficienti.

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.

QTA si rivolge a modelli noti di regressione delle query causati dalle modifiche nelle versioni di stima della cardinalità (SQL Server). Ad esempio, quando si aggiorna un database da SQL Server 2012 (11.x) e il livello di compatibilità del database 110, a SQL Server 2017 (14.x) e il livello di compatibilità del database 140, alcune query potrebbero regredire perché sono state progettate specificamente per funzionare con la versione ce esistente 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 solo una modifica specifica nella versione più recente introduce la regressione, è possibile suggerire che la query usi solo la parte pertinente della versione ce precedente che funzionava meglio per la query specifica, pur usando tutti gli altri miglioramenti delle versioni ce 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 CE ricercati dall'analisi delle query sono:

Indipendenza e correlazione: se il presupposto di indipendenza fornisce stime migliori per la query specifica, l'hint USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') per la query fa sì che SQL Server generi un piano di esecuzione usando la selettività minima quando si stimano AND i predicati per i filtri da tenere conto della correlazione. Per altre informazioni, vedere Suggerimenti della query USE HINT e Versioni della Stima della Cardinalità.
Contenimento semplice e contenimento di base: se un contenimento di join diverso fornisce stime migliori per la query specifica, l'hint USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') per la query fa sì che SQL Server generi un piano di esecuzione usando il presupposto Di contenimento semplice anziché il presupposto di contenimento di base predefinito. Per altre informazioni, vedere Suggerimenti della query USE HINT e Versioni della Stima della 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).

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 forza determinati comportamenti che potrebbero essere risolti negli 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. Forzando i comportamenti, è possibile impedire al carico di lavoro di trarre vantaggio dai 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). Viene usato per proporre le impostazioni consigliate di Query Store per consentire provvisoriamente 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 mostrano lo stato corrente di Query Store nel database di destinazione e 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 query stantia proposta è pari al doppio del valore previsto per la durata del carico di lavoro, espresso in giorni. Ciò è dovuto al fatto che Query Store deve contenere informazioni sul carico di lavoro di base e sul carico di lavoro di aggiornamento post-database.
  Al termine, selezionare Avanti.
  
  Importante
  
  La dimensione massima proposta è un valore arbitrario che può essere adatto per un breve carico di lavoro a tempo. Tuttavia, potrebbe non essere sufficiente contenere informazioni sui carichi di lavoro di base e dopo l'aggiornamento del database per carichi di lavoro con utilizzo intensivo, ovvero quando potrebbero essere generati molti 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 ha distribuito guide di piano, non è possibile usare 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. Al termine del carico di lavoro, selezionare Fine con l'esecuzione del carico di lavoro e selezionare Avanti.
  
  Nota
  
  La finestra QTA può essere chiusa mentre il carico di lavoro è in esecuzione. Tornare alla sessione che rimane in stato attivo in un secondo momento riprende dallo stesso passaggio in cui è stato interrotto.
2. Aggiorna database richiede l'autorizzazione per aggiornare il livello di compatibilità del database alla destinazione desiderata. Per procedere con il passaggio secondario successivo, selezionare Sì.
  
  La pagina seguente conferma che il livello di compatibilità del database è stato aggiornato correttamente.
3. La raccolta dati osservata richiede all'utente di eseguire di nuovo il ciclo del carico di lavoro rappresentativo, in modo che Query Store possa raccogliere una baseline comparativa usata per cercare 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. Al termine del carico di lavoro, selezionare Fine con l'esecuzione del carico di lavoro 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.

Non è possibile selezionare le query con Tunable impostato su False per la sperimentazione.

Importante

Un avviso indica che una volta che QTA passa alla fase di sperimentazione, non è possibile tornare alla pagina Visualizza 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 ampliata 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.
- % Modifica: specifica la modifica della percentuale per la metrica selezionata tra lo stato precedente e successivo alla sperimentazione, che rappresenta la quantità di miglioramento misurato 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 pertinente 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 ha distribuito guide di piano, non è possibile usare 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.

Autorizzazioni

È necessaria l'appartenenza al ruolo db_owner.

Commenti e suggerimenti

Questa pagina è stata utile?

Last updated on 2025-10-10