Gå til indhold

Parallelisering? Hvorfor ikke bare bruge alle CPU’er hver gang?

Parallelisering er når SQL Serveren benytter mere end én CPU til at udføre sin forespørgsel. Det lyder jo teoretisk som den absolut bedste og hurtigste måde at hente data på, men parallelisering har også en pris.
Billede af Unit IT

Unit IT

SQL

For at forstå hvad parallelisering er, så forestil dig dette eksempel:


Du har en bunke med 1000 bolde i 3 farver; rød, gul og grøn. Du vil gerne vide hvor mange røde der er, så du går i gang med at sortere dem, for så at tælle dem sammen til sidst. De tager selvfølgelig lang tid, så du får 2 venner til at hjælpe dig. I deler bunken i 3 lige store og så går i gang med at tælle dem sammen. Til sidst samler du deres resultater og lægger det sammen og så har du summen af de røde. Det går væsentligt hurtigere, da I har paralleliseret opgaven ved at dele byrden i 3 dele. På samme måde gør SQL Serveren så hvis vi skal oversætte eksemplet har vi paralleliseret ud over 3 CPU’er. Det lyder og er smart… MEN det har også en pris. For når der skal paralleliseres ud så skal ”bunken” først deles ud og så skal der tælles sammen til sidst når alle CPU’er er færdige med deres arbejde.


Overordnet set er der 2 ting, der styrer hvad SQL Serveren må, når det kommer til parallelisering. Max Degree of Parallelism (Maxdop) og Cost threshold for Parallesism. På ældre versioner af SQL Serveren blev dette kun sat på Server niveau, men på 2016 versionen og nyere er dette en database sætting også. Default installeres SQL Serveren med Maxdop = 0, som betyder alle CPU’er må bruges og Cost threshold for Parallesism = 5

Parallelisering-1

Du finder indstillingerne ved at højreklikke på Server i SSMS og vælge properties -> Advanced:

Parallelisering-2

 

Parallelisering-3

Parallelisering-4

Bare for at tage den med det samme; de anbefalede indstillinger i dag er MAXDOP = 0 og Cost threshold for Parallesism = 50. Jeg skal nok vende tilbage til hvad og hvorfor om lidt – men først: 

Hvad er Cost threshold for Parallesism? 

Når SQL Serveren skal udføre et Query forsøger den altid at hente data så hurtigt som muligt og ved at bruge færrest ressourcer. For at vide hvad er den optimale måde at hente data er, benyttes et sæt af regler. Hver logisk operator, altså de operatorer, som henter eller berører data, får en ”Cost” f.eks. vil et Clustered Index Seek måske have en cost på 3, hvor en Index scan måske har en cost på 8. SQL Serveren prøver så mange forskellige måder at hente det samme data ud fra det regelsæt at hver mulighed får en ”Cost” ogtil sidst tages så den kombination, der giver den laveste cost. Det er ikke helt fair over for det utrolig dygtige folk hos Microsoft, som har udviklet denne motor, som laver beregningen. Det er ret komplekst hvad der sker, men forhåbentlig giver det en ide om hvad der sker. Nu hvor vi ved at der kommer en Cost på de fleste operatorer i planen (execution plan) kan vi tage fat på Cost threshold for Parallesism. For netop dette tal fortæller hvornår SQL Serveren må teste at bruge parallelisering. Når værdien for Cost threshold for Parallesism er 5 betyder det, at en operation skal ”koste” mere end 5 før SQL Serveren afsøger muligheden for at hente/bearbejde data parallelt. På ældre hardware var 5 et fint tal, men i dag på hardware med væsentlig flere kræfter er standarden 50. her ved 50 fungerer de fleste forespørgsler fint. Bemærk at defaultindstillingerne når der er installeret en SQL Server er 5! Du kan selv kigge efter som beskrevet oven for. 

Selv om en cost på en operator er mere end Cost threshold for Parallesism indstillingen er det ikke ensbetydende med at SQL Serveren paralleliserer – det må teste muligheden, men hvis det er hurtigere at bruge 1 CPU så vil den gøre det.   

Lad os prøve et eksempel:

Først bruger jeg dette Query mod Adventureworks2008R2 databasen:

 

SELECT *
FROM Sales.SalesOrderDetail sod    
	INNER JOIN Production.Product p
			ON sod.ProductID = p.ProductID

Det giver mig denne plan, og ved at holde musen over den første operator kan vi se at der ikke er paralleliseret:

 

Parallelisering-5

Degree of Parallesism = 1 Betyder at der ikke er paralleliseret.

Hvis jeg benytte et query hint OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) så tvinger jeg SQL Serveren til at parallelisere se her:

Parallelisering-6

Degree of Parallelism = 4 her er brugt 4 CPU’er for at hente data.. hvis vi prøver at kigge på de to planer samtidig.


Planen uden parallelisering:

Parallelisering-7

Planen med parallelisering:

Parallelisering-8

I denne plan kommer der 2 andre operatorer:

Parallelisering-9Parallelisering-10

Bemærk at der under hver af dem er en cost, og netop denne cost er årsagen til at det ikke altid kan betale sig at parallelisere.For hvis det koster mere at dele og samle data end det gør at hente det med 1 CPU så er det selvfølgelig det SQL Serveren skal gøre.

Jeg sætter statistics io til og køre igen begge query

Her uden parallelisering:

 

SQL Server Execution Times:
   CPU time = 922 ms,  elapsed time = 3869 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 

Her med  parallelisering:

SQL Server Execution Times:
   CPU time = 1625 ms,  elapsed time = 3894 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Bemærk elapsed time den parallelle plan er meget lidt langsommere, men hvis vi hæfter os ved CPU time så er det tydeligt, at den parallelle plan har brugt mere tid på CPU’erne, fordi det er summen af alle CPU’er, man kan faktisk godt opleve at CPU time er højere end elapsed time for en plan, igen fordi det er summen af alle CPU’er.

Hvis vi kigger i executionplanen’s XML for den parallelle plan så kan vi se hvad hver CPU laver:

Parallelisering-11

Bemærk at hver CPU har fat i et antal af rækker (ActualRows) vi kan sammenligne med planen som ikke kører parallelt:

Parallelisering-12

Her er det tydeligt at der er brugt en CPU. Se (ActualRows)

Parallelisering-13

Kan vi selv kontrollere hvordan der paralleliseres?

Ja, det kan vi godt, som udgangspunkt så gør SQL Serveren det selv utrolig godt så hvis server indstillingerne er sat ”rigtigt” behøver du ikke gøre så meget mere (MAXDOP = 0, Cost threshold for Parallesism = 50).

MEN, der kan selvfølgelig være nogle forespørgsler som kan have glæde af en anden paralleliseringsmulighed end den der er sat på serveren, hvis MAXDOP f.eks. er sat til 1, men du har en forespørgsel der vil performe bedre med en anden MAXDOP.

Jeg ændrer min test Server til maxdop = 1 og prøver igen:

 

SELECT *
FROM Sales.SalesOrderDetail sod    
	INNER JOIN Production.Product p
			ON sod.ProductID = p.ProductID
ORDER BY Style
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE') )

Nu paralleliseres planen ikke:

parallelisering 14

 

Hvis jeg derimod benytter et Queryhint Option (MAXDOP 2) så overskriver jeg indstillingerne og siger at der gerne må bruges 2 CPU’er og får nu en parallel plan:

og kigger vi i XML’en for executionplanen er det ganske rigtigt at 2 CPU’er har været i brug

Parallelisering-16

og kigger vi i XML’en for executionplanen er det ganske rigtigt at 2 CPU’er har været i brug:

Parallelisering-17

Parallelisering er godt, når der er en tilpas stor mængde data der skal bearbejdes. Husk at det ”koster” for SQL Serveren at først dele arbejdet ud for igen at samle det sammen.

Læs eventuelt mere om madxop her på docs https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

Og her mere om cost threshold for Parallelism her: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15

Tak fordi du læste med. Som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, hvis du har spørgsmål eller udfordringer med dit SQL miljø.

Relaterede indlæg