Riduzione delle prestazioni delle query dopo l'aggiornamento da SQL Server 2012 o versioni precedenti a 2014 o versioni successive

Dopo l'aggiornamento di SQL Server dalla versione 2012 o precedente alla versione 2014 o successiva, è possibile che si verifichi il problema seguente: la maggior parte delle query originali viene eseguita correttamente, ma alcune query vengono eseguite più lentamente rispetto alla versione precedente. Anche se esistono molte possibili cause e fattori che contribuiscono, una causa relativamente comune è la modifica del modello di stima della cardinalità (CE) dopo l'aggiornamento. Sono state introdotte modifiche significative ai modelli ce a partire da SQL Server 2014.

Questo articolo illustra i passaggi e le soluzioni per risolvere i problemi di prestazioni delle query che si verificano quando si usa la stima di cardinalità predefinita, ma non si verificano quando si usa la stima di cardinalità legacy.

Risoluzione dei problemi: identificare se le modifiche ce sono il problema e individuare il motivo

Passaggio 1: Identificare se viene usata la stima di cardinalità predefinita

1. Scegliere una query che viene eseguita più lentamente dopo l'aggiornamento.
2. Eseguire la query e raccogliere il piano di esecuzione.
3. Nel piano di esecuzione Finestra Proprietà selezionare CardinalityEstimationModelVersion.
4. Il valore 70 indica la stima di cardinalità legacy e il valore 120 o superiore indica l'uso della stima di cardinalità predefinita.

Se viene usata la ce legacy, le modifiche ce non sono la causa del problema di prestazioni. Se viene usata la stima di cardinalità predefinita, andare al passaggio successivo.

Passaggio 2: Identificare se Query Optimizer può generare un piano migliore usando la stima di cardinalità legacy

Eseguire la query con la stima di cardinalità legacy. Se offre prestazioni migliori rispetto all'uso della stima di cardinalità predefinita, andare al passaggio successivo. Se le prestazioni non migliorano, le modifiche ce non sono la causa.

Passaggio 3: Scoprire perché la query offre prestazioni migliori con la stima di cardinalità legacy

Testare i vari hint per le query correlate alla stima della cardinalità per la query. Per SQL Server 2014, usare i flag di traccia corrispondenti 4137, 9472 e 4139 per testare la query. Determinare quali hint o flag di traccia influiscono positivamente sulle prestazioni in base a questi test.

Risoluzione

Per risolvere il problema, utilizzare uno dei seguenti metodi alternativi:

• Ottimizzare la query.

  Comprensibilmente, non è sempre possibile riscrivere le query, ma soprattutto quando sono presenti solo alcune query che possono essere riscritte, questo approccio deve essere la prima scelta. Le query scritte in modo ottimale offrono prestazioni migliori indipendentemente dalle versioni ce.

• Usare gli hint di query identificati nel passaggio 3.

  Questo approccio mirato consente ad altri carichi di lavoro di trarre vantaggio dai presupposti e dai miglioramenti di ce predefiniti. Inoltre, è un'opzione più affidabile rispetto alla creazione di una guida di piano. E non richiede Query Store (QDS), a differenza dell'uso forzato di un piano (opzione più affidabile).

• Forza un buon piano.

  Si tratta di un'opzione favorevole e può essere usata per definire come destinazione query specifiche. È possibile forzare un piano usando una guida di piano o QDS. QDS è in genere più facile da usare.

• Usare la configurazione con ambito database per forzare la stima di cardinalità legacy.

  Si tratta di un approccio meno preferito perché si tratta di un'impostazione a livello di database e si applica a tutte le query su questo database. Tuttavia, a volte è necessario quando un approccio mirato non è fattibile. È certamente l'opzione più semplice da implementare.

• Usare il flag di traccia 9841 per forzare la ce legacy a livello globale. A tale scopo, usare DBCC TRACEON o impostare il flag di traccia come parametro di avvio.

  Si tratta dell'approccio meno mirato e deve essere usato solo come mitigazione temporanea quando non è possibile applicare alcuna delle altre opzioni.

Opzioni per abilitare la cardinalità legacy

Livello di query: usare l'opzione Query Hint o QUERYTRACEON

• Per SQL Server 2016 SP1 e versioni successive, usare hint FORCE_LEGACY_CARDINALITY_ESTIMATION per la query, ad esempio:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Abilitare il flag di traccia 9481 per forzare un piano ce legacy. Ecco un esempio:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Livello di database: impostare il livello di compatibilità o la configurazione con ambito

• Per SQL Server 2016 e versioni successive, modificare la configurazione con ambito database:

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Modificare il livello di compatibilità per il database. È l'unica opzione a livello di database disponibile per SQL Server 2014. Si noti che questa modifica influisce più che solo sulla ce. Per determinare l'impatto delle modifiche del livello di compatibilità, passare a Livello di compatibilità ALTER DATABASE (Transact-SQL) ed esaminare le tabelle "Differenze" in esso contenute.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Livello server: usare il flag di traccia

Usare il flag di traccia 9481 per forzare la cardinalità legacy a livello di server:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Domande frequenti

D1: Si è interessati all'aggiornamento a una versione più recente di SQL Server e si è interessati alle regressioni delle prestazioni dello strumento di stima della cardinalità. Quale pianificazione dell'aggiornamento è consigliata per ridurre al minimo i problemi?

Per i database preesistenti in esecuzione a livelli di compatibilità inferiori, il flusso di lavoro consigliato per l'aggiornamento di Query Processor a un livello di compatibilità superiore è descritto in Modificare la modalità di compatibilità del database e usare Query Store e gli scenari di utilizzo di Query Store. La metodologia introdotta nell'articolo si applica per i passaggi a 130 o versioni successive per SQL Server e database SQL di Azure.

D2: Non ho tempo per testare le modifiche ce. Cosa posso fare in questo caso?

Per le applicazioni e i carichi di lavoro preesistenti, non è consigliabile passare alla stima di cardinalità predefinita fino a quando non sono stati eseguiti test di regressione sufficienti. In caso di dubbi, è consigliabile aggiornare SQL Server e passare al livello di compatibilità più recente disponibile. Per precauzione, abilitare anche il flag di traccia 9481 per SQL Server 2014 o configurare la configurazione ON con ambito database LEGACY_CARDINALITY_ESTIMATION per SQL Server 2016 e versioni successive fino a quando non si ha la possibilità di eseguire il test.

D3: Esistono svantaggi dell'uso permanente della ce legacy?

I miglioramenti e le correzioni future della stima della cardinalità sono incentrati sulle versioni più recenti. La versione 70 è uno stato intermedio accettabile. Tuttavia, dopo un attento test, è consigliabile passare a una versione ce più recente per trarre vantaggio dalle correzioni ce più recenti. Esiste una probabilità elevata di modifiche al piano di query quando si passa dalla stima di cardinalità legacy, quindi testare prima di apportare modifiche ai sistemi di produzione. Le modifiche possono migliorare le prestazioni delle query in molti casi, ma in alcuni casi le prestazioni delle query potrebbero peggiorare.

D4: Ho migliaia di database e non voglio attivare manualmente LEGACY_CARDINALITY_ESTIMATION per ognuno. Esiste un metodo alternativo?

Per SQL Server 2014, abilitare il flag di traccia 9481 per usare la stima di cardinalità legacy per tutti i database indipendentemente dal livello di compatibilità. Per SQL Server 2016 e versioni successive, eseguire la query seguente per scorrere i database. L'impostazione verrà abilitata anche quando il database viene ripristinato o collegato in un altro server.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Per database SQL di Azure, è possibile creare un ticket di supporto per abilitare questo flag di traccia a livello di sottoscrizione, ma non a livello di server.

D5: L'esecuzione con la ce legacy impedisce l'accesso alle nuove funzionalità?

Anche con LEGACY_CARDINALITY_ESTIMATION abilitato, si otterrà comunque l'accesso alle funzionalità più recenti incluse nella versione di SQL Server e nel livello di compatibilità del database associato. Ad esempio, un database con LEGACY_CARDINALITY_ESTIMATION abilitato per l'esecuzione a livello di compatibilità del database 140 in SQL Server 2017 può comunque trarre vantaggio dalla famiglia di funzionalità di elaborazione delle query adattive.

D6: Quando la ce legacy esce dal supporto?

A questo punto non è previsto di interrompere il supporto della ce legacy. Tuttavia, i miglioramenti e le correzioni future della stima della cardinalità sono incentrati sulle versioni più recenti della stima della cardinalità.

D7: Sono presenti solo alcune query regredite con la stima di cardinalità predefinita, ma la maggior parte delle prestazioni delle query è la stessa o anche migliorata. Cosa devo fare?

Un'alternativa più granulare al flag di traccia con ambito server 9481 o alla configurazione con ambito database LEGACY_CARDINALITY_ESTIMATION è l'uso del costrutto USE HINT con ambito query. Per altre informazioni, vedere l'argomento dell'hint per la query USE HINT in SQL Server 2016 e USE HINT.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION consente di impostare il modello ce di Query Optimizer sulla versione 70, indipendentemente dal livello di compatibilità del database. Vedere Livello di query: Usare l'hint per la query o l'opzione QUERYTRACEON.

In alternativa, se esiste una sola query problematica con la stima di cardinalità predefinita, è possibile forzare un piano ce legacy archiviato in Query Store o usarlo FORCE_LEGACY_CARDINALITY_ESTIMATION insieme a una guida di piano.

D8: Se le prestazioni delle query sono regredite a causa di una modifica del piano correlata a stime significative rispetto o sottovalutazioni quando si usa la stima di cardinalità predefinita, il problema verrà risolto nel prodotto?

Ce è un problema complesso e gli algoritmi si basano sui dati meno perfetti disponibili per le stime, ad esempio le statistiche per tabelle e indici. Non sono disponibili informazioni per alcuni costrutti out-of-model come funzioni con valori di tabella e modelli basati su molti presupposti (ad esempio correlazione o indipendenza dei predicati e delle colonne, distribuzione uniforme dei dati, contenimento e così via).

Data la combinazione illimitata di schemi, dati e carichi di lavoro dei clienti, è quasi impossibile scegliere modelli che funzionano per tutti i casi. Anche se alcune modifiche nella ce predefinita possono contenere bug (come qualsiasi altro software) e possono essere risolti, altri problemi sono causati da una modifica del modello.

Le modifiche apportate alle versioni ce, in particolare passando da 70 a 120, includono molte scelte diverse per i modelli usati. Ad esempio, quando si stimano i filtri, si presuppone un certo livello di correlazione tra i predicati perché, in pratica, tale correlazione esiste di frequente e il modello CE 70 sottovaluta i risultati in questi casi. Anche se tali modifiche sono state testate per molti carichi di lavoro e sono state migliorate molte query, per altre query, la stima di cardinalità legacy è stata una corrispondenza migliore e quindi con la stima di cardinalità predefinita, è possibile osservare le regressioni delle prestazioni.

Sfortunatamente, non è considerato un bug. In situazioni di questo tipo, usare una soluzione alternativa, ad esempio l'ottimizzazione della query, proprio come è necessario eseguire con la stima di cardinalità legacy se le prestazioni delle query non sono accettabili o forzare un modello ce precedente o un piano di esecuzione specifico.

D9: Esiste una risorsa per ottenere informazioni dettagliate sulle modifiche della cardinalità nella stima di cardinalità predefinita e sull'impatto sulle prestazioni delle query?

Per informazioni dettagliate, vedere Ottimizzazione dei piani di query con lo strumento di stima della cardinalità di SQL Server 2014 e leggere la sezione "What Changed in SQL Server 2014?" (Cosa è cambiato in SQL Server 2014).