Performance Tuning

Uge 12: Parallelle Execution Planer

I denne uge stepper jeg lidt op af stigen i sværhedsgrad og vil gerne præsentere Parallelle Execution Planer til dig. En query med en Parallel Execution Plan er en query der bruger flere tråde (threads) på SQL serveren til at foretage de nødvendige operationer i den fysiske execution plan.

I første afsnit gennemgår jeg de mest gænge operatorer i parallelle execution planer og efterfølgende lidt flere detaljer om hvordan SQL serveren beslutter anvendelsen af disse.

Parallelle Operators

Mange gange oplever jeg en misforståelse omkring parallelle execution planer, at de anvender et givent sæt af tråde (f.eks 8 tråde på en 8 kerne server) til hele execution planen. Det er ikke helt sådan virkeligheden på SQL serveren er stykket sammen. SQL serveren tildeler mange såkaldte Worker tråde (worker threads) til alle operators som er bygget til parallel eksekvering. Dette betyder at en stor parallel execution plan kan bruge et meget stort antal tråde. SQL Serveren skelner mellem to typer operators i en parallel plan:

  • Parallelism-aware operators
    • En stor del af de traditionelle operators kan også arbejde med flere tråde, de er parallelism-aware – Index Scan, Index Seek, Nested Loop, Hash Join og Sort – for bare at nævne nogen af dem
  • Exchange operators
    • Disse operators bliver brugt til at fordele og sammensætte rækker mellem de forskellige tråde i en parallel execution plan

SQL Serveren har implementeret 3 Exchange Operators:

  • Distribute Streams: Bliver brugt til at fordele en enkelt tråds rækker ud på flere tråde i en såkalt Multi-threaded region i den parallelle execution plan
  • Repartition Streams: Bliver brugt til at gen-fordele rækkerne mellem trådene (hvis for eks den førliggende operator er Parallel Hash Join)
  • Gather Streams: Bliver brugt til at samle rækker fra flere tråde til en enkelt tråd – fra multi-threaded region til single-threaded region i execution planen

Når man ser en parallel execution plan, vil man altid se en kombination af begge typer operators. Enhver execution plan skal producere et single-threaded output, derfor vil man altid finde en Gather Streams omkring slutningen af en parallel execution plan (altså helt til venstre):

Parallelle Execution Planer

Om Optimizeren genererer en parallel execution plan eller ej, afhænger også af indholdet i den afsendte query – såsom:

  • T-SQL og SQLCLR UDFs (dog med undtagelse af de UDFs som kan compiles til Inline UDFs)
  • Indbyggede funktioner som OBJECT_ID(), ERROR_NUMBER(), @@TRANCOUNT

Der er også flere query elementer som forcerer en single-treaded region i den parallele execution plan_

  • System Table Scans
  • Sequence Funktioner
  • Backward Scans
  • Recursive Queries
  • Table Values Functions (igen med undtagelse af dem der kan inlines)
  • TOP operator

Og jo færre single-threaded regioner i en parallel execution plan, jo hurtigere vil queryen levere data. Prøv at huske på det næste gang du synes din query er for langsom.

Hvornår bliver en query parallel?

Enhver execution plan får en såkald Cost Factor tildelt fra SQL serveren. Denne Cost Factor er en værdi i form af et tal, der fortæller SQL serveren hvor “dyr” en execution plan er. Jo højere værdi, jo dyrere (i ressourcer) er det at ekekvere den specifikke execution plan.

På SQL serveren er der en konfiguration kaldet Cost-Threshold for Parallelism, som definerer græsen i cost factoren hvorved Optimizeren begynder at overveje parallelle execution planer. Default er 5 (hvilket er meget lavt) som betyder at en Cost Factor højere end 5 vil give optimizeren mulighed for at lave en parallel execution plan, så længe parallellisering er muligt.

Når en parallel execution plan bliver compilet af Query Optimizeren vil konfigurationen af Max Degree of Parallelism (MAXDOP) definere hvor mange tråde, der kan blive anvendt i hver enkelt parallelle operator i execution planen.

Som nævnt tidligere vil enhver operator i den parallelle execution plan køre med flere tråde, og ikke kun i den samlede plan. Tråde kan blive delt imellem operators når de ikke længere bruges, og låses derfor ikke til en enkelt operator i execution planen.

Som standard er MAXDOP sat til 0 ved installation. Derfor vil SQL serveren per default parallellisere en query på alle kerner som er assignet til SQL serveren. Dette kan give performance problemer hvis man arbejder i et NUMA system (Non Uniform Memory Accesss) – typisk eksempel er en viruel server. Derfor er det god skik at sætte MAXDOP til antallet af NUMA noder i systemet (inkl. kerner/NUMA noder fra Hyperthreading).

Opsummering – Parallelle Execution Planer

Dette indlæg har været et af de mere tunge af slagsen, men også meget interessant (synes jeg selv…). Jeg er kommet omkring de forskelle operatorer i en parallel execution plan og skrevet lidt om hvordan SQL serveren behandler disse.

Har du fået blod på tanden og vil læse mere om parallelle execution planer og deres arbejde på SQL serveren er herunder lidt links til nogen gode indlæg:

Og en note – vi er allerede 3 måneder inden i denne serie af blogindlæg – og stadig kun halvvejs. Jeg håber I er med på resten af serien.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish