Demistificazione del processo di ottimizzazione di SQL Server

Question

Vorremmo vedere tutte le varianti del piano di query considerate durante un'ottimizzazione di query da un ottimizzatore di SQL Server. SQL Server offre una visione abbastanza dettagliata utilizzando le querytraceonopzioni. Ad esempio QUERYTRACEON 3604, QUERYTRACEON 8615ci permette di stampare la struttura MEMO e QUERYTRACEON 3604, QUERYTRACEON 8619stampare un elenco di regole di trasformazione applicate durante il processo di ottimizzazione. È fantastico, tuttavia, abbiamo diversi problemi con gli output di traccia:

1. Sembra che la struttura MEMO contenga solo varianti finali del piano di query o varianti che sono state successivamente riscritte in quello finale. C'è un modo per trovare piani di query "infruttuosi / poco promettenti"?
2. Gli operatori in MEMO non contengono un riferimento alle parti SQL. Ad esempio, l'operatore LogOp_Get non contiene un riferimento a una tabella specifica.
3. Le regole di trasformazione non contengono un riferimento preciso agli operatori MEMO, pertanto, non possiamo essere sicuri quali operatori siano stati trasformati dalla regola di trasformazione.

Lascia che lo mostri con un esempio più elaborato. Fammi avere due tabelle artificiali Ae B:

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id  
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id, 
    CAST(id % 9173 AS int) fkb, 
    CAST(id % 911 AS int) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;


WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id  
        FROM x ones, x tens, x hundreds, x thousands       
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search, 
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

In questo momento, eseguo una semplice query

SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE, 
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)
    

Ottengo un output piuttosto complesso che descrive la struttura MEMO (puoi provare da solo) con 15 gruppi. Ecco l'immagine, che visualizza la struttura MEMO utilizzando un albero.

Dall'albero si può osservare che c'erano alcune regole applicate prima che l'ottimizzatore trovasse il piano di query finale. Ad esempio join commute( JoinCommute), join to hash join( JNtoHS) o Enforce sort( EnforceSort). Come accennato è possibile stampare l'intero set di regole di riscrittura applicate dall'ottimizzatore utilizzando le QUERYTRACEON 3604, QUERYTRACEON 8619opzioni. I problemi:

1. Possiamo trovare la regola di riscrittura JNtoSM( Join to sort merge) nell'elenco 8619, tuttavia, l'operatore di unione di ordinamento non è nella struttura MEMO. Capisco che il sort-merge fosse probabilmente più costoso, ma perché non è in MEMO?
2. Come sapere se l' LogOp_Getoperatore in MEMO fa riferimento alla tabella A o alla tabella B?
3. Se vedo la regola GetToIdxScan - Get -> IdxScannell'elenco 8619, come associarla agli operatori MEMO?

C'è un numero limitato di risorse su questo. Ho letto molti dei post del blog di Paul White sulle regole di trasformazione e MEMO, tuttavia, le domande di cui sopra rimangono senza risposta. Grazie per qualsiasi aiuto.

Answer 1

Proverò a rispondere alle tue domande:

1. Sembra che la struttura MEMO contenga solo varianti finali del piano di query o varianti che sono state successivamente riscritte in quello finale. C'è un modo per trovare piani di query "infruttuosi / poco promettenti"?

No, purtroppo non c'è modo di farlo. @Ronaldo ha incollato un bel link nel commento. Il mio suggerimento è di utilizzare il fileInclude Live Query Statistics

e prova a scoprire se vedi un piano di query diverso. Utilizzare top 10, top 1000, o *e vedrete che saranno proposti diversi piani di query. È inoltre possibile utilizzare query hinte forzare il piano di query su un modello diverso. Fondamentalmente "fai il tuo piano di query scartato"

2. Gli operatori in MEMO non contengono un riferimento alle parti SQL. Ad esempio, l'operatore LogOp_Get non contiene un riferimento a una tabella specifica.

Usa QUERYTRACEON 8605, vedo un riferimento alla tabella:

3. Le regole di trasformazione non contengono un riferimento preciso agli operatori MEMO, quindi non possiamo essere sicuri quali operatori siano stati trasformati dalla regola di trasformazione

Non vedo nessuno GetToIdxScan - Get -> IdxScannella query che hai fornito. Il mio suggerimento è di usare Use QUERYTRACEON 8605, oppure QUERYTRACEON 8606dovrebbe esserci un riferimento lì.

MODIFICARE:

Quindi "... è possibile visualizzare ulteriori informazioni sui piani candidati in SQL Server".

La risposta è no , perché non ci sono altri piani di query candidati. In effetti, è un'idea sbagliata comune che SQL Server restituisca il miglior piano di query. SQL Server semplicemente non può calcolare per te tutte le possibili soluzioni: ci vorrebbero ... non so ... minuti ...? ore...? Calcolare ogni singola soluzione è irrealizzabile.

Ma se vuoi indagare sul motivo per cui il tuo piano di query sceglie quel modello, puoi utilizzare:

• SET SHOWPLAN_ALL ON : e SQL Server ti restituirà un albero della logica di ogni singolo calcolo del tuo piano di query

• DBCC SHOW_STATISTICS('A', 'PK_A'): che ti mostrerà le statistiche su una tabella di destinazione e un vincolo. Ho creato una chiave per mostrarti i risultati, naturalmente vedrai più informazioni se la tua tabella viene interrogata più spesso

• USE HINT('force_legacy_cardinality_estimation') : ti consentirà di utilizzare la precedente stima della cardinalità, in modo da poter verificare se il piano di query potrebbe essere stato più veloce con la stima della cardinalità legacy.