Aanbevelingen voor het verminderen van toewijzingsconflicten in SQL Server tempdb-database

  • Artikel

Dit artikel helpt u bij het oplossen van het probleem waarbij u ernstige blokkeringen ziet wanneer de server een zware belasting ondervindt.

Originele productversie: SQL Server
Origineel KB-nummer: 2154845

Symptomen

Op een server waarop Microsoft SQL Server wordt uitgevoerd, ziet u ernstige blokkeringen wanneer de server een zware belasting ondervindt. Dynamische beheerweergaven [sys.dm_exec_request of sys.dm_os_waiting_tasks] geeft aan dat deze aanvragen of taken wachten op tempdb-resources . Bovendien is PAGELATCH_UPhet wachttype , en verwijst de wachtresource naar pagina's in tempdb. Deze pagina's hebben mogelijk de indeling 2:1:1, 2:1:3, enzovoort (PFS- en SGAM-pagina's in tempdb).

Opmerking

Als een pagina gelijkmatig deelbaar is op 8088, is het een PFS-pagina. Pagina 2:3:905856 is bijvoorbeeld een PFS in file_id=3 in tempdb.

Bij de volgende bewerkingen wordt tempdb uitgebreid gebruikt:

  • Terugkerende bewerking voor het maken en neerzetten van tijdelijke tabellen (lokaal of globaal).
  • Tabelvariabelen die tempdb gebruiken voor opslag.
  • Werktabellen die zijn gekoppeld aan CURSORS.
  • Werktabellen die zijn gekoppeld aan een ORDER BY-component.
  • Werktabellen die zijn gekoppeld aan een GROUP BY-component.
  • Werkbestanden die zijn gekoppeld aan HASH-PLANNEN.

Deze activiteiten kunnen conflictproblemen veroorzaken.

Oorzaak

Wanneer de tempdb-database intensief wordt gebruikt, kan SQL Server conflicten ervaren wanneer wordt geprobeerd pagina's toe te wijzen. Afhankelijk van de mate van conflicten kan dit ertoe leiden dat query's en aanvragen waarbij tempdb is betrokken, kort niet meer reageren.

Tijdens het maken van objecten moeten twee (2) pagina's worden toegewezen vanuit een gemengde omvang en worden toegewezen aan het nieuwe object. De ene pagina is voor de indextoewijzingstoewijzing (IAM) en de tweede voor de eerste pagina voor het object. SQL Server houdt gemengde hoeveelheden bij met behulp van de SGAM-pagina (Shared Global Allocation Map). Elke SGAM-pagina houdt ongeveer 4 gigabyte aan gegevens bij.

Als u een pagina wilt toewijzen vanuit de gemengde mate, moet SQL Server de pagina Pagina vrije ruimte (PFS) scannen om te bepalen welke gemengde pagina gratis kan worden toegewezen. Op de PFS-pagina wordt de beschikbare ruimte op elke pagina bijgehouden en op elke PFS-pagina worden ongeveer 8000 pagina's bijgehouden. De juiste synchronisatie wordt onderhouden om wijzigingen aan te brengen in de PFS- en SGAM-pagina's; en dat kan andere modifiers voor korte perioden laten staan.

Wanneer SQL Server zoekt naar een gemengde pagina om toe te wijzen, wordt de scan altijd gestart op hetzelfde bestand en dezelfde SGAM-pagina. Dit veroorzaakt intense conflicten op de SGAM-pagina wanneer verschillende toewijzingen van gemengde pagina's aan de gang zijn. Dit kan de problemen veroorzaken die worden beschreven in de sectie Symptomen .

Opmerking

Activiteiten die de toewijzing ongedaan maken, moeten ook de pagina's wijzigen. Dit kan bijdragen aan de toegenomen conflicten.

Zie de sectie Verwijzingen voor meer informatie over de verschillende toewijzingsmechanismen die worden gebruikt door SQL Server (SGAM, GAM, PFS, IAM).

Oplossing

  • SQL Server 2016 en nieuwere versies:

    Beoordelen

    Raadpleeg voor meer informatie over deze aanbevelingen en andere wijzigingen die zijn geïntroduceerd in SQL 2016

  • SQL Server 2014 en eerdere versies:

    Probeer de volgende methoden om de gelijktijdigheid van tempdb te verbeteren:

    • Verhoog het aantal gegevensbestanden in tempdb om de schijfbandbreedte te maximaliseren en conflicten in toewijzingsstructuren te verminderen. Als het aantal logische processors kleiner is dan of gelijk is aan acht (8), gebruikt u in de regel hetzelfde aantal gegevensbestanden als logische processors. Als het aantal logische processors groter is dan acht (8), gebruikt u acht gegevensbestanden. Als het conflict zich blijft voordoen, verhoogt u het aantal gegevensbestanden met veelvouden van vier (4) tot het aantal logische processors totdat het conflict is teruggebracht tot een acceptabel niveau. U kunt ook wijzigingen aanbrengen in de workload of code.

    • Overweeg de aanbevelingen voor aanbevolen procedures te implementeren in Werken met tempdb in SQL Server 2005.

    • Als de vorige stappen het toewijzingsconflict niet aanzienlijk verminderen en het conflict zich op SGAM-pagina's bevindt, implementeert u traceringsvlag -T1118. Onder deze traceringsvlag wijst SQL Server volledige omvang toe aan elk databaseobject, waardoor het conflict op SGAM-pagina's wordt geëlimineerd.

      Opmerking

Verhoog het aantal tempdb-gegevensbestanden met gelijke grootten

Als de grootte van het individuele gegevensbestand van tempdb bijvoorbeeld 8 GB is en de grootte van het logboekbestand 2 GB is, is het raadzaam om het aantal gegevensbestanden te verhogen tot acht (8) (elk van 1 GB om de grootte gelijk te houden) en het logboekbestand ongewijzigd te laten. Als u de verschillende gegevensbestanden op afzonderlijke schijven hebt, is dit een extra prestatievoordeel. Dit is echter niet vereist. De bestanden kunnen naast elkaar op hetzelfde schijfvolume bestaan.

Het optimale aantal tempdb-gegevensbestanden is afhankelijk van de mate van conflicten in tempdb. Als uitgangspunt kunt u tempdb zo configureren dat deze ten minste gelijk is aan het aantal logische processors dat is toegewezen voor SQL Server. Voor hogere systemen kan het beginnummer acht (8) zijn. Als het conflict niet wordt verminderd, moet u mogelijk het aantal gegevensbestanden verhogen.

U wordt aangeraden gelijke grootte van gegevensbestanden te gebruiken. SQL Server 2000 Service Pack 4 (SP4) heeft een oplossing geïntroduceerd die gebruikmaakt van een round robin-algoritme voor gemengde paginatoewijzingen. Vanwege deze verbetering verschilt het beginbestand voor elke opeenvolgende toewijzing van gemengde pagina's (als er meer dan één bestand bestaat). Het nieuwe toewijzingsalgoritme voor SGAM is pure round robin en voldoet niet aan de proportionele opvulling om de snelheid te behouden. U wordt aangeraden alle tempdb-gegevensbestanden van dezelfde grootte te maken.

Hoe het verhogen van het aantal tempdb-gegevensbestanden conflicten vermindert

In de volgende lijst wordt uitgelegd hoe het verhogen van het aantal tempdb-gegevensbestanden met gelijke grootte de conflicten vermindert:

  • Als u één gegevensbestand voor de tempdb hebt, hebt u slechts één GAM-pagina en één SGAM-pagina voor elke 4 GB aan ruimte.

  • Als u het aantal gegevensbestanden met dezelfde grootte voor tempdb verhoogt, worden voor elk gegevensbestand een of meer GAM- en SGAM-pagina's gemaakt.

  • Het toewijzingsalgoritme voor GAM wijst één mate tegelijk (acht aaneengesloten pagina's) toe van het aantal bestanden op een round robin-manier, waarbij de proportionele opvulling wordt toegepast. Als u dus 10 bestanden van gelijke grootte hebt, is de eerste toewijzing van Bestand1, de tweede van Bestand2, de derde van Bestand3, enzovoort.

  • De resourceconflicten van de PFS-pagina worden verminderd omdat acht pagina's tegelijk zijn gemarkeerd als VOLLEDIG omdat GAM de pagina's toedeelt.

Hoe de implementatie van traceringsvlag -T1118 conflicten vermindert

Opmerking

Deze sectie is alleen van toepassing op SQL Server 2014 en eerdere versies.

In de volgende lijst wordt uitgelegd hoe het gebruik van traceringsvlag -T1118 conflicten vermindert:

  • -T1118 is een instelling voor de hele server.
  • Neem de traceringsvlag -T1118 op in de opstartparameters voor SQL Server, zodat de traceringsvlag van kracht blijft, zelfs nadat SQL Server is gerecycled.
  • -T1118 verwijdert bijna alle toewijzingen met één pagina op de server.
  • Door de meeste toewijzingen met één pagina uit te schakelen, vermindert u het conflict op de SGAM-pagina.
  • Als -T1118 is ingeschakeld, worden bijna alle nieuwe toewijzingen gemaakt vanaf een GAM-pagina (bijvoorbeeld 2:1:2) die acht (8) pagina's (één mate) tegelijk toewijst aan een object in plaats van één pagina vanaf een bereik voor de eerste acht (8) pagina's van een object, zonder de traceringsvlag.
  • De IAM-pagina's gebruiken nog steeds de toewijzingen van één pagina van de SGAM-pagina, zelfs als -T1118is ingeschakeld. Wanneer dit echter wordt gecombineerd met hotfix 8.00.0702 en verhoogde tempdb-gegevensbestanden , is het netto-effect een vermindering van het conflict op de SGAM-pagina. Zie de volgende sectie voor ruimteproblemen.

Nadelen

Het nadeel van het gebruik van -T1118 is dat u mogelijk een toename in databasegrootte ziet als aan de volgende voorwaarden wordt voldaan:

  • Nieuwe objecten worden gemaakt in een gebruikersdatabase.
  • Elk van de nieuwe objecten neemt minder dan 64 kB aan opslagruimte in beslag.

Als aan deze voorwaarden wordt voldaan, kunt u 64 kB (acht pagina's * 8 kB = 64 kB) toewijzen aan een object dat slechts 8 kB aan ruimte vereist, waardoor 56 kB aan opslagruimte wordt verkpild. Als het nieuwe object echter meer dan 64 kB (acht pagina's) gebruikt in de levensduur, is er geen nadeel aan de traceringsvlag. Daarom kan SQL Server in het ergste geval tijdens de eerste toewijzing zeven (7) extra pagina's toewijzen aan nieuwe objecten die nooit meer dan één (1) pagina overschrijden.

Verwijzingen