Adaptive Query Processing – Adaptive Join

Jakiś czas temu pisałem już o tym, że SQL Server 2017 wprowadził (a raczej zapoczątkował) sporo zmian jeśli chodzi o wykonywanie zapytań z wydajnościowego punktu widzenia. Chodzi mianowicie o rodzinę mechanizmów nazwanych Adaptive Query Processing. Pod tą nazwą kryją się możliwości dostosowania planu zapytania w zależności od okoliczności podczas wykonywania zapytania. Technologia ta z produkcyjnego punktu widzenia obecnie składa się z trzech elementów tj. Interleaved Execution (o którym pisałem już tutaj), Memory Grant Feedback oraz Adaptive Join.Ciekawe w tym wszystkim jest to, że dla developerów jest to właściwie transparentne i działa bez żadnych zmian w kodzie. Ponadto wraz z SQL Server 2019 dostaniemy do dyspozycji kilka kolejnych mechanizmów próbujących w inteligentny sposób wspomóc przetwarzanie naszych zapytań. Jeden z mechanizmów (Memory Grant Feedback) zostanie udostępniony również dla trybu Row, a nie tylko Batch jak ma to miejsce obecnie. Całą rodzinę technologii możecie zobaczyć na poniższym zestawieniu zaproponowanym przez Microsoft (źródło):

 

Niniejszy artykuł chciałbym poświęcić tematowi dostępnego od SQL Server 2017 Adaptive Joins ponieważ niesie on ze sobą dosyć ciekawe udogodnienia i możliwości. Serdecznie zapraszam do lektury.

Na samym początku kilka słów niezbędnej teorii. Operacja złączenia zbiorów danych w SQL Server może być wykonana na kilka różnych sposobów i nie mówię tutaj o typach złączeń dostępnych w składni języka TSQL, ale o fizycznym algorytmie ich wykonania. Ogólnie rzecz biorąc mamy trzy algorytmy takie jak:

• Nested Loops,
• Merge Join,
• Hash Match.

O każdym z nich już pisałem w ramach niniejszego bloga, jeśli chcecie sobie odświeżyć tą wiedzę to artykuły znajdują się tutaj,tutaj oraz tutaj. O tym który algorytm zostanie wybrany decydują konkretne czynniki związane przede wszystkim z cardinality estimation, a więc z szacunkową liczbą wierszy. Jeden algorytm świetnie sprawdza się dla mniejszych zbiorów, drugi dla zbiorów posortowanych, a jeszcze jeden działa najlepiej na dużych nieuporządkowanych zbiorach. Wszystko działa naprawdę dobrze jednak praktyka pokazuje, że bardzo często napotykamy na pewne problemy spowodowane tym, że mamy np. nieaktualne statystyki lub zapytanie jest na tyle skomplikowane, że w późniejszej części zapytania estymacja się po prostu rozjeżdża.

Błędna estymacja może spowodować, że zamiast Hash Match dostaniemy Nested Loops (lub odwrotnie) co ma oczywiście negatywny wpływ na wydajność zapytania lub powoduje, że niepotrzebnie rezerwujemy zasoby. W przypadku błędnego dopasowania algorytmu złączenia z pomocą przychodzi nam funkcjonalność, która jest bohaterem niniejszego artykułu czyli Adaptive Join. Krótko mówiąc jest to nic innego jak dostosowanie algorytmu złączenia w trakcie wykonywania zapytania.

Adaptive Join opiera się na wyborze tego czy dane powinny być połączone przy użyciu Nested Loops czy też Hash Match:

Spójrzmy na jej działanie na konkretnym przykładzie, do testów użyjemy AdventureWorks2017. Na samym początku zmienimy sobie COMPATIBILITY LEVEL aby zobrazować działanie naszego zapytania testowego bez Adaptive Join (ustawimy go na 130 czyli na SQL Server 2016):

ALTER DATABASE AdventureWorks2017
SET COMPATIBILITY_LEVEL=130

W dalszej części tworzymy zapytanie testowe, które składa się z szeregu złączeń oraz prostego grupowania oraz sortowania:

SELECT 
	 ST.Name AS Territory
	,PS.Name AS Subcategory
	,P.Color AS ProductColor
	,SUM(SOD.OrderQty*SOD.UnitPrice) AS OrderQty
	,AVG(SOD.UnitPrice) AS AvgUnitPrice
FROM [Production].[Product] AS P
INNER JOIN [Production].[ProductSubcategory] AS PS
	ON P.[ProductSubcategoryID]=PS.ProductSubcategoryID
INNER JOIN [Sales].[SalesOrderDetail] AS SOD
	ON SOD.ProductID=P.ProductID
INNER JOIN [Sales].[SalesOrderHeader] AS SOH
	ON SOH.SalesOrderID=SOD.SalesOrderID
INNER JOIN [Sales].[Customer] AS SC
	ON SC.CustomerID=SOH.CustomerID
INNER JOIN [Sales].[SalesTerritory] AS ST
	ON ST.TerritoryID=SC.TerritoryID
GROUP BY
	 ST.Name
	,PS.Name
	,P.Color
ORDER BY
	 OrderQty DESC
	,AvgUnitPrice

Raczej nic specjalnego w powyższym kodzie nie ma ot zapytanie jakich wiele. W dalszym kroku “zepsujmy” nieco statystyki związane z tabelami użytymi w zapytaniu tak aby SQL Server “myślał”, że tabele mają po jednym wierszu. Dokonamy tego wykorzystując składnię UPDATE STATISTICS:

UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH ROWCOUNT = 1, PAGECOUNT = 2

UPDATE STATISTICS [Sales].[SalesOrderHeader] WITH ROWCOUNT = 1, PAGECOUNT = 2

UPDATE STATISTICS [Sales].[Customer] WITH ROWCOUNT = 1, PAGECOUNT = 2

UPDATE STATISTICS [Production].[Product] WITH ROWCOUNT = 1, PAGECOUNT = 2

Po wykonaniu zapytania otrzymaliśmy następujący plan zapytania:

Łatwo dostrzec, że zgodnie z tym czego się spodziewaliśmy (ze względu na niską liczebność) wszystkie złączenia zostały wykonane używając algorytmu Nested Loops. Z rzeczy wartych odnotowania warto wspomnieć, że operator Sort zrzucił dane do tempdb co jest spowodowane oczywiście błędnymi estymacjami i zbyt małym grantem pamięci. Kolejną rzeczą na jaką możemy zwrócić uwagę są statystyki operacji IO:

Dosyć duża ilość odczytów związana jest z samym sposobem przetwarzaniem w pętli Nested Loops i na pierwszy rzut oka nie jest to odpowiedni algorytm dla tego zapytania. No dobrze, co więc możemy zrobić? W tym konkretnym przypadku możemy zaktualizować statystyki jednakże czasem zdarza się tak, że przy bardziej skomplikowanych zapytaniach estymacje się rozjadą mimo aktualnych statystyk. Jest to typowy scenariusz wykorzystania Adaptive Join. Zanim zmienimy COMPATIBILITY MODE stwórzmy sztuczny Columnstore Index, po to aby uzyskać batchowowy tryb przetwarzania (pisałem o tym tutaj) bo jak już wspomniałem omawiany na ten moment mechanizm działa tylko z tym trybem:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCI_Fake 
ON [Production].[Product] ([ProductID]) 
WHERE [ProductID]=-1
GO

Indeks jest filtrowany z warunkiem niemożliwym do spełnienia, a więc będzie zawsze pusty ale jednocześnie da nam odpowiedni tryb przetwarzania. Dalej zmieniamy COMPATIBILITY MODE:

ALTER DATABASE AdventureWorks2017
SET COMPATIBILITY_LEVEL=140

Po ponownym uruchomieniu zapytania testowego dostajemy następujący plan:

Tym razem zamiast Nested Loops widzimy nowy operator tj. Adaptive Join zanim przejdziemy do jego analizy rzućmy okiem na IO:

Ilość logicznych odczytów jest już zdecydowanie mniejsza i może nam dawać powody do zadowolenia. Zastanawiające mogą być również te wyszarzone operatory Index Seek – czym one są? Są to operatory, które mogą być wykorzystane w przypadku gdy jako algorytm użyty zostanie Nested Loops, w przypadku Hash Match są po prostu nieużywane (użyty jako do fazy Probe złączenia Hash Match został Index Scan). Poniżej w celach poglądowych możecie zobaczyć odwrotną sytuację tzn. gdy użyty został Nested Loops – w tym miejscu Index Seek jest już jak najbardziej użyty, a “bezrobotny” pozostał Index Scan:

Wróćmy jednak do naszego oryginalnego planu. Zobaczmy jak wyglądają właściwości jednego z występujących na planie operatorów Adaptive Join:

Interesujące z punktu widzenia opisywanego mechanizmu są następujące właściwości:

• Is Adaptive – TRUE wskazuje, że mamy do czynienia z Adaptive Join,
• Estimated Join Type – typ złączenia, który był estymowany przez optymalizator na podstawie rzeczywistych statystyk,
• Actual Join Type – typ złączenia, który w rzeczywistości został wykorzystany,
• Adaptive Treshold Rows – próg w postaci liczby wierszy poniżej którego zostanie użyty Nested Loops, a powyżej którego wykorzystany zostanie Hash Match.

Ciekawą właściwością jest wspomniany wyżej próg ilości wierszy. Wartość na powyższym zrzucie oznacza, że Nested Loops zostanie wykorzystany tylko wtedy gdy będzie… 1 wiersz. Zobaczmy czy zapytanie rzeczywiście tak się zachowa jak dopiszemy odpowiedni warunek WHERE:

SELECT 
....
WHERE SOH.AccountNumber='10-4030-028389'
....
ORDER BY
	 OrderQty DESC
	,AvgUnitPrice

Rzeczywiście tak się też stało:

Fizycznie wykorzystany został Nested Loops, dlaczego? Ponieważ estymowana liczba wierszy wyniosła 1 czyli nie przekroczyła progu wynoszącego 1.70519. W porządku, skąd zatem bierze się wartość progowa? Na podstawie rzeczywistych statystyk! Wcześniej poleceniem UPDATE STATISTICS sfałszowaliśmy statystyki tak aby optymalizator myślał, że w każdej z tabel użytych w zapytaniu jest jeden wiersz. Spróbujmy teraz zmienić je w drugą stronę tj. tak aby każda tabela miała estymaty w milionach:

UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH ROWCOUNT = 100000000, PAGECOUNT = 2000000

UPDATE STATISTICS [Sales].[SalesOrderHeader] WITH ROWCOUNT = 100000000, PAGECOUNT = 2000000

UPDATE STATISTICS [Sales].[Customer] WITH ROWCOUNT = 100000000, PAGECOUNT = 2000000

UPDATE STATISTICS [Production].[Product] WITH ROWCOUNT = 100000000, PAGECOUNT = 2000000

Po ponownym wykonaniu zapytania plan wygląda już nieco inaczej jednakże nie jest to na ten moment ważne, przyjrzyjmy się obliczonemu progowi:

Próg wyniósł ponad 561 tysięcy czyli dosyć dużo, pytanie w takim miejscu jest takie czy powinniśmy tutaj użyć AJ czy może polegać na standardowym podejściu? Wszystko oczywiście zależy.

Musimy zdawać sobie sprawę, że przedstawione przykłady są ekstremalne jednakże powyższy przykład świadczy jedynie o tym, iż statystyki nadal są niezmiernie ważne i nic tego nie zmieni. W niektórych przypadkach możemy oczywiście nie chcieć używać AJ z różnych powodów i mamy możliwość wyłączenia tego mechanizmu. Dostępnych jest kilka możliwości np.:

• włączyć lokalnie lub globalnie flagę 9398,
• użyć hinta DISABLE_BATCH_MODE_ADAPTIVE_JOINS,
• użyć Database Scope Configuration o nazwie DISABLE_BATCH_MODE_ADAPTIVE_JOINS.

Warto zwrócić uwagę, że operator Adaptive Join nie pojawi się zawsze i nie zastąpi tradycyjnego podejścia. Istnieje wiele czynników, które wykluczają jego pojawienie. Możemy je śledzić przy pomocy Extended Events, a konkretnie eventu adaptive_join_skipped:

Wartości jakie może przyjąć ten event to:

• eajsrExchangeTypeNotSupported
• eajsrHJorNLJNotFound
• eajsrInvalidAdaptiveThreshold
• eajsrMultiConsumerSpool
• eajsrOuterCardMaxOne
• eajsrOuterSideParallelMarked
• eajsrUnMatchedOuter

np. w trakcie moich testów uzyskałem eajsrUnMatchedOuter wykonując funkcję ROW_NUMBER:

Czynników jest wiele ale najczęściej sprowadza się to do tego, że część naszego zapytania ze złączeniem nie może być wykonana zarówno z wykorzystaniem HASH Match oraz Nested Loops. Polecam tą dyskusję na forum gdzie użytkownik podał kilka przykładów które Adaptive Join nie obsługuje ale mieć w tyle głowy, że technologia się zmienia i to co kiedyś nie było obsługiwane teraz już może być.

To by było na tyle jeśli chodzi o Adaptive Joins. Technologia z całą pewnością jest krokiem w dobrą stronę jednakże należy być świadomym jak ona działa i że nie jest lekiem na wszystkie problemy. Jak zawsze ogólna rada jest taka aby testować, testować i jeszcze raz testować, a potem cieszyć się z ewentualnych zysków wydajnościowych. Pozdrawiam.