SQL Server 2019 – Intelligent Query Processing – Batch Mode on Rowstore

W dzisiejszym artykule kontynuujemy testy związane z Intelligent Query Processing – ostatnio udało się opisać testy związane ze Scalar Function Inlining dziś natomiast powiemy sobie parę słów o równie ciekawym mechanizmie jakim bez wątpienia jest Batch Mode on Rowstore.

Zanim przejdziemy do demonstracji kilka faktów wprowadzających w omawianą funkcjonalność. Batch mode to nic innego jak możliwość przetwarzania przez operatory planu zapytania wielu wierszy jednocześnie (nawet do 900 wierszy) co jest nieco innym podejściem niż w klasycznym podejściu gdzie operator mógł pracować tylko z jednym wierszem jednocześnie. Funkcjonalność ta pojawiła się wraz z SQL Server 2012 i indeksem kolumnowym i do wersji 2017 była z tą technolgią nierozerwalnie związana. Opisywany tryb ma wiele zalet jak chociażby to, że dzięki przetwarzaniu większej ilości wierszy lepiej wykorzystywane były wielordzeniowe procesory CPU i ich cache, które stały się standardem w dzisiejszych czasach. Ponadto omawiana technologia oferuje nam dodatkowe benefity takie jak mniejsza ilość instrukcji jakie musi wykonać procesor aby przetworzyć pojedynczy wiersz.  Jedną z nowości związanych z SQL Server 2019 było pojawienie się przetwarzania batchowego bez konieczności posiadania indeksu kolumnowego na odpytywanych tabelach. Zanim ktoś zwróci mi uwagę powiem, że oczywiście istnieją pewne tricki, które umożliwiają osiągnięcie trybu batchowego nawet w zapytaniach, które nie są “do końca” związane z indeksem kolumnowym (również udało mi się o tym pisać tutaj) ale jak sama nazwa wskazuje są to sztuczki, a nie prawdziwe wsparcie.

Jak zatem uzyskać Batch Mode bez indeksu kolumnowego? Wystarczy aby nasza baza miała Compatibility Level ustawiony na 150. Optymalizator zbada używając heurystyk na to czy dane zapytanie działać lepiej w trybie przetwarzania Batch czy może w starym Row. Z tego co udało mi się znaleźć to te tajemnicze heurystyki bazują przede wszystkim na następujących zmiennych:

• rozmiar tabel w zapytaniu,
• operatory na planie zapytania,
• liczebność wierszy.

Te oraz zapewne dodatkowe czynniki wpływają na wybór trybu batch. Oczywiście w trakcie poszukiwania planu zapytania nadal działa stara zasada “good enough plan” czyli jeśli optymalizator znajdzie akceptowalny plan z uwzględnieniem trybu row to zaprzestanie poszukiwań planu z batch mode. Może się również zdarzyć sytuacja, że tryb batch nie będzie dawał zbyt wiele i wtedy optymalizator może pozostać przy klasycznym trybie. Pamiętajmy, że Batch Mode na stertach i klasycznych indeksach opartych o B-Tree nie oznacza wcale, że będziemy mieli wszystkie benefity indeksów kolumnowych w naszych zapytaniach jak kompresja, Predicate pushdown etc. Batch mode to tylko jeden z jedna z wielu funkcjonalności związanych z CI. Dodatkową cechą trybu batchowego o której często się zapomina jest to, że zawsze plany będą w pełni optymalizowane nawet jak są bardzo proste czyli nie będziemy mieli do czynienia z Trivial Plan optimization.

Sama technologia na moment pisania niniejszego artykułu ma pewne ograniczenia i jest to brak możliwości jej użycia gdy w grę wchodzą tabele In-memory OLTP, gdy występują w zapytaniu kolumny LOB, XML, SPARSE.

Koniec z teorią, przejdźmy do praktyki. Na początku przygotujemy sobie dane testowe na podstawie bazy AdventureWorks2017. Aby to zrobić przygotowałem skrypt, który powiększymy jedną z dostępnych tam tabel:

DROP TABLE IF EXISTS Sales.SalesOrderDetailExtended
GO

SELECT * INTO Sales.SalesOrderDetailExtended
FROM [Sales].[SalesOrderDetail]
GO

SET IDENTITY_INSERT Sales.SalesOrderDetailExtended ON

INSERT INTO Sales.SalesOrderDetailExtended WITH (TABLOCK)
(
[SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [LineTotal], [rowguid], [ModifiedDate] 
)
SELECT 
[SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [LineTotal], [rowguid], [ModifiedDate] 
FROM [Sales].[SalesOrderDetail]
GO 200

SET IDENTITY_INSERT Sales.SalesOrderDetailExtended OFF

Zgodnie z tym co napisałem wcześniej, aby opisywany mechanizm mógł zostać wykorzystany zmieniamy Compatibility level na 150:

ALTER DATABASE [ADVENTUREWORKS2017] SET COMPATIBILITY_LEVEL = 150;

Właściwie to wszystko czego potrzebujemy. Pierwszym zapytaniem jakiego plan obejrzymy jest proste zliczenie wierszy:

SELECT COUNT(*) FROM Sales.SalesOrderDetailExtended

Plan powyższego zapytania przedstawia się w następujący sposób:

 

Widzimy, że właściwość Actual oraz Estimated Execution Mode wskazuje, że operator Table Scan (Heap) został wykonany w trybie Batch. Dla oszczędności miejsca mogę Wam powiedzieć, że agregacja przy pomocy Hash Match również została wykonana w tym właśnie trybie. Wiemy zatem w którym miejscu sprawdzać tryb przetwarzania teraz sprawdźmy sobie różnice w stosunku do standardowego zapytania – aby to zrobić uruchomiłem poniższe zapytania razem w jednym batchu:

SELECT 
	 SC.CustomerID
	,MAX(SOH.OrderDate) AS OrderDate
	,SUM(SOD.OrderQty) AS OrderQty
	,count(*)
FROM [Sales].[SalesOrderDetailExtended] AS SOD
JOIN [Sales].[SalesOrderHeader] AS SOH
	ON SOD.SalesOrderID=SOH.SalesOrderID
JOIN [Sales].[Customer] AS SC
	ON SC.CustomerID=SOH.CustomerID
GROUP BY
	SC.CustomerID
ORDER BY OrderDate DESC
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));


SELECT 
	 SC.CustomerID
	,MAX(SOH.OrderDate) AS OrderDate
	,SUM(SOD.OrderQty) AS OrderQty
	,count(*)
FROM [Sales].[SalesOrderDetailExtended] AS SOD
JOIN [Sales].[SalesOrderHeader] AS SOH
	ON SOD.SalesOrderID=SOH.SalesOrderID
JOIN [Sales].[Customer] AS SC
	ON SC.CustomerID=SOH.CustomerID
GROUP BY
	SC.CustomerID
ORDER BY OrderDate DESC
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

Mamy dwa niemal identyczne zapytania z tym, że w jednym użyliśmy hinta DISALLOW_BATCH_MODE, a w drugim ALLOW_BATCH_MODE (myślę, że to że pierwszy wyłącza, a drugi umożliwia wykorzystanie trybu batch wyjaśniać nie trzeba). Poniżej możecie zobaczyć analizę kosztową:

Dolny plan to oczywiście ten z Batch Mode i jasno widzimy, że pod kątem porównania kosztów mamy bezapelacyjnego lidera. Za chwilę sprawdzimy sobie również konkretne liczby jednak chciałbym chwilę poświęcić temu ostrzeżeniu na planie z batchowym trybem przetwarzania. Z moich obserwacji wynika, że w trybie Batch przydziały pamięci są zawsze nieco większe co jest warte odnotowania szczególnie, że mając czasem problem ze zrzutami na dysk związanymi z za niskimi estymatami to Batch mode może być jakimś wyjściem z takiej sytuacji. Aby porównać konkretne liczby zapisałem jeden z planów na dysku twardym według poniższego schematu:

Otrzymanemu porównaniu można się przyjrzeć na poniższym zrzucie ekranowym:

 

Czerwonym kolorem zaznaczyłem Memory Grant, który jest wyraźnie większy w trybie Batch. Ponadto pod kolorem zielonym możemy dostrzec znaczną różnicę w czasie wykonania na korzyść trybu batch. Czyli niemal wszystko zostało wykonane zgodnie z oczekiwaniami.

Widzimy zatem, że zmiana jak najbardziej na plus ale jak zawsze polecam testować.Udało mi się znaleźć informację, że tryb BAtch Mode on Rowstore wprowadza magiczną liczbę wierszy powyżej której tryb batch będzie rozpatrywany. Liczbą tą jest 131072, jak mamy mniej wierszy to możemy pożegnać się z trybem batch. Sprawdźmy to zachowanie na przykładzie. Moją rozszerzoną tabelę użyłem jako źródło i pobrałem 141071 wierszy używając składni SELECT INTO:

DROP TABLE IF EXISTS [Sales].[SalesOrderDetailExtended2]

SELECT TOP 131071 * 
INTO [Sales].[SalesOrderDetailExtended2]
FROM [Sales].[SalesOrderDetailExtended]

Następnie powstałą tabelę Sales.SalesOrderDetailExtended2 użyłem w takim samym zapytaniu testowym jak wyżej:

SELECT SC.CustomerID, 
       MAX(SOH.OrderDate) AS OrderDate, 
       SUM(SOD.OrderQty) AS OrderQty, 
       COUNT(*)
FROM [Sales].[SalesOrderDetailExtended2] AS SOD
     JOIN [Sales].[SalesOrderHeader] AS SOH ON SOD.SalesOrderID = SOH.SalesOrderID
     JOIN [Sales].[Customer] AS SC ON SC.CustomerID = SOH.CustomerID
GROUP BY SC.CustomerID
ORDER BY OrderDate DESC;

Na planie zapytania rzeczywiśćie mamy tryb Row:

Gdy zwiększymy liczbę wierszy o jeden czyli mając  131072 wierszy…

DROP TABLE IF EXISTS [Sales].[SalesOrderDetailExtended2]

SELECT TOP 131072 * 
INTO [Sales].[SalesOrderDetailExtended2]
FROM [Sales].[SalesOrderDetailExtended]

… otrzymaliśmy tryb batch:

Dosyć ciekawe prawda? Wygląda na to, że ta liczba jest zapisana wewnątrz heurystyk i jest głównym czynnikiem wpływającym na wybór trybu.

Zmierzając ku końcowi chciałbym wspomnieć o nowych database scope configurations gdzie możemy standardowo wyłączyć tą funkcjonalność:

ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON|OFF

W różnych miejscach możecie również natrafić na nieudokumentowany hint OVERRIDE_BATCH_MODE_HEURISTICS, który usuwa wszelkie obostrzenia wobec m.in ilości wierszy i wymusza tryb batch. Do testów może to być jakieś wyjście jednak wiadomo, że powinniśmy tego unikać. Mamy też nieudokumentowaną flagę 11080, która wyłącza tryb Batch ale jej chyba w ogóle nie warto używać.

Podsumowując widzimy kolejną bardzo fajną funkcjonalność w SQL Server. Podobnie jak inne ulepszenia tego typu ma dużo plusów jednakże z całą pewnością nie jest panaceum na wszystkie problemy. Warto też pamiętać, że to nie jest tak, że wszystkie zapytania z Columnstore otrzymują Batch mode (np. kursory nigdy nie były i nie będą przetwarzały danych w Batchach) tak samo będzie w przypadku Batch on Rowstore. Pamiętajmy również, że głównym beneficjentem trybu batchowego są zapytania analityczne czyli dosyć ciężkie agregaty na większej ilości wierszy jeśli Wasz workload takowych nie posiada to nie ma sensu myśleć o usprawnieniach trybu Batch. Pozdrawiam!