Demistyfikacja procesu optymalizacji SQL Server

Question

Chcielibyśmy zobaczyć wszystkie warianty planu kwerend brane pod uwagę podczas optymalizacji kwerend przez optymalizator SQL Server. SQL Server oferuje dość szczegółowy wgląd za pomocą querytraceonopcji. Na przykład QUERYTRACEON 3604, QUERYTRACEON 8615pozwala nam wydrukować strukturę MEMO i QUERYTRACEON 3604, QUERYTRACEON 8619wydrukować listę reguł transformacji zastosowanych w procesie optymalizacji. To świetnie, jednak mamy kilka problemów z wyjściami śledzenia:

1. Wydaje się, że struktura MEMO zawiera tylko ostateczne warianty planu zapytania lub warianty, które zostały później przepisane na ostateczny. Czy istnieje sposób, aby znaleźć „nieudane / niepomyślne” plany zapytań?
2. Operatory w MEMO nie zawierają odniesienia do części SQL. Na przykład operator LogOp_Get nie zawiera odwołania do określonej tabeli.
3. Reguły transformacji nie zawierają precyzyjnego odniesienia do operatorów MEMO, dlatego nie możemy być pewni, które operatory zostały przekształcone przez regułę transformacji.

Pokażę to na bardziej rozbudowanym przykładzie. Mam dwa sztuczne stoły Ai 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;

W tej chwili wykonuję jedno proste zapytanie

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)
    

Otrzymuję dość złożone wyjście, które opisuje strukturę MEMO (możesz spróbować samemu) mającą 15 grup. Oto zdjęcie, które wizualizuje strukturę MEMO za pomocą drzewa.

Z drzewa można zauważyć, że zanim optymalizator znalazł ostateczny plan zapytania, zastosowano pewne reguły. Na przykład join commute( JoinCommute), join to hash join( JNtoHS) lub Enforce sort( EnforceSort). Jak wspomniano, możliwe jest wydrukowanie całego zestawu reguł przepisywania zastosowanych przez optymalizator za pomocą QUERYTRACEON 3604, QUERYTRACEON 8619opcji. Problemy:

1. Na liście 8619 możemy znaleźć regułę przepisywania JNtoSM( Join to sort merge), jednak operator sortowania scalającego nie znajduje się w strukturze MEMO. Rozumiem, że sortowanie-scalanie było prawdopodobnie droższe, ale dlaczego nie ma go w MEMO?
2. Skąd wiadomo, czy LogOp_Getoperator w MEMO odwołuje się do tabeli A, czy do tabeli B?
3. Jeśli widzę regułę GetToIdxScan - Get -> IdxScanna liście 8619, jak odwzorować ją na operatorów MEMO?

Istnieje ograniczona liczba zasobów na ten temat. Przeczytałem wiele postów na blogu Paula White'a o zasadach transformacji i MEMO, jednak powyższe pytania pozostają bez odpowiedzi. Dzięki za wszelką pomoc.

Answer 1

Postaram się odpowiedzieć na Twoje pytania:

1. Wydaje się, że struktura MEMO zawiera tylko ostateczne warianty planu kwerendy lub warianty, które zostały później przepisane na ostateczny. Czy istnieje sposób, aby znaleźć „nieudane / niepomyślne” plany zapytań?

Nie, niestety nie da się tego zrobić. @Ronaldo wkleił ładny link w komentarzu. Moja sugestia jest taka, aby użyćInclude Live Query Statistics

i spróbuj dowiedzieć się, czy widzisz inny plan zapytań. Użyj top 10, top 1000lub, *a zobaczysz, że zostaną zaproponowane różne plany zapytań. Możesz również użyć query hinti wymusić inny wzorzec planu kwerend. Zasadniczo „zrób swój własny odrzucony plan zapytań”

2. Operatory w MEMO nie zawierają odniesienia do części SQL. Na przykład operator LogOp_Get nie zawiera odwołania do określonej tabeli.

Użyj QUERYTRACEON 8605, widzę odniesienie do tabeli:

3. Reguły transformacji nie zawierają precyzyjnego odniesienia do operatorów MEMO, dlatego nie możemy być pewni, które operatory zostały przekształcone przez regułę transformacji

Nie widzę żadnego GetToIdxScan - Get -> IdxScanw podanym przez Ciebie zapytaniu. Proponuję użyć Użyj QUERYTRACEON 8605lub QUERYTRACEON 8606powinno tam być odniesienie.

EDYTOWAĆ:

Zatem „… czy można zobaczyć więcej informacji o planach kandydatów w SQL Server”.

Odpowiedź brzmi: nie , ponieważ nie ma innego planu zapytań kandydata. W rzeczywistości powszechnym błędem jest przekonanie, że SQL Server zwraca najlepszy plan zapytań. SQL Server po prostu nie może obliczyć dla Ciebie wszystkich możliwych rozwiązań: zajęłoby to ... nie wiem ... minut ...? godziny...? Obliczenie każdego rozwiązania jest niewykonalne.

Ale jeśli chcesz zbadać, dlaczego Twój plan zapytań wybrał ten wzorzec, którego możesz użyć:

• SET SHOWPLAN_ALL ON : a SQL Server zwróci drzewo logiki każdego obliczenia planu kwerend

• DBCC SHOW_STATISTICS('A', 'PK_A'): który pokaże statystyki dotyczące tabeli docelowej i ograniczenia. Stworzyłem klucz, aby pokazać wyniki, naturalnie zobaczysz więcej informacji, jeśli twoja tabela jest częściej sprawdzana

• USE HINT('force_legacy_cardinality_estimation') : pozwoli ci użyć poprzedniego oszacowania liczności, dzięki czemu możesz sprawdzić, czy plan zapytania mógł być szybszy przy starszym szacowaniu liczności.