Skip to content

Perché questo CTE ricorsivo con un parametro non usa un indice mentre lo fa con un letterale?

Questa recensione è stata analizzata dai nostri esperti garantendo così la veridicità di questo articolo.

Soluzione:

La risposta di Randi Vertongen spiega correttamente come ottenere il piano desiderato con la versione parametrizzata della query. Questa risposta la integra affrontando il titolo della domanda, nel caso in cui siate interessati ai dettagli.

SQL Server riscrive le espressioni di tabella comune (CTE) ricorsive in coda come iterazione. Tutto ciò che è stato fatto dal Indice pigro è l'implementazione in tempo reale della traduzione iterativa. Ho scritto un resoconto dettagliato di come funziona questa sezione di un piano di esecuzione in risposta all'uso di EXCEPT in un'espressione ricorsiva di tabella comune.

Si desidera specificare un predicato (filtro) all'esterno della CTE e fare in modo che l'ottimizzatore di query spinga questo filtro verso il basso all'interno della ricorsione (riscritta come iterazione) e lo applichi al membro dell'ancora. Ciò significa che la ricorsione inizia solo con i record che corrispondono a ParentId = @Id.

Si tratta di un'aspettativa ragionevole, sia che si tratti di un valore letterale, di una variabile o di un parametroed; Tuttavia, l'ottimizzatore può fare solo le cose per cui sono state scritte delle regole. Le regole specificano come viene modificato l'albero logico della query per ottenere una particolare trasformazione. Esse includono la logica per assicurarsi che il risultato finale sia sicuro, cioè che restituisca esattamente gli stessi dati della specifica query originale in tutti i casi possibili.

La regola responsabile dell'inserimento dei predicati in una CTE ricorsiva è chiamata SelOnIterator - una selezione relazionale (= predicato) su un iteratore che implementa la ricorsione. Più precisamente, questa regola può copiare una selezione fino all'iteratore ancora dell'iterazione ricorsiva:

Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

Questa regola può essere disabilitata con il suggerimento non documentato OPTION(QUERYRULEOFF SelOnIterator). Quando viene utilizzato, l'ottimizzatore non può più spingere i predicati con un valore letterale fino all'ancora di un CTE ricorsivo. Questo non è il caso, ma illustra il punto.

In origine, questa regola era limitata ai predicati con valori letterali. Si poteva far funzionare anche con variabili o parametri, specificando OPTION (RECOMPILE), poiché questo suggerimento abilita l'opzione Ottimizzazione dell'inserimento di parametri, in base alla quale il valore letterale della variabile (o del parametro) viene utilizzato durante la compilazione del piano. Il piano non viene memorizzato nella cache, quindi lo svantaggio è una nuova compilazione a ogni esecuzione.

A un certo punto, la variabile SelOnIterator è stata migliorata per funzionare anche con variabili e parametri. Per evitare modifiche inattese al piano, questa regola è stata protetta con il flag di traccia 4199, il livello di compatibilità con il database e il livello di compatibilità con l'hotfix dell'ottimizzatore di query. Si tratta di uno schema abbastanza normale per i miglioramenti degli ottimizzatori, che non sono sempre documentati. I miglioramenti sono normalmente positivi per la maggior parte delle persone, ma c'è sempre la possibilità che qualsiasi modifica introduca una regressione per qualcuno.

Voglio inserire la CTE in una vista riutilizzabile

Si potrebbe usare una funzione inline con valore di tabella invece di una vista. Fornire il valore che si vuole spingere verso il basso come parametro e inserire il predicato nel membro di ancoraggio ricorsivo.

Se si preferisce, si può anche abilitare il flag di traccia 4199 a livello globale. Ci sono molte modifiche all'ottimizzatore coperte da questo flag, quindi è necessario testare attentamente il carico di lavoro con questo flag abilitato ed essere pronti a gestire le regressioni.

Anche se al momento non ho il titolo dell'hotfix vero e proprio, il piano di query migliore verrà utilizzato quando si abilitano gli hotfix dell'ottimizzatore di query sulla versione in uso (SQL Server 2012).

Altri metodi sono:

  • Utilizzo di OPTION(RECOMPILE) in modo che il filtraggio avvenga prima, sul
    valore letterale.
  • Su SQL Server 2016 o superiore le hotfix precedenti a questa versione vengono
    automaticamente e la query dovrebbe essere eseguita in maniera equivalente al
    piano di esecuzione migliore.

Correzioni rapide dell'ottimizzatore di query

È possibile attivare queste correzioni con

  • Traceflag 4199 prima di SQL Server 2016
  • ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON; a partire da SQL Server 2016. (non necessario per la correzione)

Il filtraggio su @id viene applicato prima sia ai membri ricorsivi che a quelli di ancoraggio nel piano di esecuzione con la correzione attivata.

Il traceflag può essere aggiunto a livello di query:

OPTION(QUERYTRACEON 4199)

Quando si esegue la query su SQL Server 2012 SP4 GDR o SQL Server 2014 SP3 con il traceflag 4199, viene scelto il piano di query migliore:

ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
    OPTION( QUERYTRACEON 4199 );

END;
GO
EXEC #c 24;

Piano di query su SQL Server 2014 SP3 con traceflag 4199

Piano di query su SQL Server 2012 SP4 GDR con traceflag 4199

Piano delle query su SQL Server 2012 SP4 GDR senza traceflag 4199

Il consenso principale è quello di abilitare il traceflag 4199 a livello globale quando si utilizza una versione precedente a SQL Server 2016. In seguito si può discutere se abilitarlo o meno. Qui è disponibile un Q/A su questo argomento.


Livello di compatibilità 130 o 140

Quando si testa la query parametrizzata su un database con compatibility_level = 130 o 140, il filtraggio avviene prima:

enter image description here

A causa del fatto che le "vecchie" correzioni del traceflag 4199 sono abilitate su SQL Server 2016 e successivi.


OPZIONE(RICOMPILA)

Anche se viene utilizzata una procedura, SQL Server sarà in grado di filtrare sul valore letterale quando aggiunge OPTION(RECOMPILE);.

ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
OPTION(
RECOMPILE )

END;
GO

enter image description here

Piano di query su SQL Server 2012 SP4 GDR con OPTION(RECOMPILE)

Se il nostro post ti è stato utile, sarebbe molto utile se lo condividessi con altri appassionati di programmazione e ci aiutassi a diffondere queste informazioni.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.