Entmystifizierung des SQL Server-Optimierungsprozesses

Nov 16 2020

Wir möchten, dass alle Varianten des Abfrageplans bei einer Abfrageoptimierung durch einen SQL Server-Optimierer berücksichtigt werden. SQL Server bietet mithilfe von querytraceonOptionen recht detaillierte Einblicke . Zum Beispiel QUERYTRACEON 3604, QUERYTRACEON 8615ermöglicht es uns , MEMO Struktur auszudrucken und QUERYTRACEON 3604, QUERYTRACEON 8619eine Liste von Transformationsregeln während des Optimierungsprozesses angewandt ausdrucken. Das ist großartig, aber wir haben einige Probleme mit Trace-Ausgaben:

Es scheint, dass die MEMO-Struktur nur endgültige Varianten des Abfrageplans oder Varianten enthält, die später in die endgültige umgeschrieben wurden. Gibt es eine Möglichkeit, "erfolglose / nicht vielversprechende" Abfragepläne zu finden?
Die Operatoren in MEMO enthalten keinen Verweis auf SQL-Teile. Beispielsweise enthält der Operator LogOp_Get keinen Verweis auf eine bestimmte Tabelle.
Die Transformationsregeln enthalten keinen genauen Verweis auf MEMO-Operatoren. Daher können wir nicht sicher sein, welche Operatoren durch die Transformationsregel transformiert wurden.

Lassen Sie es mich an einem ausführlicheren Beispiel zeigen. Lassen Sie mich zwei künstliche Tische haben Aund 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;

Im Moment führe ich eine einfache Abfrage aus

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)

Ich erhalte eine recht komplexe Ausgabe, die die MEMO-Struktur (Sie können es selbst versuchen) mit 15 Gruppen beschreibt. Hier ist das Bild, das die MEMO-Struktur anhand eines Baums visualisiert.

Aus dem Baum kann man ersehen, dass bestimmte Regeln angewendet wurden, bevor der Optimierer den endgültigen Abfrageplan gefunden hat. Zum Beispiel join commute( JoinCommute), join to hash join( JNtoHS) oder Enforce sort( EnforceSort). Wie bereits erwähnt, ist es möglich, den gesamten Satz der vom Optimierer angewendeten Umschreibregeln mithilfe von QUERYTRACEON 3604, QUERYTRACEON 8619Optionen auszudrucken . Die Probleme:

Möglicherweise finden wir die Umschreibregel JNtoSM( Join to sort merge) in der 8619-Liste, der Sortier-Merge-Operator befindet sich jedoch nicht in der MEMO-Struktur. Ich verstehe, dass das Sortieren wahrscheinlich teurer war, aber warum ist es nicht in MEMO?
Woher wissen, ob der LogOp_GetOperator in MEMO auf Tabelle A oder Tabelle B verweist?
Wenn ich die Regel GetToIdxScan - Get -> IdxScanin der 8619-Liste sehe , wie ordne ich sie den MEMO-Operatoren zu?

Es gibt eine begrenzte Anzahl von Ressourcen dazu. Ich habe viele der Paul White-Blogposts über Transformationsregeln und MEMO gelesen, die oben genannten Fragen bleiben jedoch unbeantwortet. Vielen Dank für jede Hilfe.

Antworten

FrancescoMantovani Nov 26 2020 at 03:38

Ich werde versuchen, auf Ihre Fragen zu antworten:

1. Es scheint, dass die MEMO-Struktur nur endgültige Varianten des Abfrageplans oder Varianten enthält, die später in die endgültige umgeschrieben wurden. Gibt es eine Möglichkeit, "erfolglose / nicht vielversprechende" Abfragepläne zu finden?

Nein, leider gibt es keine Möglichkeit dazu. @ Ronaldo hat einen schönen Link in den Kommentar eingefügt. Mein Vorschlag ist, die zu verwendenInclude Live Query Statistics

und versuchen Sie herauszufinden, ob Sie einen anderen Abfrageplan sehen. Verwenden Sie top 10, top 1000oder, *und Sie werden sehen, dass verschiedene Abfragepläne vorgeschlagen werden. Sie können query hintIhren Abfrageplan auch verwenden und auf ein anderes Muster zwingen. Grundsätzlich "Machen Sie Ihren eigenen verworfenen Abfrageplan"

2. Die Operatoren in MEMO enthalten keinen Verweis auf SQL-Teile. Beispielsweise enthält der Operator LogOp_Get keinen Verweis auf eine bestimmte Tabelle.

Verwenden Sie QUERYTRACEON 8605, ich kann einen Verweis auf die Tabelle sehen:

3. Die Transformationsregeln enthalten keinen genauen Verweis auf MEMO-Operatoren. Daher können wir nicht sicher sein, welche Operatoren durch die Transformationsregel transformiert wurden

Ich sehe keine GetToIdxScan - Get -> IdxScanin der von Ihnen angegebenen Abfrage. Mein Vorschlag ist, Use zu verwenden QUERYTRACEON 8605, oder QUERYTRACEON 8606es sollte dort eine Referenz geben.

BEARBEITEN:

So „... ist es möglich , mehr Informationen über die Kandidaten - Pläne in SQL Server zu sehen.“

Die Antwort lautet " Nein" , da kein anderer Kandidatenabfrageplan vorhanden ist. In der Tat ist ein häufiges Missverständnis, dass SQL Server Ihnen den besten Abfrageplan zurückgibt . SQL Server kann einfach nicht alle möglichen Lösungen für Sie berechnen: Das würde ... ich weiß nicht ... Minuten ... dauern? Std...? Die Berechnung jeder einzelnen Lösung ist nicht möglich.