Diagnostisera och lösa spärrkonkurration på SQL Server

Den här guiden beskriver hur du identifierar och löser problem med spärrkonkurrens som observeras när du kör SQL Server-program på system med hög samtidighet med vissa arbetsbelastningar.

I takt med att antalet CPU-kärnor på servrar fortsätter att öka kan den associerade ökningen av samtidigheten introducera konkurrenspunkter för datastrukturer som måste nås på ett seriellt sätt i databasmotorn. Detta gäller särskilt för OLTP-arbetsbelastningar (transaktionsbearbetning med hög genomströmning och hög samtidighet). Det finns flera verktyg, tekniker och sätt att hantera dessa utmaningar samt metoder som kan följas vid utformning av program som kan bidra till att undvika dem helt och hållet. I den här artikeln beskrivs en viss typ av konkurrens om datastrukturer som använder spinlocks för att serialisera åtkomsten till dessa datastrukturer.

Vad är SQL Server-låsstrid?

Spärrar är enkla synkroniseringsprimitiver som används av SQL Server-motorn för att garantera konsekvens för minnesstrukturer, inklusive index, datasidor och interna strukturer, till exempel sidor som inte är lövsidor i ett B-träd. SQL Server använder buffertspärrar för att skydda sidor i buffertpoolen och I/O-spärrar för att skydda sidor som ännu inte har lästs in i buffertpoolen. När data skrivs till eller läss från en sida i SQL Server-buffertpoolen måste en arbetstråd först hämta en buffertspärr för sidan. Det finns olika typer av buffertspärrar för åtkomst till sidor i buffertpoolen, inklusive exklusiv spärr (PAGELATCH_EX) och delad spärr (PAGELATCH_SH). När SQL Server försöker komma åt en sida som inte redan finns i buffertpoolen publiceras en asynkron I/O för att läsa in sidan i buffertpoolen. Om SQL Server behöver vänta på att I/O-undersystemet ska svara väntar det på en exklusiv (PAGEIOLATCH_EX) eller delad (PAGEIOLATCH_SH) I/O-spärr beroende på typen av begäran. Detta görs för att förhindra att en annan arbetstråd läser in samma sida i buffertpoolen med en inkompatibel spärr. Lås används också för att skydda åtkomsten till andra interna minnesstrukturer än buffertpoolsidor. dessa kallas för icke-buffertlås.

Konkurrens på sidlås är det vanligaste scenariot som påträffas i system med flera processorer och därför fokuserar de flesta av den här artikeln på dessa.

Spärrkontention uppstår när flera trådar samtidigt försöker förvärva inkompatibla lås till samma struktur i minnet. Eftersom en spärr är en intern kontrollmekanism, avgör SQL-motorn automatiskt när dessa ska användas. Eftersom beteendet för lås är deterministiskt kan programbeslut inklusive schemadesign påverka det här beteendet. Den här artikeln syftar till att tillhandahålla följande information:

• Bakgrundsinformation om hur lås används av SQL Server.
• Verktyg som används för att undersöka spärrkonkurration.
• Hur man avgör om mängden konkurrens som observeras är problematisk.

Vi diskuterar några vanliga scenarier och hur du bäst hanterar dem för att minska konkurrensen.

Hur använder SQL Server lås?

En sida i SQL Server är 8 kB och kan lagra flera rader. För att öka samtidigheten och prestandan hålls buffertlås endast för varaktigheten av den fysiska åtgärden på sidan, till skillnad från lås, som hålls under varaktigheten av den logiska transaktionen.

Latches är interna för SQL-motorn och används för att ge minneskonsistens, medan lås används av SQL Server för att ge logisk transaktionskonsekvens. I följande tabell jämförs lås med lås:

Spärrlägen och kompatibilitet för SQL Server

Viss spärrkonflikt kan förväntas som en normal del av driften av SQL Server-motorn. Det är oundvikligt att flera samtidiga spärrbegäranden med varierande kompatibilitet sker i ett system med hög samtidighet. SQL Server tillämpar spärrkompatibilitet genom att kräva att inkompatibla spärrbegäranden väntar i en kö tills utestående spärrbegäranden har slutförts.

Lås förvärvas i ett av fem olika lägen, som hänför sig till åtkomstnivå. SQL Server-spärrlägen kan sammanfattas på följande sätt:

• KP: Håll spärren. Säkerställer att den refererade strukturen inte kan förstöras. Används när en tråd vill titta på en buffertstruktur. Eftersom KP-spärren är kompatibel med alla lås förutom destrueringsspärren (DT) anses KP-spärren vara lätt, vilket innebär att effekten på prestanda när den används är minimal. Eftersom KP-spärren inte är kompatibel med DT-spärren förhindrar den att någon annan tråd förstör den refererade strukturen. En KP-spärr förhindrar till exempel att den struktur som den refererar till förstörs av den lata skrivprocessen. Mer information om hur den lata skrivprocessen används med hantering av SQL Server-buffertsidor finns i Skriva sidor i databasmotorn.

• SH: Delad låsning. Krävs för att läsa den refererade strukturen (till exempel läsa en datasida). Flera trådar kan samtidigt komma åt en resurs för läsning under en delad spärr.

• UP: Uppdateringsspärr. Kompatibel med SH (delad spärr) och KP, men inga andra och tillåter därför inte att en EX spärr skriver till den refererade strukturen.

• EX: Exklusiv spärr. Blockerar andra trådar från att skriva till eller läsa från den refererade strukturen. Ett exempel på användning är att ändra innehållet på en sida för skydd mot skadade sidor.

• DT: Förstör spärren. Måste förvärvas innan innehållet i den refererade strukturen förstörs. Till exempel måste en DT-lås hämtas av den lata skrivprocessen för att frigöra en ren sida innan den läggs till i listan över fria buffertar som andra trådar kan använda.

Spärrlägen har olika kompatibilitetsnivåer, till exempel är en delad spärr (SH) kompatibel med en uppdatering (UP) eller behåll (KP) spärr men inte kompatibel med en destrueringsspärr (DT). Flera lås kan hämtas samtidigt på samma struktur så länge låsen är kompatibla. När en tråd försöker hämta en spärr som hålls i ett läge som inte är kompatibelt placeras den i en kö för att vänta på en signal som anger att resursen är tillgänglig. En spinlock av typen SOS_Task används för att skydda väntekön genom att framtvinga serialiserad åtkomst till kön. Den här spinlocken måste erhållas för att lägga till objekt i kön. SOS_Task-spinlocket signalerar också trådar i kön när oförenliga spärrar släpps, vilket gör att de väntande trådarna kan förvärva en kompatibel spärr och fortsätta arbeta. Väntekön hanteras först in, först ut (FIFO) när spärrbegäranden släpps. Svarvarna följer det här FIFO-systemet för att säkerställa rättvisa och förhindra trådsvältning.

Kompatibilitet för spärrläge visas i följande tabell (Ja anger kompatibilitet och Nej indikerar inkompatibilitet):

SQL Server-superlås och underlås

Med den ökande förekomsten av NUMA-baserade system med flera socketar/flera kärnor introducerade SQL Server 2005 Superlatches, även kallade underetiketter, som endast är effektiva på system med 32 eller fler logiska processorer. Superlatcher förbättrar effektiviteten i SQL-motorn för vissa användningsmönster i mycket samtidiga OLTP-arbetsbelastningar. Till exempel när vissa sidor har ett mönster med tung skrivskyddad delad (SH) åtkomst, men skrivs till sällan. Ett exempel på en sida med ett sådant åtkomstmönster är en B-trädrotsida, det vill säga ett index. SQL-motorn kräver att en delad spärr hålls på rotsidan när en sidindelning sker på vilken nivå som helst i B-trädet. I en infogningstung och hög samtidig OLTP-arbetsbelastning ökar antalet siduppdelningar i stort sett linjärt med genomströmningen, vilket kan försämra prestandan. Superlatchar kan möjliggöra bättre prestanda för åtkomst till delade sidor när flera arbetstrådar som körs samtidigt kräver SH superlås. För att åstadkomma detta höjer SQL Server Engine dynamiskt en spärr på en sådan sida till en Superlatch. En Superlatch partitionerar en enda spärr i en matris med underlatch-strukturer, en underlatch per partition per CPU-kärna, där huvudspärren fungerar som en proxyomdirigering och global tillståndssynkronisering inte krävs för skrivskyddade spärrar. När du gör det behöver arbetaren, som alltid är tilldelad till en specifik PROCESSOR, bara hämta den delade () underordnad somSH tilldelats den lokala schemaläggaren.

Förvärv av kompatibla lås, till exempel en delad Superlatch, använder färre resurser och skalar åtkomsten till frekventa sidor bättre än en icke-partitionerad delad spärr eftersom borttagning av kravet på global tillståndssynkronisering avsevärt förbättrar prestandan genom att bara komma åt lokalt NUMA-minne. Omvänt är det dyrare att skaffa en exklusiv (EX) Superlatch än att skaffa en EX vanlig spärr eftersom SQL måste signalera över alla underetiketter. När en Superlatch observeras använda ett mönster för tung EX åtkomst kan SQL-motorn nedgradera den när sidan har avlägsnats från buffertpoolen. Följande diagram visar en normal spärr och en partitionerad Superlatch:

Använd SQL Server:Latches-objektet och tillhörande räknare i Prestandaövervakaren för att samla in information om superlatcher, inklusive antalet superlatcher, Superlatch-kampanjer per sekund och Superlatch-degraderingar per sekund. Mer information om SQL Server:Latches-objektet och associerade räknare finns i SQL Server, Latches-objekt.

Väntetyper för lås

Kumulativ vänteinformation spåras av SQL Server och kan nås med hjälp av DMW (Dynamic Management View). sys.dm_os_wait_stats SQL Server använder tre spärrväntetyper som definieras av motsvarande wait_type i sys.dm_os_wait_stats DMV:

• Buffertlås (BUF): används för att säkerställa konsekvens hos index- och datasidor för användarobjekt. De används också för att skydda åtkomsten till datasidor som SQL Server använder för systemobjekt. Buffertspärrar används till exempel för att skydda sidor som hanterar allokeringar. Dessa inkluderar sidorna Sidfritt utrymme (PFS), Global Allokeringskarta (GAM), Delad global allokeringskarta (SGAM) och IAM-sidor (Index Allocation Map). Buffertlås rapporteras i sys.dm_os_wait_stats med en wait_type av PAGELATCH_*.

• Icke-buffertspärr (icke-BUF): används för att garantera konsekvens för andra minnesinterna strukturer än buffertpoolsidor. Möjliga väntetider för lås som inte är buffrade rapporteras som en wait_type av LATCH_*.

• I/O-spärr: en delmängd buffertlås som garanterar konsekvens i samma strukturer som skyddas av buffertlås när dessa strukturer kräver inläsning i buffertpoolen med en I/O-åtgärd. I/O-lås förhindrar att en annan tråd läser in samma sida i buffertpoolen med en inkompatibel spärr. Associerad med en wait_type av PAGEIOLATCH_*.

  Anmärkning

  Om du ser betydande PAGEIOLATCH väntetider innebär det att SQL Server väntar på I/O-undersystemet. Medan en viss mängd PAGEIOLATCH väntetider förväntas och normalt beteende, bör du undersöka varför I/O-undersystemet är under press om de genomsnittliga PAGEIOLATCH väntetiderna konsekvent överstiger 10 millisekunder (ms).

Om du vid undersökningen av sys.dm_os_wait_stats DMV stöter på icke-buffertlås, måste sys.dm_os_latch_stats undersökas för att få en detaljerad uppdelning av kumulativ vänteinformation för icke-buffertlås. Alla buffertspärrväntningar klassificeras under spärrklassen BUFFER och de återstående används för att klassificera icke-buffertlås.

Symptom och orsakerna bakom SQL Server-spärrkonkurrens

I ett upptaget system med hög samtidighet är det normalt att se aktiv konkurrens om strukturer som ofta används och skyddas av svarstider och andra kontrollmekanismer i SQL Server. Det anses problematiskt när konkurrensen och väntetiden som är associerad med att erhålla låset för en sida är tillräcklig för att minska CPU-användningen, vilket hindrar genomströmningen.

Exempel på lås-konflikt

I följande diagram representerar den blå linjen dataflödet i SQL Server, mätt med Transaktioner per sekund. den svarta linjen representerar genomsnittlig väntetid för sidspärr. I det här fallet utför varje transaktion ett INSERT i ett grupperat index med ett sekventiellt ökande inledande värde, till exempel när du fyller i en IDENTITY kolumn med datatypen bigint. När antalet processorer ökar till 32 är det uppenbart att det totala dataflödet har minskat och väntetiden för sidspärren har ökat till cirka 48 millisekunder, vilket framgår av den svarta linjen. Den här omvända relationen mellan genomströmning och sidlåsmekanismens väntetid är ett vanligt scenario som enkelt diagnostiseras.

Prestanda när låskonflikten löses

Som diagrammet nedan visar har SQL Server inte längre en flaskhals vid väntetider för sidlås, och dataflödet har ökat med 300% mätt i transaktioner per sekund. Detta gjordes med tekniken använd hashpartitionering med en beräknad kolumn som beskrivs senare i den här artikeln. Den här prestandaförbättringen riktas mot system med ett stort antal kärnor och en hög samtidighetsnivå.

Faktorer som påverkar låsningstävlan

Spärrkonkurrens som hindrar prestanda i OLTP-miljöer orsakas vanligtvis av hög samtidighet relaterad till en eller flera av följande faktorer:

Diagnostisera SQL Server-låskonflikt

Det här avsnittet innehåller information om hur du diagnostiserar SQL Server-spärrkonkurration för att avgöra om det är problematiskt för din miljö.

Verktyg och metoder för att diagnostisera spärrkonflikt

De primära verktygen som används för att diagnostisera låskonflikter är:

• Prestandaövervakare för att övervaka processoranvändning och väntetider i SQL Server och fastställa om det finns en relation mellan processoranvändning och väntetider för spärr.

• SQL Server DMV:er, som kan användas för att fastställa den specifika typ av spärr som orsakar problemet och den berörda resursen.

• I vissa fall måste minnesdumpar av SQL Server-processen hämtas och analyseras med Windows felsökningsverktyg.

Den tekniska processen för att diagnostisera spärrkonkurration kan sammanfattas i följande steg:

1. Fastställ att det finns en konflikt som kan vara spärrrelaterad.

2. Använd DMV-vyerna som anges i bilaga: SQL Server-spärrkonkurreringsskript för att fastställa vilken typ av spärr och resurser som påverkas.

3. Minska konkurrensen med hjälp av någon av de tekniker som beskrivs i Hantera spärrkonkurration för olika tabellmönster.

Indikatorer på låsblockering

Som tidigare nämnts är spärrkonkurrens endast problematisk när den konkurrens- och väntetid som är associerad med att hämta sidlås hindrar dataflödet från att öka när CPU-resurser är tillgängliga. För att fastställa en acceptabel konkurrensmängd krävs en holistisk metod som tar hänsyn till prestanda- och dataflödeskrav tillsammans med tillgängliga I/O- och CPU-resurser. Det här avsnittet guidar dig genom att fastställa hur låskonflikter påverkar den arbetsbelastning som beskrivs nedan:

1. Mät övergripande väntetider under ett representativt test.
2. Rangordna dem i ordning.
3. Fastställa andelen väntetider som är relaterade till låsningar.

Kumulativ vänteinformation är tillgänglig från DMV:en sys.dm_os_wait_stats . Den vanligaste typen av spärrkonkurrens är buffertspärrkonkurrens, som observeras som en ökning av väntetiderna för spärrar med en wait_type av PAGELATCH_*. Lås som inte är buffertar grupperas under väntetypen LATCH*. Som följande diagram visar bör du först ta en övergripande översikt över systemvänteläget med DMV:n sys.dm_os_wait_stats för att fastställa procentandelen av den totala väntetiden som orsakas av buffert- och icke-buffertlås. Om du stöter på icke-buffertlås sys.dm_os_latch_stats måste DMV också undersökas.

I följande diagram beskrivs relationen mellan den information som returneras av dmv:erna sys.dm_os_wait_stats och sys.dm_os_latch_stats .

Mer information om DMV finns sys.dm_os_wait_statsi sys.dm_os_wait_stats i SQL Server-hjälpen.

Mer information om DMV finns sys.dm_os_latch_stats i sys.dm_os_latch_stats i SQL Server-hjälpen.

Följande mått på väntetid för spärr är indikatorer på att överdriven spärrkonkurration påverkar programmets prestanda:

• Genomsnittlig väntetid för sidspärr ökar konsekvent med dataflödet: Om den genomsnittliga väntetiden för sidspärrar ökar konsekvent med dataflödet och om den genomsnittliga väntetiden för buffertspärr också ökar över förväntade disksvarstider bör du undersöka aktuella väntande uppgifter med DMV sys.dm_os_waiting_tasks . Medelvärden kan vara vilseledande om de analyseras isolerat, så det är viktigt att titta på systemet live när det är möjligt för att förstå arbetsbelastningsegenskaper. Kontrollera särskilt om det finns höga väntetider på PAGELATCH_EX och/eller PAGELATCH_SH begäranden på några sidor. Följ dessa steg för att diagnostisera ökande genomsnittliga väntetider för sidlås med genomströmning:

  • Använd exempelskripten Fråga sys.dm_os_waiting_tasks Sorterade efter sessions-ID eller Beräkna väntetider under en tidsperiod för att titta på aktuella väntande uppgifter och mäta genomsnittlig väntetid för spärren.

  • Använd exempelskriptet Frågebuffertbeskrivningar för att fastställa objekt som orsakar spärrkonkurring för att fastställa indexet och den underliggande tabellen där konkurrensen förekommer.

  • Mät genomsnittlig väntetid för sidspärr med prestandaövervakarens räknare MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time eller genom att sys.dm_os_wait_stats köra DMV.

  Anmärkning

  Om du vill beräkna den genomsnittliga väntetiden för en viss väntetyp (returnerad sys.dm_os_wait_stats som wt_:type), dividerar du den totala väntetiden (returneras som wait_time_ms) med antalet väntande aktiviteter (returneras som waiting_tasks_count).

• Procentandel av den totala väntetiden som spenderas på väntetyper för spärrar under hög belastning: Om den genomsnittliga väntetiden för spärrar som procent av den totala väntetiden ökar i takt med programbelastningen kan spärrkonkurrens påverka prestandan och bör undersökas.

  Mät väntetider för sidspärrar och icke-sidspärrar med prestandaräknarna för SQL Server, Wait Statistics-objekt. Jämför sedan värdena för dessa prestandaräknare med prestandaräknare som är associerade med CPU, I/O, minne och nätverksdataflöde. Till exempel är transaktioner per sekund och batchbegäranden per sekund två bra mått på resursutnyttjande.

  Anmärkning

  Relativ väntetid för varje väntetyp ingår inte i sys.dm_os_wait_stats DMV eftersom denna DMW mäter väntetider sedan den senaste gången som sql Server-instansen startades eller den kumulativa väntestatistiken återställdes med hjälp av DBCC SQLPERF. Om du vill beräkna den relativa väntetiden för varje väntetyp tar du en ögonblicksbild av sys.dm_os_wait_stats före den högsta belastningen, efter den högsta belastningen och beräknar sedan skillnaden. Exempelskriptet Calculate Waits Over a Time Period (Beräkna väntetider över en tidsperiod ) kan användas för detta ändamål.

  För en miljö som inte är produktionsmiljö rensar sys.dm_os_wait_stats du DMV:en med följande kommando:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Ett liknande kommando kan köras för att rensa sys.dm_os_latch_stats DMV:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Dataflödet ökar inte och minskar i vissa fall när programbelastningen ökar och antalet processorer som är tillgängliga för SQL Server ökar: Detta illustrerades i Exempel på spärrkonkurrens.

• CPU-användningen ökar inte när programarbetsbelastningen ökar: Om processoranvändningen i systemet inte ökar när samtidigheten som drivs av programmets dataflöde ökar, är detta en indikator på att SQL Server väntar på något och symptomatiskt för spärrkonkurrens.

Analysera grundorsaken. Även om vart och ett av ovanstående villkor är sant är det fortfarande möjligt att grundorsaken till prestandaproblemen ligger någon annanstans. I de flesta fall orsakas faktiskt suboptimal CPU-användning av andra typer av väntetider, till exempel blockering av lås, I/O-relaterade väntetider eller nätverksrelaterade problem. Som tumregel är det alltid bäst att lösa resursvänteläget som representerar den största andelen av den totala väntetiden innan du fortsätter med mer djupgående analys.

Analysera aktuella låsningsmekanismer för väntebuffertar

Konkurrens om buffertspärrar visar sig som en ökning av väntetiderna för spärrar med en wait_type av antingen PAGELATCH_* eller PAGEIOLATCH_* som visas i sys.dm_os_wait_stats DMV. För att se systemet i realtid, kör följande fråga på ett system för att slå samman sys.dm_os_wait_stats, sys.dm_exec_sessions och sys.dm_exec_requests DMV:er. Resultatet kan användas för att fastställa den aktuella väntetypen för sessioner som körs på servern.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Statistiken som exponeras av den här frågan beskrivs på följande sätt:

Följande fråga returnerar information för alla icke-buffertlås:

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Statistiken som exponeras av den här frågan beskrivs på följande sätt:

Värdena som returneras av denna DMV är kumulativa sedan förra gången databasmotorn startades om eller DMV:en återställdes. Använd kolumnen sqlserver_start_time i sys.dm_os_sys_info för att hitta den senaste starttiden för databasmotorn. I ett system som har körts länge innebär det att viss statistik som max_wait_time_ms sällan är användbar. Följande kommando kan användas för att återställa väntestatistiken för denna DMV:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

SQL Server-låskonfliktscenarier

Följande scenarier har observerats för att orsaka överdriven spärrkonkurretion.

Infoga konkurrens på sista sidan/avslutande sida

En vanlig OLTP-metod är att skapa ett grupperat index på en identitet eller datumkolumn. Detta bidrar till att upprätthålla en god fysisk organisation av indexet, vilket kan ha stor nytta av prestanda för både läsningar och skrivningar till indexet. Den här schemadesignen kan dock oavsiktligt leda till spärrkonkurration. Det här problemet ses oftast med en stor tabell med små rader, och införingar i ett index som innehåller en sekventiellt ökande inledande nyckelkolumn, såsom stigande heltal eller datetime-nyckel. I det här scenariot utför programmet sällan eller aldrig uppdateringar eller borttagningar, vilket är undantaget för arkiveringsåtgärder.

I följande exempel vill tråd ett och tråd två båda infoga en post som kommer att lagras på sidan 299. Ur ett logiskt låsningsperspektiv är det inga problem eftersom lås på radnivå används, och exklusiva lås på båda posterna på samma sida kan hållas samtidigt. Men för att säkerställa integriteten för fysiskt minne kan endast en tråd i taget hämta en exklusiv spärr så att åtkomsten till sidan serialiseras för att förhindra förlorade uppdateringar i minnet. I det här fallet tar tråd 1 det exklusiva låset och tråd 2 väntar, vilket registrerar en PAGELATCH_EX väntan för denna resurs i väntestatistiken. Detta visas via wait_type-värdet i sys.dm_os_waiting_tasks DMV:n.

Den här konflikten kallas ofta för ”Sista sidinfogning”-konflikt eftersom den förekommer på B-trädets högra sida enligt följande diagram.

Den här typen av spärrkonkurration kan förklaras på följande sätt. När en ny rad infogas i ett index använder SQL Server följande algoritm för att köra ändringen:

1. Navigera i B-trädet för att hitta rätt sida för att lagra den nya posten.

2. Lås sidan med PAGELATCH_EX, förhindra andra från att ändra den och hämta delade lås (PAGELATCH_SH) på alla sidor som inte är lövsidor.

   Anmärkning

   I vissa fall kräver EX SQL-motorn att lås hämtas även på B-trädsidor som inte är löv. När en siddelning till exempel inträffar måste alla sidor som påverkas direkt vara exklusivt låsta (PAGELATCH_EX).

3. Registrera en loggpost att raden har ändrats.

4. Lägg till raden på sidan och markera sidan som smutsig.

5. Frigör alla sidor.

Om tabellindexet baseras på en sekventiellt ökande nyckel går varje ny infogning till samma sida i slutet av B-trädet tills den sidan är full. I scenarier med hög samtidighet kan detta orsaka konkurrens på B-trädets högra kant och kan inträffa i klustrade och icke-klustrade index. Tabeller som påverkas av den här typen av konflikt accepterar främst INSERT frågor och sidor för problematiska index är normalt relativt kompakta (till exempel är storleken på raden ~165 byte (inklusive överliggande kostnader) lika med ~49 rader per sida). I det här insert-heavy-exemplet förväntar vi oss PAGELATCH_EX/PAGELATCH_SH att väntetider inträffar, och det här är den typiska observationen. Om du vill undersöka väntetider för pagespärr jämfört med väntetider för trädlåsspärr ska du använda DMV sys.dm_db_index_operational_stats.

I följande tabell sammanfattas de viktigaste faktorerna som observerats med den här typen av spärrkonkurration:

Spärrkonkurration på små tabeller med ett icke-grupperat index och slumpmässiga infogningar (kötabell)

Det här scenariot visas vanligtvis när en SQL-tabell används som en tillfällig kö (till exempel i ett asynkront meddelandesystem).

I det här scenariot kan exklusiv (EX) och delad (SH) spärrkonkurnitet uppstå under följande villkor:

• Åtgärder för att infoga, välja, uppdatera eller ta bort sker under hög samtidighet.
• Radstorleken är relativt liten (vilket leder till täta sidor).
• Antalet rader i tabellen är relativt litet. leder till ett grunt B-träd, definierat genom att ha ett indexdjup på två eller tre.

Spärrkonkurration kan uppstå även om åtkomsten är slumpmässig i B-trädet, till exempel när en icke-sekventiell kolumn är den inledande nyckeln i ett grupperat index. Följande skärmbild är från ett system som har den här typen av spärrkonkurring. I det här exemplet beror konkurrensen på sidornas densitet som orsakas av liten radstorlek och ett relativt grunt B-träd. När samtidigheten ökar uppstår låsblockering på sidorna även om infogningar är slumpmässiga i B-trädet eftersom GUID är den ledande kolumnen i indexet.

I följande skärmbild sker väntetiderna på både buffertdatasidor och sidor med ledigt utrymme (PFS). Även när antalet datafiler ökade var spärrkonflikt vanlig på buffertdatasidor.

I följande tabell sammanfattas de viktigaste faktorerna som observerats med den här typen av spärrkonkurration:

Kombinationen av ett grunt B-träd och slumpmässiga infogningar i index kan leda till siduppdelningar i B-trädet. För att kunna utföra en siddelning måste SQL Server hämta delade (SH) spärrar på alla nivåer och sedan hämta exklusiva (EX) spärrar på sidor i B-trädet som är inblandade i siddelningarna. Även när samtidigheten är hög och data kontinuerligt infogas och tas bort kan B-trädrotsdelningar inträffa. I det här fallet kan andra infogningar behöva vänta på eventuella lås som inte är lås på buffertar som hämtats i B-trädet. Detta visas som ett stort antal väntetider på den ACCESS_METHODS_HOBT_VIRTUAL_ROOT spärrtyp som observerats i sys.dm_os_latch_stats DMV.

Följande skript kan ändras för att fastställa djupet i B-trädet för indexen i den berörda tabellen.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Spärrkonkurration på sidor med ledigt utrymme (PFS)

PFS står för sidfritt utrymme, SQL Server allokerar en PFS-sida för varje 8 088 sidor (börjar med PageID = 1) i varje databasfil. Varje byte på PFS-sidan registrerar information, inklusive hur mycket ledigt utrymme som finns på sidan, om den är allokerad eller inte och om sidan lagrar spökposter. PFS-sidan innehåller information om de sidor som är tillgängliga för allokering när en ny sida krävs av en infognings- eller uppdateringsåtgärd. PFS-sidan måste uppdateras i flera scenarier, bland annat när allokeringar eller friallokeringar sker. Eftersom användningen av en uppdateringsspärr (UP) krävs för att skydda PFS-sidan kan spärrkonkurration på PFS-sidor uppstå om du har relativt få datafiler i en filgrupp och ett stort antal CPU-kärnor. Ett enkelt sätt att lösa detta är att öka antalet filer per filgrupp.

Om många PAGELATCH_UP väntetider observeras för PFS- eller SGAM-sidor i tempdb, följ dessa steg för att eliminera denna flaskhals:

1. Lägg till datafiler tempdb så att antalet tempdb-datafiler är lika med antalet processorkärnor på servern.

2. Aktivera SQL Server-spårningsflagga 1118.

Mer information om flaskhalsar i allokering som orsakas av konkurrens på systemsidor finns i blogginlägget Vad är flaskhals för allokering?

Tabellvärdesfunktioner och spärrkonkurration på tempdb

Det finns andra faktorer utöver allokeringskonkurration som kan orsaka spärrkonkurration på tempdb, till exempel tung TVF-användning i frågor.

Hantera spärrkonkurration för olika tabellmönster

I följande avsnitt beskrivs tekniker som kan användas för att hantera eller kringgå prestandaproblem som rör överdriven spärrkonkurration.

Använda en icke-sekventiell ledande indexnyckel

En metod för att hantera spärrkonkurrens är att ersätta en sekventiell indextangent med en icke-sekventiell tangent för att fördela infogningarna jämnt över ett indexintervall.

Detta görs vanligtvis genom att ha en inledande kolumn i indexet som distribuerar arbetsbelastningen proportionellt. Det finns några alternativ här:

Alternativ: Använd en kolumn i tabellen för att distribuera värden över indexnyckelintervallet

Utvärdera din arbetsbelastning för ett naturligt värde som kan användas för att distribuera infogningar över nyckelintervallet. Tänk dig till exempel ett bankscenario för uttagsautomater där ATM_ID kan vara en bra kandidat för att distribuera infogningar i en transaktionstabell för uttag, eftersom en kund endast kan använda en uttagsautomat åt gången. På samma sätt i ett POS-system, kanske Checkout_ID eller ett Butiks-ID skulle vara ett naturligt värde som kan användas för att distribuera inmatningar över ett nyckelintervall. Den här tekniken kräver att du skapar en sammansatt indexnyckel där den inledande nyckelkolumnen antingen är värdet för den identifierade kolumnen eller någon hash för det värdet i kombination med en eller flera extra kolumner för att ge unikhet. I de flesta fall fungerar en hash av värdet bäst, eftersom för många distinkta värden resulterar i dålig fysisk organisation. I ett kassasystem kan till exempel en hash genereras från butiks-ID:t med ett modulo-tal, vilket matchar antalet CPU-kärnor. Den här tekniken skulle resultera i ett relativt litet antal intervall i tabellen, men det skulle räcka för att distribuera infogningar på ett sådant sätt att undvika spärrkonkurration. Följande bild illustrerar den här tekniken.

Det här mönstret implementerades under ett prestandalabbngagemang och löste spärrkonkurration i ett system med 32 fysiska CPU-kärnor. Tabellen användes för att lagra slutsaldot i slutet av en transaktion. varje affärstransaktion utförde en enda infogning i tabellen.

Ursprunglig tabelldefinition

Vid användning av den ursprungliga tabelldefinitionen observerades överdriven lås konkurrens på det klustrade indexet pk_table1.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Ändra ordning på indexdefinitionen

Omordningen av nyckelkolumnerna i indexet med UserID som inledande kolumn i primärnyckeln gav en nästan slumpmässig fördelning av infogningar över sidorna. Den resulterande fördelningen var inte 100% slumpmässig eftersom inte alla användare är online samtidigt, men fördelningen var slumpmässig nog för att lindra överdriven spärrkonkurring. En varning för att ändra ordning på indexdefinitionen är att alla utvalda frågor mot den här tabellen måste ändras för att använda både UserID och TransactionID som likhetspredikat.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Använda ett hash-värde som den inledande kolumnen i primärnyckeln

Följande tabelldefinition kan användas för att generera en modulo som överensstämmer med antalet processorer, HashValue genereras med hjälp av det sekventiellt ökande värdet TransactionID för att säkerställa en enhetlig fördelning över B-trädet:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Alternativ: Använd ett GUID som den inledande nyckelkolumnen i indexet

Om det inte finns någon naturlig avgränsare kan en GUID-kolumn användas som en inledande nyckelkolumn i indexet för att säkerställa en enhetlig fördelning av infogningar. När du använder GUID som den inledande kolumnen i indexnyckelns uppsättning kan du använda partitionering för andra funktioner, men den här tekniken kan också introducera potentiella nackdelar med fler siduppdelningar, dålig fysisk organisation och låg sidtäthet.

Använd hashpartitionering med en beräknad kolumn

Tabellpartitionering i SQL Server kan användas för att minimera överdriven spärrkonkurring. Att skapa ett hashpartitioneringsschema med en beräknad kolumn i en partitionerad tabell är en vanlig metod som kan utföras med följande steg:

1. Skapa en ny filgrupp eller använd en befintlig filgrupp för att lagra partitionerna.

2. Om du använder en ny filgrupp balanserar du enskilda filer jämnt över LUN och ser till att använda en optimal layout. Om åtkomstmönstret omfattar en hög infogningshastighet måste du skapa samma antal filer som det finns fysiska CPU-kärnor på SQL Server-datorn.

3. CREATE PARTITION FUNCTION Använd kommandot för att partitionera tabellerna i X-partitioner, där X är antalet fysiska CPU-kärnor på SQL Server-datorn. (minst upp till 32 partitioner)

   Anmärkning

   En 1:1-justering av antalet partitioner till antalet CPU-kärnor är inte alltid nödvändigt. I många fall kan detta vara ett värde som är mindre än antalet CPU-kärnor. Att ha fler partitioner kan leda till mer omkostnader för frågor som måste söka i alla partitioner, och i dessa fall kan färre partitioner hjälpa till. I SQLCAT-testning på 64- och 128 logiska CPU-system med verkliga kundarbetsbelastningar har 32 partitioner varit tillräckliga för att lösa överdriven spärrkonkurretion och nå skalningsmål. I slutändan bör det idealiska antalet partitioner fastställas genom testning.

4. Använd kommandot CREATE PARTITION SCHEME:

   • Binda partitionsfunktionen till filgrupperna.
   • Lägg till en hash-kolumn av typen tinyint eller smallint i tabellen.
   • Beräkna en bra hash-fördelning. Använd till exempel HASHBYTES med modulo eller BINARY_CHECKSUM.

Följande exempelskript kan anpassas för implementeringen:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Det här skriptet kan användas för att hashpartitionera en tabell som har problem som orsakas av infogningskonkurens för senaste sida/avslutande sida. Den här tekniken flyttar konkurrensen från den sista sidan genom att partitionera tabellen och distribuera infogningar mellan tabellpartitioner med en åtgärd för hash-värdemodulus.

Vad hashpartitionering med en beräknad kolumn gör

Som följande diagram illustrerar flyttar den här tekniken konkurrensen från den sista sidan genom att återskapa indexet på hash-funktionen och skapa samma antal partitioner som det finns fysiska CPU-kärnor på SQL Server-datorn. Insättningarna går fortfarande in i slutet av det logiska intervallet (ett sekventiellt ökande värde), men hashvärdemodulsoperationen säkerställer att insättningarna delas upp mellan de olika B-träden, vilket avhjälper flaskhalsen. Detta illustreras i följande diagram:

Kompromisser vid användning av hashpartitionering

Även om hashpartitionering kan eliminera konkurrens om infogningar finns det flera kompromisser att tänka på när du bestämmer dig för om du vill använda den här tekniken eller inte:

• Select-frågor måste i de flesta fall ändras för att inkludera hash-partitionen i predikatet och leda till en frågeplan som inte ger någon partitionseliminering när dessa frågor körs. Följande skärmbild visar en felaktig plan utan partitionseliminering efter att hashpartitionering har implementerats.

  

• Det eliminerar risken för partitionseliminering på vissa andra frågor, till exempel intervallbaserade rapporter.

• När du ansluter en hashpartitionerad tabell till en annan tabell behöver den andra tabellen vara hashpartitionerad på samma nyckel för att uppnå partitionseffektivisering, och hashnyckeln ska ingå i kopplingsvillkoren.

• Hash-partitionering förhindrar partitionering för andra hanteringsfunktioner, till exempel arkivering av skjutfönster och funktioner för partitionsväxling.

Hash-partitionering är en effektiv strategi för att minimera överdriven spärrkonkurrens eftersom det ökar det totala systemets dataflöde genom att minska konkurrensen om infogningar. Eftersom det finns vissa kompromisser kanske det inte är den optimala lösningen för vissa åtkomstmönster.

Sammanfattning av tekniker som används för att hantera spärrkonkurration

Följande två avsnitt innehåller en sammanfattning av de tekniker som kan användas för att hantera överdriven spärrkonkurration:

Icke-sekventiell nyckel/index

fördelar:

• Tillåter användning av andra partitioneringsfunktioner, till exempel arkivering av data med ett skjutfönsterschema och funktioner för partitionsväxling.

Nackdelar:

• Möjliga utmaningar när du väljer en nyckel/index för att säkerställa "tillräckligt nära" enhetlig fördelning av infogningar hela tiden.
• GUID som en inledande kolumn kan användas för att garantera en enhetlig fördelning med varningen att det kan leda till alltför stora sidesplittringar.
• Slumpmässiga infogningar över B-träd kan resultera i för många siduppdelningsoperationer och leda till låskonflikter på icke-lövsidor.

Hash-partitionering med beräknad kolumn

fördelar:

• Transparent för infogningar.

Nackdelar:

• Partitionering kan inte användas för avsedda hanteringsfunktioner, till exempel arkivering av data med alternativ för partitionsväxling.
• Kan orsaka problem med partitionseliminering för frågor som enskilda och intervallbaserade select/update och frågor som utför en koppling.
• Att lägga till en bevarad beräknad kolumn är en offlineåtgärd.

Genomgång: Diagnostisera en låsningstillfredsställelse

Följande genomgång visar de verktyg och tekniker som beskrivs i Diagnostisera SQL Server-spärrkonkurrens och hantering av spärrkonkurrens för olika tabellmönster för att lösa ett problem i ett verkligt scenario. I det här scenariot beskrivs ett kundengagemang för att utföra belastningstestning av ett försäljningsställessystem som simulerade cirka 8 000 butiker som utförde transaktioner mot ett SQL Server-program som körs på ett system med 8 socketar och 32 fysiska kärnor med 256 GB RAM.

Följande diagram beskriver maskinvaran som används för att testa platsen för försäljningssystemet:

Symptom: Varma låsningar

I det här fallet observerade vi höga väntetider där PAGELATCH_EX vi vanligtvis definierar hög som ett genomsnitt på mer än 1 ms. I det här fallet observerade vi konsekvent väntetider som översteg 20 ms.

När vi kom fram till att spärrkonkurrens var problematisk bestämde vi oss sedan för att avgöra vad som orsakade spärrkonkurrensen.

Isolera objektet som orsakar låskonflikt

Följande skript använder kolumnen resource_description för att isolera vilket index som orsakade PAGELATCH_EX konkurrensen:

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Som du ser här finns konflikten på tabellen LATCHTEST och indexnamnet CIX_LATCHTEST. Anteckningsnamn har ändrats för att anonymisera arbetsbelastningen.

Ett mer avancerat skript som avsöker upprepade gånger och använder en tillfällig tabell för att fastställa den totala väntetiden under en konfigurerbar period finns i Frågebuffertbeskrivningar för att fastställa objekt som orsakar spärrkonkurrens i tillägget.

Alternativ teknik för att isolera objektet som orsakar låsstrid

Ibland kan det vara opraktiskt att fråga sys.dm_os_buffer_descriptors. När minnet i systemet och tillgängligt för buffertpoolen ökar så ökar även den tid som krävs för att köra denna DMV. På ett 256 GB-system kan det ta upp till 10 minuter eller mer för den här DMV:en att köras. En alternativ teknik är tillgänglig och beskrivs brett enligt följande och illustreras med en annan arbetsbelastning, som vi körde i labbet:

1. Fråga aktuella väntande uppgifter med hjälp av skriptet Bilaga Fråga sys.dm_os_waiting_tasks Sorterad efter väntetid.

2. Identifiera den nyckelsida där en konvoj observeras, vilket händer när flera trådar körs på samma sida. I det här exemplet konkurrerar trådarna som utför infogningen på den avslutande sidan i B-trädet och väntar tills de kan hämta en EX spärr. Detta anges av resource_description i den första frågan, i vårt fall 8:1:111305.

3. Aktivera spårningsflagga 3604, som visar ytterligare information om sidan via DBCC PAGE med följande syntax, ersätt värdet du fick via resource_description för värdet inom parentes:

   Aktivera spårningsflagga 3604 för att aktivera konsolutdata:

   DBCC TRACEON (3604);
   

   Granska informationen på sidan:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Granska DBCC-utdata. Det bör finnas ett associerat ObjectID för metadata, i vårt fall 78623323.

   

5. Vi kan nu köra följande kommando för att fastställa namnet på det objekt som orsakar konkurrensen, vilket som förväntat är LATCHTEST.

   Anmärkning

   Kontrollera att du har rätt databaskontext, annars returnerar NULLfrågan .

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Sammanfattning och resultat

Med hjälp av tekniken ovan kunde vi bekräfta att konkurrensen inträffade på ett klustrat index med ett sekventiellt ökande nyckelvärde i tabellen som överlägset tog emot det högsta antalet infogningar. Den här typen av konkurrens är inte ovanlig för index med ett sekventiellt ökande nyckelvärde, till exempel datetime, identitet eller ett programgenererat TransactionID.

För att lösa det här problemet använde vi hashpartitionering med en beräknad kolumn och observerade en prestandaförbättring på 690%. I följande tabell sammanfattas programmets prestanda före och efter implementeringen av hash-partitionering med en beräknad kolumn. Processoranvändningen ökar i stort sett i linje med dataflödet som förväntat efter att flaskhalsen med spärrkonkurrensen togs bort:

Som du ser i föregående tabell kan korrekt identifiering och lösning av prestandaproblem som orsakas av överdriven sidspärrkonkurring ha en positiv effekt på programmets övergripande prestanda.

Bilaga: Alternativ teknik

En möjlig strategi för att undvika överdriven sidspärrkonkurration är att fylla rader med en teckenkolumn för att säkerställa att varje rad använder en hel sida. Den här strategin är ett alternativ när den totala datastorleken är liten och du behöver åtgärda EX sidspärrkonkurrationen som orsakas av följande kombination av faktorer:

• Liten radstorlek
• Grunt B-träd
• Åtkomstmönster med hög frekvens av slumpmässiga åtgärder för att infoga, välja, uppdatera och ta bort
• Små tabeller, till exempel tillfälliga kötabeller

Genom att fylla ut rader för att täcka en hel sida innebär det att SQL allokerar fler sidor, vilket gör fler sidor tillgängliga för infogningar och minskar konkurrensen om sidlås EX.

Komplettera rader så att varje rad upptar en hel sida

Ett skript som liknar följande kan användas för att fylla rader för att uppta en hel sida:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Den här tekniken förklaras för fullständighet; I praktiken har SQLCAT endast använt detta i en liten tabell med 10 000 rader i ett enda prestandaengagemang. Den här tekniken har en begränsad användning eftersom den ökar minnesbelastningen på SQL Server för stora tabeller och kan resultera i latch-konflikt på icke-lövsidor. Det extra minnestrycket kan vara en betydande begränsande faktor för tillämpningen av den här tekniken. Med mängden minne som är tillgängligt på en modern server lagras vanligtvis en stor del av arbetsuppsättningen för OLTP-arbetsbelastningar i minnet. När datauppsättningen ökar till en storlek som den inte längre passar i minnet uppstår en betydande minskning av prestanda. Därför är den här tekniken något som endast gäller för små tabeller. Den här tekniken används inte av SQLCAT för scenarier som sista sidan/konkurrens vid insättning av efterföljande sida för tabeller med stora datamängder.

Bilaga: SQL Server-spärrinnehållningsskript

Det här avsnittet innehåller skript som kan användas för att identifiera och åtgärda problem med låsbelastning.

Kör en förfrågan mot sys.dm_os_waiting_tasks sorterad efter sessions-ID

Följande exempelskript interrogerar sys.dm_os_waiting_tasks och returnerar låsväntningar ordnade efter sessions-ID:

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Fråga sys.dm_os_waiting_tasks ordnad efter väntetidens längd

Följande exempelskript frågar av sys.dm_os_waiting_tasks och returnerar spärrväntetider sorterade efter väntetid:

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Beräkna väntetider under en tidsperiod

Följande skript beräknar och returnerar spärrväntetid under en tidsperiod.

/* Snapshot the current wait 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 AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

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

Förfrågningsbuffertbeskrivningar för att fastställa objekt som orsakar spärrkonflikt

Följande skript frågar buffertbeskrivningar för att avgöra vilka objekt som är associerade med de längsta väntetiderna för spärren.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Hashpartitioneringsskript

Användningen av det här skriptet beskrivs i Använda hashpartitionering med en beräknad kolumn och bör anpassas för implementeringen.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Relaterat innehåll

• Verktyg för prestandaövervakning och justering