Kiedy predykaty SARGable mogą zostać wprowadzone do CTE lub tabeli pochodnej?

Worek z piaskiem

Pracując na Top Quality Blog Posts® natknąłem jakiegoś zachowania optymalizatora znalazłem naprawdę irytujące interesujące. Nie mam od razu wyjaśnienia, przynajmniej takiego, z którego jestem zadowolony, więc umieszczam je tutaj na wypadek, gdyby pojawił się ktoś inteligentny.

Jeśli chcesz śledzić, możesz pobrać zrzut zrzutu danych przepełnienia stosu z 2013 r . Tutaj . Korzystam z tabeli Komentarze z jednym dodatkowym indeksem.

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

Zapytanie pierwsze

Kiedy przeszukuję tabelę w ten sposób, otrzymuję dziwny plan zapytań .

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

Predykat SARGable dotyczący wyniku nie jest wpychany do CTE. Jest w operatorze filtrów znacznie później w planie.

Co uważam za dziwne, ponieważ ORDER BYjest w tej samej kolumnie co filtr.

Pytanie drugie

Jeśli zmienię zapytanie, zostanie ono wypchnięte.

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

Plan zapytań również się zmienia i działa znacznie szybciej, bez rozlewania się na dysk. Oba dają takie same wyniki, z predykatem podczas skanowania indeksu nieklastrowanego.

Pytanie trzecie

Jest to odpowiednik napisania zapytania w ten sposób:

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

Pytanie czwarte

Użycie tabeli pochodnej powoduje powstanie tego samego „złego” planu zapytań, co wstępne zapytanie CTE

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

Sprawa staje się jeszcze dziwniejsza, gdy ...

Zmieniam zapytanie, aby uporządkować dane rosnąco, a filtr na <=.

Aby uniknąć przedłużania się tego pytania, złożę wszystko.

Zapytania

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;


--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;


--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

Plany

Zaplanuj link .

Zauważ, że żadne z tych zapytań nie korzysta z indeksu nieklastrowanego - jedyne, co się tutaj zmienia, to pozycja operatora filtru. W żadnym wypadku predykat nie jest przekazywany do dostępu do indeksu.

Pojawia się pytanie!

Czy istnieje powód, dla którego predykat SARGable może być wypychany w niektórych scenariuszach, a nie w innych? Różnice w zapytaniach posortowanych w porządku malejącym są interesujące, ale różnice między tymi a rosnącymi są dziwne.

Dla wszystkich zainteresowanych, oto plany z tylko indeksem na Score:

• DESC
• ASC

W grze jest kilka problemów.

Przesuwanie predykatów przeszłości TOP

Optymalizator nie może obecnie przesuwać predykatu poza a TOP, nawet w ograniczonych przypadkach, w których byłoby to bezpieczne *. To ograniczenie uwzględnia zachowanie wszystkich zapytań w pytaniu, w których predykat ma większy zasięg niż TOP.

Obejście polega na ręcznym przepisaniu. Podstawowa kwestia jest podobna do przypadku przepychania predykatów poza funkcję okna , z tym wyjątkiem, że nie ma odpowiedniej specjalistycznej reguły, takiej jak SelOnSeqPrj.

Moja osobista opinia jest taka, że ​​zasada eksploracji SelOnToppozostaje niewdrożona, ponieważ ludzie celowo pisali zapytania, TOPstarając się zapewnić rodzaj „ogrodzenia optymalizacyjnego”.

_{* Zasadniczo oznacza to, że predykat powinien pojawić się w ORDER BYklauzuli związanej z TOP, a kierunek każdej nierówności powinien zgadzać się z kierunkiem sortowania. Transformacja musiałaby również uwzględniać zachowanie sortowania wartości NULL w programie SQL Server. Ogólnie rzecz biorąc, ograniczenia prawdopodobnie oznaczają, że ta transformacja nie byłaby ogólnie użyteczna w praktyce, aby uzasadnić dodatkowe wysiłki poszukiwawcze.}

Problemy z kosztami

Pozostałe plany wykonania w pytaniu można wyjaśnić jako wybory oparte na kosztach ze względu na rozkład wartości w Scorekolumnie (znacznie więcej wierszy <= 500 niż> = 500) oraz wpływ celu wiersza wprowadzonego przez TOP.

Na przykład zapytanie:

--Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC;

... tworzy plan z pozornie nieprzypisanym predykatem w filtrze:

Zauważ, że sortowanie szacuje się na 101 wierszy. Jest to efekt celu rzędu dodanego przez Top. Wpływa to na szacunkowy koszt sortowania i filtru na tyle, że wydaje się, że jest to tańsza opcja. Szacunkowy koszt tego planu wynosi 2401.39 sztuk.

Jeśli wyłączymy cele wierszy wskazówką dotyczącą zapytania:

--Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score <= 500
ORDER BY
    c.Score ASC
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

... opracowanym planem wykonania jest:

Predykat został wepchnięty do skanu jako rezydualny predykat niewymienny, a koszt całego planu wynosi 2402.32 jednostek.

Zauważ, że <= 500predykat nie powinien odfiltrowywać żadnych wierszy. Gdybyś wybrał mniejszą liczbę, na przykład <= 50optymalizator wolałby plan predykatów wypychanych, niezależnie od efektu celu rzędu.

Dla zapytania z Score DESCi Score >= 500orzecznika:

--Written normally
SELECT TOP (101)
    c.UserId, 
    c.[Text],
    c.Score
FROM dbo.Comments AS c
WHERE
    c.Score >= 500
ORDER BY
    c.Score DESC;

Oczekuje się, że predykat będzie bardzo selektywny, więc optymalizator zdecyduje się przekazać predykat i użyć indeksu nieklastrowanego z wyszukiwaniem:

Ponownie optymalizator rozważył wiele alternatyw i jak zwykle wybrał to jako pozornie najtańszą opcję.