Zapytanie 100 razy wolniejsze w SQL Server 2014, liczba wierszy Wiersz buforowania szacuje winowajcę?

Mam zapytanie, które działa w 800 milisekundach w SQL Server 2012 i zajmuje około 170 sekund w SQL Server 2014 . Myślę, że zawęziłem to do złej oceny liczności dla Row Count Spooloperatora. Przeczytałem trochę o operatorach buforowania (np. Tutaj i tutaj ), ale nadal mam problem ze zrozumieniem kilku rzeczy:

• Dlaczego to zapytanie wymaga Row Count Spooloperatora? Nie uważam, że jest to konieczne do poprawności, więc jaką konkretną optymalizację stara się zapewnić?
• Dlaczego SQL Server ocenia, że ​​połączenie z Row Count Spooloperatorem usuwa wszystkie wiersze?
• Czy to błąd w SQL Server 2014? Jeśli tak, złożę zgłoszenie w Connect. Ale najpierw chciałbym głębszego zrozumienia.

Uwaga: mogę ponownie napisać zapytanie jako a LEFT JOINlub dodać indeksy do tabel, aby osiągnąć akceptowalną wydajność zarówno w SQL Server 2012, jak i SQL Server 2014. Więc to pytanie dotyczy bardziej zrozumienia tego konkretnego zapytania i szczegółowego planowania, a mniej o jak inaczej sformułować zapytanie.

Wolne zapytanie

Zobacz ten Pastebin, aby uzyskać pełny skrypt testowy. Oto konkretne zapytanie testowe, na które patrzę:

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014 
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)

SQL Server 2014: szacowany plan zapytań

SQL Server uważa, że Left Anti Semi Joindo Row Count Spoolfiltruje 10 000 wierszy w dół do 1 wiersza. Z tego powodu wybiera LOOP JOINdla kolejnego przyłączenia do #existingCustomers.

SQL Server 2014: rzeczywisty plan zapytań

Zgodnie z oczekiwaniami (przez wszystkich oprócz SQL Server!) Row Count SpoolNie usunięto żadnych wierszy. Zapętlamy więc 10 000 razy, gdy SQL Server spodziewa się zapętlić tylko raz.

SQL Server 2012: szacowany plan zapytań

Podczas korzystania z SQL Server 2012 (lub OPTION (QUERYTRACEON 9481)SQL Server 2014), Row Count Spoolnie zmniejsza szacowanej liczby wierszy i wybierane jest łączenie mieszające, co daje znacznie lepszy plan.

Ponowne zapisanie LEWEGO DOŁĄCZENIA

Dla odniesienia, oto sposób, w jaki mogę ponownie napisać zapytanie, aby osiągnąć dobrą wydajność we wszystkich SQL Server 2012, 2014 i 2016. Jednak nadal interesuje mnie określone zachowanie powyższego zapytania i to, czy jest błędem w nowym SQL Server 2014 Cardinality Estimator.

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL

Dlaczego to zapytanie wymaga operatora buforowania liczby wierszy? ... jaką konkretną optymalizację stara się zapewnić?

cust_nbrKolumna #existingCustomersjest pustych. Jeśli faktycznie zawiera jakieś wartości null, poprawną odpowiedzią jest tutaj zwrócenie zerowych wierszy ( NOT IN (NULL,...) zawsze da pusty zestaw wyników).

Zapytanie można więc traktować jako

SELECT p.*
FROM   #potentialNewCustomers p
WHERE  NOT EXISTS (SELECT *
                   FROM   #existingCustomers e1
                   WHERE  p.cust_nbr = e1.cust_nbr)
       AND NOT EXISTS (SELECT *
                       FROM   #existingCustomers e2
                       WHERE  e2.cust_nbr IS NULL) 

Dzięki tamtej szpuli, aby uniknąć konieczności oceniania

EXISTS (SELECT *
        FROM   #existingCustomers e2
        WHERE  e2.cust_nbr IS NULL) 

Więcej niż raz.

Wydaje się, że jest to przypadek, w którym niewielka różnica w założeniach może spowodować katastrofalną różnicę w wydajności.

Po zaktualizowaniu pojedynczego wiersza jak poniżej ...

UPDATE #existingCustomers
SET    cust_nbr = NULL
WHERE  cust_nbr = 1;

... zapytanie zostało zakończone w mniej niż sekundę. Liczba wierszy w rzeczywistych i szacunkowych wersjach planu jest teraz prawie na miejscu.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT *
FROM   #potentialNewCustomers
WHERE  cust_nbr NOT IN (SELECT cust_nbr
                        FROM   #existingCustomers 
                       ) 

Wiersze zerowe są wyprowadzane jak opisano powyżej.

Histogramy statystyk i progi automatycznej aktualizacji w programie SQL Server nie są wystarczająco szczegółowe, aby wykryć tego rodzaju zmianę w jednym wierszu. Prawdopodobnie, jeśli kolumna ma wartość zerową, uzasadnione może być działanie na podstawie, że zawiera ona co najmniej jeden, NULLnawet jeśli histogram statystyczny obecnie nie wskazuje, że istnieje.

Dlaczego to zapytanie wymaga operatora buforowania liczby wierszy? Nie uważam, że jest to konieczne do poprawności, więc jaką konkretną optymalizację stara się zapewnić?

Zobacz dokładną odpowiedź Martina na to pytanie. Kluczową kwestią jest to, że jeśli pojedynczy wiersz w obrębie NOT INjest NULL, logika logiczna działa tak, że „poprawną odpowiedzią jest zwrócenie zerowych wierszy”. Row Count SpoolOperatora tego optymalizację (potrzeby) logiki.

Dlaczego SQL Server ocenia, że ​​przyłączenie do operatora buforowania wierszy usuwa wszystkie wiersze?

Microsoft zapewnia doskonałą białą księgę na temat SQL 2014 Cardinality Estimator . W tym dokumencie znalazłem następujące informacje:

Nowy CE zakłada, że ​​badane wartości istnieją w zbiorze danych, nawet jeśli wartość wykracza poza zakres histogramu. Nowy CE w tym przykładzie wykorzystuje średnią częstotliwość, która jest obliczana przez pomnożenie liczności tabeli przez gęstość.

Często taka zmiana jest bardzo dobra; znacznie łagodzi rosnący problem z kluczem i zwykle daje bardziej konserwatywny plan zapytań (wyższy szacunek wiersza) dla wartości, które są poza zakresem na podstawie histogramu statystycznego.

Jednak w tym konkretnym przypadku założenie, że NULLwartość zostanie znaleziona, prowadzi do założenia, że ​​połączenie z nią Row Count Spoolodfiltruje wszystkie wiersze #potentialNewCustomers. W przypadku, gdy w rzeczywistości jest NULLwiersz, jest to poprawna ocena (jak widać w odpowiedzi Martina). Jednak w przypadku, gdy nie ma NULLwiersza, efekt może być dewastujący, ponieważ SQL Server generuje oszacowanie po dołączeniu 1 wiersza, niezależnie od liczby wyświetlanych wierszy wejściowych. Może to prowadzić do bardzo złych opcji łączenia w pozostałej części planu zapytań.

Czy to błąd w SQL 2014? Jeśli tak, złożę zgłoszenie w Connect. Ale najpierw chciałbym głębszego zrozumienia.

Wydaje mi się, że znajduje się w szarej strefie między błędem a założeniem lub ograniczeniem wydajności lub ograniczenia nowego SQL Server Estimator Kardynalności. Jednak to dziwactwo może powodować znaczną regresję wydajności w stosunku do SQL 2012 w konkretnym przypadku NOT INklauzuli zerowej , która nie ma żadnych NULLwartości.

Dlatego zgłosiłem problem z połączeniem, aby zespół SQL był świadomy potencjalnych implikacji tej zmiany dla narzędzia Cardinality Estimator.

Aktualizacja: Jesteśmy teraz na CTP3 dla SQL16 i potwierdziłem, że problem tam nie występuje.

Martina Smitha odpowiedź a self-odpowiedź są adresowane do wszystkich głównych punktów poprawnie, po prostu chcę podkreślić obszar dla przyszłych czytelników:

To pytanie dotyczy bardziej zrozumienia tego konkretnego zapytania i szczegółowego planowania, a mniej tego, jak inaczej sformułować zapytanie.

Podany cel zapytania to:

-- Prune any existing customers from the set of potential new customers

To wymaganie jest łatwe do wyrażenia w SQL na kilka sposobów. To, który zostanie wybrany, jest tak samo kwestią stylu jak wszystko inne, ale specyfikacja zapytania powinna być nadal napisana, aby we wszystkich przypadkach zwracać prawidłowe wyniki. Obejmuje to rozliczanie wartości zerowych.

Pełne wyrażenie logicznego wymagania:

• Zwróć potencjalnych klientów, którzy nie są już klientami
• Wymień każdego potencjalnego klienta maksymalnie raz
• Wyklucz potencjalnych potencjalnych klientów i istniejących klientów (cokolwiek oznacza klient zerowy)

Następnie możemy napisać zapytanie spełniające te wymagania, używając dowolnej preferowanej składni. Na przykład:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr NOT IN
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

Daje to wydajny plan wykonania, który zwraca prawidłowe wyniki:

Możemy wyrazić NOT INjako <> ALLlub NOT = ANYbez wpływu na plan lub wyniki:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    DPNNC.cust_nbr <> ALL
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE
    NOT DPNNC.cust_nbr = ANY
    (
        SELECT 
            EC.cust_nbr 
        FROM #existingCustomers AS EC 
        WHERE 
            EC.cust_nbr IS NOT NULL
    );

Lub używając NOT EXISTS:

WITH DistinctPotentialNonNullCustomers AS
(
    SELECT DISTINCT 
        PNC.cust_nbr 
    FROM #potentialNewCustomers AS PNC
    WHERE 
        PNC.cust_nbr IS NOT NULL
)
SELECT
    DPNNC.cust_nbr
FROM DistinctPotentialNonNullCustomers AS DPNNC
WHERE 
    NOT EXISTS
    (
        SELECT * 
        FROM #existingCustomers AS EC
        WHERE
            EC.cust_nbr = DPNNC.cust_nbr
            AND EC.cust_nbr IS NOT NULL
    );

Nie ma w tym nic magicznego, ani nic szczególnie niekorzystnego w używaniu IN, ANYlub ALL- po prostu musimy poprawnie napisać zapytanie, aby zawsze dawało właściwe wyniki.

Najbardziej kompaktowa forma wykorzystuje EXCEPT:

SELECT 
    PNC.cust_nbr 
FROM #potentialNewCustomers AS PNC
WHERE 
    PNC.cust_nbr IS NOT NULL
EXCEPT
SELECT
    EC.cust_nbr 
FROM #existingCustomers AS EC
WHERE 
    EC.cust_nbr IS NOT NULL;

Daje to również prawidłowe wyniki, choć plan wykonania może być mniej wydajny z powodu braku filtrowania bitmap:

Oryginalne pytanie jest interesujące, ponieważ ujawnia problem wpływający na wydajność przy niezbędnej implementacji kontroli zerowej. Istota tej odpowiedzi polega na tym, że poprawne wpisanie zapytania również pozwala uniknąć problemu.