Dlaczego ta szacunkowa liczność przyłączeń jest tak duża?

Doświadczam, jak sądzę, niemożliwie wysokiej wartości szacunkowej liczności dla następującego zapytania:

SELECT dm.PRIMARY_ID
FROM
(
    SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
    LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
    LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst ON dm.PRIMARY_ID = lst.JOIN_ID;

Szacowany plan jest tutaj . Pracuję nad kopią statystyk tabel, więc nie mogę uwzględnić rzeczywistego planu. Nie sądzę jednak, aby miało to znaczenie w przypadku tego problemu.

SQL Server szacuje, że 481577 wierszy zostanie zwróconych z tabeli pochodnej „dm”. Następnie szacuje, że 4528030000 wierszy zostanie zwróconych po wykonaniu łączenia do X_LAST_TABLE, ale JOIN_ID jest kluczem podstawowym X_LAST_TIME. Oczekiwałbym, że łączna ocena liczności zostanie zawarta między 0 a 481577 wierszy. Zamiast tego szacunkowa liczba wierszy wydaje się wynosić 10% liczby wierszy, które uzyskałbym, łącząc krzyżowo stoły zewnętrzne i wewnętrzne. Matematyka w tym przypadku działa z zaokrągleniem: 481577 * 94025 * 0,1 = 45280277425, który jest zaokrąglony do 4528030000.

Szukam przede wszystkim głównej przyczyny tego zachowania. Interesują mnie również proste obejścia, ale proszę nie sugerować zmiany modelu danych lub używania tabel tymczasowych. To zapytanie jest uproszczeniem logiki w widoku. Wiem, że robienie COALESCE na kilku kolumnach i łączenie się z nimi nie jest dobrą praktyką. Częścią tego pytania jest ustalenie, czy muszę zalecić przeprojektowanie modelu danych.

Testuję na Microsoft SQL Server 2014 z włączonym starszym estymatorem liczności. TF 4199 i inne są włączone. Mogę podać pełną listę flag śledzenia, jeśli okaże się to istotne.

Oto najbardziej odpowiednia definicja tabeli:

CREATE TABLE X_LAST_TABLE (
JOIN_ID NUMERIC(18, 0) NOT NULL
    CONSTRAINT PK_X_LAST_TABLE PRIMARY KEY CLUSTERED (JOIN_ID ASC)
);

ja również Wypisałem wszystkie skrypty tworzenia tabel wraz z ich statystykami, jeśli ktoś chce odtworzyć problem na jednym ze swoich serwerów.

Aby dodać kilka moich obserwacji, użycie TF 2312 naprawia oszacowanie, ale nie jest to dla mnie opcja. TF 2301 nie naprawia oszacowania. Usunięcie jednej z tabel naprawia oszacowanie. Dziwnie, zmiana kolejności łączenia X_DETAIL_LINK również naprawia oszacowanie. Zmieniając kolejność łączenia mam na myśli przepisywanie zapytania i nie wymuszanie kolejności łączenia z podpowiedziami. Oto szacunkowy plan zapytań podczas zmiany kolejności połączeń.

Wiem, że robienie COALESCEkilku kolumn i łączenie się z nimi nie jest dobrą praktyką.

Generowanie dobrych oszacowań liczności i rozkładu jest wystarczająco trudne, gdy schemat wynosi 3NF + (z kluczami i ograniczeniami), a zapytanie jest relacyjne, a przede wszystkim SPJG (wybór-projekcja-przyłączenie do grupy według). Model CE jest zbudowany na tych zasadach. Im bardziej niezwykłe lub nierelacyjne funkcje są w zapytaniu, tym bardziej zbliża się do granic tego, co może obsłużyć ramy liczności i selektywności. Idź za daleko, a CE się podda i zgadnie .

Większość przykładów MCVE to proste SPJ (bez G), aczkolwiek z przeważnie zewnętrznymi ekwiwalentami (modelowanymi jako łączenie wewnętrzne plus anty-półjoin), a nie prostszym wewnętrznym ekwijonem (lub półjoinem). Wszystkie relacje mają klucze, chociaż nie ma kluczy obcych ani innych ograniczeń. Wszystkie połączenia oprócz jednego są łączone jeden na wielu, co jest dobre.

Wyjątkiem jest zewnętrzne połączenie wiele do wielu między X_DETAIL_1i X_DETAIL_LINK. Jedyną funkcją tego łączenia w MCVE jest potencjalne duplikowanie wierszy X_DETAIL_1. To jest niezwykłe rzecz.

Proste predykaty równości (selekcje) i operatory skalarne również są lepsze. Na przykład atrybut porównywanie równych atrybut / stała zwykle działa dobrze w modelu. Stosunkowo „łatwo” jest modyfikować histogramy i statystyki częstotliwości, aby odzwierciedlić zastosowanie takich predykatów.

COALESCEjest wbudowany CASE, który z kolei jest implementowany wewnętrznie jako IIF(i było to prawdą na długo przed IIFpojawieniem się w języku Transact-SQL). Modele CE IIFjakoUNION z dwoma wzajemnie wykluczającymi się dziećmi, z których każde składa się z projektu dotyczącego wyboru relacji wejściowej. Każdy z wymienionych składników ma obsługę modelu, więc ich połączenie jest stosunkowo proste. Mimo to, im więcej jednowarstwowych abstrakcji, tym mniej dokładny jest wynik końcowy - powód, dla którego większe plany wykonania są mniej stabilne i wiarygodne.

ISNULLz drugiej strony jest nieodłączną częścią silnika. Nie jest zbudowany przy użyciu bardziej podstawowych składników. Na przykład zastosowanie efektu ISNULLhistogramu jest tak proste, jak zastąpienie kroku NULLwartościami (i kompaktowanie w razie potrzeby). Jest to nadal stosunkowo nieprzejrzyste, jak idą operatory skalarne, i najlepiej, jeśli to możliwe, unikać. Niemniej jednak ogólnie rzecz biorąc jest bardziej przyjazny dla optymalizatora (mniej nieprzyjazny dla optymalizatora) niż CASEalternatywny oparty na nim.

CE (70 i 120+) jest bardzo złożony, nawet jak na standardy SQL Server. Nie chodzi o zastosowanie prostej logiki (z tajną formułą) do każdego operatora. CE wie o kluczach i zależnościach funkcjonalnych; umie oszacować za pomocą częstotliwości, statystyk wielowymiarowych i histogramów; i istnieje absolutna masa specjalnych przypadków, udoskonaleń, kontroli i sald oraz konstrukcji wsporczych. Często szacuje np. Połączenia na wiele sposobów (częstotliwość, histogram) i decyduje o wyniku lub korekcie na podstawie różnic między nimi.

Ostatnia ostatnia podstawowa rzecz do omówienia: Początkowa ocena liczności jest uruchamiana dla każdej operacji w drzewie zapytań, od dołu do góry. Selektywność i liczność wyprowadza się najpierw dla operatorów liści (relacje podstawowe). Zmodyfikowane histogramy oraz informacje o gęstości / częstotliwości pochodzą od operatorów macierzystych. Im wyżej w górę drzewa, tym niższa jest jakość szacunków w miarę narastania błędów.

Ta jednorazowa kompleksowa ocena stanowi punkt wyjścia i pojawia się na długo przed rozważeniem ostatecznego planu wykonania (dzieje się to jeszcze przed etapem kompilacji planu trywialnego). Drzewo zapytań w tym momencie ma tendencję do dość ścisłego odzwierciedlenia formy pisemnej zapytania (choć z usuniętymi podzapytaniami i zastosowanymi uproszczeniami itp.)

Natychmiast po wstępnym oszacowaniu SQL Server dokonuje heurystycznej zmiany kolejności łączenia, która luźno mówiąc próbuje zmienić kolejność drzewa, aby najpierw umieścić mniejsze tabele i połączenia o wysokiej selektywności. Próbuje również pozycjonować połączenia wewnętrzne przed połączeniami zewnętrznymi i krzyżować produkty. Jego możliwości nie są szerokie; jego wysiłki nie są wyczerpujące; i nie uwzględnia kosztów fizycznych (ponieważ jeszcze nie istnieją - dostępne są tylko informacje statystyczne i informacje o metadanych). Zmiana heurystyczna jest najbardziej skuteczna na prostych drzewach z wewnętrznym ekwojonem. Istnieje, aby zapewnić „lepszy” punkt wyjścia do optymalizacji opartej na kosztach.

Dlaczego ta szacunkowa liczność przyłączeń jest tak duża?

MCVE ma „niezwykłe” przeważnie redundantne połączenie wiele do wielu oraz połączenie equi z COALESCEpredykatem. Drzewo operatora ma również ostatnie połączenie wewnętrzne , którego kolejność łączenia heurystycznego nie była w stanie przesunąć drzewa w górę do bardziej preferowanej pozycji. Pomijając wszystkie skalary i rzuty, drzewo łączenia jest:

LogOp_Join [ Card=4.52803e+009 ]
    LogOp_LeftOuterJoin [ Card=481577 ]
        LogOp_LeftOuterJoin [ Card=481577 ]
            LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_LeftOuterJoin [ Card=481577 ]
                LogOp_Get TBL: X_DRIVING_TABLE(alias TBL: dt) [ Card=481577 ]
                LogOp_Get TBL: X_DETAIL_1(alias TBL: d1) [ Card=70 ]
                LogOp_Get TBL: X_DETAIL_LINK(alias TBL: lnk) [ Card=47 ]
            LogOp_Get TBL: X_DETAIL_2(alias TBL: d2) X_DETAIL_2 [ Card=119 ]
        LogOp_Get TBL: X_DETAIL_3(alias TBL: d3) X_DETAIL_3 [ Card=281 ]
    LogOp_Get TBL: X_LAST_TABLE(alias TBL: lst) X_LAST_TABLE [ Card=94025 ]

Zwróć uwagę, że błędne ostateczne oszacowanie już istnieje. Jest drukowany Card=4.52803e+009i zapisywany wewnętrznie jako zmiennoprzecinkowa podwójnej precyzji 4.5280277425e + 9 (4528027742.5 w systemie dziesiętnym).

Tabela pochodna w pierwotnym zapytaniu została usunięta, a projekcje znormalizowane. Reprezentacja SQL drzewa, na którym dokonano wstępnej oceny liczności i selektywności, to:

SELECT 
    PRIMARY_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN X_DETAIL_1 d1
    ON dt.ID = d1.ID
LEFT OUTER JOIN X_DETAIL_LINK lnk 
    ON d1.LINK_ID = lnk.LINK_ID
LEFT OUTER JOIN X_DETAIL_2 d2 
    ON dt.ID = d2.ID
LEFT OUTER JOIN X_DETAIL_3 d3 
    ON dt.ID = d3.ID
INNER JOIN X_LAST_TABLE lst 
    ON lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)

(Nawiasem mówiąc, powtórzenie COALESCEjest również obecne w ostatecznym planie - raz w końcowej skali obliczeniowej, a raz po wewnętrznej stronie połączenia wewnętrznego).

Zwróć uwagę na ostatnie połączenie. To wewnętrzne sprzężenie jest (z definicji) iloczynem kartezjańskim X_LAST_TABLEi poprzednim wynikiem łączenia, z zastosowanym wyborem (predykatem łączenia) lst.JOIN_ID = COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID). Kardynalność produktu kartezjańskiego wynosi po prostu 481577 * 94025 = 45280277425.

W tym celu musimy ustalić i zastosować selektywność predykatu. Połączenie nieprzezroczystego rozwiniętego COALESCEdrzewa (pod względem UNIONi IIF, pamiętaj), wraz z wpływem na kluczowe informacje, uzyskane histogramy i częstotliwości wcześniejszego „niezwykłego” przeważnie nadmiarowego połączenia wielu do wielu oznacza, że ​​CE nie jest w stanie uzyskać szacunkową wartość szacunkową na dowolny z normalnych sposobów.

W rezultacie wchodzi w logikę zgadywania. Logika zgadywania jest umiarkowanie złożona, z wypróbowanymi warstwami algorytmów „wykształconych” i „niezbyt wykształconych”. Jeśli nie zostanie znaleziona lepsza podstawa do odgadnięcia, model używa przypuszczenia ostatniej szansy, które dla porównania równości wynosi: sqllang!x_Selectivity_Equal= stała selektywność 0,1 (przypuszczenie 10%):

-- the moment of doom
movsd xmm0,mmword ptr [sqllang!x_Selectivity_Equal

Wynikiem jest selektywność 0,1 w kartezjańskim produkcie: 481577 * 94025 * 0,1 = 4528027742,5 (~ 4,52803e + 009), jak wspomniano wcześniej.

Przepisuje

Gdy problematyczne połączenie jest komentowane, uzyskiwane jest lepsze oszacowanie, ponieważ unika się „domysłów ostateczności” o ustalonej selektywności (kluczowe informacje są zachowywane przez połączenia 1-M). Jakość oszacowania jest wciąż niska, ponieważ COALESCEpredykat łączenia nie jest wcale przyjazny dla CE. Przypuszczam, że zrewidowane szacunki przynajmniej wydają się bardziej rozsądne dla ludzi.

Kiedy zapytanie jest zapisywane z zewnętrznym złączeniem do X_DETAIL_LINK umieszczenia na końcu , heurystyczna zmiana kolejności jest w stanie zamienić go z ostatnim łączeniem wewnętrznym na X_LAST_TABLE. Umieszczenie połączenia wewnętrznego tuż obok problemu połączenie zewnętrzne daje ograniczonym możliwościom wczesnego ponownego zamawiania możliwość poprawy ostatecznego oszacowania, ponieważ skutki najczęściej nadmiarowego „niezwykłego” połączenia zewnętrznego wiele do wielu pojawiają się po trudnej ocenie selektywności dla COALESCE. Ponownie, szacunki są niewiele lepsze niż ustalone domysły i prawdopodobnie nie sprostałyby ustalonemu przesłuchaniu w sądzie.

Zmiana kolejności połączeń wewnętrznych i zewnętrznych jest trudna i czasochłonna (nawet pełna optymalizacja na etapie 2 próbuje jedynie ograniczyć podzbiór teoretycznych ruchów).

Zagnieżdżone ISNULLsugerowane w odpowiedzi Maxa Vernona udaje się uniknąć ustalonego odgadnięcia, ale ostateczna ocena to nieprawdopodobne zero wierszy (podniesione do jednego rzędu dla przyzwoitości). Równie dobrze może to być domniemany 1 wiersz, dla wszystkich podstaw statystycznych obliczeń.

Oczekiwałbym, że łączna ocena liczności zostanie zawarta między 0 a 481577 wierszy.

Jest to uzasadnione oczekiwanie, nawet jeśli przyjmie się, że oszacowanie liczności może nastąpić w różnym czasie (podczas optymalizacji opartej na kosztach) w fizycznie różnych, ale logicznie i semantycznie identycznych poddrzewach - przy czym ostateczny plan jest rodzajem zlepionego najlepszego z najlepszy (na grupę notatek). Brak gwarancji spójności obejmującej cały plan nie oznacza, że ​​pojedyncze połączenie powinno być w stanie lekceważyć szacunek, rozumiem.

Z drugiej strony, jeśli skończymy na domysłach ostateczności , nadzieja już jest stracona, więc po co się tym przejmować. Wypróbowaliśmy wszystkie znane nam sztuczki i zrezygnowaliśmy. Jeśli nic więcej, dziki ostateczny szacunek jest doskonałym sygnałem ostrzegawczym, że nie wszystko poszło dobrze w CE podczas kompilacji i optymalizacji tego zapytania.

Kiedy wypróbowałem MCVE, 120+ CE wygenerowało zerowe (= 1) końcowe oszacowanie wiersza (jak zagnieżdżone ISNULL) dla pierwotnego zapytania, co jest tak samo nie do przyjęcia dla mojego sposobu myślenia.

Prawdziwe rozwiązanie prawdopodobnie obejmuje zmianę projektu, aby umożliwić proste sprzężenia równorzędne bez COALESCElub ISNULL, a idealnie obce klucze i inne ograniczenia przydatne w kompilacji zapytań.

Uważam, że Compute Scalaroperator wynikający z COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID)przyłączenia się X_LAST_TABLE.JOIN_IDjest podstawową przyczyną problemu. Historycznie, skalary obliczeniowe trudno było dokładnie wycenić ^{1 , 2} .

Ponieważ dostarczyłeś minimalnie kompletny weryfikowalny przykład (dzięki!) Z dokładnymi statystykami, jestem w stanie przepisać zapytanie tak, aby połączenie nie wymagało już rozszerzonej CASEfunkcjonalności COALESCE, co skutkuje znacznie dokładniejszymi szacunkami wierszy i najwyraźniej więcej dokładne całkowite koszty ^{Patrz załącznik na końcu.} :

SELECT COALESCE(dm.d1ID, dm.d2ID, dm.d3ID)
FROM
(
    SELECT d1ID = d1.JOIN_ID
        , d2ID = d2.JOIN_ID
        , d3ID = d3.JOIN_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
    LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
    LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst 
    ON (dm.d1ID IS NOT NULL AND dm.d1ID = lst.JOIN_ID)
    OR (dm.d1ID IS NULL AND dm.d2ID IS NOT NULL AND dm.d2ID = lst.JOIN_ID)
    OR (dm.d1ID IS NULL AND dm.d2ID IS NULL AND dm.d3ID IS NOT NULL AND dm.d3ID = lst.JOIN_ID);

Chociaż xID IS NOT NULLnie jest to technicznie wymagane, ponieważ ID = JOIN_IDnie dołączą się do wartości zerowych, uwzględniłem je, ponieważ wyraźniej oddaje intencję.

Plan 1 i Plan 2

Plan 1:

Plan 2:

Nowe zapytanie korzysta (?) Z równoległości. Warto również zauważyć, że nowe zapytanie ma szacunkową liczbę wyjściową wierszy wynoszącą 1, co w rzeczywistości może być gorsze na koniec dnia niż oszacowanie 4528030000 dla pierwotnego zapytania. Koszt drzewa podrzędnego dla operatora wyboru w nowym zapytaniu wynosi 243210, podczas gdy oryginalny zegar osiąga 536,535, co jest wyraźnie niższe. Powiedziawszy to, nie sądzę, aby pierwsze oszacowanie było jak najbliższe rzeczywistości.

Dodatek 1.

Po dalszych konsultacjach z różnymi osobami na temat The Heap ™, zainspirowanych dyskusją z @Lamak, wydaje mi się, że moje powyższe pytanie obserwacyjne działa okropnie, nawet z równoległością. Rozwiązanie, które umożliwia zarówno dobrą wydajność i dobre szacunki liczności składa zastępując COALESCE(x,y,z)ze związkiem ISNULL(ISNULL(x, y), z), na przykład:

SELECT dm.PRIMARY_ID
FROM
(
    SELECT ISNULL(ISNULL(d1.JOIN_ID, d2.JOIN_ID), d3.JOIN_ID) PRIMARY_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID
    LEFT OUTER JOIN X_DETAIL_2 d2 ON dt.ID = d2.ID
    LEFT OUTER JOIN X_DETAIL_3 d3 ON dt.ID = d3.ID
) dm
INNER JOIN X_LAST_TABLE lst ON dm.PRIMARY_ID = lst.JOIN_ID;

COALESCEjest przekształcany w CASEinstrukcję „pod przykryciem” przez optymalizator zapytań. W związku z tym estymatorowi liczności jest trudniej znaleźć wiarygodne statystyki dla kolumn zakopanych w środku COALESCE. ISNULLbycie funkcją wewnętrzną jest znacznie bardziej „otwarte” dla estymatora liczności. Nie jest również warte niczego, co ISNULLmożna zoptymalizować, jeśli wiadomo, że cel nie ma wartości zerowej.

Plan ISNULLwariantu wygląda następująco:

(Wklej tutaj wersję planu ).

Do Twojej wiadomości, wspierają Sentry One za ich wspaniałego Eksploratora Planów, którego użyłem do stworzenia powyższych planów graficznych.

Jak na twój warunek złączenia, stół można ustawić na wiele sposobów, to „zmiana na jeden konkretny sposób” naprawia wynik.

Załóżmy, że połączenie tylko jednej tabeli daje poprawny wynik.

SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
    FROM X_DRIVING_TABLE dt
    LEFT OUTER JOIN X_DETAIL_1 d1 ON dt.ID = d1.ID
    LEFT OUTER JOIN X_DETAIL_LINK lnk ON d1.LINK_ID = lnk.LINK_ID

Tu, w miejscu X_DETAIL_1, można użyć jednej X_DETAIL_2lub X_DETAIL_3.

Dlatego cel pozostałych 2 tabel nie jest jasny.

To tak, jakbyś rozbił stół X_DETAIL_1na 2 kolejne części.

Najprawdopodobniej „ jest błąd, gdzie jesteś wypełniania tych tabel. ” Idealnie X_DETAIL_1, X_DETAIL_2i X_DETAIL_3powinno zawierać równą ilość wierszy.

Ale co najmniej jedna tabela zawiera niepożądaną liczbę wierszy.

Przepraszam, jeśli się mylę.