Dlaczego zmiana deklarowanej kolejności kolumn łączenia wprowadza sortowanie?

Mam dwie tabele z identycznie nazwanymi, wpisanymi i indeksowanymi kolumnami kluczy. Jeden z nich ma unikalny indeks klastrowy, drugi ma nieunikalny .

Konfiguracja testowa

Skrypt instalacyjny, w tym niektóre realistyczne statystyki:

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

Repro

Kiedy dołączę do tych dwóch tabel na ich kluczach klastrowych, oczekuję połączenia jeden-do-wielu MERGE, tak:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

Oto plan zapytań, który chcę:

(Nieważne ostrzeżenia, mają one związek z fałszywymi statystykami.)

Jeśli jednak zmienię kolejność kolumn w złączeniu, to tak:

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... to się stało:

Wydaje się, że operator sortowania porządkuje strumienie zgodnie z zadeklarowaną kolejnością łączenia, tzn. c, a, b, d, e, f, g, hDodaje operację blokującą do mojego planu zapytań.

Rzeczy, na które patrzyłem

• Próbowałem zmienić kolumny na NOT NULL, te same wyniki.
• Oryginalna tabela została utworzona za pomocą ANSI_PADDING OFF, ale jej utworzenie ANSI_PADDING ONnie wpływa na ten plan.
• Próbowałem INNER JOINzamiast LEFT JOIN, bez zmian.
• Odkryłem to na 2014 SP2 Enterprise, stworzyłem repro na 2017 Developer (obecna CU).
• Usunięcie klauzuli WHERE w wiodącej kolumnie indeksu generuje dobry plan, ale w pewnym stopniu wpływa na wyniki .. :)

Wreszcie dochodzimy do pytania

• Czy to celowe?
• Czy mogę wyeliminować sortowanie bez zmiany zapytania (który jest kodem dostawcy, więc wolałbym nie ...). Mogę zmienić tabelę i indeksy.

Czy to celowe?

Tak, zgodnie z projektem. Najlepsze publiczne źródło tego stwierdzenia zostało niestety utracone, gdy Microsoft wycofał witrynę z opiniami Connect, usuwając wiele przydatnych komentarzy od programistów zespołu SQL Server.

W każdym razie, obecny projekt optymalizator nie aktywnie dążyć do uniknięcia niepotrzebnych rodzaju per se . Najczęściej spotyka się to z funkcjami okienkowania i tym podobnymi, ale można to również zaobserwować w przypadku innych operatorów, którzy są wrażliwi na porządkowanie, a zwłaszcza na zachowane porządkowanie między operatorami.

Niemniej jednak optymalizator jest całkiem dobry (w wielu przypadkach) w unikaniu niepotrzebnego sortowania, ale taki wynik zwykle występuje z powodów innych niż agresywne próbowanie różnych kombinacji porządkowania. W tym sensie chodzi nie tyle o „przestrzeń wyszukiwania”, ile o złożone interakcje między funkcjami optymalizatora ortogonalnego, które, jak wykazano, podnoszą ogólną jakość planu po akceptowalnych kosztach.

Na przykład często można uniknąć sortowania, po prostu dopasowując wymaganie dotyczące uporządkowania (np. Najwyższego poziomu ORDER BY) do istniejącego indeksu. Trywialnie w twoim przypadku może to oznaczać dodanie, ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h;ale jest to nadmierne uproszczenie (i nie do przyjęcia, ponieważ nie chcesz zmieniać zapytania).

Mówiąc bardziej ogólnie, każda grupa notatek może być powiązana z wymaganymi lub pożądanymi właściwościami, które mogą obejmować porządkowanie danych wejściowych. Gdy nie ma oczywistego powodu do egzekwowania określonego zamówienia (np. W celu spełnienia ORDER BYlub zapewnienia poprawnych wyników przez fizycznego operatora wrażliwego na zamówienie), występuje element „szczęścia”. Napisałem więcej o szczegółach tego, ponieważ dotyczy to łączenia złączenia (w trybie łączenia lub łączenia) w unikaniu sortowania z łączeniem łączenia łączenia . Wiele z nich wykracza poza obsługiwaną powierzchnię produktu, dlatego należy traktować go jako informacyjny i podlegać zmianom.

W twoim konkretnym przypadku tak, możesz dostosować indeksowanie, jak sugeruje jadarnel27, aby uniknąć sortowania; chociaż nie ma powodu, by preferować dołączenie do scalania. Możesz także zasugerować wybór między łączeniem fizycznym mieszania lub pętli za OPTION(HASH JOIN, LOOP JOIN)pomocą Przewodnika po planach bez zmiany zapytania, w zależności od Twojej wiedzy o danych, i kompromis między najlepszą, najgorszą i średnią wydajnością przypadków.

Na koniec, jako ciekawostkę, zwróć uwagę, że można tego uniknąć za pomocą prostego ORDER BY l.b, kosztem potencjalnie mniej wydajnego łączenia wielu z wieloma bosobno, ze złożoną resztą. Wspominam o tym głównie jako ilustrację interakcji między funkcjami optymalizatora, o których wspomniałem wcześniej, oraz sposobu, w jaki mogą się propagować wymagania najwyższego poziomu.

Czy mogę wyeliminować sortowanie bez zmiany zapytania (który jest kodem dostawcy, więc wolałbym nie ...). Mogę zmienić tabelę i indeksy.

Jeśli możesz zmienić indeksy, to zmiana kolejności indeksów w #rightcelu dopasowania do kolejności filtrów w złączeniu usuwa sortowanie (dla mnie):

CREATE CLUSTERED INDEX IX ON #right (c, a, b, d, e, f, g, h)

Zaskakujące (przynajmniej dla mnie), nie powoduje to, że żadne zapytanie zakończy się pewnego rodzaju.

Czy to celowe?

Patrząc na wynik niektórych dziwnych flag śledzenia , istnieje interesująca różnica w ostatecznej strukturze notatki:

Jak widać w „Grupie głównej” u góry, oba zapytania mają opcję użycia Scalanie jako głównej operacji fizycznej w celu wykonania tego zapytania.

Dobre zapytanie

Łączenie bez sortowania jest sterowane przez opcję 29 grupy 1 i grupę 31 opcji 1 (z których każda jest skanowaniem zakresu zaangażowanych indeksów). Jest filtrowany według grupy 27 (nie pokazano), która jest serią logicznych operacji porównania, które filtrują złączenie.

Złe zapytanie

Ta z sortowaniem jest sterowana przez (nowe) opcje 3, które ma każda z tych dwóch grup (29 i 31). Opcja 3 wykonuje sortowanie fizyczne na podstawie wyników wcześniej wspomnianych skanów zakresu (opcja 1 każdej z tych grup).

Dlaczego?

Z jakiegoś powodu opcja użycia 29.1 i 31.1 bezpośrednio jako źródła dla łączenia scalającego nie jest nawet dostępna dla optymalizatora w drugim zapytaniu. W przeciwnym razie myślę, że byłby wymieniony w grupie głównej wśród innych opcji. Gdyby był w ogóle dostępny, zdecydowanie wybrałby te spośród znacznie droższych operacji sortowania.

Mogę jedynie stwierdzić, że albo:

• jest to błąd (lub bardziej prawdopodobne ograniczenie) w algorytmie wyszukiwania optymalizatora
  • zmiana indeksów i złączeń tak, aby miały tylko 5 kluczy, usuwa sortowanie dla drugiego zapytania (wszystkie klucze 6, 7 i 8 mają sortowanie).
  • Oznacza to, że przestrzeń wyszukiwania z 8 kluczami jest tak duża, że ​​optymalizator po prostu nie ma czasu, aby zidentyfikować nie sortowane rozwiązanie jako realną opcję, zanim zakończy się przedwcześnie z powodu „wystarczająco dobrego planu znalezionego”
  • wydaje mi się trochę niedokładne, że kolejność warunków łączenia wpływa tak bardzo na proces wyszukiwania optymalizatora, ale tak naprawdę to trochę przesadza
• sortowanie jest wymagane w celu zapewnienia poprawności wyników
  • ten wydaje się mało prawdopodobny, ponieważ zapytanie można uruchomić bez sortowania, gdy jest mniej kluczy lub klucze są określone w innej kolejności

Mam nadzieję, że ktoś może przyjść i wyjaśnić, dlaczego takie sortowanie jest wymagane, ale pomyślałem, że różnica w budynku Memo była wystarczająco interesująca, aby opublikować odpowiedź.