Spinlockconflicten op SQL Server vaststellen en oplossen

Dit artikel bevat uitgebreide informatie over het identificeren en oplossen van problemen met betrekking tot spinlockconflicten in SQL Server-toepassingen op systemen met hoge gelijktijdigheid.

Achtergrond

In het verleden hebben standaard Windows Server-computers slechts één of twee microprocessor-/CPU-chips gebruikt en CPU's zijn ontworpen met slechts één processor of "kern". De toename van de computerverwerkingscapaciteit is bereikt door gebruik te maken van snellere CPU's, die grotendeels mogelijk zijn gemaakt door vooruitgang in de transistordichtheid. Na 'Moore's Law' hebben transistordichtheid of het aantal transistors dat op een geïntegreerd circuit kan worden geplaatst, elke twee jaar consistent verdubbeld sinds de ontwikkeling van de eerste cpu van één chip voor algemeen gebruik in 1971. In de afgelopen jaren is de traditionele benadering van het verhogen van de computerverwerkingscapaciteit met snellere CPU's uitgebreid door het bouwen van computers met meerdere CPU's. Vanaf dit schrijven biedt de Intel Nehalem CPU-architectuur plaats aan maximaal acht kernen per CPU, die bij gebruik in een acht socketsysteem vervolgens kan worden verdubbeld tot 128 logische processors met behulp van gelijktijdige multithreading -technologie (SMT). Op Intel-CPU's wordt SMT Hyper-Threading-genoemd. Naarmate het aantal logische processors op x86-compatibele computers toeneemt, nemen gelijktijdigheidsproblemen toe naarmate logische processors concurreren voor resources. In deze handleiding wordt beschreven hoe u specifieke problemen met resourceconflicten kunt identificeren en oplossen die zijn waargenomen bij het uitvoeren van SQL Server-toepassingen op systemen met hoge gelijktijdigheid met sommige workloads.

In deze sectie analyseren we de lessen die het SQLCAT-team heeft geleerd van het diagnosticeren en oplossen van problemen met spinlockconflicten. Spinlock-conflicten zijn één type gelijktijdigheidsprobleem dat wordt waargenomen in echte klantworkloads op systemen op grote schaal.

Symptomen en oorzaken van spinlock-contentie

In deze sectie wordt beschreven hoe u problemen met spinlockconflicten kunt vaststellen, wat schadelijk is voor de prestaties van OLTP-toepassingen op SQL Server. Spinlock diagnose en probleemoplossing moeten worden beschouwd als een geavanceerd onderwerp, dat kennis vereist van foutopsporingsprogramma's en Interne Windows-functies.

Spinlocks zijn lichtgewicht synchronisatieprimitieven die worden gebruikt om de toegang tot gegevensstructuren te beveiligen. Spinlocks zijn niet uniek voor SQL Server. Het besturingssysteem gebruikt deze wanneer toegang tot een bepaalde gegevensstructuur slechts korte tijd nodig is. Wanneer een thread die probeert een spinlock te verkrijgen geen toegang kan krijgen, wordt deze periodiek uitgevoerd in een lus om te bepalen of de resource beschikbaar is in plaats van onmiddellijk te genereren. Na enige tijd zal een thread die op een spinlock wacht, opleveren voordat deze de resource kan verkrijgen. Met opbrengst kunnen andere threads die op dezelfde CPU worden uitgevoerd, worden uitgevoerd. Dit gedrag staat bekend als een uitstel en wordt verderop in dit artikel uitgebreider besproken.

SQL Server maakt gebruik van spinlocks om de toegang tot een aantal interne gegevensstructuren te beveiligen. Spinlocks worden in de engine gebruikt om de toegang tot bepaalde gegevensstructuren op een vergelijkbare manier te serialiseren als sloten. Het belangrijkste verschil tussen een vergrendeling en een spinlock is het feit dat spinlocks draaien (een lus uitvoeren) gedurende een bepaalde periode controleren op beschikbaarheid van een gegevensstructuur terwijl een thread die probeert toegang te verkrijgen tot een structuur die wordt beveiligd door een vergrendeling onmiddellijk oplevert als de resource niet beschikbaar is. Voor het rendement is contextwisseling van een thread van de CPU vereist, zodat een andere thread kan worden uitgevoerd. Dit is een relatief dure bewerking en voor resources die gedurende een korte duur worden bewaard, is het over het algemeen efficiënter om een thread in een lus periodiek te laten controleren op beschikbaarheid van de resource.

Interne aanpassingen aan de database-engine die in SQL Server 2022 (16.x) zijn geïntroduceerd, maken spinlocks efficiënter.

Symptomen

In een bezet systeem met hoge gelijktijdigheid is het normaal om actieve conflicten te zien op vaak gebruikte structuren die worden beveiligd door spinlocks. Dit gebruik wordt alleen als problematisch beschouwd wanneer conflicten aanzienlijke CPU-overhead veroorzaken. Spinlock-statistieken worden weergegeven door de sys.dm_os_spinlock_stats dynamische beheerweergave (DMV) in SQL Server. Deze query levert bijvoorbeeld de volgende uitvoer op:

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

De statistieken die door deze query worden weergegeven, worden als volgt beschreven:

Voor deze discussie zijn statistieken van bijzonder belang het aantal botsingen, spins en backoff-gebeurtenissen die plaatsvinden binnen een specifieke periode waarin het systeem zwaar belast is. Wanneer een thread probeert toegang te krijgen tot een resource die wordt beveiligd door een spinlock, treedt er een botsing op. Wanneer er een botsing optreedt, wordt het aantal botsingen verhoogd en begint de thread in een lus te draaien en controleert regelmatig of de hulpbron beschikbaar is. Telkens wanneer de thread draait (lus) wordt de spin count verhoogd.

Spins per botsing is een maatstaf voor de hoeveelheid spins die zich voordoen terwijl een spinlock wordt vastgehouden door een thread, en geeft aan hoeveel spins zich voordoen terwijl threads de spinlock vasthouden. Kleine spins per botsing en een hoog aantal botsingen betekent bijvoorbeeld dat er een kleine hoeveelheid spins onder de spinlock plaatsvindt en dat er veel threads zijn die ervoor strijden. Een groot aantal spins betekent dat de tijd die wordt besteed aan het draaien in de spinlockcode relatief lang is (dat wil zeggen dat de code door een groot aantal items in een hash-bucket gaat). Naarmate conflicten toenemen (dus toenemend aantal botsingen), neemt het aantal spins ook toe.

Backoffs kunnen worden beschouwd als vergelijkbaar met spins. Om overmatig CPU-afval te voorkomen, blijven spinlocks niet voor onbepaalde tijd draaien totdat ze toegang hebben tot een vastgehouden resource. Om ervoor te zorgen dat een spinlock geen overmatig gebruik maakt van CPU-resources, spinlocks weer uit of stoppen met draaien en 'slaapstand'. Spinlocks trekken zich terug, ongeacht of ze ooit eigenaar worden van de doelresource. Dit wordt gedaan om toe te staan dat andere threads worden gepland op de CPU in de hoop dat dit productiever werk mogelijk maakt. Standaardgedrag voor de engine is om eerst een constant tijdsinterval te draaien voordat een back-off wordt uitgevoerd. Als u een spinlock probeert te verkrijgen, moet de gelijktijdigheid van de cache worden gehandhaafd. Dit is een CPU-intensieve bewerking ten opzichte van de CPU-kosten van het draaien. Daarom worden pogingen om een spinlock te verkrijgen spaarzaam uitgevoerd en niet telkens wanneer een thread draait. In SQL Server zijn bepaalde spinlocktypen (bijvoorbeeld LOCK_HASH) verbeterd door gebruik te maken van een exponentieel toenemend interval tussen pogingen om de spinlock te verkrijgen (tot een bepaalde limiet), wat vaak het effect op CPU-prestaties vermindert.

Het volgende diagram biedt een conceptuele weergave van het spinlock-algoritme:

Typische scenario's

Spinlockconflicten kunnen zich voordoen om een aantal redenen die mogelijk niet gerelateerd zijn aan beslissingen over databaseontwerp. Omdat spinlocks de toegang tot interne gegevensstructuren regelen, manifesteren spinlockconflicten zich niet op dezelfde manier als buffervergrendelingsconflicten, die rechtstreeks worden beïnvloed door keuzes in schemaontwerp en patronen van gegevensbenadering.

Het symptoom dat voornamelijk samenhangt met spinlockconflicten is een hoog CPU-verbruik als gevolg van het grote aantal spins en veel threads die proberen dezelfde spinlock te verkrijgen. Over het algemeen is dit waargenomen op systemen met 24 en meer CPU-kernen, en meestal op systemen met meer dan 32 CPU-kernen. Zoals eerder vermeld, is een bepaald niveau van contentie op spinlocks normaal voor OLTP-systemen met hoge gelijktijdigheid en aanzienlijke belasting, en er wordt vaak een groot aantal spins (miljarden/biljoenen) gerapporteerd door de sys.dm_os_spinlock_stats DMV op systemen die al lange tijd draaien. Nogmaals, het observeren van een groot aantal spins voor een bepaald spinlocktype is niet voldoende informatie om te bepalen dat er negatieve gevolgen zijn voor de prestaties van de werkbelasting.

Een combinatie van verschillende van de volgende symptomen kan duiden op spinlock contentie. Als al deze voorwaarden waar zijn, voert u nadere onderzoek uit naar mogelijke problemen met spinlockcontensie.

• Een groot aantal draaiingen en terugtrekkingen worden waargenomen voor een bepaald type spinlock.

• Het systeem ondervindt veel CPU-gebruik of pieken in CPU-verbruik. In zware CPU-scenario's ziet u hoge signaalswachttijden op SOS_SCHEDULER_YIELD (gerapporteerd door de DMV sys.dm_os_wait_stats).

• Het systeem ondervindt hoge gelijktijdigheid.

• Het CPU-gebruik en het aantal spins worden in een disproportionele verhouding tot de doorvoersnelheid verhoogd.

Een veelvoorkomend fenomeen dat gemakkelijk kan worden opgegeven, is een aanzienlijke afwijking in doorvoer- en CPU-gebruik. Veel OLTP-workloads hebben een relatie tussen (doorvoer/aantal gebruikers op het systeem) en CPU-verbruik. Hoge spins waargenomen in combinatie met een aanzienlijke afwijking van cpu-verbruik en doorvoer kan een indicatie zijn van spinlockconflicten die leiden tot CPU-overhead. Het is belangrijk om hier op te merken dat het ook gebruikelijk is om dit type afwijking op systemen te zien wanneer bepaalde query's in de loop van de tijd duurder worden. Query's die worden uitgegeven voor gegevenssets die in de loop van de tijd meer logische leesbewerkingen uitvoeren, kunnen bijvoorbeeld vergelijkbare symptomen veroorzaken.

Enkele van de meest voorkomende oorzaken voor een verhoogd CPU-verbruik zijn:

• Query's die na verloop van tijd duurder worden vanwege de groei van de onderliggende gegevens, wat resulteert in de noodzaak om extra logische leesbewerkingen van geheugengegevens uit te voeren.
• Wijzigingen in queryplannen die resulteren in suboptimale uitvoering.

Voorbeelden

In het volgende voorbeeld is er een bijna lineaire relatie tussen CPU-verbruik en doorvoer, zoals gemeten door transacties per seconde. Het is normaal om hier enige verschillen te zien, omdat overhead wordt gemaakt naarmate elke workload toeneemt. Zoals hier wordt geïllustreerd, wordt deze divergentie aanzienlijk. Er is ook een scherpe daling in de doorvoer zodra het CPU-verbruik 100%bereikt.

Bij het meten van het aantal spins met intervallen van 3 minuten kunnen we een meer exponentiële dan lineaire toename van spins zien, wat aangeeft dat spinlockconflicten problematisch kunnen zijn.

Zoals eerder vermeld, komen spinlocks het meest voor op systemen met hoge gelijktijdigheid die zwaar belast zijn.

Enkele van de scenario's die gevoelig zijn voor dit probleem zijn:

• Problemen met naamomzetting veroorzaakt door een fout bij het volledig kwalificeren van namen van objecten. Zie Beschrijving van SQL Server-blokkeringen die worden veroorzaakt door compileervergrendelingen voor meer informatie. Dit specifieke probleem wordt uitgebreid beschreven in dit artikel.

• Conflicten over hash-buckets die met vergrendelingen te maken hebben in het beheersysteem voor vergrendelingen voor workloads die frequent gebruikmaken van dezelfde vergrendeling (zoals een gedeelde vergrendeling op een vaak gelezen rij). Dit type conflict komt naar voren als een spinlock van het LOCK_HASH-type. In een bepaald geval hebben we vastgesteld dat dit probleem optreedt als gevolg van onjuist gemodelleerde toegangspatronen in een testomgeving. In deze omgeving hebben meer dan de verwachte aantallen threads voortdurend toegang tot dezelfde rij vanwege onjuist geconfigureerde testparameters.

• Hoge snelheid van DTC-transacties wanneer er sprake is van een hoge latentie tussen de MSDTC-transactiecoördinatoren. Dit specifieke probleem wordt gedetailleerd beschreven in het SQLCAT-blogbericht Het oplossen van DTC-gerelateerde wachttijden en het afstemmen van schaalbaarheid van DTC.

Spinlockconflict vaststellen

In deze sectie vindt u informatie over het diagnosticeren van sql Server-spinlockconflicten. De belangrijkste middelen die gebruikt worden om spinlockconflicten te diagnosticeren zijn:

Het algemene technische proces voor het diagnosticeren van sql Server Spinlock-conflicten is:

1. Stap 1: Bepaal dat er conflicten zijn die mogelijk gerelateerd zijn aan spinlocks.

2. Stap 2: Leg statistieken vast van waaruit sys.dm_os_spinlock_stats het spinlocktype het meeste conflict ondervindt.

3. Stap 3: Haal foutopsporingssymbolen op voor sqlservr.exe (sqlservr.pdb) en plaats de symbolen in dezelfde map als het SQL Server-.exe-bestand (sqlservr.exe) voor het exemplaar van SQL Server.\ Als u de aanroepstacks voor de back-offgebeurtenissen wilt zien, moet u symbolen hebben voor de specifieke versie van SQL Server die u uitvoert. Symbolen voor SQL Server zijn beschikbaar op de Microsoft Symbol Server. Zie Foutopsporing met symbolen voor meer informatie over het downloaden van symbolen van de Microsoft Symbol Server.

4. Stap 4: Gebruik uitgebreide SQL Server-gebeurtenissen om de back-off-gebeurtenissen te traceren voor de spinlocktypen die van belang zijn. De gebeurtenissen die moeten worden vastgelegd, zijn spinlock_backoff en spinlock_backoff_warning.

Uitgebreide gebeurtenissen bieden de mogelijkheid om de back-off-gebeurtenissen bij te houden en de aanroepstack vast te leggen voor die bewerkingen die het vaakst proberen de spinlock te verkrijgen. Door de aanroepstack te analyseren, is het mogelijk om te bepalen welk type bewerking bijdraagt aan conflicten voor een bepaalde spinlock.

Stapsgewijze diagnostische handleiding

In de volgende procedure ziet u hoe u de gereedschappen en technieken gebruikt om een probleem met spinlock-contentie in een praktijkscenario te diagnosticeren. Deze stappenplan is gebaseerd op een klantproject dat een benchmarktest uitvoert om ongeveer 6500 gelijktijdige gebruikers te simuleren op een 8-socket, 64 fysieke kern-server met 1 TB geheugen.

Symptomen

Periodieke pieken in cpu werden waargenomen, waardoor het CPU-gebruik werd gepusht naar bijna 100%. Er is een verschil tussen doorvoer en CPU-verbruik waargenomen, waardoor het probleem zich voordeed. Tegen de tijd dat de grote CPU-piek plaatsvond, was er een patroon vastgesteld van een groot aantal draaiingen die optraden tijdens momenten van intensief CPU-gebruik met regelmatige intervallen.

Dit was een extreem geval waarin het conflict zodanig was dat het leidde tot een spinlock convoyprobleem. nl-NL: Een convooi treedt op wanneer threads geen voortgang kunnen boeken in het verwerken van de workload, maar in plaats daarvan al hun verwerkingsmiddelen gebruiken om toegang tot de vergrendeling te verkrijgen. Het prestatiemeterlogboek illustreert deze verschillen tussen de doorvoer van transactielogboeken en het CPU-verbruik en uiteindelijk de grote piek in het CPU-gebruik.

Nadat een query sys.dm_os_spinlock_stats is uitgevoerd om het bestaan van significante conflicten op SOS_CACHESTORE te bepalen, werd een script voor uitgebreide gebeurtenissen gebruikt om het aantal backoff-gebeurtenissen voor de spinlock-typen te meten.

De eenvoudigste manier om de impact van de spins te kwantificeren, is om te kijken naar het aantal backoffgebeurtenissen dat door sys.dm_os_spinlock_stats binnen hetzelfde interval van 1 minuut wordt weergegeven voor het spinlocktype of -typen met het hoogste aantal spins. Deze methode is het beste om significante contentie te detecteren, omdat het aangeeft wanneer threads de spinlimiet uitputten terwijl wordt gewacht om de spinlock te verkrijgen. Het volgende script illustreert een geavanceerde techniek die gebruikmaakt van uitgebreide gebeurtenissen om gerelateerde back-off-gebeurtenissen te meten en de specifieke codepaden te identificeren waar de conflicten liggen.

Zie overzicht van uitgebreide gebeurtenissen in SQL Server voor meer informatie over uitgebreide gebeurtenissen.

Script

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketizer target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

Door de uitvoer te analyseren, zien we de call stacks voor de meest gebruikelijke codepaden voor de SOS_CACHESTORE spins. Het script is een aantal verschillende tijden uitgevoerd tijdens het tijdstip waarop het CPU-gebruik hoog was om te controleren op consistentie in de aanroepstacks die zijn geretourneerd. De call stacks met het hoogste aantal sleufbuckets komen vaak voor tussen de beide outputs (35.668 en 8.506). Deze aanroepketens hebben een aantal slots dat twee ordes van grootte groter is dan de op één na hoogste vermelding. Deze voorwaarde geeft een codepad van belang aan.

Uitvoer 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Uitvoer 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

In het vorige voorbeeld hebben de meest interessante stacks het hoogste aantal sleufs (35.668 en 8.506), dat in feite een aantal sleufs groter dan 1000 heeft.

Nu kan de vraag zijn: 'Wat moet ik doen met deze informatie'? Over het algemeen is grondige kennis van de SQL Server-engine vereist om gebruik te maken van de callstack-informatie. Op dit moment wordt het probleemoplossingsproces dus verplaatst naar een grijs gebied. In dit specifieke geval, door de aanroepstacks te bekijken, kunnen we zien dat het codepad waar het probleem optreedt betrekking heeft op beveiligings- en metagegevenszoekacties (zoals blijkt uit de volgende stackframes CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID).

In isolatie is het moeilijk om deze informatie te gebruiken om het probleem op te lossen, maar het geeft ons wel enkele ideeën waar u aanvullende probleemoplossing kunt richten om het probleem verder te isoleren.

Omdat dit probleem leek te zijn gerelateerd aan codepaden die beveiligingsgerelateerde controles uitvoeren, hebben we besloten om een test uit te voeren waarin de toepassingsgebruiker die verbinding maakt met de database bevoegdheden heeft gekregen sysadmin . Hoewel deze techniek nooit wordt aanbevolen in een productieomgeving, bleek het in onze testomgeving een nuttige probleemoplossingsstap te zijn. Toen de sessies werden uitgevoerd met verhoogde rechten (sysadmin), verdwenen de CPU-pieken veroorzaakt door wedijver.

Opties en tijdelijke oplossingen

Duidelijk, het oplossen van problemen met spinlockconflicten kan een niet-triviale taak zijn. Er is geen 'gemeenschappelijke beste benadering'. De eerste stap bij het oplossen van een prestatieprobleem is het identificeren van de hoofdoorzaak. Het gebruik van de technieken en hulpprogramma's die in dit artikel worden beschreven, is de eerste stap bij het uitvoeren van de analyse die nodig is om inzicht te hebben in de spinlock-gerelateerde conflicten.

Naarmate er nieuwe versies van SQL Server worden ontwikkeld, blijft de engine de schaalbaarheid verbeteren door code te implementeren die beter is geoptimaliseerd voor systemen met hoge gelijktijdigheid. SQL Server heeft veel optimalisaties geïntroduceerd voor systemen met hoge gelijktijdigheid, waarvan een exponentieel uitstel is voor de meest voorkomende conflicten. Er zijn verbeteringen aangebracht vanaf SQL Server 2012 die dit gebied hebben verbeterd door gebruik te maken van exponentiële backoff-algoritmen voor alle spinlocks binnen de engine.

Bij het ontwerpen van hoogwaardige toepassingen waarvoor extreme prestaties en schaal nodig zijn, kunt u overwegen hoe u het codepad dat nodig is binnen SQL Server zo kort mogelijk kunt houden. Een korter codepad betekent dat er minder werk wordt uitgevoerd door de database-engine en dat conflicten op natuurlijke wijze worden vermeden. Veel beste praktijken hebben als neveneffect dat de hoeveelheid werk die de engine moet verrichten wordt verminderd, wat leidt tot een geoptimaliseerde werkbelasting.

Een paar beste praktijken van eerder in dit artikel als voorbeelden:

• Volledig gekwalificeerde namen: Volledig in aanmerking komende namen van alle objecten resulteren in het verwijderen van de noodzaak voor SQL Server om codepaden uit te voeren die vereist zijn om namen op te lossen. We hebben geschilpunten waargenomen die ook op het SOS_CACHESTORE spinlocktype zijn opgetreden wanneer geen gebruik wordt gemaakt van volledig gekwalificeerde namen bij het oproepen van opgeslagen procedures. Als u deze namen niet volledig in aanmerking kunt nemen, is het nodig dat SQL Server het standaardschema voor de gebruiker opzoekt, wat resulteert in een langer codepad dat nodig is om de SQL uit te voeren.

• Geparameteriseerde query's: Een ander voorbeeld is het gebruik van geparameteriseerde query's en opgeslagen procedure-aanroepen om het werk te verminderen dat nodig is om uitvoeringsplannen te genereren. Dit resulteert opnieuw in een korter codepad voor uitvoering.

• LOCK_HASH Conflicten: Conflicten over bepaalde slotstructuur of hash bucket-botsingen zijn soms onvermijdelijk. Hoewel de SQL Server-engine het merendeel van de vergrendelingsstructuren partitioneert, zijn er nog steeds momenten wanneer het verkrijgen van een vergrendeling resulteert in toegang tot dezelfde hash-bucket. Een toepassing heeft bijvoorbeeld toegang tot dezelfde rij door veel threads tegelijk (dus referentiegegevens). Deze typen problemen kunnen worden benaderd door technieken die deze referentiegegevens in het databaseschema uitschalen of optimistisch gelijktijdigheidsbeheer en geoptimaliseerde vergrendeling gebruiken, indien mogelijk.

De eerste verdedigingslinie bij het afstemmen van SQL Server-workloads is altijd de standaardafstemmingsprocedures (bijvoorbeeld indexering, queryoptimalisatie, I/O-optimalisatie, enzovoort). Naast de standaardafstemming die men zou uitvoeren, is het echter van belang om praktijken te volgen die de hoeveelheid code verminderen die nodig is om bewerkingen uit te voeren. Zelfs wanneer aanbevolen procedures worden gevolgd, is er nog steeds een kans dat spinlockconflicten kunnen optreden op drukke systemen met hoge gelijktijdigheid. Het gebruik van de hulpprogramma's en technieken in dit artikel kan helpen om deze soorten problemen te isoleren of uit te sluiten en te bepalen wanneer het nodig is om de juiste Microsoft-resources in te schakelen om u te helpen.

Bijlage: Automatiseren van het vastleggen van geheugendumps

Het volgende script voor uitgebreide gebeurtenissen is zeer nuttig om de verzameling van geheugendumps te automatiseren wanneer er aanzienlijke spinlockconflicten zijn. In sommige gevallen zijn geheugendumps vereist om een volledige diagnose van het probleem uit te voeren of worden ze door de Microsoft-teams gevraagd om een diepgaande analyse uit te voeren.

Het volgende SQL-script kan worden gebruikt om het proces van het vastleggen van geheugendumps te automatiseren om spinlockconflicten te analyseren:

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.

Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs;
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Bijlage: Statistieken van spinlocks registreren na verloop van tijd

Het volgende script kan worden gebruikt om spinlockstatistieken gedurende een specifieke periode te bekijken. Elke keer dat het wordt uitgevoerd, zal het het verschil teruggeven tussen de huidige waarden en de eerder verzamelde waarden.

/* Snapshot the current spinlock stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb. if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

--clean up table
DELETE
FROM #_spin_waits
WHERE snap_time = @previous_snap_time;

Verwante inhoud

• hulpprogramma's voor het bewaken en afstemmen van prestaties