SQL Server 2014: jakieś wyjaśnienie niespójnego oszacowania liczności samodzielnego przyłączenia?

Rozważ następujący plan zapytań w programie SQL Server 2014:

W planie zapytań samozłączenie ar.fId = ar.fIddaje szacunkową wartość 1 wiersza. Jest to jednak logicznie niespójne oszacowanie: arma 20,608wiersze i tylko jedną wyraźną wartość fId(dokładnie odzwierciedloną w statystykach). Dlatego to połączenie tworzy pełny iloczyn krzyżowy wierszy ( ~424MMwierszy), co powoduje, że zapytanie jest uruchamiane przez kilka godzin.

Trudno mi zrozumieć, dlaczego SQL Server miałby przedstawić oszacowanie, które można tak łatwo udowodnić, że jest niespójne ze statystykami. Jakieś pomysły?

Wstępne dochodzenie i dodatkowe szczegóły

Na podstawie odpowiedzi Paula tutaj wydaje się, że zarówno heurystyka SQL 2012, jak i SQL 2014 do oszacowania liczności złączeń powinny z łatwością poradzić sobie z sytuacją, w której należy porównać dwa identyczne histogramy.

Zacząłem od wyjścia z flagi śledzenia 2363, ale nie byłem w stanie tego łatwo zrozumieć. Czy następujący fragment oznacza, że ​​SQL Server porównuje histogramy dla fIdi bIdw celu oszacowania selektywności łączenia, które używa tylko fId? Jeśli tak, to oczywiście nie byłoby poprawne. A może źle czytam wynik flagi śledzenia?

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

Zauważ, że opracowałem kilka obejść, które są zawarte w pełnym skrypcie repro i sprowadzam to zapytanie do milisekund. To pytanie koncentruje się na zrozumieniu zachowania, jak go uniknąć w przyszłych zapytaniach i ustaleniu, czy jest to błąd, który należy zgłosić w firmie Microsoft.

Oto pełny skrypt repro , oto pełne dane wyjściowe z flagi śledzenia 2363 , a oto definicje zapytań i tabel na wypadek, gdybyś chciał szybko na nie spojrzeć bez otwierania pełnego skryptu:

WITH cte AS (
    SELECT ar.fId, 
        ar.bId,
        MIN(CONVERT(INT, ar.isT)) AS isT,
        MAX(CONVERT(INT, tcr.isS)) AS isS
    FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar 
    LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
        ON tcr.rId = 508
        AND tcr.fId = ar.fId
        AND tcr.bId = ar.bId
    GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s 
JOIN cte t 
    ON t.fId = s.fId 
    AND t.isT = 1

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
    fId INT NOT NULL,
    bId INT NOT NULL,
    isT BIT NOT NULL
    PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
    rId INT NOT NULL,
    fId INT NOT NULL,
    bId INT NOT NULL,
    isS BIT NOT NULL
    PRIMARY KEY (rId, fId, bId, isS)
)

Trudno mi zrozumieć, dlaczego SQL Server miałby przedstawić oszacowanie, które można tak łatwo udowodnić, że jest niespójne ze statystykami.

Konsystencja

Nie ma ogólnej gwarancji spójności. Szacunki mogą być obliczane na różnych (ale logicznie równoważnych) poddrzewach w różnym czasie, przy użyciu różnych metod statystycznych.

Nie ma nic złego w logice, która mówi, że połączenie tych dwóch identycznych poddrzewa powinno stworzyć produkt krzyżowy, ale nie ma również nic, co mówi, że wybór rozumowania jest bardziej rozsądny niż jakikolwiek inny.

Wstępne oszacowanie

W twoim konkretnym przypadku początkowa ocena liczności dla łączenia nie jest przeprowadzana na dwóch identycznych poddrzewach . Kształt drzewa w tym czasie to:

  LogOp_Join
     LogOp_GbAgg
        LogOp_LeftOuterJoin
           LogOp_Get TBL: ar
           LogOp_Select
              LogOp_Get TBL: tcr
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [tcr] .rId
                 ScaOp_Const Wartość = 508
           ScaOp_Logical x_lopAnd
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar] .fId
                 ScaOp_Identifier [tcr] .fId
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar] .bId
                 ScaOp_Identifier [tcr] .bId
        AncOp_PrjList 
           AncOp_PrjEl Expr1003 
              ScaOp_AggFunc stopMax
                 ScaOp_Convert int
                    ScaOp_Identifier [tcr] .isS
     LogOp_Select
        LogOp_GbAgg
           LogOp_LeftOuterJoin
              LogOp_Get TBL: ar
              LogOp_Select
                 LogOp_Get TBL: tcr
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [tcr] .rId
                    ScaOp_Const Wartość = 508
              ScaOp_Logical x_lopAnd
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar] .fId
                    ScaOp_Identifier [tcr] .fId
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar] .bId
                    ScaOp_Identifier [tcr] .bId
           AncOp_PrjList 
              AncOp_PrjEl Expr1006 
                 ScaOp_AggFunc stopMin
                    ScaOp_Convert int
                       ScaOp_Identifier [ar] .isT
              AncOp_PrjEl Expr1007 
                 ScaOp_AggFunc stopMax
                    ScaOp_Convert int
                       ScaOp_Identifier [tcr] .isS
        ScaOp_Comp x_cmpEq
           ScaOp_Identifier Expr1006 
           ScaOp_Const Wartość = 1
     ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [ar] .fId
        ScaOp_Identifier QCOL: [ar] .fId

Pierwsze wejście sprzężenia zostało uproszczone od nieprojektowanego agregatu, a drugie wejście sprzężenia ma predykat t.isT = 1wypchnięty pod nim, gdzie t.isTjest MIN(CONVERT(INT, ar.isT)). Mimo to obliczenia selektywności dla isTpredykatu można zastosować CSelCalcColumnInIntervalna histogramie:

  CSelCalcColumnInInterval
      Kolumna: COL: Expr1006 

Załadowany histogram dla kolumny QCOL: [ar] .isT ze statystyk o id 3

Selektywność: 4,85248e-005

Wygenerowano kolekcję statystyk: 
  CStCollFilter (ID = 11, CARD = 1)
      CStCollGroupBy (ID = 10, CARD = 20608)
          CStCollOuterJoin (ID = 9, CARD = 20608 x_jtLeftOuter)
              CStCollBaseTable (ID = 3, CARD = 20608 TBL: ar)
              CStCollFilter (ID = 8, CARD = 1)
                  CStCollBaseTable (ID = 4, CARD = 28 TBL: tcr)

(Prawidłowe) oczekiwanie polega na zmniejszeniu liczby 20 608 wierszy do 1 wiersza przez ten predykat.

Dołącz do oceny

Powstaje teraz pytanie, w jaki sposób 20 608 wierszy z drugiego wejścia sprzężenia będzie pasować do tego jednego wiersza:

  LogOp_Join
      CStCollGroupBy (ID = 7, CARD = 20608)
          CStCollOuterJoin (ID = 6, CARD = 20608 x_jtLeftOuter)
              ...

      CStCollFilter (ID = 11, CARD = 1)
          CStCollGroupBy (ID = 10, CARD = 20608)
              ...

      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [ar] .fId
          ScaOp_Identifier QCOL: [ar] .fId

Ogólnie istnieje kilka różnych sposobów szacowania złączenia. Możemy na przykład:

• Wyprowadź nowe histogramy u każdego operatora planu w każdym poddrzewie, wyrównaj je na złączeniu (interpolując wartości kroków, jeśli to konieczne) i zobacz, jak się pokrywają; lub
• Wykonaj prostsze „zgrubne” wyrównanie histogramów (stosując wartości minimalne i maksymalne, a nie krok po kroku); lub
• Oblicz osobne selektywności dla samych kolumn łączenia (z tabeli podstawowej i bez filtrowania), a następnie dodaj efekt selektywności predykatu (ów).
• ...

W zależności od używanego estymatora liczności i niektórych heurystyk, można zastosować dowolną z nich (lub odmianę). Więcej informacji można znaleźć w białej księdze Microsoft optymalizującej plany zapytań za pomocą narzędzia Estymator kardynalności SQL Server 2014 .

Pluskwa?

Teraz, jak zauważono w pytaniu, w tym przypadku „proste” jednokolumnowe łączenie (włączone fId) korzysta z CSelCalcExpressionComparedToExpressionkalkulatora:

Plan obliczeń:

  CSelCalcExpressionComparedToExpression [ar] .fId x_cmpEq [ar] .fId

Załadowany histogram dla kolumny QCOL: [ar] .bId ze statystyk o id 2
Załadowany histogram dla kolumny QCOL: [ar] .fId ze statystyk o id 1

Selektywność: 0

W tym obliczeniu ocenia się, że połączenie 20 608 wierszy z 1 odfiltrowanym rzędem będzie miało zerową selektywność: żadne wiersze nie będą pasować (zgłoszone jako jeden wiersz w ostatecznych planach). Czy to źle? Tak, prawdopodobnie w tym nowym CE jest błąd. Można argumentować, że 1 wiersz pasuje do wszystkich wierszy lub żaden, więc wynik może być rozsądny, ale istnieje powód, by sądzić inaczej.

Szczegóły są w rzeczywistości dość trudne, ale oczekiwanie, że oszacowanie będzie oparte na niefiltrowanych fIdhistogramach, zmodyfikowanych przez selektywność filtra, dając 20608 * 20608 * 4.85248e-005 = 20608wiersze, jest bardzo rozsądne.

Wykonanie tego obliczenia oznaczałoby użycie kalkulatora CSelCalcSimpleJoinWithDistinctCountszamiast CSelCalcExpressionComparedToExpression. Nie ma udokumentowanego sposobu, aby to zrobić, ale jeśli jesteś ciekawy, możesz włączyć nieudokumentowaną flagę śledzenia 9479:

Zauważ, że końcowe połączenie daje 20 608 wierszy z dwóch jednorzędowych danych wejściowych, ale nie powinno to być zaskoczeniem. Jest to ten sam plan, który został opracowany przez pierwotny CE zgodnie z TF 9481.

Wspomniałem, że szczegóły są trudne (i czasochłonne w badaniu), ale o ile mogę stwierdzić, podstawowa przyczyna problemu jest związana z orzeczeniem rId = 508, z zerową selektywnością. Oszacowanie zerowe jest podnoszone do jednego wiersza w normalny sposób, co wydaje się przyczyniać do oszacowania zerowej selektywności w danym złączeniu, gdy uwzględnia niższe predykaty w drzewie wejściowym (stąd statystyki ładowania bId).

Zezwolenie złączeniu zewnętrznemu na zachowanie oszacowania wewnętrznej strony od zera w wierszu (zamiast podnoszenia do jednego rzędu) (aby zakwalifikować się wszystkie rzędy zewnętrzne) daje oszacowanie złączenia „bez błędów” za pomocą dowolnego kalkulatora. Jeśli chcesz to zbadać, nieudokumentowaną flagą śledzenia jest 9473 (sam):

Zachowanie oszacowania liczności łączenia CSelCalcExpressionComparedToExpressionmożna również zmodyfikować, aby nie uwzględniało `` bId '' za pomocą innej nieudokumentowanej flagi wariancji (9494). Wspominam o nich wszystkich, ponieważ wiem, że interesujecie się takimi rzeczami; nie dlatego, że oferują rozwiązanie. Dopóki nie zgłosisz problemu do firmy Microsoft i nie rozwiążą go (lub nie), wyrażenie zapytania w inny sposób jest prawdopodobnie najlepszym rozwiązaniem. Bez względu na to, czy zachowanie jest zamierzone, czy nie, powinni zainteresować się regresją.

Wreszcie, aby uporządkować jeszcze jedną rzecz wymienioną w skrypcie odtwarzania: ostateczna pozycja filtru w planie pytań jest wynikiem eksploracji opartej na kosztach, GbAggAfterJoinSelprzenoszącej agregat i filtr nad złączenie, ponieważ wynik łączenia jest tak mały Liczba rzędów. Filtr był początkowo poniżej złączenia, zgodnie z oczekiwaniami.