Dlaczego to zapytanie nie używa buforu indeksu?

Zadaję to pytanie, aby lepiej zrozumieć zachowanie optymalizatora i zrozumieć ograniczenia wokół buforów indeksu. Załóżmy, że umieściłem na stercie liczby całkowite od 1 do 10000:

CREATE TABLE X_10000 (ID INT NOT NULL);
truncate table X_10000;

INSERT INTO X_10000 WITH (TABLOCK)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

I wymuś połączenie zagnieżdżonej pętli z MAXDOP 1:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Jest to raczej nieprzyjazna czynność wobec SQL Server. Połączenia zagnieżdżonej pętli często nie są dobrym wyborem, gdy obie tabele nie mają żadnych odpowiednich indeksów. Oto plan:

Zapytanie zajmuje 13 sekund na mojej maszynie z pobranymi 100000000 wierszami ze szpuli tabeli. Nie rozumiem jednak, dlaczego zapytanie musi być wolne. Optymalizator zapytań ma możliwość tworzenia indeksów w locie za pośrednictwem buforów indeksów . To zapytanie wydaje się być idealnym kandydatem do buforowania indeksu.

Poniższe zapytanie zwraca te same wyniki co pierwsze, ma bufor buforowania i kończy się w niecałą sekundę:

SELECT *
FROM X_10000 a
CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca
OPTION (LOOP JOIN, MAXDOP 1);

To zapytanie ma również bufor indeksu i kończy się w niecałą sekundę:

SELECT *
FROM X_10000 a
INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (LOOP JOIN, MAXDOP 1);

Dlaczego oryginalne zapytanie nie ma buforu indeksu? Czy istnieje zestaw udokumentowanych lub nieudokumentowanych podpowiedzi lub flag śledzenia, który da bufor buforowy? Znalazłem to powiązane pytanie , ale nie w pełni odpowiada ono na moje pytanie i nie mogę zmusić tajemniczej flagi śledzenia do działania dla tego zapytania.

Jak wiadomo wyszukiwanie optymalizatora nie jest wyczerpujące. Próbuje rzeczy, które mają sens w kontekście i które często przynoszą dywidendy na podstawie rzeczywistych zapytań. Wymuszanie łączenia w pętli między dwiema jednokolumnowymi nieindeksowanymi tabelami sterty nie jest takim scenariuszem. To powiedziawszy, oto kilka szczegółów:

SQL Server lubi transformację stosuje się do złączeń wcześniej, ponieważ zna więcej sztuczek z łączeniami. Później może rozważyć przekształcenie sprzężenia z powrotem w aplikację. Różnica między dwoma skorelowane parametrów (odnośniki zewnętrzne). Ma to sens, gdy po wewnętrznej stronie znajduje się odpowiedni indeks. Twój przykład nie ma indeksów, więc optymalizator nie jest przekonany do zbadania tłumaczenia aplikacji.

Proste (nie dotyczy) złączenie ma predykat złączenia w operatorze złączenia zamiast odniesień zewnętrznych. Optymalizacja szpuli dla braku zastosowania jest zwykle leniwą szpulą stołową, ponieważ po wewnętrznej stronie nie ma predykatu, tylko przy łączeniu.

Optymalizator nie rozważa tworzenia indeksu w locie, aby umożliwić zastosowanie; raczej sekwencja zdarzeń jest zwykle odwrotna: należy zastosować transformację, ponieważ istnieje dobry indeks.

Czasami możesz zachęcić do zastosowania zamiast łączenia, używając APPLYskładni w zapytaniu. Nieudokumentowana flaga śledzenia 9114 może w tym pomóc, zniechęcając optymalizatora do tłumaczenia logicznego zastosowania na złączenie z góry. Na przykład:

SELECT * 
FROM dbo.X_1000 AS a
CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b
OPTION (QUERYTRACEON 9114);

Preferowana jest szpula indeksująca do zastosowania, ponieważ odniesienie zewnętrzne oznacza, że ​​wybór jest zastosowany po wewnętrznej stronie połączenia. Często zobaczysz to poprzez, SelToIndexOnTheFlyale istnieją inne ścieżki. Zobacz mój artykuł Eager Index Spool and The Optimizer .