Dlaczego wybranie wszystkich wynikowych kolumn tego zapytania jest szybsze niż wybranie jednej kolumny, na której mi zależy?

Mam zapytanie, w którym użycie select *nie tylko znacznie mniej odczytów, ale także zużywa znacznie mniej czasu procesora niż użycie select c.Foo.

To jest zapytanie:

select top 1000 c.ID
from ATable a
    join BTable b on b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
    join CTable c on c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
where (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff)
    and b.IsVoided = 0
    and c.ComplianceStatus in (3, 5)
    and c.ShipmentStatus in (1, 5, 6)
order by a.LastAnalyzedDate

To zakończyło się 2 473 658 odczytami logicznymi, głównie w Tabeli B. Użyło 26 562 CPU i miało czas trwania 7 965.

Oto wygenerowany plan zapytań:

Na PasteThePlan: https://www.brentozar.com/pastetheplan/?id=BJAp2mQIQ

Kiedy zmienię c.IDna *, zapytanie zakończyło się 107 049 logicznymi odczytami, dość równomiernie rozłożonymi między wszystkimi trzema tabelami. Używał 4266 procesorów i miał czas trwania 1 147.

Oto wygenerowany plan zapytań:

Na PasteThePlan: https://www.brentozar.com/pastetheplan/?id=SyZYn7QUQ

Próbowałem użyć wskazówek do zapytania zasugerowanych przez Joe Obbisha z następującymi wynikami:
select c.IDbez podpowiedzi: https://www.brentozar.com/pastetheplan/?id=SJfBdOELm
select c.ID z podpowiedzi: https://www.brentozar.com/pastetheplan/ ? id = B1W ___ N87
select * bez podpowiedzi: https://www.brentozar.com/pastetheplan/?id=HJ6qddEIm
select * z podpowiedź: https://www.brentozar.com/pastetheplan/?id=rJhhudNIQ

Użycie OPTION(LOOP JOIN)podpowiedzi z select c.IDdrastycznie zmniejszyło liczbę odczytów w porównaniu z wersją bez podpowiedzi, ale nadal daje około 4x liczbę odczytów select *zapytania bez żadnych podpowiedzi. Dodanie OPTION(RECOMPILE, HASH JOIN)do select *zapytania spowodowało, że działało znacznie gorzej niż cokolwiek innego, co próbowałem.

Po zaktualizowaniu danych statystycznych na temat tabel i ich indeksów przy użyciu WITH FULLSCANThe select c.IDkwerenda działa znacznie szybciej:
select c.IDprzed aktualizacją: https://www.brentozar.com/pastetheplan/?id=SkiYoOEUm
select * przed aktualizacją: https://www.brentozar.com/ pastetheplan /? id = ryrvodEUX
select c.ID po aktualizacji: https://www.brentozar.com/pastetheplan/?id=B1MRoO487
select * po aktualizacji: https://www.brentozar.com/pastetheplan/?id=Hk7si_V8m

select *nadal przewyższa select c.IDpod względem całkowitego czasu trwania i łącznej liczby odczytów ( select *ma około połowy odczytów), ale zużywa więcej procesora. Ogólnie rzecz biorąc, są znacznie bliżej niż przed aktualizacją, jednak plany wciąż się różnią.

To samo zachowanie obserwuje się w 2016 r. W trybie zgodności w 2014 r. Iw 2014 r. Co może tłumaczyć rozbieżność między tymi dwoma planami? Czy to możliwe, że „poprawne” indeksy nie zostały utworzone? Czy statystyki mogą być nieco nieaktualne?

Próbowałem przenieść predykaty do ONczęści złączenia na wiele sposobów, ale plan zapytań jest za każdym razem taki sam.

Po przebudowie indeksu

Odbudowałem wszystkie indeksy z trzech tabel biorących udział w zapytaniu. c.IDnadal wykonuje najwięcej odczytów (ponad dwa razy więcej *), ale użycie procesora to około połowa *wersji. c.IDWersja rozlane również w tempdb na sortowania ATable:
c.ID: https://www.brentozar.com/pastetheplan/?id=HyHIeDO87
* : https://www.brentozar.com/pastetheplan/?id=rJ4deDOIQ

Próbowałem też zmusić go do działania bez równoległości, co dało mi najlepiej działające zapytanie: https://www.brentozar.com/pastetheplan/?id=SJn9-vuLX

Zauważam, że liczba operacji operatorów PO wielkim wyszukiwaniu indeksu, który wykonuje zlecenie, wykonywana była tylko 1000 razy w wersji jednowątkowej, ale znacznie więcej w wersji równoległej, między 2622 a 4315 wykonaniami różnych operatorów.

Prawdą jest, że wybranie większej liczby kolumn oznacza, że ​​SQL Server może wymagać większej pracy, aby uzyskać żądane wyniki zapytania. Jeśli optymalizator zapytań był w stanie opracować idealny plan zapytań dla obu zapytań, uzasadnione byłoby oczekiwaćSELECT *zapytanie, które ma działać dłużej niż zapytanie, które wybiera wszystkie kolumny ze wszystkich tabel. Zauważyłeś coś przeciwnego dla pary zapytań. Porównując koszty należy zachować ostrożność, ale całkowite szacunkowe koszty wolnego kwerendy wynoszą 1090,08 jednostek optymalizacyjnych, a całkowite szacunkowe koszty szybkich zapytań wynoszą 6823.11 jednostek optymalizacyjnych. W takim przypadku można powiedzieć, że optymalizator źle radzi sobie z szacowaniem całkowitych kosztów zapytania. Wybrał inny plan dla zapytania SELECT * i spodziewał się, że ten plan będzie droższy, ale tutaj tak nie było. Ten rodzaj niedopasowania może wystąpić z wielu powodów, a jedną z najczęstszych przyczyn są problemy z oszacowaniem liczności. Koszty operatora są w dużej mierze determinowane szacunkami liczności. Jeśli oszacowanie liczności w kluczowym punkcie planu jest niedokładne, wówczas całkowity koszt planu może nie odzwierciedlać rzeczywistości. Jest to rażące uproszczenie, ale mam nadzieję, że pomoże to zrozumieć, co się tutaj dzieje.

Zacznijmy od omówienia, dlaczego SELECT *zapytanie może być droższe niż wybranie pojedynczej kolumny. SELECT *Zapytanie może włączyć niektóre indeksy obejmujące w noncovering indeksów, co może oznaczać, że optymalizator musi zrobić pracę dodawania uzyskać wszystkie kolumny potrzebnych albo może trzeba czytać z większym indeksem.SELECT *może również skutkować większymi pośrednimi zestawami wyników, które należy przetworzyć podczas wykonywania zapytania. Możesz to zobaczyć w działaniu, patrząc na szacowane rozmiary wierszy w obu zapytaniach. W szybkim zapytaniu rozmiary wierszy wynoszą od 664 bajtów do 3019 bajtów. W wolnym zapytaniu rozmiary wierszy wynoszą od 19 do 36 bajtów. Blokowanie operatorów, takich jak sortowanie lub kompilacje skrótów, będzie wiązało się z wyższymi kosztami danych o większym rozmiarze wiersza, ponieważ SQL Server wie, że droższe jest sortowanie większych ilości danych lub zamiana ich w tabelę skrótów.

Patrząc na szybkie zapytanie, optymalizator szacuje, że musi wykonać 2,4 miliona wyszukiwań indeksu Database1.Schema1.Object5.Index3. Stąd pochodzi większość kosztów planu. Jednak faktyczny plan ujawnia, że ​​na tym operatorze wykonano tylko 1332 wyszukiwania indeksu. Jeśli porównasz rzeczywiste z szacowanymi wierszami dla zewnętrznych części tych połączeń pętli, zobaczysz duże różnice. Optymalizator uważa, że ​​potrzeba będzie więcej wyszukiwań indeksu, aby znaleźć pierwsze 1000 wierszy wymaganych dla wyników zapytania. Dlatego zapytanie ma stosunkowo wysoki plan kosztów, ale kończy się tak szybko: operator, który był najdroższy, wykonał mniej niż 0,1% oczekiwanej pracy.

Patrząc na wolne zapytanie, otrzymujesz plan z przeważnie sprzężeniami mieszającymi (uważam, że sprzężenie pętli służy tylko do obsługi zmiennej lokalnej). Szacunki dotyczące liczności zdecydowanie nie są idealne, ale jedynym prawdziwym problemem związanym z oszacowaniem jest na końcu tego rodzaju. Podejrzewam, że większość czasu spędza się na skanach tabel z setkami milionów wierszy.

Pomocne może być dodanie wskazówek dotyczących zapytań do obu wersji zapytania, aby wymusić plan zapytań powiązany z inną wersją. Wskazówki dotyczące zapytań mogą być dobrym narzędziem do ustalenia, dlaczego optymalizator dokonał niektórych swoich wyborów. Jeśli dodasz OPTION (RECOMPILE, HASH JOIN)do SELECT *zapytania, spodziewam się, że zobaczysz plan podobny do zapytania łączenia mieszającego. Oczekuję również, że koszty zapytania będą znacznie wyższe dla planu łączenia mieszającego, ponieważ rozmiary wierszy są znacznie większe. Być może dlatego zapytanie nie zostało wybrane dla SELECT *zapytania. Jeśli dodasz OPTION (LOOP JOIN)do zapytania, które wybiera tylko jedną kolumnę, spodziewam się, że zobaczysz plan zapytania podobny do tego dlaSELECT *pytanie. W takim przypadku zmniejszenie rozmiaru wiersza nie powinno mieć większego wpływu na całkowity koszt zapytania. Możesz pominąć kluczowe wyszukiwania, ale to niewielki procent szacowanego kosztu.

Podsumowując, spodziewam się, że większe rozmiary wierszy potrzebne do spełnienia SELECT *zapytania popchną optymalizator w stronę planu łączenia w pętli zamiast planu łączenia w postaci skrótu. Koszt połączenia w pętli jest wyższy niż powinien być z powodu problemów z oszacowaniem liczności. Zmniejszenie rozmiarów wierszy przez wybranie tylko jednej kolumny znacznie zmniejsza koszt planu łączenia mieszającego, ale prawdopodobnie nie będzie miał większego wpływu na koszt planu łączenia pętlowego, więc otrzymujesz mniej wydajny plan łączenia mieszającego. Trudno powiedzieć coś więcej o anonimowym planie.

Niewłaściwe statystyki mogą z pewnością spowodować, że optymalizator wybierze złą metodę wyszukiwania danych. Czy próbowałeś robić UPDATE STATISTICS ... WITH FULLSCANczyli pełny REBUILDna indeksie? Spróbuj i sprawdź, czy to pomoże.

AKTUALIZACJA

Według aktualizacji PO:

Po zaktualizowaniu danych statystycznych na temat tabel i ich indeksów przy użyciu WITH FULLSCANThe select c.IDkwerenda działa znacznie szybciej

Więc teraz, jeśli jedyną podjętą czynnością było UPDATE STATISTICS, to spróbuj zrobić indeks REBUILD(nie REORGANIZE), jak widziałem, że pomoc w szacunkowym zliczaniu wierszy, w których oba UPDATE STATISTICSi indeks REORGANIZEnie.

1. Czy możesz dołączyć skrypty indeksowe?
2. Czy wyeliminowałeś możliwe problemy z „wąchaniem parametrów”? https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
3. Ta technika okazała się pomocna w niektórych przypadkach:
   a) przepisz każdą tabelę jako podkwerendę, przestrzegając następujących zasad:
   b) WYBIERZ - umieść najpierw kolumny łączące
   c) POTWIERDZAJ - przejdź do odpowiednich podkwerend
   d) ORDER BY - przejdź do ich odpowiednie podkwerendy, posortuj JOIN COLUMNS FIRST
   e) Dodaj zapytanie otoki dla końcowego sortowania i wybierz.

Chodzi o to, aby wstępnie posortować kolumny łączenia wewnątrz każdego podselekcji, umieszczając kolumny łączenia na pierwszym miejscu na każdej liście wyboru.

Oto, co mam na myśli ....

SELECT ... wrapper query
FROM
(
    SELECT ...
    FROM
        (SELECT ClientID, ShipKey, NextAnalysisDate
         FROM ATABLE
         WHERE (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff) -- Predicates
         ORDER BY OrderKey, ClientID, LastAnalyzedDate  ---- Pre-sort the join columns
        ) as a
        JOIN 
        (SELECT OrderKey, ClientID, OrderID, IsVoided
         FROM BTABLE
         WHERE IsVoided = 0             ---- Include all predicates
         ORDER BY OrderKey, OrderID, IsVoided       ---- Pre-sort the join columns
        ) as b ON b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
        JOIN
        (SELECT OrderID, ShipKey, ComplianceStatus, ShipmentStatus, ID
         FROM CTABLE
         WHERE ComplianceStatus in (3, 5)       ---- Include all predicates
             AND ShipmentStatus in (1, 5, 6)        ---- Include all predicates
         ORDER BY OrderID, ShipKey          ---- Pre-sort the join columns
        ) as c ON c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
) as d
ORDER BY d.LastAnalyzedDate