Guida di ottimizzazione e convalida post-migrazione

Si applica a:SQL Server

Il passaggio post-migrazione di SQL Server è molto importante per riconciliare l'accuratezza e la completezza dei dati, nonché per individuare problemi di prestazioni relativi al carico di lavoro.

Scenari comuni relativi alle prestazioni

Di seguito sono riportati alcuni scenari comuni relativi alle prestazioni rilevati dopo la migrazione alla piattaforma SQL Server e viene indicato come risolverli. Sono inclusi gli scenari specifici della migrazione da SQL Server a SQL Server (versioni precedenti a versioni più recenti), nonché la migrazione dalla piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, a SQL Server.

Regressioni delle query dovute a modifiche della versione CE

Si applica a: migrazione da SQL Server a SQL Server.

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.

Ciò avviene perché a partire da SQL Server 2014 (12.x) tutte le modifiche di Query Optimizer sono legate 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 a una versione più recente. Questa funzionalità, in combinazione con Archivio query, offre un alto livello di controllo sulle prestazioni delle query nel processo di aggiornamento.

Per altre informazioni sulle modifiche di Query Optimizer introdotte in SQL Server 2014 (12.x), vedere la sezione relativa all'ottimizzazione dei piani di query con la stima di cardinalità di SQL Server 2014.

Procedura di risoluzione

Modificare il livello di compatibilità del database in base alla versione di origine e seguire il flusso di lavoro consigliato per l'aggiornamento, come illustrato nell'immagine seguente:

Per altre informazioni su questo argomento, vedere Mantenere la stabilità delle prestazioni durante l'aggiornamento a SQL Server.

Sensibilità all'analisi dei parametri

Si applica a: migrazione da piattaforma esterna (ad esempio Oracle, DB2, MySQL e Sybase) a SQL Server.

Nota

Per le migrazioni da SQL Server a SQL Server, se questo problema si verificava nell'istanza di SQL Server di origine, la semplice migrazione a una versione più recente di SQL Server non risolverà il problema descritto in questo scenario.

SQL Server compila i piani di query sulle stored procedure analizzando i parametri di input alla prima compilazione e generando un piano con parametri riutilizzabile e ottimizzato per la distribuzione di questi dati di input. Anche se non vengono usate stored procedure, la maggior parte delle istruzioni che generano piani semplici includerà dei parametri. Dopo che un piano è stato inizialmente memorizzato nella cache, per le esecuzioni future verrà eseguito il mapping a un piano precedentemente memorizzato nella cache. Un potenziale problema si presenta nel caso in cui durante questa prima compilazione non siano stati usati i set di parametri più comuni per il normale carico di lavoro. Per parametri diversi, lo stesso piano di esecuzione diventa inefficiente. Per altre informazioni su questo argomento, vedere il blog sull'analisi dei parametri.

Procedura di risoluzione

1. Usare l'hint RECOMPILE. Ogni volta viene calcolato un piano adattato al valore di ogni parametro.
2. Riscrivere la stored procedure in modo da usare l'opzione (OPTIMIZE FOR(<input parameter> = <value>)). Individuare il valore che soddisfa la maggior parte del carico di lavoro pertinente, creando e gestendo un piano che diventa efficiente per il valore con parametri.
3. Riscrivere la stored procedure usando la variabile locale all'interno della stored procedure. Ora l'utilità di ottimizzazione usa il vettore di densità per le stime, ottenendo lo stesso piano indipendentemente dal valore del parametro.
4. Riscrivere la stored procedure in modo da usare l'opzione (OPTIMIZE FOR UNKNOWN). Si otterrà lo stesso effetto dell'uso della tecnica della variabile locale.
5. Riscrivere la query in modo da usare l'hint DISABLE_PARAMETER_SNIFFING. Si otterrà lo stesso effetto dell'uso della tecnica della variabile locale disabilitando totalmente l'analisi dei parametri, a meno che non vengano usate OPTION(RECOMPILE), WITH RECOMPILE o OPTIMIZE FOR <value>.

Suggerimento

Sfruttare la funzionalità di analisi del piano di Management Studio per comprendere rapidamente se si tratta di un problema. Altre informazioni sono disponibili qui.

Indici mancanti

Si applica a: migrazione da piattaforma esterna (ad esempio Oracle, DB2, MySQL e Sybase) e SQL Server a SQL Server.

Gli indici non corretti o mancanti causano un maggiore I/O che a sua volta determina un uso superiore della memoria e uno spreco di CPU. La causa del problema può essere la modifica del profilo del carico di lavoro, ad esempio l'uso di predicati diversi, che può avere invalidato la struttura degli indici esistente. Le prove di una strategia di indicizzazione inadeguata o di modifiche apportate al profilo del carico di lavoro includono:

• Ricerca di indici duplicati, ridondati, raramente usati e completamente inutilizzati.
• Particolare attenzione prestata a indici inutilizzati con aggiornamenti.

Procedura di risoluzione

1. Usare il piano di esecuzione grafico per i riferimenti a indici mancanti.
2. Suggerimenti di indicizzazione generati da Ottimizzazione guidata motore di database.
3. Usare Missing Indexes DMV (DMV degli indici mancanti) oppure SQL Server Performance Dashboard.
4. Usare script pre-esistenti in grado di usare DMV esistenti che offrano informazioni su indici mancanti, duplicati, ridondanti, raramente usati e completamente inutilizzati, ma che possano anche rivelare se i riferimenti agli indici includono hint o sono hardcoded in procedure e funzioni esistenti nel database.

Suggerimento

Esempi di questi script pre-esistenti includono Index Creation e Index Information.

Impossibilità di usare i predicati per filtrare i dati

Si applica a: migrazione da piattaforma esterna (ad esempio Oracle, DB2, MySQL e Sybase) e SQL Server a SQL Server.

Nota

Per le migrazioni da SQL Server a SQL Server, se questo problema si verificava nell'istanza di SQL Server di origine, la semplice migrazione a una versione più recente di SQL Server non risolverà il problema descritto in questo scenario.

SQL Server Query Optimizer si basa solo sulle informazioni note in fase di compilazione. Se un carico di lavoro si basa su predicati che è possibile conoscere solo in fase di esecuzione, le probabilità di scegliere un piano insoddisfacente aumentano. Per un piano di qualità migliore, i predicati devono essere SARGable o Search Argumentable.

Alcuni esempi di predicati non SARGable:

• Conversioni di dati implicite, ad esempio da VARCHAR a NVARCHAR o da INT a VARCHAR. Cercare avvisi di runtime CONVERT_IMPLICIT nei piani di esecuzione effettivi. Anche la conversione da un tipo a un altro può causare una perdita di precisione.
• Espressioni indeterminate complesse, ad esempio WHERE UnitPrice + 1 < 3.975, ma non WHERE UnitPrice < 320 * 200 * 32.
• Espressioni che usano funzioni, ad esempio WHERE ABS(ProductID) = 771 o WHERE UPPER(LastName) = 'Smith'
• Stringhe con un carattere jolly iniziale, ad esempio WHERE LastName LIKE '%Smith', ma non WHERE LastName LIKE 'Smith%'.

Procedura di risoluzione

1. Dichiarare sempre variabili/parametri come tipo di dati di destinazione desiderato.

• Ciò può richiedere il confronto del costrutto di codice definito dall'utente archiviato nel database, ad esempio stored procedure, funzioni o viste definite dall'utente, con le tabelle di sistema contenenti informazioni sui tipi di dati usati nelle tabelle sottostanti (ad esempio, sys.columns).

2. Se non è possibile passare al punto precedente del codice, modificare il tipo di dati nella tabella in modo che corrisponda alla dichiarazione di variabile o parametro.
3. Riflettere sull'utilità dei costrutti seguenti:

• Funzioni usate come predicati
• Ricerche con caratteri jolly
• Espressioni complesse basate su dati a colonne: valutare la necessità di creare invece colonne calcolate persistenti che possono essere indicizzate.

Nota

Tutte le operazioni indicate sopra possono essere eseguite a livello di codice.

Uso di funzioni con valori di tabella (con istruzioni multiple e inline)

Si applica a: migrazione da piattaforma esterna (ad esempio Oracle, DB2, MySQL e Sybase) e SQL Server a SQL Server.

Nota

Per le migrazioni da SQL Server a SQL Server, se questo problema si verificava nell'istanza di SQL Server di origine, la semplice migrazione a una versione più recente di SQL Server non risolverà il problema descritto in questo scenario.

Le funzioni con valori di tabella restituiscono un tipo di dati tabella che può costituire un'alternativa alle viste. Per le viste è possibile usare una sola istruzione SELECT, mentre le funzioni definite dall'utente possono contenere istruzioni aggiuntive che consentono una logica più efficace di quella consentita nelle viste.

Importante

Poiché la tabella di output di una funzione con valori di tabella con istruzioni multiple non viene creata in fase di compilazione, Query Optimizer di SQL Server si basa sull'euristica e non su statistiche effettive per determinare le stime delle righe. L'aggiunta di indici alle tabelle di base non risolverà il problema. Per le funzioni con valori di tabella con istruzioni multiple, SQL Server usa una stima fissa di 1 per il numero di righe che si prevede verrà restituito da una funzione con valori di tabella con istruzioni multiple (a partire da SQL Server 2014 (12.x), questa stima fissa è di 100 righe).

Procedura di risoluzione

1. Se la funzione con valori di tabella con istruzioni multiple è un'istruzione singola, convertirla in funzione inline con valori di tabella.

   CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
   RETURNS @tblAddress TABLE
   ([Address] VARCHAR(60) NOT NULL)
   AS
   BEGIN
     INSERT INTO @tblAddress ([Address])
     SELECT TOP 1 [AddressLine1]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   RETURN
   END
   

   L'esempio di formato inline viene visualizzato successivamente.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
   RETURNS TABLE
   AS
   RETURN (
     SELECT TOP 1 [AddressLine1] AS [Address]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   )
   

2. Se è più complessa, valutare l'uso dei risultati intermedi archiviati nelle tabelle ottimizzate per la memoria o nelle tabelle temporanee.

Altre letture

Procedure consigliate per l'archivio query
Tabelle ottimizzate per la memoria
Funzioni definite dall'utente
Table Variables and Row Estimations - Part 1 (Variabili di tabella e stime delle righe: parte 1)
Table Variables and Row Estimations - Part 2 (Variabili di tabella e stime delle righe: parte 2)
Memorizzazione nella cache e riutilizzo del piano di esecuzione