Dlaczego mój plan zapytania nie jest równoległy?

SQL Server od już bardzo dawna potrafi wykonywać zapytania wykorzystując wiele wątków. O całym mechanizmie równoległości (ang. parallelism) opowiedziano już naprawdę wiele podczas różnego rodzaju prezentacji i artykułów, jednakże mam wrażenie, że nadal nie do końca jest to temat znany. W ramach niniejszego artykułu postanowiłem zebrać kilka faktów na ten temat oraz wypunktować kilka przyczyn, które powodują,  że nasz plan jest wykonywany na pojedynczym wątku mimo, że mógłby być równoległy. Zapraszam do zapoznania się z niniejszym tekstem.

Na samym początku powiedzmy sobie o dwóch arcyważnych ustawieniach sterujących omawianym mechanizmem, a mianowicie Max Degree Of Parallelism oraz Cost Treshold Of Parallelism. Pierwsze z nich może być ustawione na poziomie całego serwera, bazy danych, zapytania czy też może być przypisany do określonej puli zasobów w Resource governor, pozwala ono nam sterować tym ile wątków maksymalnie może być wykorzystanych w ramach pojedynczego zapytania. To, że ustawimy MAXDOP na np. 16 to wcale nie oznacza, że nasze zapytanie wykorzysta właśnie tyle wątków (może być wykorzystane mniej ze względu np. na brak dostępnych wątków lub brak pamięci operacyjnej). Ogólnie rzecz biorąc MAXDOP ustawiony sztywno na wartość 1 spowoduje, że nasz plan będzie wykonywany na jednym wątku. Przetestujmy to zachowanie póki co bez jawnego wskazania MAXDOP:

USE WideWorldImporters
GO

SELECT SI.InvoiceLineID,
       SI.InvoiceID,
       SI.Description,
	  COUNT(*) AS Counter
FROM [Sales].[InvoiceLines] AS SI
GROUP BY SI.InvoiceLineID,
       SI.InvoiceID,
       SI.Description
ORDER BY SI.InvoiceID DESC;

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

Plan jest równoległy co symbolizują graficzne znaczniki przy każdym z operatorów oraz występowanie operatora Gather Streams, który robi nic innego jak “zbiera” rezultat wykonania sortowania przez każdy z wątków. Po wejściu we właściwości operatora SORT możemy zauważyć ile wierszy było posortowanych przez każdy z wątków:

Mamy trzy wątki z czego dwa z nich były wątkami roboczymi, a jeden (Thread 0) był wątkiem kontrolnym. Wszystko wygląda dobrze i nie ma tutaj nic co mogło nas zaniepokoić. Wykonajmy to zapytanie raz jeszcze – tym razem z hintem MAXDOP 1:

Oczywiście nasz plan jest jednowątkowy. Wraz z tym przykładem wiemy, że pierwszą przyczyną tego, że nasz plan nie jest równoległy może być wymuszenie przetwarzania jednowątkowego na dowolnym z poziomów. Oczywiście po samym graficznym planie ciężko wywnioskować, że akurat ustawienie MAXDOP spowodowało wykonanie jednowątkowe, jednakże od wersji SQL Server 2012 mamy do dyspozycji właściwość NonParallelPlanReason, która wskaże nam w niektórych przypadkach dlaczego mamy taki stan rzeczy:

Oczywiście to nie jedyna wartość jaką ta właściwość może przyjąć, ale o tym później. W tym miejscu chciałbym również wspomnieć jaka wartość  MAXDOP jest wykorzystywana w momencie gdy mamy ją ustawioną na kilku poziomach.

Hierarchia wygląda następująco:

Jak interpretować powyższy rysunek? Ustawienie na poziomie serwera za pomocą sp_configure obowiązuje wtedy gdy nie jest ustawiony MAXDOP na żadnym innym poziomie. Ustawienie  na poziomie bazy danych ma wyższy priorytet niż na poziomie serwera, w przypadku ustawień na obu poziomach to ustawienie na poziomie bazy danych będzie miało wyższy priorytet. W przypadku gdy  MAXDOP został ustawiony w zapytaniu jako hint to ma on wyższy priorytet niż ustawienie serwerowe i bazodanowe. Taki sam priorytet ma ustawienie w Resource Governor – w tym miejscu może pojawić się pytanie co gdy ustawimy MAXDOP na poziomie zapytania i na poziomie RG? Wtedy zastosowane zostanie ustawienie z mniejszą wartością. Myślę, że warto znać te zależności aby uniknąć nieporozumień, szczególnie że nie mamy nigdzie informacji na jakim poziomie MAXDOP został ustawiony.

Wracając do meritum, kolejną przyczyną otrzymania jednowątkowego planu jest ustawienie affinity mask na tylko jeden rdzeń:

ALTER SERVER CONFIGURATION 
SET PROCESS AFFINITY CPU = 0
GO

Po uruchomieniu zapytania bez żadnych hintów oczywiście użyty został jeden wątek,a przedstawiona wcześniej właściwość otrzymała wartość Estimated DOP Is One:

Jest to całkiem intuicyjne i bez problemu możemy się dowiedzieć dlaczego nie użyliśmy wielu wątków.

Na samym początku wspomniałem, że obok MAXDOP bardzo ważną właściwością jest Cost Treshold Of Parallelism. Właściwość ta definiuje powyżej jakiego kosztu rozważany będzie plan równoległy. Znamy dobre praktyki i wiemy, że powinna to być wartość stosunkowo wysoka tak aby nie marnować zasobów na proste zapytania. Jak możecie się domyślać wystarczy, że nasze zapytanie będzie poniżej CTOP i będzie wykonywane na jednym wątku. Aby to przetestować sprawdźmy koszt naszego zapytania testowego:

Koszt niewiele przekroczył wartość 14, zwiększmy więc próg do 15:

sp_configure 'cost threshold for parallelism',15
RECONFIGURE

Oczywiście otrzymaliśmy plan jednowątkowy. Niestety tym razem nie dostaliśmy właściwości NonParallelPlanReason:

Jeżeli więc nie uzyskacie wskazówki dlaczego plan jest seryjny to na samym początku polecam sprawdzić koszt i to czy przekracza on próg czy też nie.

Kolejnym przypadkiem jaki chciałbym Wam przedstawić jest sytuacja gdzie mimo przekroczenia progu plan jest seryjny. Napisałem proste zapytanie, które robi trzykrotnie CROSS JOINA tej samej tabeli (używając CROSS JOIN możemy mieć pewność, że koszt będzie rósł do gigantycznych rozmiarów) :

SELECT *
FROM [Sales].[InvoiceLines] AS SI
CROSS JOIN [Sales].[InvoiceLines] AS SI2
CROSS JOIN [Sales].[InvoiceLines] AS SI3
CROSS JOIN [Sales].[InvoiceLines] AS SI4

Koszt takiego zapytania wyniósł 11838300000000000!

Przekroczyliśmy nasz próg wielokrotnie – jak w takim razie wygląda plan? Pomijając jego brzydotę jest on jednowątkowy:

Również tym razem nie mamy wskazówki o powodzie planu jednowątkowego. Poradzimy sobie w inny sposób, a mianowicie niejako wymusimy wykonanie na wielu wątkach. Użyjemy do tego celu flagi 8649:

SELECT *
FROM [Sales].[InvoiceLines] AS SI
CROSS JOIN [Sales].[InvoiceLines] AS SI2
CROSS JOIN [Sales].[InvoiceLines] AS SI3
CROSS JOIN [Sales].[InvoiceLines] AS SI4
OPTION(QUERYTRACEON 8649)

 

Koszt wyżej przedstawionego planu wielowątkowego również ma bardzo wysoką wartość 109584000000000000:

Przy takich wartościach łatwo się pogubić więc podpowiem Wam, że plan równoległy jest dużo kosztowniejszy – i to jest powód dla którego SQL Server postanowił użyć planu jednowątkowego. Co ciekawe zapytanie na wielu wątkach bardzo często może wykonać się szybciej jednakże sam czas nie jest jedynym wyznacznikiem kosztu. W sytuacjach gdzie chcemy niejako wskazać SQL Server żeby preferował użycie wielu wątków, możemy użyć wspomnianej wcześniej flagi jednakże włączenie flagi na poziomie zapytania bardzo wysokich uprawnień i wtedy z pomocą przychodzi nam hint ‘ENABLE_PARALLEL_PLAN_PREFERENCE‘ dostępny w CU2 dla SQL Server 2016 SP1.

Oprócz wymienionych przeze mnie powodów pojawienia się planu jednowątkowego jest cała masa funkcjonalności SQL Server, które wykluczają użycie wielu wątków. Czasami zdarza się, że określony operator nie działa na wielu wątkach. Jednakże w wielu przypadkach użycie jednej tego typu funkcjonalności spowoduje całkowite wykluczenie równoległości z planu! Poniżej przedstawiam pięć wybranych przykładów:

1) Skalarne funkcje użytkownika

Całkowicie wykluczają wielowątkowość i nie ma od tego wyjątków.

sp_configure 'cost threshold for parallelism',0
RECONFIGURE

CREATE FUNCTION udf_Trim (@String NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
BEGIN
    RETURN LTRIM(RTRIM(@String))
END

Otrzymany komunikat nie jest zbyt szczegółowy i będzie się pojawiał dosyć często, jednakże widząc go może nam się zapalić zielona lampka i możemy podejrzewać co się dzieje.

2) Wsteczne wyszukiwanie – Backward Scan

Kolejna przyczyna to BACKWARD SCAN czyli sytuacja gdzie skanujemy indeks od tyłu. Dla przykładu poniżej sortujemy po InvoiceLineID malejąco gdzie na tej kolumnie został założony indeks w kierunku rosnącym:

SELECT SI.InvoiceLineID,
       SI.InvoiceID,
       SI.Description AS Description,
	  COUNT(*) AS Counter
FROM [Sales].[InvoiceLines] AS SI
GROUP BY SI.InvoiceLineID,
       SI.InvoiceID,
       SI.Description
ORDER BY SI.InvoiceLineID DESC

Efekt? Plan jednowątkowy:

3) Użycie funkcji i tabel systemowych

W zapytaniach angażujących tabele użytkownika z danymi – unikajcie zapisów  z funkcjami systemowymi:

SELECT 
	  OBJECT_ID('Sales.InvoiceLines'),
	  SI.InvoiceLineID,
       SI.InvoiceID,
       SI2.Description,
	  COUNT(*) AS Counter
FROM [Sales].[InvoiceLines] AS SI
CROSS JOIN [Sales].[InvoiceLines] AS SI2
GROUP BY SI.InvoiceLineID,
       SI.InvoiceID,
       SI2.Description
ORDER BY SI.InvoiceLineID ASC,SI.InvoiceID ASC

4) Funkcje rankingu

Czyli ROW_NUMBER, RANK, DENSE_RANK i NTILE. Nie są one wykonywane na wielu wątkach bo i sposób ich działania jest jednowątkowy – na szczęście tylko operatory  Sequence Project i Segment nie używają parallelismu:

Oczywiście w SQL Server 2016 mamy operator Window Aggregate, który już obsługuje wiele wątków.

5) Operator TOP

Sytuacja podobna jak powyżej i skutkuje dokładnie tym samym:

Oczywiście to nie wszystkie ograniczenia tego typu w SQL Server – polecam zapoznać się z postem Erika Darlinga, który wymienił nieco więcej operacji w SQL Server, które nie są wykonywane równolegle (artykuł znajdziecie tutaj zapoznajcie się też z komentarzami – ciekawe spostrzeżenia).

Na sam koniec chciałbym Wam również wylistować wybrane (nie wszystkie) wartości jakie może przyjąć właściwość NonParallelPlanReason:

• MaxDopSetToOne– ustawienie MAXDOP 1
• EstimatedDOPIsOne – estymowany MAXDOP 1 (powyżej przykład)
• NoParallelFastForwardCursor – brak wsparcia równoległości dla kursorów “Fast forward”
• NoParallelCursorFetchByBookmark – brak wspracia równoległości dla operacji Fetch By bookmark
• ParallelismDisabledByTraceFlag – włączona flaga 8687
• NoParallelCreateIndexInNonEnterpriseEdition – brak równoległego tworzenia indeksów w edycjach innych niż Enterprise
• NoParallelPlansInDesktopOrExpressEdition – brak możliwości tworzenia równoległych planów dla wersji Express i Desktop
• CLRUserDefinedFunctionRequiresDataAccess – funkcje CLR z dostępem do danych
• TSQLUserDefinedFunctionsNotParallelizable – funkcje użytkownika typu skalarnego lub MSTV nie wspierają równoległości
• DMLQueryReturnsOutputToClient – operacje DML zwracające coś do narzędzia klienckiego (klauzula OUTPUT)
• MixedSerialAndParallelOnlineIndexBuildNotSupported – plany zarówno będące po części równoległe i seryjne nie mogą występować dla pojedynczej operacji przebudowania indeksu online
• CouldNotGenerateValidParallelPlan – plan równoległy nie przeszedł weryfikacji
• NoParallelForMemoryOptimizedTables – brak równoległego planu dla tabel memory-optimized

Jak możecie zauważyć powyżej pułapek jest cała masa. Ogólnie rzecz biorąc omawiany temat jest bardzo szeroki i można by pisać o bardzo wielu aspektach związanych ze współbieżnością mam jednak nadzieję, że te kilka przypadków, które tutaj zawarłem okaże się dla was przydatne.