Rekommendationer för att minska allokeringskonkurrationen i SQL Server tempdb-databas

  • Artikel

Den här artikeln hjälper dig att lösa problemet där du märker allvarlig blockering när servern har hög belastning.

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 2154845

Symptom

På en server som kör Microsoft SQL Server ser du allvarlig blockering när servern har hög belastning. Dynamiska hanteringsvyer [sys.dm_exec_request eller sys.dm_os_waiting_tasks] anger att dessa begäranden eller uppgifter väntar på tempdb-resurser . Dessutom är PAGELATCH_UPväntetypen , och vänteresursen pekar på sidor i tempdb. Dessa sidor kan ha formatet 2:1:1, 2:1:3 och så vidare (PFS- och SGAM-sidor i tempdb).

Obs!

Om en sida är jämnt delbar med 8088 är den en PFS-sida. Sidan 2:3:905856 är till exempel en PFS i file_id=3 i tempdb.

Följande åtgärder använder tempdb i stor utsträckning:

  • Upprepad skapande-och-släpp-åtgärd för temporära tabeller (lokal eller global).
  • Tabellvariabler som använder tempdb för lagring.
  • Arbetstabeller som är associerade med CURSORS.
  • Arbetstabeller som är associerade med en ORDER BY-sats.
  • Arbetstabeller som är associerade med en GROUP BY-sats.
  • Arbetsfiler som är associerade med HASH-PLANER.

Dessa aktiviteter kan orsaka konkurrensproblem.

Orsak

När tempdb-databasen används mycket kan SQL Server uppleva konkurrens när den försöker allokera sidor. Beroende på graden av konkurrens kan detta orsaka att frågor och begäranden som involverar tempdb kort slutar svara.

När objektet skapas måste två (2) sidor allokeras från en blandad omfattning och tilldelas till det nya objektet. En sida är för indexallokeringskartan (IAM) och den andra är för den första sidan för objektet. SQL Server spårar blandade utrymmen med hjälp av sidan Delad global allokeringskarta (SGAM). Varje SGAM-sida spårar cirka 4 gigabyte data.

Om du vill allokera en sida från den blandade omfattningen måste SQL Server genomsöka sidan Sidutrymme (PFS) för att avgöra vilken blandad sida som ska allokeras. PFS-sidan håller reda på ledigt utrymme på varje sida och varje PFS-sida spårar cirka 8 000 sidor. Lämplig synkronisering upprätthålls för att göra ändringar på PFS- och SGAM-sidorna; och som kan fördröja andra modifierare under korta perioder.

När SQL Server söker efter en blandad sida att allokera startas alltid genomsökningen på samma fil- och SGAM-sida. Detta orsakar intensiv konkurrens på SGAM-sidan när flera allokeringar med blandade sidor pågår. Detta kan orsaka de problem som beskrivs i avsnittet Symptom .

Obs!

Avallokeringsaktiviteter måste också ändra sidorna. Detta kan bidra till ökad konkurrens.

Mer information om de olika allokeringsmekanismer som används av SQL Server (SGAM, GAM, PFS, IAM) finns i avsnittet Referenser.

Åtgärd

Öka antalet tempdb-datafiler som har lika stor storlek

Om den enskilda datafilstorleken för tempdb till exempel är 8 GB och loggfilens storlek är 2 GB, rekommenderar vi att du ökar antalet datafiler till åtta (8) (var och en av 1 GB för att behålla samma storlek) och lämna loggfilen som den är. Att ha de olika datafilerna på separata diskar skulle ge ytterligare prestandafördelar. Detta krävs dock inte. Filerna kan samexistera på samma diskvolym.

Det optimala antalet tempdb-datafiler beror på graden av konkurrens som visas i tempdb. Som utgångspunkt kan du konfigurera tempdb så att det är minst lika med antalet logiska processorer som har tilldelats för SQL Server. För system med högre slutpunkt kan startnumret vara åtta (8). Om konkurrensen inte minskar kan du behöva öka antalet datafiler.

Vi rekommenderar att du använder lika stor storlek på datafiler. SQL Server 2000 Service Pack 4 (SP4) introducerade en korrigering som använder en resursallokeringsalgoritm för blandat sidtilldelning. På grund av den här förbättringen är startfilen olika för varje på varandra följande mixed page-allokering (om det finns fler än en fil). Den nya allokeringsalgoritmen för SGAM är ren resursallokering och respekterar inte den proportionella fyllningen för att upprätthålla hastigheten. Vi rekommenderar att du skapar alla tempdb-datafiler med samma storlek.

Hur en ökning av antalet tempdb-datafiler minskar konkurrensen

I följande lista förklaras hur en ökning av antalet tempdb-datafiler med lika storlek minskar konkurrensen:

  • Om du har en datafil för tempdb har du bara en GAM-sida och en SGAM-sida för varje 4 GB utrymme.

  • Om du ökar antalet datafiler som har samma storlekar för tempdb skapas effektivt en eller flera GAM- och SGAM-sidor för varje datafil.

  • Allokeringsalgoritmen för GAM allokerar en omfattning i taget (åtta sammanhängande sidor) från antalet filer på ett resursallokeringssätt samtidigt som den proportionella fyllningen respekteras. Om du har 10 lika stora filer är därför den första allokeringen från File1, den andra från File2, den tredje från File3 och så vidare.

  • Resurskonkurrationen på PFS-sidan minskas eftersom åtta sidor i taget markeras som FULL eftersom GAM allokerar sidorna.

Så här minskar implementeringen av spårningsflaggan -T1118 konkurrensen

Obs!

Det här avsnittet gäller endast för SQL Server 2014 och tidigare versioner.

I följande lista förklaras hur användningen av spårningsflaggan -T1118 minskar konkurrensen:

  • -T1118 är en serveromfattande inställning.
  • Inkludera spårningsflaggan -T1118 i startparametrarna för SQL Server så att spårningsflaggan fortsätter att gälla även efter att SQL Server har återvunnits.
  • -T1118 tar bort nästan alla ensidesallokeringar på servern.
  • Genom att inaktivera de flesta ensidesallokeringar minskar du konkurrensen på SGAM-sidan.
  • Om -T1118 är aktiverat görs nästan alla nya allokeringar från en GAM-sida (till exempel 2:1:2) som allokerar åtta (8) sidor (en omfattning) åt gången till ett objekt i stället för en enda sida från en omfattning för de första åtta (8) sidorna i ett objekt, utan spårningsflaggan.
  • IAM-sidorna använder fortfarande ensidesallokeringar från SGAM-sidan, även om -T1118 är aktiverat. Men när den kombineras med snabbkorrigering 8.00.0702 och ökade tempdb-datafiler är nettoeffekten en minskning av konkurrensen på SGAM-sidan. Mer information om utrymmesproblem finns i nästa avsnitt.

Nackdelar

Nackdelen med att använda -T1118 är att du kan se ökningar i databasstorlek om följande villkor är uppfyllda:

  • Nya objekt skapas i en användardatabas.
  • Vart och ett av de nya objekten upptar mindre än 64 KB lagringsutrymme.

Om dessa villkor är uppfyllda kan du allokera 64 KB (åtta sidor * 8 KB = 64 KB) för ett objekt som bara kräver 8 KB utrymme, vilket slösar bort 56 KB lagringsutrymme. Men om det nya objektet använder mer än 64 kB (åtta sidor) under sin livstid finns det ingen nackdel med spårningsflaggan. I värsta fall kan SQL Server därför allokera sju (7) ytterligare sidor under den första allokeringen endast för nya objekt som aldrig växer längre än en (1) sida.

Referenser