Risolvere i problemi relativi alle query lente interessate dal timeout di Query Optimizer

  • Articolo

Si applica a: SQL Server

Questo articolo presenta timeout di Optimizer, come può influire sulle prestazioni delle query e come ottimizzare le prestazioni.

Che cos'è il timeout di Optimizer?

SQL Server usa query optimizer (QO) basate sui costi. Per informazioni su QO, vedere Guida all'architettura di elaborazione delle query. Query Optimizer basato sui costi seleziona un piano di esecuzione delle query con il costo più basso dopo aver compilato e valutato più piani di query. Uno degli obiettivi di SQL Server Query Optimizer consiste nel dedicare un tempo ragionevole all'ottimizzazione delle query rispetto all'esecuzione di query. L'ottimizzazione di una query deve essere molto più veloce rispetto all'esecuzione. Per raggiungere questa destinazione, QO ha una soglia predefinita di attività da considerare prima di arrestare il processo di ottimizzazione. Quando la soglia viene raggiunta prima che QO consideri tutti i piani possibili, raggiunge il limite di timeout di Optimizer. Un evento Timeout di Optimizer viene segnalato nel piano di query come TimeOut in Motivo della terminazione anticipata dell'ottimizzazione dell'istruzione. È importante comprendere che questa soglia non è basata sull'ora di clock, ma sul numero di possibilità considerate dall'ottimizzatore. Nelle versioni correnti SQL Server QO vengono considerate più di mezzo milione di attività prima che venga raggiunto un timeout.

Il timeout di Optimizer è progettato in SQL Server e in molti casi non è un fattore che influisce sulle prestazioni delle query. In alcuni casi, tuttavia, la scelta del piano di query SQL potrebbe essere influenzata negativamente dal timeout di Optimizer e potrebbero verificarsi prestazioni di query più lente. Quando si verificano questi problemi, comprendere il meccanismo di timeout di Optimizer e il modo in cui possono essere interessate query complesse può aiutare a risolvere i problemi e migliorare la velocità delle query.

Il risultato del raggiungimento della soglia di timeout di Optimizer è che SQL Server non ha considerato l'intero set di possibilità di ottimizzazione. Ovvero, potrebbe aver perso piani che potrebbero produrre tempi di esecuzione più brevi. QO si interromperà alla soglia e considererà il piano di query con costi minimi a quel punto, anche se potrebbero esserci opzioni migliori e non esplorate. Tenere presente che il piano selezionato dopo il raggiungimento di un timeout di Optimizer può produrre una durata di esecuzione ragionevole per la query. In alcuni casi, tuttavia, il piano selezionato potrebbe comportare un'esecuzione di query non ottimale.

Come rilevare un timeout di Optimizer?

Di seguito sono riportati i sintomi che indicano un timeout di Optimizer:

  • Query complessa

    È disponibile una query complessa che include molte tabelle unite in join, ad esempio otto o più tabelle unite in join.

  • Query lenta

    La query può essere eseguita lentamente o più lentamente rispetto all'esecuzione in un'altra versione o in un altro sistema SQL Server.

  • Il piano di query mostra StatementOptmEarlyAbortReason=Timeout

    • Il piano di query viene visualizzato StatementOptmEarlyAbortReason="TimeOut" nel piano di query XML.

      <?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

    • Controllare le proprietà dell'operatore di piano più a sinistra in Microsoft SQL Server Management Studio. È possibile vedere che il valore di Reason For Early Termination of Statement Optimization è TimeOut.

      Screenshot che mostra il timeout dell'utilità di ottimizzazione nel piano di query in SSMS.

Cosa causa un timeout di Optimizer?

Non esiste un modo semplice per determinare quali condizioni potrebbero causare il raggiungimento o il superamento della soglia dell'utilità di ottimizzazione. Le sezioni seguenti sono alcuni fattori che influiscono sul numero di piani esplorati da QO quando si cerca il piano migliore.

  • In quale ordine devono essere unite le tabelle?

    Ecco un esempio delle opzioni di esecuzione dei join a tre tabelle (Table1, Table2, Table3):

    • Unire Table1 con Table2 e il risultato con Table3
    • Unire Table1 con Table3 e il risultato con Table2
    • Unire Table2 con Table3 e il risultato con Table1

    Nota: Maggiore è il numero di tabelle, maggiori sono le possibilità.

  • Quale struttura di accesso dell'heap o dell'albero binario (HoBT) usare per recuperare le righe da una tabella?

    • Indice cluster
    • Indice non cluster1
    • Indice non cluster2
    • Heap della tabella

  • Quale metodo di accesso fisico usare?

    • Ricerca indice
    • Analisi dell'indice
    • Analisi della tabella

  • Quale operatore di join fisico usare?

    • Join di cicli annidati (NJ)
    • Hash join (HJ)
    • Merge join (MJ)
    • Join adattivo (a partire da SQL Server 2017 (14.x))

    Per altre informazioni, vedere Join.

  • Eseguire parti della query in parallelo o in serie?

    Per altre informazioni, vedere Elaborazione di query parallele.

Anche se i fattori seguenti ridurranno il numero di metodi di accesso considerati e quindi le possibilità considerate:

  • Predicati di query (filtri nella WHERE clausola)
  • Esistenza di vincoli
  • Combinazioni di statistiche ben progettate e aggiornate

Nota: Il fatto che QO raggiunga la soglia non significa che finirà con una query più lenta. Nella maggior parte dei casi, le prestazioni della query sono buone, ma in alcuni casi l'esecuzione delle query potrebbe essere più lenta.

Esempio di come vengono considerati i fattori

Per illustrare, si consideri un esempio di join tra tre tabelle (t1, t2e ) e t3ogni tabella ha un indice cluster e un indice non cluster.

Considerare innanzitutto i tipi di join fisici. Ci sono due join coinvolti qui. Poiché esistono tre possibilità di join fisico (NJ, HJ e MJ), la query può essere eseguita in 32 = 9 modi.

  1. NJ - NJ
  2. NJ - HJ
  3. NJ - MJ
  4. HJ - NJ
  5. HJ - HJ
  6. HJ - MJ
  7. MJ - NJ
  8. MJ - HJ
  9. MJ - MJ

Si consideri quindi l'ordine di join, calcolato usando permutazioni: P (n, r). L'ordine delle prime due tabelle non è importante, quindi possono esserci P(3,1) = 3 possibilità:

  • Aggiungere un join t1 con t2 e quindi con t3
  • Aggiungere un join t1 con t3 e quindi con t2
  • Aggiungere un join t2 con t3 e quindi con t1

Si considerino quindi gli indici cluster e non cluster che possono essere usati per il recupero dei dati. Inoltre, per ogni indice sono disponibili due metodi di accesso, la ricerca o l'analisi. Ciò significa che per ogni tabella sono disponibili 22 = 4 scelte. Sono disponibili tre tabelle, quindi possono esserci 43 = 64 scelte.

Infine, considerando tutte queste condizioni, possono esserci 9*3*64 = 1728 piani possibili.

Si supponga ora che nella query siano presenti n tabelle unite in join e che ogni tabella abbia un indice cluster e un indice non cluster. Considerare i fattori seguenti:

  • Ordini di join: P(n,n-2) = n!/2
  • Tipi di join: 3n-1
  • Tipi di indice diversi con metodi di ricerca e analisi: 4n

Moltiplicare tutti questi piani sopra e possiamo ottenere il numero di piani possibili: 2*n!*12n-1. Quando n = 4, il numero è 82.944. Quando n = 6, il numero è 358.318.080. Pertanto, con l'aumento del numero di tabelle coinvolte in una query, il numero di piani possibili aumenta geometricamente. Inoltre, se si include la possibilità di parallelismo e altri fattori, è possibile immaginare quanti piani possibili verranno presi in considerazione. Pertanto, è più probabile che una query con molti join raggiunga la soglia di timeout di Optimizer rispetto a una con meno join.

Si noti che i calcoli precedenti illustrano lo scenario peggiore. Come abbiamo sottolineato, esistono fattori che ridurranno il numero di possibilità, ad esempio predicati di filtro, statistiche e vincoli. Ad esempio, un predicato di filtro e statistiche aggiornate ridurranno il numero di metodi di accesso fisico perché potrebbe essere più efficiente usare una ricerca di indice rispetto a un'analisi. Ciò comporterà anche una selezione più ridotta di join e così via.

Perché viene visualizzato un timeout di Optimizer con una query semplice?

Niente con Query Optimizer è semplice. Esistono molti scenari possibili e il grado di complessità è così elevato che è difficile cogliere tutte le possibilità. Query Optimizer può impostare dinamicamente la soglia di timeout in base al costo del piano trovato in una determinata fase. Ad esempio, se viene trovato un piano relativamente efficiente, il limite di attività per cercare un piano migliore può essere ridotto. Pertanto, la stima della cardinalità sottovalutata può essere uno scenario per raggiungere un timeout di Optimizer in anticipo. In questo caso, l'obiettivo dell'indagine è CE. Si tratta di un caso più raro rispetto allo scenario relativo all'esecuzione di una query complessa illustrata nella sezione precedente, ma è possibile.

Risoluzioni

Un timeout di Optimizer visualizzato in un piano di query non significa necessariamente che sia la causa delle scarse prestazioni delle query. Nella maggior parte dei casi, potrebbe non essere necessario fare nulla per questa situazione. Il piano di query che SQL Server finisce con può essere ragionevole e la query in esecuzione potrebbe avere buone prestazioni. Si potrebbe non sapere mai che si è verificato un timeout di Optimizer.

Se si trova la necessità di ottimizzare e ottimizzare, provare a seguire questa procedura.

Passaggio 1: Stabilire una linea di base

Verificare se è possibile eseguire la stessa query con lo stesso set di dati in una build diversa di SQL Server, usando una configurazione CE diversa o in un sistema diverso (specifiche hardware). Un principio guida nell'ottimizzazione delle prestazioni è "non ci sono problemi di prestazioni senza una linea di base". Sarebbe quindi importante stabilire una linea di base per la stessa query.

Passaggio 2: Cercare le condizioni "nascoste" che portano al timeout di Optimizer

Esaminare la query in dettaglio per determinarne la complessità. Al momento dell'esame iniziale, potrebbe non essere ovvio che la query sia complessa e coinvolga molti join. Uno scenario comune in questo caso è che sono coinvolte viste o funzioni con valori di tabella. Ad esempio, in superficie, la query può sembrare semplice perché unisce due visualizzazioni. Tuttavia, quando si esaminano le query all'interno delle viste, è possibile che ogni vista unisca sette tabelle. Di conseguenza, quando le due viste vengono unite in join, si finisce con un join a 14 tabelle. Se la query usa gli oggetti seguenti, eseguire il drill-down in ogni oggetto per verificare l'aspetto delle query sottostanti al suo interno:

Per tutti questi scenari, la risoluzione più comune sarebbe riscrivere la query e suddividerla in più query. Per altri dettagli, vedere Passaggio 7: Perfezionare la query .

Sottoquery o tabelle derivate

La query seguente è un esempio che unisce due set separati di query (tabelle derivate) con 4-5 join in ognuno. Tuttavia, dopo l'analisi per SQL Server, verrà compilato in una singola query con otto tabelle unite.

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

Espressioni di tabella comuni (CTE)

L'uso di più espressioni di tabella comuni non è una soluzione appropriata per semplificare una query ed evitare timeout di Optimizer. Più CTE aumenteranno solo la complessità della query. Pertanto, è controproduttivo usare le CTE durante la risoluzione dei timeout di Optimizer. Le CTE sembrano interrompere una query in modo logico, ma verranno combinate in una singola query e ottimizzate come un singolo join di grandi dimensioni di tabelle.

Di seguito è riportato un esempio di CTE che verrà compilato come una singola query con molti join. Può sembrare che la query sulla my_cte sia un join semplice a due oggetti, ma in realtà ci sono altre sette tabelle unite in join in CTE.

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

Visualizzazioni

Assicurarsi di aver controllato le definizioni di visualizzazione e aver ottenuto tutte le tabelle coinvolte. Analogamente alle CTE e alle tabelle derivate, i join possono essere nascosti all'interno delle viste. Ad esempio, un join tra due viste può essere in definitiva una singola query con otto tabelle coinvolte:

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

Funzioni con valori di tabella (TVF)

Alcuni join possono essere nascosti all'interno di TFV. L'esempio seguente mostra ciò che viene visualizzato come join tra due tfv e una tabella può essere un join a nove tabelle.

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Unione

Gli operatori union combinano i risultati di più query in un singolo set di risultati. Combinano anche più query in una singola query. È quindi possibile ottenere una singola query complessa. L'esempio seguente si concluderà con un singolo piano di query che coinvolge 12 tabelle.

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

Passaggio 3: Se si dispone di una query di base che viene eseguita più velocemente, usare il relativo piano di query

Se si determina che un piano di base specifico ottenuto dal passaggio 1 è migliore per la query tramite test, usare una delle opzioni seguenti per forzare QO a selezionare tale piano:

Passaggio 4: Ridurre le scelte dei piani

Per ridurre la possibilità di un timeout di Optimizer, provare a ridurre le possibilità che QO deve prendere in considerazione nella scelta di un piano. Questo processo prevede il test della query con diverse opzioni di suggerimento. Come per la maggior parte delle decisioni con QO, le scelte non sono sempre deterministiche in superficie perché c'è una grande varietà di fattori da considerare. Pertanto, non esiste una singola strategia di successo garantita e il piano selezionato può migliorare o ridurre le prestazioni della query selezionata.

Forzare un ordine JOIN

Usare OPTION (FORCE ORDER) per eliminare le permutazioni dell'ordine:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

Ridurre le possibilità JOIN

Se altre alternative non sono state utili, provare a ridurre le combinazioni del piano di query limitando le scelte degli operatori di join fisici con hint di join. Ad esempio: OPTION (HASH JOIN, MERGE JOIN), OPTION (HASH JOIN, LOOP JOIN) o OPTION (MERGE JOIN).

Nota: È consigliabile prestare attenzione quando si usano questi hint.

In alcuni casi, la limitazione dell'utilità di ottimizzazione con un minor numero di scelte di join può causare la mancata disponibilità dell'opzione di join migliore e potrebbe effettivamente rallentare la query. Inoltre, in alcuni casi, un join specifico è richiesto da un ottimizzatore (ad esempio, l'obiettivo di riga) e la query potrebbe non generare un piano se tale join non è un'opzione. Di conseguenza, dopo aver destinato gli hint di join per una query specifica, verificare se è disponibile una combinazione che offre prestazioni migliori ed elimina il timeout di Optimizer.

Ecco due esempi di come usare tali hint:

  • Usare OPTION (HASH JOIN, LOOP JOIN) per consentire solo i join hash e loop ed evitare il merge join nella query:

    SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)

  • Applicare un join specifico tra due tabelle:

    SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

Passaggio 5: Modificare la configurazione ce

Provare a modificare la configurazione ce passando da Legacy CE a New CE. La modifica della configurazione ce può comportare la selezione di un percorso diverso da parte dell'oggetto QO quando SQL Server valuta e crea piani di query. Quindi, anche se si verifica un problema di timeout di Optimizer, è possibile che si verifichi un piano che funzioni in modo più ottimale rispetto a quello selezionato usando la configurazione CE alternativa. Per altre informazioni, vedere Come attivare il piano di query migliore (stima della cardinalità).

Passaggio 6: Abilitare le correzioni di Optimizer

Se le correzioni di Query Optimizer non sono state abilitate, è consigliabile abilitarle usando uno dei due metodi seguenti:

  • Livello server: usare il flag di traccia T4199.
  • Livello di database: usare ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ON o modificare i livelli di compatibilità del database per SQL Server 2016 e versioni successive.

Le correzioni QO possono far sì che l'utilità di ottimizzazione prenda un percorso diverso nell'esplorazione del piano. Pertanto, può scegliere un piano di query più ottimale. Per altre informazioni, vedere SQL Server modello di manutenzione del flag di traccia dell'hotfix di Query Optimizer 4199.

Passaggio 7: Perfezionare la query

Valutare la possibilità di suddividere la singola query multitabella in più query separate usando tabelle temporanee. La suddivisione della query è solo uno dei modi per semplificare l'attività per l'utilità di ottimizzazione. Ad esempio:

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

Per ottimizzare la query, provare a suddividere la singola query in due query inserendo una parte dei risultati del join in una tabella temporanea:

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...