Delen via

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

  • Artikel

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

Oorspronkelijke productversie: SQL Server
Oorspronkelijk 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] geven aan dat deze aanvragen of taken wachten op tempdb-resources . Daarnaast is PAGELATCH_UPhet wachttype en de wachtresource verwijst 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).

Notitie

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

Voor de volgende bewerkingen wordt tempdb uitgebreid gebruikt:

  • Terugkerende bewerking voor 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 conflicten veroorzaken.

Oorzaak

Wanneer de tempdb-database intensief wordt gebruikt, kan SQL Server conflicten ondervinden bij het toewijzen van pagina's. Afhankelijk van de mate van conflicten kan dit ertoe leiden dat query's en aanvragen waarbij tempdb betrokken is, 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. Eén pagina is voor de Index Allocation Map (IAM) en de tweede pagina is voor de eerste pagina voor het object. SQL Server houdt gemengde gebieden bij met behulp van de pagina Shared Global Allocation Map (SGAM). Elke SGAM-pagina houdt ongeveer 4 gigabyte aan gegevens bij.

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

Wanneer SQL Server zoekt naar een gemengde pagina die moet worden toegewezen, wordt de scan altijd gestart op hetzelfde bestand en op de SGAM-pagina. Dit veroorzaakt intense conflicten op de SGAM-pagina wanneer er verschillende toewijzingen op gemengde pagina's worden uitgevoerd. Dit kan de problemen veroorzaken die worden beschreven in de sectie Symptomen .

Notitie

De toewijzingsactiviteiten 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

Het aantal tempdb-gegevensbestanden met gelijke grootte verhogen

Als de grootte van één 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 naar acht (8) (elk van 1 GB om de grootte te behouden) en het logboekbestand te laten zoals dat is. Het gebruik van de verschillende gegevensbestanden op afzonderlijke schijven zou extra prestatievoordelen bieden. Dit is echter niet vereist. De bestanden kunnen naast elkaar bestaan op hetzelfde schijfvolume.

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 systemen met een hoger niveau 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 met 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 ruimte.

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

  • Het toewijzingsalgoritme voor GAM wijst één mate per keer toe (acht aaneengesloten pagina's) van het aantal bestanden op round robin-wijze terwijl de proportionele opvulling wordt uitgevoerd. Als u dus 10 bestanden met gelijke grootte hebt, is de eerste toewijzing afkomstig van File1, de tweede van File2, de derde uit File3, enzovoort.

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

Hoe het implementeren van traceringsvlag -T1118 conflicten vermindert

Notitie

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 serverbrede instelling.
  • 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 van één pagina op de server.
  • Door de meeste toewijzingen van één pagina uit te schakelen, vermindert u de conflicten op de SGAM-pagina.
  • Als -T1118 is ingeschakeld, worden bijna alle nieuwe toewijzingen gemaakt van een GAM-pagina (bijvoorbeeld 2:1:2) die acht (8) pagina's (één mate) tegelijk aan een object toewijst in plaats van één pagina uit een bepaalde mate 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 -T1118 is ingeschakeld. Wanneer het echter wordt gecombineerd met hotfix 8.00.0702 en verhoogde tempdb-gegevensbestanden , is het netto-effect een vermindering van conflicten op de SGAM-pagina. Zie de volgende sectie voor ruimteproblemen.

Nadelen

Het nadeel van het gebruik van -T1118 is dat de databasegrootte mogelijk toeneemt 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 opslag in beslag.

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

Verwijzingen