Dlaczego szacowany koszt (tego samego) 1000 poszukiwań dla unikalnego indeksu różni się w tych planach?

W poniższych zapytaniach szacuje się, że oba plany wykonania wykonają 1000 wyszukiwań na unikalnym indeksie.

Poszukiwania są sterowane przez uporządkowane skanowanie w tej samej tabeli źródłowej, więc najwyraźniej powinno się kończyć szukanie tych samych wartości w tej samej kolejności.

Obie zagnieżdżone pętle mają <NestedLoops Optimized="false" WithOrderedPrefetch="true">

Czy ktoś wie, dlaczego to zadanie kosztuje w pierwszym planie 0,172434, a w drugim 3.01702?

(Powodem pytania jest to, że pierwsze zapytanie zostało zasugerowane jako optymalizacja ze względu na pozornie znacznie niższy koszt planu. Właściwie to wygląda na to, że działa więcej, ale ja tylko próbuję wyjaśnić tę rozbieżność. .)

Ustawiać

CREATE TABLE dbo.Target(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL);

CREATE TABLE dbo.Staging(KeyCol int PRIMARY KEY, OtherCol char(32) NOT NULL); 

INSERT INTO dbo.Target
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1,  
     master..spt_values v2;

INSERT INTO dbo.Staging
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY @@SPID), LEFT(NEWID(),32)
FROM master..spt_values v1;

Zapytanie 1 _{Link „Wklej plan”}

WITH T
     AS (SELECT *
         FROM   Target AS T
         WHERE  T.KeyCol IN (SELECT S.KeyCol
                             FROM   Staging AS S))
MERGE T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES(S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol;

Zapytanie 2 _{Link „Wklej plan”}

MERGE Target T
USING Staging S
ON ( T.KeyCol = S.KeyCol )
WHEN NOT MATCHED THEN
  INSERT ( KeyCol, OtherCol )
  VALUES( S.KeyCol, S.OtherCol )
WHEN MATCHED AND T.OtherCol > S.OtherCol THEN
  UPDATE SET T.OtherCol = S.OtherCol; 

Zapytanie 1

Zapytanie 2

Powyższe zostało przetestowane na SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

@Joe Obbish podkreśla w komentarzach, że prostsze byłoby repro

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

vs

SELECT *
FROM staging AS S 
  LEFT OUTER JOIN (SELECT * FROM Target) AS T 
    ON T.KeyCol = S.KeyCol;

W przypadku tabeli pomostowej rzędu 1000 oba powyższe nadal mają ten sam kształt planu z zagnieżdżonymi pętlami, a plan bez tabeli pochodnej wydaje się tańszy, ale w przypadku tabeli pomostowej rzędu 10 000 i tej samej tabeli docelowej jak powyżej różnica kosztów zmienia plan kształt (z pełnym skanowaniem i łączeniem scalającym, które wydają się względnie bardziej atrakcyjne niż kosztowne poszukiwania), pokazując, że ta rozbieżność kosztów może mieć inne konsekwencje niż tylko utrudnianie porównywania planów.

Czy ktoś wie, dlaczego to zadanie kosztuje w pierwszym planie 0,172434, a w drugim 3.01702?

Ogólnie rzecz biorąc, wyszukiwanie po wewnętrznej stronie poniżej zagnieżdżonego połączenia pętli jest kosztowane przy założeniu losowego wzorca we / wy. Istnieje prosta, oparta na zastępowaniu, redukcja dla kolejnych dostępów, z uwzględnieniem prawdopodobieństwa, że ​​wymagana strona została już wprowadzona do pamięci przez poprzednią iterację. Ta podstawowa ocena powoduje powstanie standardowego (wyższego) kosztu.

Jest jeszcze jeden kosztowy wkład, Smart Seek Costing , o którym mało wiadomo. Domyślam się (i to wszystko na tym etapie), że SSC próbuje bardziej szczegółowo oszacować koszt operacji wejścia / wyjścia po stronie wewnętrznej, być może biorąc pod uwagę lokalne uporządkowanie i / lub zakres wartości do pobrania. Kto wie.

Na przykład pierwsza operacja wyszukiwania powoduje wyświetlenie nie tylko żądanego wiersza, ale wszystkich wierszy na tej stronie (w kolejności indeksu). Biorąc pod uwagę ogólny wzorzec dostępu, pobranie 1000 wierszy na 1000 prób wymaga tylko 2 odczytów fizycznych, nawet z wyłączonym wyprzedzeniem odczytu i pobieraniem wstępnym. Z tej perspektywy domyślna kalkulacja We / Wy reprezentuje znaczne przeszacowanie, a koszt skorygowany o SSC jest bliższy rzeczywistości.

Rozsądne wydaje się oczekiwanie, że SSC byłby najbardziej skuteczny, gdy pętla steruje wyszukiwaniem indeksu mniej więcej bezpośrednio, a odniesienie zewnętrzne łączenia jest podstawą operacji wyszukiwania. Z tego, co mogę powiedzieć, SSC jest zawsze próbowane dla odpowiednich operacji fizycznych, ale najczęściej nie powoduje żadnej korekty w dół, gdy szukanie jest oddzielone od łączenia innymi operacjami. Proste filtry są jednym wyjątkiem od tego, być może dlatego, że SQL Server często może je wypchnąć do operatora dostępu do danych. W każdym razie optymalizator ma dość głębokie wsparcie dla selekcji.

To niefortunne, że skalar obliczeniowy dla zewnętrznych rzutów podzapytania wydaje się tutaj zakłócać SSC. Skalary obliczeniowe są zwykle przenoszone ponad złączenie, ale te muszą pozostać tam, gdzie są. Mimo to większość normalnych skalarów obliczeniowych jest dość przezroczysta dla optymalizacji, więc jest to nieco zaskakujące.

Niezależnie od tego, kiedy operacja fizyczna PhyOp_Rangejest wykonywana z prostego zaznaczenia indeksu SelIdxToRng, SSC jest skuteczne. Gdy zastosowana jest bardziej złożona SelToIdxStrategy(selekcja w tabeli do strategii indeksu), wynikowy PhyOp_Rangeuruchamia SSC, ale nie powoduje redukcji. Ponownie wydaje się, że prostsze, bardziej bezpośrednie operacje najlepiej działają z SSC.

Chciałbym móc dokładnie powiedzieć, co robi SSC i pokazać dokładne obliczenia, ale nie znam tych szczegółów. Jeśli chcesz zbadać dostępne ograniczone dane wyjściowe śledzenia, możesz zastosować nieudokumentowaną flagę śledzenia 2398. Przykładowy wynik to:

Koszt inteligentnego wyszukiwania (7.1) :: 1.34078e + 154, 0,001

Ten przykład dotyczy grupy notatek 7, alternatywy 1, pokazującej górną granicę kosztu i współczynnik 0,001. Aby zobaczyć czystsze czynniki, należy odbudować tabele bez równoległości, aby strony były tak gęste, jak to możliwe. Bez tego współczynnik jest bardziej podobny do 0,000821 dla twojej przykładowej tabeli docelowej. Oczywiście istnieją tam dość oczywiste relacje.

SSC można również wyłączyć za pomocą nieudokumentowanej flagi śledzenia 2399. Gdy ta flaga jest aktywna, oba koszty mają wyższą wartość.

Nie jestem pewien, czy to odpowiedź, ale komentarz jest nieco długi. Przyczyną tej różnicy jest czysta spekulacja z mojej strony i być może może być pokarmem dla innych.

Uproszczone zapytania z planami wykonania.

SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN Target AS T 
    ON T.KeyCol = S.KeyCol;

SELECT S.KeyCol, 
       S.OtherCol,
       T.*
FROM staging AS S 
  LEFT OUTER JOIN (
                  SELECT *
                  FROM Target
                  ) AS T 
    ON T.KeyCol = S.KeyCol;

Główną różnicą między tymi równoważnymi zapytaniami, które mogą doprowadzić do identycznych planów wykonania, jest obliczeniowy operator skalarny. Nie wiem, dlaczego musi tam być, ale myślę, że jest to tak daleko, jak optymalizator może zoptymalizować tabelę pochodną.

Domyślam się, że obecność skalara obliczeniowego jest tym, co podnosi koszt operacji IO dla drugiego zapytania.

From Inside the Optimizer: Plan Costing

Koszt procesora jest obliczany jako 0,0001581 dla pierwszego wiersza i 0,000011 dla kolejnych wierszy.
...
Koszt we / wy 0,003125 wynosi dokładnie 1/320 - odzwierciedlając założenie modelu, że podsystem dyskowy może wykonać 320 losowych operacji we / wy na sekundę
...
komponent kalkulacyjny jest wystarczająco inteligentny, aby rozpoznać całkowitą liczbę strony, które należy pobrać z dysku, nigdy nie mogą przekroczyć liczby stron wymaganych do przechowywania całej tabeli.

W moim przypadku tabela zajmuje 5618 stron i aby uzyskać 1000 wierszy z 1000000 wierszy, szacunkowa potrzebna liczba stron wynosi 5,618, co daje koszt zamówienia 0,015625.

Koszt CPU dla obu zapytań szwy być takie same 0.0001581 * 1000 executions = 0.1581.

Tak więc, zgodnie z powyższym artykułem, możemy obliczyć koszt pierwszego zapytania na 0,173725.

Zakładając, że mam rację co do tego, w jaki sposób skalar obliczeniowy robi bałagan z kosztów IO, można go wyliczyć do 3,2831.

Nie do końca to, co pokazano w planach, ale jest dokładnie tam, w okolicy.

(Byłoby lepiej jako komentarz do odpowiedzi Paula, ale nie mam jeszcze wystarczającej liczby przedstawicieli).

Chciałem przedstawić listę flag śledzenia (i kilka DBCCstwierdzeń), do których doszedłem do wniosku, na wypadek, gdyby pomocne było zbadanie podobnych rozbieżności w przyszłości. Wszystkie te nie powinny być wykorzystywane w produkcji .

Najpierw rzuciłem okiem na ostatnią notatkę, aby zobaczyć, jakich operatorów fizycznych używa się. Z pewnością wyglądają tak samo zgodnie z graficznymi planami wykonania. Użyłem więc flag śledzenia 3604i 8615pierwszy przekierowuje dane wyjściowe do klienta, a drugi ujawnia ostateczną notatkę:

SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN Target AS T
       ON T.KeyCol = S.KeyCol
OPTION(QUERYTRACEON 3604, -- Output client info
       QUERYTRACEON 8615, -- Shows Final Memo structure
       RECOMPILE);

Wracając z Root Group, znalazłem te prawie identyczne PhyOp_Rangeoperatory:

1. PhyOp_Range 1 ASC 2.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 0.175559(Distance = 2)
2. PhyOp_Range 1 ASC 3.0 Cost(RowGoal 0,ReW 0,ReB 999,Dist 1000,Total 1000)= 3.01702(Distance = 2)

Jedyną oczywistą różnicą było dla mnie 2.0i 3.0, które odnoszą się do ich „grupy notatek 2, oryginalne” i „grupy notatek 3, oryginalne”. Sprawdzając notatkę, odnoszą się do tej samej rzeczy - więc nie ujawniono jeszcze różnic.

Po drugie, zajrzałem do całego bałaganu śladów flag, które okazały się dla mnie bezowocne - ale mają kilka interesujących treści. Większość podniosłem od Benjamina Nevareza . Szukałem wskazówek co do zasad optymalizacji, które zostały zastosowane w jednym przypadku, a nie w drugim.

 SELECT S.*, T.KeyCol
 FROM Staging AS S
      LEFT OUTER JOIN Target AS T
        ON T.KeyCol = S.KeyCol
 OPTION (QUERYTRACEON 3604, -- Output info to client
         QUERYTRACEON 2363, -- Show stats and cardinality info
         QUERYTRACEON 8675, -- Show optimization process info
         QUERYTRACEON 8606, -- Show logical query trees
         QUERYTRACEON 8607, -- Show physical query tree
         QUERYTRACEON 2372, -- Show memory utilization info for optimization stages 
         QUERYTRACEON 2373, -- Show memory utilization info for applying rules
         RECOMPILE );

Po trzecie, przyjrzałem się, które zasady zastosowano do naszych, PhyOp_Rangektóre wyglądają tak podobnie. Użyłem kilku flag śledzenia wspomnianych przez Paula w poście na blogu .

SELECT S.*, T.KeyCol
FROM Staging AS S
      LEFT OUTER JOIN (SELECT KeyCol
                      FROM Target) AS T
       ON T.KeyCol = S.KeyCol
OPTION (QUERYTRACEON 3604, -- Output info to client
        QUERYTRACEON 8619, -- Show applied optimization rules
        QUERYTRACEON 8620, -- Show rule-to-memo info
        QUERYTRACEON 8621, -- Show resulting tree
        QUERYTRACEON 2398, -- Show "smart seek costing"
        RECOMPILE );

Z wyjścia, widzimy, że Direct- JOINstosować tę zasadę, aby dostać nasz PhyOp_Rangeoperator: Rule Result: group=7 2 <SelIdxToRng>PhyOp_Range 1 ASC 2 (Distance = 2). SUBSELECT stosować tę zasadę zamiast: Rule Result: group=9 2 <SelToIdxStrategy>PhyOp_Range 1 ASC 3 (Distance = 2). W tym miejscu są również wyświetlane informacje o „inteligentnym wyszukiwaniu kosztów” związane z każdą regułą. Dla Direct- JOINto jest wyjście (dla mnie) Smart seek costing (7.2) :: 1.34078e+154 , 0.001. Dla podselekcji, to jest wyjście: Smart seek costing (9.2) :: 1.34078e+154 , 1.

W końcu nie mogłem wiele wyciągnąć wniosków - ale odpowiedź Paula wypełnia większość luki. Chciałbym zobaczyć więcej informacji o kosztach inteligentnego wyszukiwania.

To też nie jest odpowiedź - jak zauważył Mikael, trudno jest omówić ten problem w komentarzach ...

Co ciekawe, jeśli przekształcisz podzapytanie (select KeyCol FROM Target)w wbudowany TVF, zobaczysz, że plan i jego koszty są takie same jak proste oryginalne zapytanie:

CREATE FUNCTION dbo.cs_test()
RETURNS TABLE
WITH SCHEMABINDING
AS 
RETURN (
    SELECT KeyCol FROM dbo.Target
    );

/* "normal" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN Target AS T ON T.KeyCol = S.KeyCol;

/* "subquery" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (SELECT KeyCol FROM Target) AS T ON T.KeyCol = S.KeyCol;

/* "inline-TVF" variant */
SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN dbo.cs_test() t ON s.KeyCol = t.Keycol

Plany zapytań ( link pastetheplan ):

Odliczenie prowadzi mnie do wniosku, że silnik wyceny jest zdezorientowany co do potencjalnego wpływu, jaki może mieć ten rodzaj podzapytania .

Weźmy na przykład:

SELECT S.KeyCol, s.OtherCol, T.KeyCol 
FROM staging AS S 
    LEFT OUTER JOIN (
        SELECT KeyCol = CHECKSUM(NEWID()) 
        FROM Target
        ) AS T ON T.KeyCol = S.KeyCol;

Ile by to kosztowało? Optymalizator zapytań wybiera bardzo podobny plan do powyższego wariantu „podzapytania”, zawierający skalar obliczeniowy ( link pastetheplan.com ):

Skalar obliczeniowy ma zupełnie inny koszt niż pokazany powyżej wariant „podzapytania”, ale nadal jest to po prostu zgadywanie, ponieważ optymalizator kwerendy nie ma możliwości ustalenia a priori, jaka może być liczba zwróconych wierszy. Plan używa dopasowania skrótu dla lewego sprzężenia zewnętrznego, ponieważ oszacowania wierszy są niepoznawalne, a zatem ustawione na liczbę wierszy w tabeli docelowej.

Nie wyciągam z tego wielkiego wniosku, z wyjątkiem tego, że zgadzam się z pracą Mikaela w jego odpowiedzi i mam nadzieję, że ktoś inny wymyśli lepszą odpowiedź.