Dlaczego tabela temp jest bardziej skutecznym rozwiązaniem problemu Halloween niż chętna szpula?

Rozważ następujące zapytanie, które wstawia wiersze z tabeli źródłowej tylko wtedy, gdy nie ma ich już w tabeli docelowej:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

Jeden z możliwych kształtów obejmuje połączenie scalające i chętną szpulę. Chętny operator szpuli jest obecny, aby rozwiązać problem Halloween :

Na moim komputerze powyższy kod działa w około 6900 ms. Kod repro do tworzenia tabel znajduje się na dole pytania. Jeśli jestem niezadowolony z wydajności, mogę spróbować załadować wiersze do wstawienia do tabeli tymczasowej zamiast polegać na chętnej szpuli. Oto jedna możliwa implementacja:

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

Nowy kod wykonuje się w około 4400 ms. Potrafię uzyskać aktualne plany i skorzystać z Actual Time Statistics ™, aby sprawdzić, gdzie spędzany jest czas na poziomie operatora. Pamiętaj, że zapytanie o rzeczywisty plan powoduje znaczne obciążenie tych zapytań, więc sumy nie będą pasować do poprzednich wyników.

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

Plan zapytań z chętnym buforem wydaje się poświęcać znacznie więcej czasu operatorom wstawiania i buforowania w porównaniu do planu korzystającego z tabeli tymczasowej.

Dlaczego plan z tabelą temp jest bardziej wydajny? Czy i tak chętna szpula nie jest głównie tylko wewnętrznym stołem tymczasowym? Myślę, że szukam odpowiedzi, które koncentrują się na wewnętrznych. Widzę, jak różnią się stosy połączeń, ale nie mogę zrozumieć dużego obrazu.

Jestem na SQL Server 2017 CU 11 na wypadek, gdyby ktoś chciał wiedzieć. Oto kod do wypełnienia tabel używanych w powyższych zapytaniach:

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

To właśnie nazywam Manual Halloween Protection .

Przykład użycia tego polecenia z instrukcją aktualizacji można znaleźć w moim artykule Optymalizacja zapytań o aktualizację . Należy zachować ostrożność, aby zachować tę samą semantykę, na przykład blokując tabelę docelową przed wszystkimi jednoczesnymi modyfikacjami podczas wykonywania oddzielnych zapytań, jeśli jest to istotne w twoim scenariuszu.

Dlaczego plan z tabelą temp jest bardziej wydajny? Czy i tak chętna szpula nie jest głównie tylko wewnętrznym stołem tymczasowym?

Szpula ma niektóre cechy tabeli tymczasowej, ale te dwie nie są dokładnymi odpowiednikami. W szczególności szpula jest zasadniczo nieuporządkowaną wkładką rząd po rzędzie w strukturze b-drzewa . Korzysta z optymalizacji blokowania i rejestrowania, ale nie obsługuje optymalizacji ładowania masowego .

W rezultacie często można uzyskać lepszą wydajność, dzieląc zapytanie w naturalny sposób: masowo ładuje nowe wiersze do tymczasowej tabeli lub zmiennej, a następnie wykonuje zoptymalizowane wstawianie (bez wyraźnej ochrony Halloween) z obiektu tymczasowego.

Dokonanie tego rozdziału pozwala również na dodatkową swobodę dostrajania fragmentów oryginalnego wyciągu, niezależnie od siebie.

Na marginesie warto zastanowić się, w jaki sposób można rozwiązać problem Halloween za pomocą wersji rzędowych. Być może przyszła wersja SQL Server zapewni tę funkcję w odpowiednich okolicznościach.

Jak wspomniał Michael Kutz w komentarzu, możesz również zbadać możliwość wykorzystania optymalizacji wypełniania dziur, aby uniknąć wyraźnego HP. Jednym ze sposobów osiągnięcia tego celu w wersji demonstracyjnej jest utworzenie unikalnego indeksu (klastrowanego, jeśli chcesz) w IDkolumnie A_HEAP_OF_MOSTLY_NEW_ROWS.

CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

Dzięki tej gwarancji optymalizator może korzystać z wypełniania dziur i udostępniania zestawów wierszy:

MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
    ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

Co ciekawe, nadal będziesz w stanie osiągnąć lepszą wydajność w wielu przypadkach, stosując starannie wdrożoną Ręczną Ochronę Halloween.

Aby nieco rozszerzyć odpowiedź Paula, wydaje się, że część różnicy między upływem czasu między podejściem do bufora a tempem sprowadza się do braku wsparcia dla DML Request Sortopcji w planie buforowania. W przypadku nieudokumentowanej flagi śledzenia 8795 czas, który upłynął w podejściu do tabeli temp, skacze z 4400 ms do 5600 ms.

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1, QUERYTRACEON 8795);

Należy zauważyć, że nie jest to dokładnie równoważne z wkładką wykonaną przez plan buforowania. To zapytanie zapisuje znacznie więcej danych w dzienniku transakcji.

Ten sam efekt można zobaczyć na odwrót z pewnymi sztuczkami. Można zachęcić SQL Server do używania sortowania zamiast szpuli do ochrony Halloween. Jedna implementacja:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (987654321) 
maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
ORDER BY maybe_new_rows.ID, maybe_new_rows.ID + 1
OPTION (MAXDOP 1, QUERYTRACEON 7470, MERGE JOIN);

Teraz plan zawiera operator TOP N Sort w miejsce szpuli. Sortowanie jest operatorem blokującym, więc szpula nie jest już potrzebna:

Co ważniejsze, teraz mamy wsparcie dla tej DML Request Sortopcji. Ponownie patrząc na rzeczywistą statystykę czasu, operator wstawiania zajmuje teraz tylko 1623 ms. Wykonanie całego planu zajmuje około 5400 ms bez żądania rzeczywistego planu.

Jak wyjaśnia Hugo , chętny operator szpuli zachowuje porządek. Można to najłatwiej zauważyć w TOP PERCENTplanie. Szkoda, że ​​pierwotne zapytanie ze szpuli nie może lepiej wykorzystać posortowanej natury danych w szpuli.