Handleiding voor thread- en taakarchitectuur

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Planning van besturingssysteemtaken

Threads zijn de kleinste verwerkingseenheden die door een besturingssysteem worden uitgevoerd en waarmee de toepassingslogica kan worden gescheiden in verschillende gelijktijdige uitvoeringspaden. Threads zijn handig wanneer complexe toepassingen veel taken hebben die tegelijkertijd kunnen worden uitgevoerd.

Wanneer een besturingssysteem een exemplaar van een toepassing uitvoert, wordt er een eenheid gemaakt die een proces wordt genoemd om het exemplaar te beheren. Het proces heeft een uitvoeringsdraad. Dit is de reeks programmeerinstructies die door de toepassingscode worden uitgevoerd. Als een eenvoudige toepassing bijvoorbeeld één set instructies bevat die serieel kunnen worden uitgevoerd, wordt die reeks instructies verwerkt als één taak en is er slechts één uitvoeringspad (of thread) via de toepassing. Complexere toepassingen kunnen meerdere taken hebben die gelijktijdig kunnen worden uitgevoerd in plaats van serieel. Een toepassing kan dit doen door afzonderlijke processen te starten voor elke taak, een resource-intensieve bewerking of afzonderlijke threads te starten, die relatief minder resource-intensief zijn. Daarnaast kan elke thread worden gepland voor uitvoering, onafhankelijk van de andere threads die aan een proces zijn gekoppeld.

Met threads kunnen complexe toepassingen effectiever gebruik maken van een processor (CPU), zelfs op computers met één CPU. Met één CPU kan slechts één thread tegelijk worden uitgevoerd. Als één thread een langlopende bewerking uitvoert die niet gebruikmaakt van de CPU, zoals een schijf lezen of schrijven, kan een ander van de threads worden uitgevoerd totdat de eerste bewerking is voltooid. Door threads uit te voeren terwijl andere threads wachten tot een bewerking is voltooid, kan een toepassing het gebruik van de CPU maximaliseren. Dit geldt met name voor intensieve I/O-toepassingen met meerdere gebruikers, zoals een databaseserver. Computers met meerdere CPU's kunnen één thread per CPU tegelijk uitvoeren. Als een computer bijvoorbeeld acht CPU's heeft, kan deze acht threads tegelijk uitvoeren.

SQL Server taakplanning

Binnen het bereik van SQL Server is een aanvraag de logische weergave van een query of batch. Een aanvraag vertegenwoordigt ook bewerkingen die zijn vereist voor systeemthreads, zoals controlepunt of logboekschrijver. Aanvragen bestaan in verschillende statussen gedurende hun levensduur en kunnen wachttijden verzamelen wanneer resources die nodig zijn om de aanvraag uit te voeren niet beschikbaar zijn, zoals vergrendelingen of sloten. Zie sys.dm_exec_requests voor meer informatie over statussen van aanvragen.

Opdrachten

Een taak vertegenwoordigt de werkeenheid die moet worden voltooid om aan de aanvraag te voldoen. Een of meer taken kunnen worden toegewezen aan één aanvraag.

• Parallelle aanvragen hebben verschillende actieve taken die gelijktijdig worden uitgevoerd in plaats van serieel, met één bovenliggende taak (of coördinatietaak) en meerdere onderliggende taken. Een uitvoeringsplan voor een parallelle aanvraag kan seriële vertakkingen hebben: gebieden van het plan met operators die niet parallel worden uitgevoerd. De oudertaak is ook verantwoordelijk voor het uitvoeren van deze seriële operators.
• Seriële aanvragen hebben slechts één actieve taak op een bepaald tijdstip tijdens de uitvoering. Taken bestaan in verschillende statussen gedurende hun levensduur. Zie sys.dm_os_tasks voor meer informatie over taakstatussen. Taken met de status ONDERBROKEN wachten op resources die nodig zijn om de taak uit te voeren om beschikbaar te komen. Zie sys.dm_os_waiting_tasks voor meer informatie over wachttaken.

Medewerkers

Een SQL Server-werkdraad, ook wel worker of draad genoemd, is een logische weergave van een besturingssysteemdraad. Bij het uitvoeren van seriële aanvragen wordt met de SQL Server-database-engine een werkrol geactiveerd om de actieve taak (1:1) uit te voeren. Bij het uitvoeren van parallelle aanvragen in de rijmodus wijst de SQL Server Database Engine een werkrol toe om de onderliggende werknemers te coördineren die verantwoordelijk zijn voor het voltooien van taken die aan hen zijn toegewezen (ook 1:1), de bovenliggende thread (of coördinatiethread). Aan de bovenliggende thread is een bovenliggende taak gekoppeld. De hoofdthread is het toegangspunt van het verzoek en bestaat zelfs voordat de engine een query parseert. De belangrijkste verantwoordelijkheden van de bovenliggende thread zijn:

• Coördineer een parallelle scan.
• Parallelle onderliggende werknemers starten.
• Verzamel rijen van parallelle threads en verzend deze naar de client.
• Lokale en globale aggregaties uitvoeren.

Het aantal werkdraad dat voor elke taak wordt gestart, is afhankelijk van:

• Of de aanvraag in aanmerking komt voor parallelle uitvoering zoals bepaald door de queryoptimalisatie.

• Wat de werkelijke beschikbare mate van parallelle uitvoering (DOP) in het systeem is, op basis van de huidige belasting. Dit kan verschillen van de geschatte DOP, die is gebaseerd op de serverconfiguratie voor maximale mate van parallelle uitvoering (MAXDOP). De serverconfiguratie voor MAXDOP kan bijvoorbeeld 8 zijn, maar de beschikbare DOP tijdens runtime kan slechts 2 zijn, wat van invloed is op de queryprestaties. Geheugendruk en gebrek aan werknemers zijn twee omstandigheden die de beschikbare DOP tijdens runtime verminderen.

Taakplanners

Een scheduler, ook wel SOS-scheduler genoemd, beheert werkdraden die verwerkingstijd nodig hebben om taken uit te voeren. Elke planner wordt toegewezen aan een afzonderlijke processor (CPU). De tijd waarin een taak actief kan blijven in een scheduler, wordt het OS-kwantum genoemd, met een maximum van 4 ms. Nadat de kwantumtijd is verlopen, geeft een werknemer zijn tijd door aan andere werknemers die toegang tot CPU-resources nodig hebben en wijzigt zijn status. Deze samenwerking tussen werknemers om de toegang tot CPU-resources te maximaliseren, wordt coöperatieve planning genoemd, ook wel niet-preventieve planning genoemd. Op zijn beurt wordt de wijziging in de werkstatus doorgegeven aan de taak die aan die werkrol is gekoppeld en aan de aanvraag die aan de taak is gekoppeld. Zie sys.dm_os_workers voor meer informatie over werkstatussen. Voor meer informatie over schedulers, zie sys.dm_os_schedulers.

Kortom, een aanvraag kan een of meer taken hebben om werkeenheden uit te voeren. Elke taak wordt toegewezen aan een worker thread die verantwoordelijk is voor het voltooien van de taak. Elke workerthread moet worden ingepland (op een scheduler geplaatst) voor actieve uitvoering van de taak.

Houd rekening met het volgende scenario:

• Worker 1 is een langlopende taak, bijvoorbeeld een leesquery met leesdoorloop op schijftabellen. Werker 1 vindt dat de vereiste gegevenspagina's zich al in de bufferpool bevinden, dus het hoeft niet op te geven om te wachten op I/O-bewerkingen, en kan zijn volledige kwantum verbruiken voordat het opgeeft.
• Werker 2 voert kortere sub-milliseconde taken uit en moet daarom pauzeren voordat zijn volledige kwantum is uitgeput.

In dit scenario en maximaal SQL Server 2014 (12.x) mag Worker 1 de scheduler in principe in beslag maken door meer algemene kwantumtijd te hebben.

Vanaf SQL Server 2016 (13.x) omvat coöperatief plannen een LDF-planning (Large Deficit First). Met LDF-planning worden kwantumgebruikspatronen bewaakt en één worker-thread monopoliseert de planner niet. In hetzelfde scenario mag Worker 2 herhaalde kwantum's verbruiken voordat Worker 1 meer kwantum is toegestaan, waardoor Worker 1 de scheduler in een onvriendelijk patroon niet in beslag kan nemen.

Planning van parallelle taken

Stel dat een SQL Server die is geconfigureerd met MaxDOP 8 en CPU-affiniteit is geconfigureerd voor 24 CPU's (schedulers) tussen NUMA-knooppunten 0 en 1. Schedulers 0 tot en met 11 behoren tot NUMA-knooppunt 0, schedulers 12 tot en met 23 behoren tot NUMA-knooppunt 1. Een toepassing verzendt de volgende query (aanvraag) naar de database-engine:

SELECT h.SalesOrderID,
    h.OrderDate,
    h.DueDate,
    h.ShipDate
FROM Sales.SalesOrderHeaderBulk AS h
INNER JOIN Sales.SalesOrderDetailBulk AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE (h.OrderDate >= '2014-3-28 00:00:00');

Het uitvoeringsplan toont een Hash Join tussen twee tabellen en elk van de operators die parallel worden uitgevoerd, zoals aangegeven door de gele cirkel met twee pijlen. Elke Parallelisme-operator is een andere vertakking in het plan. Daarom zijn er drie vertakkingen in het volgende uitvoeringsplan.

Hoewel er drie vertakkingen in het uitvoeringsplan zijn, kunnen op elk moment tijdens de uitvoering slechts twee vertakkingen gelijktijdig worden uitgevoerd in dit uitvoeringsplan:

1. De vertakking waarin een geclusterde indexscan wordt gebruikt op de Sales.SalesOrderHeaderBulk (build-invoer van de join) wordt alleen uitgevoerd.
2. Vervolgens wordt de vertakking waarin een geclusterde indexscan wordt gebruikt op de Sales.SalesOrderDetailBulk (testinvoer van de join) gelijktijdig uitgevoerd met de vertakking waar de bitmap is gemaakt en momenteel wordt de Hash-overeenkomst uitgevoerd.

In de Showplan XML ziet u dat 16 werkrolthreads zijn gereserveerd en gebruikt op NUMA-knooppunt 0:

<ThreadStat Branches="2" UsedThreads="16">
  <ThreadReservation NodeId="0" ReservedThreads="16" />
</ThreadStat>

De threadreservering zorgt ervoor dat de database-engine voldoende werkthreads heeft om alle taken uit te voeren die nodig zijn voor de aanvraag. Threads kunnen worden gereserveerd op verschillende NUMA-knooppunten of worden gereserveerd in slechts één NUMA-knooppunt. Threadreservering wordt uitgevoerd tijdens runtime voordat de uitvoering wordt gestart en is afhankelijk van de load van de scheduler. Het aantal gereserveerde werkthreads wordt algemeen afgeleid van de formule concurrent branches * runtime DOP en sluit de bovenliggende werkthread uit. Elke vertakking is beperkt tot het aantal worker threads dat gelijk is aan MaxDOP. In dit voorbeeld zijn er twee gelijktijdige vertakkingen en is MaxDOP ingesteld op 8, daarom 2 * 8 = 16.

Bekijk ter referentie het liveuitvoeringsplan van livequerystatistieken, waarbij één vertakking is voltooid en twee vertakkingen gelijktijdig worden uitgevoerd.

De SQL Server Database Engine wijst een worker thread toe om een actieve taak uit te voeren (1:1), die kan worden geobserveerd tijdens het uitvoeren van query's door te queryen op de sys.dm_os_tasks DMV, zoals te zien is in het volgende voorbeeld.

SELECT parent_task_address, task_address,
       task_state, scheduler_id, worker_address
FROM sys.dm_os_tasks
WHERE session_id = <insert_session_id>
ORDER BY parent_task_address, scheduler_id;

Hier is het resultatenoverzicht. U ziet dat er 17 actieve taken zijn voor de vertakkingen die momenteel worden uitgevoerd: 16 onderliggende taken die overeenkomen met de gereserveerde threads, plus de bovenliggende taak of coördinatietaak.

U ziet dat aan elk van de 16 onderliggende taken een andere werkdraad is toegewezen (te zien in de worker_address-kolom), maar alle werkdraden zijn toegewezen aan dezelfde pool van acht planners (0,5,6,7,8,9,10,11) en dat de bovenliggende taak is toegewezen aan een planner buiten deze pool (3).

Een werkrolthread kan alleen actief blijven in de planner tijdens het kwantum (4 ms) en moet de scheduler opleveren nadat dat kwantum is verstreken, zodat een werkrolthread die aan een andere taak is toegewezen, actief kan worden. Wanneer het kwantum van een werkrol verloopt en niet meer actief is, wordt de desbetreffende taak in een FIFO-wachtrij geplaatst in een RUNNABLE-status, totdat deze weer naar een STATUS ACTIEF wordt verplaatst, ervan uitgaande dat de taak geen toegang nodig heeft tot resources die momenteel niet beschikbaar zijn, zoals een vergrendeling of vergrendeling, in welk geval de taak in een onderbroken status wordt geplaatst in plaats van RUNNABLE, tot die tijd zijn deze resources beschikbaar.

Kortom, een parallel verzoek initieert meerdere taken. Elke taak moet toegewezen worden aan één werkdraad. Elke werkdraad moet worden toegewezen aan een enkele planner. Daarom kan het aantal schedulers dat wordt gebruikt niet meer zijn dan het aantal parallelle taken per vertakking, ingesteld door de MaxDOP-configuratie of query hint. De coördinerende thread draagt niet bij aan de MaxDOP-limiet.

Toewijzing van threads aan CPU's

Elke instantie van SQL Server start standaard elke thread en het besturingssysteem distribueert threads van exemplaren van SQL Server tussen de processors (CPU's) op een computer, op basis van belasting. Als procesaffiniteit is ingeschakeld op besturingssysteemniveau, wijst het besturingssysteem elke thread toe aan een specifieke CPU. De SQL Server Database Engine wijst daarentegen SQL Server-werkthreads toe aan SQL Server-schedulers die de threads gelijkmatig verdelen over de CPU's, volgens het round-robin-principe.

Als u multitasking wilt uitvoeren, bijvoorbeeld wanneer meerdere toepassingen toegang hebben tot dezelfde set CPU's, verplaatst het besturingssysteem soms werkrolthreads tussen verschillende CPU's. Hoewel deze activiteit efficiënt is vanuit een besturingssysteem, kan deze activiteit de prestaties van SQL Server verminderen onder zware systeembelastingen, omdat elke processorcache herhaaldelijk opnieuw wordt geladen met gegevens. Het toewijzen van CPU's aan specifieke threads kan de prestaties onder deze omstandigheden verbeteren door het opnieuw laden van processoren te elimineren en threadmigratie tussen CPU's te verminderen (waardoor contextwisselingen worden verminderd); een dergelijke koppeling tussen een thread en een processor wordt processoraffiniteit genoemd. Als affiniteit is ingeschakeld, wijst het besturingssysteem elke thread toe aan een specifieke CPU.

De optie affiniteitsmasker wordt ingesteld met ALTER SERVER CONFIGURATION. Wanneer het affiniteitsmasker niet is ingesteld, wijst SQL Server werkthreads gelijkmatig toe aan de planners die niet gemaskeerd zijn.

Threadpooling helpt de prestaties te optimaliseren wanneer grote aantallen clients zijn verbonden met de server. Normaal gesproken wordt voor elke queryaanvraag een afzonderlijke thread van het besturingssysteem gemaakt. Met honderden verbindingen met de server kan het gebruik van één thread per queryaanvraag echter grote hoeveelheden systeembronnen verbruiken. Met de optie max worker threads kan SQL Server een pool met werkthreads maken om een groter aantal queryverzoeken te verwerken, wat de prestaties verbetert.

Gebruik van de lichtgewicht pooloptie

De overhead bij het schakelen tussen threadcontexten is mogelijk niet erg groot. De meeste exemplaren van SQL Server zien geen prestatieverschillen tussen het instellen van de lichtgewicht pooloptie op 0 of 1. De enige exemplaren van SQL Server die kunnen profiteren van lichtgewicht pooling zijn exemplaren die worden uitgevoerd op een computer met de volgende kenmerken:

• Een grote multi-CPU-server
• Alle CPU's draaien bijna op maximale capaciteit
• Er is een hoge mate van contextwisselingen

Deze systemen kunnen een kleine toename van de prestaties zien als de lichtgewicht poolwaarde is ingesteld op 1.

Thread- en glasvezeluitvoering

Microsoft Windows maakt gebruik van een numeriek prioriteitssysteem dat varieert van 1 tot en met 31 om threads te plannen voor uitvoering. Zero is gereserveerd voor gebruik van het besturingssysteem. Wanneer verschillende threads wachten om uit te voeren, verzendt Windows de thread met de hoogste prioriteit.

Standaard is elk exemplaar van SQL Server een prioriteit van 7, die wordt aangeduid als de normale prioriteit. Deze standaardinstelling geeft SQL Server-threads een hoge prioriteit om voldoende CPU-resources te verkrijgen zonder dat dit van invloed is op andere toepassingen.

De optie prioriteitsverhogingsconfiguratie kan worden gebruikt om de prioriteit van de threads te verhogen van een exemplaar van SQL Server tot 13. Dit wordt een hoge prioriteit genoemd. Deze instelling geeft SQL Server-threads een hogere prioriteit dan de meeste andere toepassingen. SQL Server-threads worden dus over het algemeen verzonden wanneer ze klaar zijn om te worden uitgevoerd en niet worden verschoven door threads van andere toepassingen. Dit kan de prestaties verbeteren wanneer een server alleen exemplaren van SQL Server uitvoert en geen andere toepassingen. Als een geheugenintensieve bewerking echter plaatsvindt in SQL Server, hebben andere toepassingen waarschijnlijk geen hoge prioriteit om de SQL Server-thread vooraf te laten gaan.

Als u meerdere exemplaren van SQL Server op een computer uitvoert en prioriteitsverbeteringen inschakelt voor slechts enkele exemplaren, kunnen de prestaties van alle exemplaren die op normale prioriteit worden uitgevoerd, nadelig worden beïnvloed. Bovendien kunnen de prestaties van andere toepassingen en onderdelen op de server achteruitgaan als de prioriteitsboost is ingeschakeld. Daarom mag het alleen worden gebruikt onder nauw gecontroleerde omstandigheden.

Dynamisch toevoegen van CPU

Hot add CPU is de mogelijkheid om CPU's dynamisch toe te voegen aan een actief systeem. Het toevoegen van CPU's kan fysiek plaatsvinden door nieuwe hardware toe te voegen, logisch door online hardwarepartitionering of virtueel via een virtualisatielaag.

Vereisten voor hot add CPU:

• Vereist hardware die ondersteuning biedt voor het dynamisch toevoegen van CPU's (hot add CPU).
• Vereist een ondersteunde versie van Windows Server Datacenter of Enterprise Edition. Vanaf Windows Server 2012 wordt hot add ondersteund in de Standard-editie.
• Vereist SQL Server Enterprise Edition.
• SQL Server kan niet worden geconfigureerd voor het gebruik van zachte NUMA. Zie Soft-NUMA (SQL Server) voor meer informatie over soft NUMA.

SQL Server gebruikt cpu's niet automatisch nadat deze zijn toegevoegd. Hiermee voorkomt u dat SQL Server CPU's gebruikt die voor een ander doel kunnen worden toegevoegd. Nadat u CPU's hebt toegevoegd, voert u de instructie RECONFIGURE uit, zodat SQL Server de nieuwe CPU's herkent als beschikbare resources.

Als het affiniteit64-masker is geconfigureerd, moet het affiniteit64-masker worden gewijzigd voor gebruik van de nieuwe CPU's.

Aanbevolen procedures voor het uitvoeren van SQL Server op computers met meer dan 64 CPU's

Toewijzing van hardware-threads aan processors

Gebruik het affiniteitsmasker en de configuratieopties voor affiniteit64-maskerservers niet om processors te binden aan specifieke threads. Deze opties zijn beperkt tot 64 CPU's. Gebruik in plaats daarvan de SET PROCESS AFFINITY optie ALTER SERVER CONFIGURATION .

Beheer van de bestandsgrootte van transactielogboeken

Vertrouw niet op automatisch groeien om de grootte van het transactielogboekbestand te vergroten. Het transactielogboek verhogen moet een serieel proces zijn. Als u het logboek uitbreidt, kunnen schrijfbewerkingen voor transacties niet verdergaan totdat de logboekextensie is voltooid. In plaats daarvan moet u de ruimte voor de logboekbestanden vooraf toewijzen door de bestandsgrootte in te stellen op een waarde die groot genoeg is om de typische werkbelasting in de omgeving te ondersteunen.

Maximale mate van parallelle uitvoering instellen voor indexbewerkingen

De prestaties van indexbewerkingen, zoals het maken of herbouwen van indexen, kunnen worden verbeterd op computers met veel CPU's door het herstelmodel van de database tijdelijk in te stellen op het bulksgewijs vastgelegde of eenvoudige herstelmodel. Deze indexbewerkingen kunnen aanzienlijke logboekactiviteit genereren en logboekconflicten kunnen invloed hebben op de optimale graad van parallelisme (DOP) die door SQL Server wordt bepaald.

Naast het aanpassen van de maximale mate van parallelle uitvoering (MAXDOP) -serverconfiguratie, kunt u overwegen om de parallelle uitvoering voor indexbewerkingen aan te passen met behulp van de optie MAXDOP. Zie Parallelle indexbewerkingen configurerenvoor meer informatie. Voor meer informatie en richtlijnen over het aanpassen van de maximale mate van parallelle serverconfiguratie, zie De maximale mate van parallelle serverconfiguratie configureren.

Optie voor maximaal aantal werkthreads

SQL Server configureert dynamisch de serverconfiguratieoptie maximale werkdraad bij het opstarten. SQL Server gebruikt het aantal beschikbare CPU's en de systeemarchitectuur om deze serverconfiguratie te bepalen tijdens het opstarten, met behulp van een gedocumenteerde formule.

Deze optie is een geavanceerde optie en moet alleen worden gewijzigd door een ervaren databaseprofessional.

Als u vermoedt dat er een prestatieprobleem is, is dit waarschijnlijk niet de beschikbaarheid van worker threads. De oorzaak is waarschijnlijker iets zoals I/O dat ervoor zorgt dat de worker threads wachten. U kunt het beste de hoofdoorzaak van een prestatieprobleem vinden voordat u de instelling voor maximale werkthreads wijzigt. Als u echter handmatig het maximum aantal werkthreads moet instellen, moet deze configuratiewaarde altijd worden ingesteld op een waarde van ten minste zeven keer het aantal CPU's dat aanwezig is op het systeem. Voor meer informatie, zie De maximale werkerthreads configureren.

Vermijd het gebruik van SQL Trace en SQL Server Profiler

Het is raadzaam om SQL Trace en SQL Profiler niet te gebruiken in een productieomgeving. De overhead voor het uitvoeren van deze hulpprogramma's neemt ook toe naarmate het aantal CPU's toeneemt. Als u SQL Trace in een productieomgeving moet gebruiken, beperkt u het aantal traceringsevenementen tot een minimum. Profileer en test elke traceringsbeurtenis zorgvuldig onder belasting en vermijd combinaties van gebeurtenissen die de prestaties aanzienlijk beïnvloeden.

Het aantal tempdb gegevensbestanden instellen

Het aantal bestanden is afhankelijk van het aantal (logische) processors op de computer. Als het aantal logische processors kleiner is dan of gelijk is aan acht, gebruikt u in de regel hetzelfde aantal gegevensbestanden als logische processors. Als het aantal logische processors groter is dan acht, gebruikt u acht gegevensbestanden en verhoogt u het aantal gegevensbestanden met veelvouden van 4 totdat het conflict wordt beperkt tot acceptabele niveaus of wijzigingen aanbrengt in de workload/code. Houd ook rekening met andere aanbevelingen voor tempdb, beschikbaar in tempdb-prestaties optimaliseren in SQL Server.

Door echter zorgvuldig rekening te houden met de gelijktijdigheidsbehoeften, tempdbkunt u de overhead voor databasebeheer verminderen. Als een systeem bijvoorbeeld 64 CPU's heeft en meestal slechts 32 query's gebruikt tempdb, zal het verhogen van het aantal tempdb bestanden naar 64 de prestaties niet verbeteren.

SQL Server-onderdelen die meer dan 64 CPU's kunnen gebruiken

De volgende tabel bevat SQL Server-onderdelen en geeft aan of ze meer dan 64 CPU's kunnen gebruiken.