Guida di ottimizzazione e convalida post-migrazione

Si applica a:SQL Server

I passaggi post-migrazione di SQL Server sono molto importanti per riconciliare l'accuratezza e la completezza dei dati, nonché per individuare problemi di prestazioni relativi al carico di lavoro.

Scenari di prestazioni comuni

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) e la migrazione dalla piattaforma esterna (ad esempio Oracle, DB2, MySQL e Sybase) a SQL Server.

Regressioni delle query dovute a modifiche della versione CE (stima della cardinalità)

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.

Per altre informazioni sul CE, consultare la pagina Stima della cardinalità (SQL Server).

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 articolo, consultare la pagina 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 è presente nell'istanza di SQL Server di origine, la migrazione a una versione più recente di SQL Server as-is non risolve 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 include 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 vengono 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 articolo, consultare la pagina Sensibilità 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

Usare la funzionalità di analisi del piano di Management Studio per comprendere rapidamente se si tratta di un problema. Per altre informazioni, vedere Novità di SSMS: Risoluzione dei problemi di prestazioni delle query semplificata.

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 eventuali riferimenti agli indici mancanti.

2. Suggerimenti di indicizzazione generati da Ottimizzazione guidata motore di database.

3. Usare il sys.dm_db_missing_index_details.

4. Usare script preesistenti che possono usare DMV esistenti per fornire informazioni dettagliate su eventuali indici mancanti, duplicati, ridondanti, usati raramente e completamente inutilizzati, ma anche se qualsiasi riferimento all'indice viene hinted/hardcoded in procedure e funzioni esistenti nel database.

Suggerimento

Esempi di questi script preesistenti includono la creazione dell'indice e le informazioni sugli indici.

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 è presente nell'istanza di SQL Server di origine, la migrazione a una versione più recente di SQL Server as-is non risolve 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.

Nota

Il termine SARGable nei database relazionali fa riferimento a un predicatoin grado di usare un indice pervelocizzare l'esecuzione della query. Per altre informazioni, vedere Guida all'architettura e progettazione degli indici di SQL Server e Azure SQL.

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 tipi di dati di destinazione desiderati.

   Ciò potrebbe comportare il confronto di qualsiasi costrutto di codice definito dall'utente archiviato nel database (ad esempio stored procedure, funzioni definite dall'utente o viste) con tabelle di sistema che contengono 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 queste operazioni possono essere eseguite a livello di codice.

Uso di funzioni con valori di tabella (con istruzioni multiple o incorporate)

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 è presente nell'istanza di SQL Server di origine, la migrazione a una versione più recente di SQL Server as-is non risolve 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.

Poiché la tabella di output di una funzione a valori di tabella con più istruzioni (MSTVF) non è creata al momento della compilazione, l'Ottimizzatore di query di SQL Server si affida alle euristiche anziché alle statistiche reali per determinare le stime delle righe.

Anche se gli indici vengono aggiunti alle tabelle di base, questa operazione non è utile.

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 l'MSTVF è solo un'istruzione singola, eseguire la conversione in una funzione con valori di tabella incorporati.

   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.

Contenuto correlato

• Procedure consigliate per il monitoraggio dei carichi di lavoro con Query Store
• Database di esempio per OLTP in 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