Guida di ottimizzazione e convalida post-migrazionePost-migration Validation and Optimization Guide

In questo argomento si applica a: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Il passaggio post-migrazione di SQL ServerSQL Server è molto importante per riconciliare l'accuratezza e la completezza dei dati, nonché per individuare problemi di prestazioni relativi al carico di lavoro. SQL ServerSQL Server post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload.

Scenari comuni relativi alle prestazioniCommon Performance Scenarios

Di seguito sono riportati alcuni scenari comuni relativi alle prestazioni rilevati dopo la migrazione alla piattaforma SQL ServerSQL Server e viene indicato come risolverli.Below are some of the common performance scenarios encountered after migrating to SQL ServerSQL Server Platform and how to resolve them. Sono inclusi gli scenari specifici della migrazione da SQL ServerSQL Server a SQL ServerSQL Server (versioni precedenti a versioni più recenti), nonché la migrazione dalla piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, a SQL ServerSQL Server.These include scenarios that are specific to SQL ServerSQL Server to SQL ServerSQL Server migration (older versions to newer versions), as well as foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

Regressioni delle query dovute a modifiche della versione CEQuery regressions due to change in CE version

Si applica a: migrazione da SQL ServerSQL Server a SQL ServerSQL Server.Applies to: SQL ServerSQL Server to SQL ServerSQL Server migration.

Quando si esegue la migrazione da versioni precedenti di SQL ServerSQL Server a SQL Server 2014SQL Server 2014 o successiva e si aggiorna il livello di compatibilità del database alla versione più recente, un carico di lavoro può essere esposto al rischio di regressione delle prestazioni.When migrating from an older versions of SQL ServerSQL Server to SQL Server 2014SQL Server 2014 or newer, and upgrading the database compatibility level to the latest one, a workload may be exposed to the risk of performance regression.

Ciò avviene perché a partire da SQL Server 2014SQL Server 2014 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.This is because starting with SQL Server 2014SQL Server 2014, all Query Optimizer changes are tied to the latest database compatibility level, so plans are not changed right at point of upgrade but rather when a user changes the COMPATIBILITY_LEVEL database option to the latest one. Questa funzionalità, in combinazione con Archivio query, offre un alto livello di controllo sulle prestazioni delle query nel processo di aggiornamento.This capability, in combination with Query Store gives you a great level of control over the query performance in the upgrade process.

Per altre informazioni sulle modifiche di Query Optimizer introdotte in SQL Server 2014SQL Server 2014, vedere la sezione relativa all'ottimizzazione dei piani di query con la stima di cardinalità di SQL Server 2014.For more information on Query Optimizer changes introduced in SQL Server 2014SQL Server 2014, see Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

Procedura di risoluzioneSteps to resolve

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:Change the database compatibility level to the source version, and follow the recommended upgrade workflow as shown in the following picture:

query-store-usage-5query-store-usage-5

Per altre informazioni su questo argomento, vedere Mantenere la stabilità delle prestazioni durante l'aggiornamento a SQL Server.For more information on this topic, see Keep performance stability during the upgrade to newer SQL Server.

Sensibilità all'analisi dei parametriSensitivity to parameter sniffing

Si applica a: migrazione da piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, a SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) to SQL ServerSQL Server migration.

Nota

Per le migrazioni da SQL ServerSQL Server a SQL ServerSQL Server, se questo problema si verificava nell'istanza di SQL ServerSQL Server di origine, la semplice migrazione a una versione più recente di SQL ServerSQL Server non risolverà il problema descritto in questo scenario.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

SQL ServerSQL 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. compiles query plans on stored procedures by using sniffing the input parameters at the first compile, generating a parameterized and reusable plan, optimized for that input data distribution. Anche se non vengono usate stored procedure, la maggior parte delle istruzioni che generano piani semplici includerà dei parametri.Even if not stored procedures, most statements generating trivial plans will be parameterized. Dopo che un piano è stato inizialmente memorizzato nella cache, per le esecuzioni future verrà eseguito il mapping a un piano precedentemente memorizzato nella cache.After a plan is first cached, any future execution maps to a previously cached plan. 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.A potential problem arises when that first compilation may not have used the most common sets of parameters for the usual workload. Per parametri diversi, lo stesso piano di esecuzione diventa inefficiente.For different parameters, the same execution plan becomes inefficient. Per altre informazioni su questo argomento, vedere il blog sull'analisi dei parametri.For more information on this topic, see Parameter Sniffing.

Procedura di risoluzioneSteps to resolve

  1. Usare l'hint RECOMPILE.Use the RECOMPILE hint. Ogni volta viene calcolato un piano adattato al valore di ogni parametro.A plan is calculated every time adapted to each parameter value.
  2. Riscrivere la stored procedure in modo da usare l'opzione (OPTIMIZE FOR(<input parameter> = <value>)).Rewrite the stored procedure to use the option (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.Decide which value to use that suits most of the relevant workload, creating and maintaining one plan that becomes efficient for the parameterized value.
  3. Riscrivere la stored procedure usando la variabile locale all'interno della stored procedure.Rewrite the stored procedure using local variable inside the procedure. Ora l'utilità di ottimizzazione usa il vettore di densità per le stime, ottenendo lo stesso piano indipendentemente dal valore del parametro.Now the optimizer uses the density vector for estimations, resulting in the same plan regardless of the parameter value.
  4. Riscrivere la stored procedure in modo da usare l'opzione (OPTIMIZE FOR UNKNOWN).Rewrite the stored procedure to use the option (OPTIMIZE FOR UNKNOWN). Si otterrà lo stesso effetto dell'uso della tecnica della variabile locale.Same effect as using the local variable technique.
  5. Riscrivere la query in modo da usare l'hint DISABLE_PARAMETER_SNIFFING.Rewrite the query to use the 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>.Same effect as using the local variable technique by totally disabling parameter sniffing, unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR <value> is used.

Suggerimento

Usare la funzionalità di analisi del piano di Management StudioManagement Studio per comprendere rapidamente se si tratta di un problema.Leverage the Management StudioManagement Studio Plan Analysis feature to quickly identify if this is an issue. Altre informazioni sono disponibili qui.More information available here.

Indici mancantiMissing indexes

Si applica a: migrazione da piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, e da SQL ServerSQL Server a SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

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.Incorrect or missing indexes causes extra I/O that leads to extra memory and CPU being wasted. 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.This maybe because workload profile has changed such as using different predicates, invalidating existing index design. Le prove di una strategia di indicizzazione inadeguata o di modifiche apportate al profilo del carico di lavoro includono:Evidence of a poor indexing strategy or changes in workload profile include:

  • Ricerca di indici duplicati, ridondati, raramente usati e completamente inutilizzati.Look for duplicate, redundant, rarely used and completely unused indexes.
  • Particolare attenzione prestata a indici inutilizzati con aggiornamenti.Special care with unused indexes with updates.

Procedura di risoluzioneSteps to resolve

  1. Usare il piano di esecuzione grafico per i riferimenti a indici mancanti.Leverage the graphical execution plan for any Missing Index references.
  2. Suggerimenti di indicizzazione generati da Ottimizzazione guidata motore di database.Indexing suggestions generated by Database Engine Tuning Advisor.
  3. Usare Missing Indexes DMV (DMV degli indici mancanti) oppure SQL Server Performance Dashboard.Leverage the Missing Indexes DMV or through the 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.Leverage pre-existing scripts that can use existing DMVs to provide insight into any missing, duplicate, redundant, rarely used and completely unused indexes, but also if any index reference is hinted/hard-coded into existing procedures and functions in your database.

Suggerimento

Esempi di questi script pre-esistenti includono Index Creation e Index Information.Examples of such pre-existing scripts include Index Creation and Index Information.

Impossibilità di usare i predicati per filtrare i datiInability to use predicates to filter data

Si applica a: migrazione da piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, e da SQL ServerSQL Server a SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

Nota

Per le migrazioni da SQL ServerSQL Server a SQL ServerSQL Server, se questo problema si verificava nell'istanza di SQL ServerSQL Server di origine, la semplice migrazione a una versione più recente di SQL ServerSQL Server non risolverà il problema descritto in questo scenario.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

SQL ServerSQL Server Query Optimizer si basa solo sulle informazioni note in fase di compilazione. Query Optimizer can only account for information that is known at compile time. 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.If a workload relies on predicates that can only be known at execution time, then the potential for a poor plan choice increases. Per un piano di qualità migliore, i predicati devono essere SARGable o Search Argumentable.For a better-quality plan, predicates must be SARGable, or Search Argumentable.

Alcuni esempi di predicati non SARGable:Some examples of non-SARGable predicates:

  • Conversioni di dati implicite, ad esempio da VARCHAR a NVARCHAR o da INT a VARCHAR.Implicit data conversions, like VARCHAR to NVARCHAR, or INT to VARCHAR. Cercare avvisi di runtime CONVERT_IMPLICIT nei piani di esecuzione effettivi.Look for runtime CONVERT_IMPLICIT warnings in the Actual Execution Plans. Anche la conversione da un tipo a un altro può causare una perdita di precisione.Converting from one type to another can also cause a loss of precision.
  • Espressioni indeterminate complesse, ad esempio WHERE UnitPrice + 1 < 3.975, ma non WHERE UnitPrice < 320 * 200 * 32.Complex undetermined expressions such as WHERE UnitPrice + 1 < 3.975, but not WHERE UnitPrice < 320 * 200 * 32.
  • Espressioni che usano funzioni, ad esempio WHERE ABS(ProductID) = 771 o WHERE UPPER(LastName) = 'Smith'Expressions using functions, such as WHERE ABS(ProductID) = 771 or WHERE UPPER(LastName) = 'Smith'
  • Stringhe con un carattere jolly iniziale, ad esempio WHERE LastName LIKE '%Smith', ma non WHERE LastName LIKE 'Smith%'.Strings with a leading wildcard character, such as WHERE LastName LIKE '%Smith', but not WHERE LastName LIKE 'Smith%'.

Procedura di risoluzioneSteps to resolve

  1. Dichiarare sempre variabili/parametri come tipo di dati di destinazione desiderato.Always declare variables/parameters as the intended target data type.
    • 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).This may involve comparing any user-defined code construct that is stored in the database (such as stored procedures, user-defined functions or views) with system tables that hold information on data types used in underlying tables (such as 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.If unable to traverse all code to the previous point, then for the same purpose, change the data type on the table to match any variable/parameter declaration.
  3. Riflettere sull'utilità dei costrutti seguenti:Reason out the usefulness of the following constructs:
    • Funzioni usate come predicatiFunctions being used as predicates;
    • Ricerche con caratteri jollyWildcard searches;
    • Espressioni complesse basate su dati a colonne: valutare la necessità di creare invece colonne calcolate persistenti che possono essere indicizzate.Complex expressions based on columnar data – evaluate the need to instead create persisted computed columns, which can be indexed;

Nota

Tutte le operazioni indicate sopra possono essere eseguite a livello di codice.All of the above can be done programmatically.

Uso di funzioni con valori di tabella (con istruzioni multiple e inline)Use of Table Valued Functions (Multi-Statement vs Inline)

Si applica a: migrazione da piattaforma esterna, ad esempio Oracle, DB2, MySQL e Sybase, e da SQL ServerSQL Server a SQL ServerSQL Server.Applies to: Foreign platform (such as Oracle, DB2, MySQL and Sybase) and SQL ServerSQL Server to SQL ServerSQL Server migration.

Nota

Per le migrazioni da SQL ServerSQL Server a SQL ServerSQL Server, se questo problema si verificava nell'istanza di SQL ServerSQL Server di origine, la semplice migrazione a una versione più recente di SQL ServerSQL Server non risolverà il problema descritto in questo scenario.For SQL ServerSQL Server to SQL ServerSQL Server migrations, if this issue existed in the source SQL ServerSQL Server, migrating to a newer version of SQL ServerSQL Server as-is will not address this scenario.

Le funzioni con valori di tabella restituiscono un tipo di dati tabella che può costituire un'alternativa alle viste.Table Valued Functions return a table data type that can be an alternative to views. 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.While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more logic than is possible in views.

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 ServerSQL Server si basa sull'euristica e non su statistiche effettive per determinare le stime delle righe.Since the output table of an MSTVF (Multi-Statement Table Valued Function) is not created at compile time, the SQL ServerSQL Server Query Optimizer relies on heuristics, and not actual statistics, to determine row estimations. L'aggiunta di indici alle tabelle di base non risolverà il problema.Even if indexes are added to the base table(s), this is not going to help. Per le funzioni con valori di tabella con istruzioni multiple, SQL ServerSQL 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 2014SQL Server 2014 questa stima fissa è di 100 righe).For MSTVFs, SQL ServerSQL Server uses a fixed estimation of 1 for the number of rows expected to be returned by an MSTVF (starting with SQL Server 2014SQL Server 2014 that fixed estimation is 100 rows).

Procedura di risoluzioneSteps to resolve

  1. Se la funzione con valori di tabella con istruzioni multiple è un'istruzione singola, convertirla in funzione inline con valori di tabella.If the Multi-Statement TVF is single statement only, convert to Inline TVF.

    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
    

    PerTo

    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.If more complex, consider using intermediate results stored in Memory-Optimized tables or temporary tables.

Ulteriori informazioniAdditional Reading

Procedure consigliate per l'archivio queryBest Practice with the Query Store
Tabelle con ottimizzazione per la memoriaMemory-Optimized Tables
Funzioni definite dall'utenteUser-Defined Functions
Table Variables and Row Estimations - Part 1 (Variabili di tabella e stime delle righe: parte 1)Table Variables and Row Estimations - Part 1
Table Variables and Row Estimations - Part 2 (Variabili di tabella e stime delle righe: parte 2)Table Variables and Row Estimations - Part 2
Memorizzazione nella cache e riutilizzo del piano di esecuzioneExecution Plan Caching and Reuse