Dwa sposoby na uzyskanie Batch Mode na planie zapytania

Indeksy kolumnowe są jedną z moich ulubionych technologii wbudowanych w SQL Server. Oprócz oszałamiających zysków wydajnościowych związanych z kompresją czy też eliminacją kolumn i segmentów przyniosły one ze sobą nowy tryb przetwarzania zwany Batch Mode. Alternatywą dla tego przetwarzania było tradycyjne przetwarzanie wierszowe. Jak zapewne wiecie poszczególne “klocki” planu zapytań są iteratorami i w dużym uproszczeniu działają one w taki sposób, że logiczny przepływ kontroli rozpoczyna się od lewej do prawej gdzie poszczególne operatory proszą o kolejny wiersz operator poprzedzający itp. Przetwarzanie takie odbywa się wiersz po wierszu, a tryb batchowy pozwala na przetwarzanie większymi porcjami bo od 64 do 900 wierszy! O zyskach wydajnościowych z tym związanych chyba nie trzeba pisać – tylko poprzez zmianę trybu przetwarzania otrzymujemy wiele co postaram się przedstawić w dalszej części artykułu.

Tryb batchowy jest nierozerwalnie związany z indeksem kolumnowym i póki co nie może on być używany gdy indeks kolumnowy nie pojawia się na którymkolwiek obiekcie uczestniczącym w zapytaniu. Od pewnego czasu możemy tworzyć tabele na których będą założone zarówno tradycyjne indeksy rowstore jak i indeksy columnstore (podejście takie nazywamy Operational Analytics – pisałem o nim tutaj) w praktyce jednak dosyć rzadko zdarza się, że taki scenariusz jest implementowany – szczególnie w przypadku baz OLTP. Czy to oznacza, że tracimy możliwość implementacji trybu batchowego w systemach tego typu? Oczywiście, że nie! Społeczność znalazła kilka wytrychów umożliwiających użycie Batch Mode – sprawdźmy jak to wygląda.

Najpierw napiszmy sobie proste zapytanie aby zobaczyć w jakim trybie jest ono przetwarzane:

SELECT ROW_NUMBER() OVER(PARTITION BY i.DeliveryMethodID ORDER BY i.InvoiceID) AS RN,
       i.InvoiceID
FROM Sales.Invoices i
     INNER JOIN Sales.InvoiceLines il ON il.InvoiceID = i.InvoiceID
WHERE i.InvoiceID < 100000;
GO

Plan takiego zapytania przedstawia się następująco:

Jak rozpoznać czy poszczególne operatory działają w trybie batchowym? Wystarczy poszukać odpowiedniej informacji we właściwościach:

Jak możecie zauważyć każdy operator, który działa w trybie wierszowym. Nie jest to zaskoczenie ze względu na to, że tabele Sales.Invoices oraz Sales.InvoiceLines nie posiadają indeksów kolumnowych co automatycznie wyklucza użycie pożądanego przez nas trybu.

Aby mimo wszystko zapewnić tryb batchowy możemy stworzyć sobie pustą tabelę na, której założymy klastrowany indeks kolumnowy:

CREATE TABLE dbo.ForBatchModeOnly
(ID INT)
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Test ON dbo.ForBatchModeOnly
GO

Następnie dorzućmy naszą pustą tabelę do zapytania testowego jako LEFT JOIN z niemożliwym do spełnienia warunkiem złączenia. SQL Server jest na tyle inteligentnym oprogramowaniem, że wykryje ten fakt jeszcze przed wykonaniem połączenie i całkowicie ten zapis zignoruje.

SELECT ROW_NUMBER() OVER(PARTITION BY i.DeliveryMethodID ORDER BY i.InvoiceID) AS RN,
       i.InvoiceID
FROM Sales.Invoices i
     INNER JOIN Sales.InvoiceLines il ON il.InvoiceID = i.InvoiceID
/*DUMMY TABLE JOIN*/
LEFT JOIN dbo.ForBatchModeOnly ON 1 = 2 
WHERE i.InvoiceID < 100000
GO

Zobaczymy jak wygląda plan zapytania:

Zauważyliśmy pewne zmiany, przede wszystkim zniknęły operatory Segment i Sequence Project odpowiedzialne za funkcję okna zastąpione poprzez Window Aggregate. Jest to jedna z moich ulubionych nowości SQL Server 2016 bo właśnie ten operator wydajnie przetwarza funkcje okna i do tego działa w trybie batchowym! Sprawdźmy to:

Pojawił się tryb batchowy! Tylko i wyłącznie przez sam fakt istnienia indeksu kolumnowego na jednej z tabel – mimo, że na planie wykonania nie został on w żadnym wypadku użyty. Porównajmy teraz to samo zapytanie z trybem batchowym i bez niego pod kątem kosztowym (tryb batchowy na górze, tryb wierszowy na dole):

14% do 86% pokazuje dosyć dobitnie który plan jest mniej kosztowny – ze względu na to, że nie mamy rozjazdów w estymatach wierszy możemy traktować koszt jako miarodajny wskaźnik. Czasowo również wygląda to lepiej po stronie trybu batchowego który wykonał się w czasie 3958ms w porównaniu do 5058ms trybu wierszowego. Na powyższy zrzucie zaznaczyłem jedną bardzo istotną rzecz – chodzi mianowicie o zrzut do tempdb który pojawił się w planie trybu wierszowego. Zrzut ten wynika z tego, że rozjechały nam się statystyki po złączeniu – dosyć często spotykany widok. Dlaczego natomiast takiego zrzutu nie ma w trybie batchowym? Odpowiedź jest prosta, wynika to z tego, że gdy mamy tryb batchowy to jednocześnie zwiększamy przydziały pamięci do poszczególnych operatorów co jest świetną techniką optymalizacyjną w przypadku zrzutów do tempdb w dalszych częściach planu. Zobaczmy jak wyglądały memory granty na operatorze Sort:

Różnica jest dosyć duża prawda? Ale dzięki temu łatwo wyeliminować zrzuty do tempdb – bo czasem zbyt duży przydział pamięci jest mniejszym problemem niż zrzut do tempdb i spowodowane tym faktem zwiększone IO. Jeśli chodzi o wybór trybu batchowego to przedstawione rozwiązanie jest całkiem proste do implementacji ale często zdarza się tak, że nie możemy zmodyfikować istniejących zapytań – co wtedy? Mamy do dyspozycji kolejny wytrych! Window aggregate posiada jeszcze jedną zaletę, może działać na wielu wątkach czego nie potrafili jego poprzednicy.

Od wersji SQL Server 2016 możemy implementować filtrowane indeksy kolumnowe – dlaczego więc nie wykorzystać tego faktu do wywołania trybu batchowego? Stwórzmy sobie na jednej z naszych tabel filtrowany indeks kolumnowy:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCIX_test
ON Sales.InvoiceLines (LineProfit)
WHERE LineProfit=0 AND LineProfit=1
GO

Nie jest to jednak standardowy indeks kolumnowy, w jego definicji zobaczycie sprzeczny warunek filtrujący myślę, że w tym momencie niektórym z was zapaliła się zielona lampka – tak taki zapis nie spowolni wstawiania danych a znacząco wpłynie na wydajność zapytań odczytujących. Indeks tego typu zostanie zignorowany niemal w każdej operacji na tabeli. Sprawdźmy nasz plan wykonania i upewnijmy się, że tryb batchowy został zaimplementowany w taki sposób jak byśmy sobie tego życzyli – tym razem do sprawdzenia tego faktu użyjemy widoku Plan Tree w Plan Explorerze:

Wszystkie trzy operatory działają w trybie batchowym! Filtrowany indeks kolumnowy jest dużo lepszym rozwiązaniem przede wszystkim dlatego, że jest niemal transparentny i nie wymaga ingerencji w kod. Oczywiście w sieci możecie znaleźć jeszcze inne sposoby jednakże na ten moment wydaje mi się, że te dwa podejścia warte są rozważenia. Myślę, że w przyszłości nie trzeba będzie stosować żadnego rodzaju obejść aby móc uzyskać odpowiedni tryb przetwarzania. Na ten moment musimy sobie jakoś radzić – mam nadzieję, że przedstawione techniki będą dla was przydatne.