Dlaczego szacunki wierszy programu SQL Server zmieniają się, gdy dodam wskazówkę dotyczącą przyłączenia?

Mam zapytanie, które łączy kilka tabel i działa dość źle - oszacowania wierszy są znacznie (1000 razy) wyłączone, a połączenie zagnieżdżonych pętli jest wybrane, co powoduje wielokrotne skanowanie tabeli. Kształt zapytania jest dość prosty, wygląda mniej więcej tak:

SELECT t1.id
FROM t1
INNER JOIN t2 ON t1.id = t2.t1_id
LEFT OUTER JOIN t3 ON t2.id = t3.t2_id
LEFT OUTER JOIN t4 ON t3.t4_id = t4.id 
WHERE t4.id = some_GUID

Rozważając zapytanie, zauważyłem, że kiedy sugeruję, aby użyć sprzężenia scalania dla jednego ze sprzężeń, działa ono wiele razy szybciej. Rozumiem to - Scalanie złączenia jest lepszą opcją dla danych, które są łączone, ale SQL Server po prostu nie szacuje tego przy wyborze zagnieżdżonych pętli.

Nie do końca rozumiem, dlaczego ta wskazówka dotycząca łączenia zmienia wszystkie prognozy dla wszystkich operatorów planu? Po przeczytaniu różnych artykułów i książek założyłem, że oszacowania liczności są wykonywane przed zbudowaniem planu, więc użycie podpowiedzi nie zmieniłoby oszacowań, ale raczej wyraźnie powiedziało SQL Serverowi, aby użył konkretnej fizycznej implementacji łączenia.

Widzę jednak, że wskazówka dotycząca scalania powoduje, że wszystkie szacunki stają się prawie idealne. Dlaczego tak się dzieje i czy są jakieś popularne techniki, dzięki którym optymalizator zapytań dokonuje lepszej oceny bez podpowiedzi - biorąc pod uwagę, że statystyki oczywiście na to pozwalają?

UPD: anonimowe plany wykonania można znaleźć tutaj: https://www.dropbox.com/s/hchfuru35qqj89s/merge_join.sqlplan?dl=0 https://www.dropbox.com/s/38sjtv0t7vjjfdp/no_hints_join.sqlplan?dl = 0

Sprawdziłem statystyki używane przez oba zapytania przy użyciu TF 3604, 9292 i 9204, a te są identyczne. Jednak indeksy, które są skanowane / wyszukiwane, różnią się między zapytaniami.

Poza tym próbowałem uruchomić kwerendę z OPTION (FORCE ORDER)- działa nawet szybciej niż przy użyciu łączenia scalającego, wybierając HASH MATCH dla każdego sprzężenia.

Po przeczytaniu różnych artykułów i książek założyłem, że szacunki liczności są wykonywane przed zbudowaniem planu.

Nie dokładnie. Szacunkowe liczność pochodzi (po uproszczeń i inne prace), który ma wpływ na początkowy porządku łączenia, wybranej przez Optimizer.

Jednak późniejsze eksploracje (podczas optymalizacji opartej na kosztach) mogą i często prowadzą do obliczenia nowych oszacowań liczności. Te późniejsze CE mogą być mniej lub bardziej „dokładne”. Jeśli wyniki są niedoszacowane, optymalizator może wybrać plan, który wygląda taniej, ale w rzeczywistości działa znacznie dłużej.

Zasadniczo nie ma gwarancji, że oszacowania liczności dla poddanych semantycznie identycznych poddrzewi dadzą takie same wyniki. W końcu jest to proces statystyczny, a niektóre operacje mają głębsze wsparcie CE niż inne.

W twoim przypadku wydaje się, że istnieje jeszcze jeden czynnik - optymalizator wprowadza (lub porusza się) szczyt, który wyznacza cel rzędu w poddrzewie poniżej:

Jeśli włączysz flagę śledzenia 4138 (w wersji 2008 R2 lub nowszej), możesz znaleźć szacunki bardziej zgodne z oczekiwaniami, a może nawet, że optymalizator nie wybierze już zagnieżdżonych pętli.

Widzę jednak, że wskazówka dotycząca scalania powoduje, że wszystkie szacunki stają się prawie idealne.

W grę wchodzi element szczęścia. Ludzie mają tendencję do pisania zapytań, a przynajmniej połączeń, w kolejności, w jakiej oczekują, że zostaną wykonane fizycznie. Korzystanie z podpowiedzi do łączenia wiąże się z domniemanym FORCE ORDER, ustalając w ten sposób kolejność łączenia w celu dopasowania do formy tekstowej i wyłączając wiele reguł eksploracji optymalizatora, które mogą prowadzić do ponownej oceny liczności.

Poza tym próbowałem uruchomić kwerendę z OPTION (FORCE ORDER)- działa nawet szybciej niż przy użyciu łączenia scalającego, wybierając HASH MATCH dla każdego sprzężenia.

Jest to to samo, co wskazanie połączenia, ale nie ogranicza wyboru operatora łączenia fizycznego. Ponownie, jeśli zdarzyło Ci się napisać logiczną kolejność łączenia zapytań, całkiem prawdopodobne jest, że otrzymasz rozsądny plan. Oczywiście w ten sposób tracisz wiele zdolności optymalizatora, co może nie dać optymalnych rezultatów w bardziej ogólnych sytuacjach.

Prawdopodobnie nie będziesz chciał używać FORCE ORDERzbyt często, ponieważ jest to niezwykle potężna wskazówka (dyrektywa), która ma szersze efekty niż proste wymuszanie kolejności złączeń; na przykład uniemożliwia optymalizatorowi przenoszenie agregatów i wprowadzanie agregacji częściowych. Bardzo odradzam korzystanie z tej wskazówki, z wyjątkiem wyjątkowych okoliczności i przez naprawdę doświadczonych tunerów.

Szczegółowa analiza wymagałaby więcej czasu niż obecnie, a także dostępu do kopii bazy danych zawierającej wyłącznie statystyki.

Gdzie neguje lewą stronę
Dlaczego utrudniać optymalizator?
Przy 3 lub więcej sprzężeniach optymalizator będzie dążył do przejścia w obronę i do sprzężeń w pętli, ponieważ to chroni pamięć
. Lub warunek w sprzężeniu również będzie miał tendencję do przechodzenia w sprzężenie w pętli - czy mam twarde dowody, że nastąpi to za każdym razem - nie - wciąż rzeczywistość
Dzięki wielu złączeniom możesz wyciągać warunki z miejsca do złączenia, kiedy możesz

SELECT t1.id
  FROM t1
  JOIN t2 
        ON t1.id = t2.t1_id
  JOIN t3 
        ON t2.id = t3.t2_id
  JOIN t4 
        ON t3.t4_id = t4.id 
       AND t4.id = some_GUID 

Albo jeszcze lepiej - założę się, że to spełni lub pokona twoje wskazówki lub siłę

SELECT t1.id
  FROM t1
  JOIN t2 
        ON t1.id = t2.t1_id
  JOIN t3 
        ON t2.id = t3.t2_id
       AND t3.t4_id = some_GUID

Problem ze wskazówkami polega na tym, że dotyczą one danych w określonym stanie. Napisz czyste zapytanie i pozwól optymalizatorowi wykonać swoją pracę. Czasami potrzebuje więcej statystyk, aby zrobić właściwą rzecz, ale potem się zablokuje.

Dlaczego różne szacunki. Inne plany. Zacznij od zapytań, które dają optymalizatorowi szansę na walkę.