Tempdb-ruimteresourcebeheer

Van toepassing op: SQL Server 2025 (17.x) en latere versies

Wanneer u het beheer van ruimteresources inschakelt tempdb, verbetert u de betrouwbaarheid en voorkomt u storingen door te verhinderen dat ongecontroleerde query's of workloads een grote hoeveelheid ruimte in tempdb verbruiken.

Vanaf SQL Server 2025 (17.x) kunt u de resource governor gebruiken om een limiet af te dwingen voor de totale hoeveelheid ruimte die tempdb door een workloadgroep wordt verbruikt. Wanneer een verzoek (een query) de limiet probeert te overschrijden, breekt Resource Governor dit af met een specifieke foutmelding die aangeeft dat de limiet van de werklastgroep wordt gehandhaafd.

In feite kunt u de gedeelde tempdb ruimte partitioneren tussen verschillende workloads. U kunt bijvoorbeeld een hogere limiet instellen voor een workloadgroep die wordt gebruikt door een bedrijfskritieke toepassing en een lagere limiet instellen voor de default werkbelastinggroep die door alle andere workloads wordt gebruikt.

Zie Zelfstudie: Voorbeelden voor het configureren van tempdb-ruimteresourcebeheer voor stapsgewijze configuratievoorbeelden.

Aan de slag met resource governor

Resource Governor biedt een flexibel framework voor het instellen van verschillende tempdb ruimtelimieten voor verschillende toepassingen, gebruikers, gebruikersgroepen, enzovoort. U kunt ook limieten instellen op basis van aangepaste logica.

Als resource governor in SQL Server nieuw voor u is, raadpleegt u resource governor voor meer informatie over de concepten en mogelijkheden ervan.

Zie Handleiding: Voorbeelden en best practices van Resource Governor-configuratie voor een stappenplan en de beste praktijken voor de configuratie van de Resource Governor.

Limieten instellen voor tempdb-ruimteverbruik

U kunt het ruimteverbruik door een workloadgroep op een van de volgende twee manieren beperken tempdb:

• Stel een vaste limiet in met behulp van het GROUP_MAX_TEMPDB_DATA_MB argument.

  Gebruik de vaste limiet wanneer u de vereisten voor workloadgebruik tempdb van tevoren kent of wanneer de tempdb grootte niet verandert.

• Stel een percentagelimiet in met behulp van het GROUP_MAX_TEMPDB_DATA_PERCENT argument.

  Gebruik de percentagelimiet wanneer u de maximale grootte in de loop van de tijd kunt wijzigen en u wilt dat de tempdb ruimte die beschikbaar is voor elke workloadgroep proportioneel wordt gewijzigd zonder de configuratie van tempdb de werkbelastinggroep te wijzigen. Als u bijvoorbeeld een Azure-VM met SQL Server omhoog schaalt en de maximale tempdb grootte verhoogt, neemt ook de tempdb ruimte die beschikbaar is voor elke workloadgroep met een procentlimiet toe.

Zie GROUP_MAX_TEMPDB_DATA_MB of GROUP_MAX_TEMPDB_DATA_PERCENT voor meer informatie over de argumenten CREATE WORKLOAD GROUP en ALTER WORKLOAD GROUP.

Als u zowel vaste als procentlimieten voor dezelfde werkbelastinggroep opgeeft, heeft de vaste limiet voorrang op de procentlimiet.

Op een bepaald SQL Server-exemplaar kunt u een combinatie van workloadgroepen hebben met vaste limieten, percentagelimieten of geen limieten voor tempdb ruimteverbruik. Als u effectieve limieten wilt weergeven, raadpleegt u het voorbeeld van effectieve tempdb-ruimtelimieten per workloadgroep weergeven.

Configuratie van percentagelimiet

Wanneer u de ALTER RESOURCE GOVERNOR RECONFIGURE instructie uitvoert, worden procentlimieten van kracht volgens de volgende tabel:

Zie het voorbeeld van de configuratie van het tempdb om uw configuratie weer te geven.

Houd rekening met het volgende wanneer u percentagelimieten gebruikt:

• Als u de GROUP_MAX_TEMPDB_DATA_PERCENT instelt ALTER RESOURCE GOVERNOR en uitvoert, maar de configuratie van het gegevensbestand niet voldoet aan de vereisten, wordt de instructie voltooid en worden de procentlimieten opgeslagen, maar worden ze niet afgedwongen. In dit geval ontvangt u een waarschuwingsbericht 10989, ernst 10, dat ook is vastgelegd in het foutenlogboek:

  GROUP_MAX_TEMPDB_DATA_PERCENT is not in effect because tempdb
  configuration requirements aren't met.
  

• Als u de percentagelimieten effectief wilt maken, moet u gegevensbestanden opnieuw configureren tempdb om aan de vereisten te voldoen en opnieuw uit te voeren ALTER RESOURCE GOVERNOR RECONFIGURE . Zie FILEGROWTH voor meer informatie over het configureren van MAXSIZE, ALTER DATABASE en .

• Als een percentagelimiet van kracht is en u gegevensbestanden toevoegt, verwijdert of het formaat tempdb ervan wijzigt, moet u resource ALTER RESOURCE GOVERNOR RECONFIGURE governor bijwerken met de nieuwe maximale grootte ( tempdb 100%).

Hoe het werkt

In deze sectie wordt het beheer van ruimteresources uitgebreid beschreven tempdb .

• Wanneer gegevenspagina's in tempdb worden toegewezen en ongedaan gemaakt, houdt Resource Governor de boekhouding bij van de tempdb ruimte verbruikt door elke workloadgroep.

  Als resource governor is ingeschakeld en een tempdb limiet voor ruimteverbruik is ingesteld voor een workloadgroep en een aanvraag (een query) die wordt uitgevoerd in de workloadgroep probeert het totale tempdb ruimteverbruik door de groep boven de limiet te brengen, wordt de aanvraag afgebroken met fout 1138, ernst 17:

  Could not allocate a new page for database 'tempdb' because that
  would exceed the limit set for workload group 'workload-group-name'".
  

  Wanneer een aanvraag wordt afgebroken met fout 1138, wordt de waarde in de total_tempdb_data_limit_violation_count kolom van de sys.dm_resource_governor_workload_groups dynamische beheerweergave (DMV) met één verhoogd en wordt de tempdb_data_workload_group_limit_reached uitgebreide gebeurtenis geactiveerd.

• Resource Governor houdt alle tempdb gebruik bij die kan worden toegeschreven aan een workloadgroep, waaronder tijdelijke tabellen, variabelen (inclusief tabelvariabelen), parameters met tabelwaarden, niet-temporale tabellen, cursors en tempdb gebruik tijdens queryverwerking, zoals spools, overlopen, werktabellen en werkbestanden.

  Het ruimteverbruik voor globale tijdelijke tabellen en niet-tijdgebonden tabellen wordt tempdb opgenomen in de workloadgroep die de eerste rij in de tabel invoegt, zelfs als sessies in andere werkbelastinggroepen rijen in dezelfde tabel toevoegen, wijzigen of verwijderen.

• De geconfigureerde tempdb verbruikslimieten voor elke workloadgroep worden weergegeven in de sys.resource_governor_workload_groups catalogusweergave, in de group_max_tempdb_data_mb en group_max_tempdb_data_percent kolommen.

  Het huidige verbruik en het piekverbruik van tempdb geheugenruimte door een workloadgroep worden weergegeven in de sys.dm_resource_governor_workload_groups DMV, in respectievelijk de tempdb_data_space_kb en peak_tempdb_data_space_kb kolommen.

  Aanbeveling

  tempdb_data_space_kb en peak_tempdb_data_space_kb kolommen in sys.dm_resource_governor_workload_groups worden gehandhaafd, zelfs als er geen limieten voor tempdb ruimteverbruik zijn ingesteld.

  U kunt de classificatiefunctie en workloadgroepen maken zonder in eerste instantie limieten in te stellen. Bewaak tempdb het gebruik door elke groep in de loop van de tijd om representatieve gebruikspatronen vast te stellen en stel vervolgens limieten in zoals vereist.

• tempdb het gebruik door de versiearchieven, inclusief het permanente versiearchief (PVS) wanneer versneld databaseherstel (ADR) is ingeschakeld tempdb, wordt niet beheerd omdat rijversies kunnen worden gebruikt door aanvragen in meerdere workloadgroepen.

• De ruimteconsumptie in tempdb is opgegeven als het aantal gebruikte gegevenspagina's van 8 kB. Zelfs als een pagina niet volledig is gevuld met gegevens, wordt 8 kB toegevoegd aan het tempdb verbruik door een workloadgroep.

• tempdb ruimtebeheer wordt gedurende de levensduur van een werkgroep onderhouden. Als een workloadgroep wordt verwijderd terwijl globale tijdelijke tabellen of niet-tijdelijke tabellen met de gegevens die aan deze workloadgroep zijn toegewezen zich in tempdb bevinden, wordt de ruimte die door deze tabellen wordt gebruikt niet meegeteld onder een andere workloadgroep.

• tempdb ruimteresourcebeheer bepaalt de ruimte in tempdb gegevensbestanden, maar niet de schijfruimte op de onderliggende volumes. Tenzij u gegevensbestanden vooraf vergroot tempdb naar de gewenste grootte, kan de ruimte op de volumes waar tempdb zich bevindt, worden gebruikt door andere bestanden. Als er geen resterende ruimte is om tempdb gegevensbestanden te laten groeien, tempdb is er mogelijk onvoldoende ruimte voordat een limiet voor de werkbelastinggroep voor tempdb het verbruik van ruimte wordt bereikt.

• Ruimteresourcebeheer is tempdb van toepassing op gegevensbestanden, maar niet op het transactielogboekbestand. Schakel tempdb in om ervoor te zorgen dat het transactielogboek tempdb geen grote hoeveelheid ruimte verbruikt.

Verschillen met ruimtetracering op session-niveau

De sys.dm_db_session_space_usage DMV biedt tempdb ruimtetoewijzings- en deallocatiestatistieken voor elke sessie. Zelfs als er slechts één sessie in een workloadgroep is, komen de statistieken van ruimtegebruik van deze DMV mogelijk niet exact overeen met de statistieken uit de sys.dm_resource_governor_workload_groups-weergave, om de volgende redenen:

• In tegenstelling tot sys.dm_resource_governor_workload_groups, : sys.dm_db_session_space_usage
  • Geeft geen ruimtegebruik weer tempdb door de taken die momenteel worden uitgevoerd. sys.dm_db_session_space_usage Statistieken worden bijgewerkt wanneer een taak is voltooid. Statistieken in sys.dm_resource_governor_workload_groups worden continu bijgewerkt.
  • Houdt geen pagina's bij van de Index Allocation Map (IAM). Zie de handleiding voor pagina- en gebiedsarchitectuur voor meer informatie.
• Nadat rijen zijn verwijderd, of wanneer een tabel, index of partitie wordt verwijderd of leeggemaakt, maakt de database-engine de gegevenspagina's vrij. De deallocatie kan synchroon zijn of worden uitgevoerd door een asynchroon achtergrondproces. sys.dm_resource_governor_workload_groups weerspiegelt deze pagina-deallocaties wanneer ze optreden, zelfs als de sessie die deze deallocaties heeft veroorzaakt, is gesloten en niet meer aanwezig is in sys.dm_db_session_space_usage.

Aanbevolen procedures voor tempdb-ruimteresourcebeheer

Voordat u ruimteresourcebeheer configureert tempdb , moet u rekening houden met de volgende aanbevolen procedures:

• Bekijk de algemene beste werkwijzen voor resource governor.

• Vermijd voor de meeste scenario's het instellen van de tempdb limiet voor ruimteverbruik op een kleine waarde of nul, met name voor de default workloadgroep. Als u deze limiet instelt op een kleine waarde of nul, kunnen veel algemene taken mislukken als ze ruimte tempdbin moeten toewijzen. Als u bijvoorbeeld de vaste of procentlimiet instelt op 0 voor de default werkbelastinggroep, kunt u Objectverkenner mogelijk niet openen in SQL Server Management Studio (SSMS).

• Tenzij u aangepaste workloadgroepen en een classificatiefunctie maakt die workloads in hun eigen groepen plaatst, moet u het beperken van het gebruik van de tempdb-workloadgroep door default vermijden. Als u het tempdbruimteverbruik door de defaultworkloadgroep beperkt, kunnen query's fout 1138 retourneren. Deze fout treedt op wanneer tempdb nog steeds ongebruikte ruimte heeft die door geen enkele gebruikersworkload kan worden benut.

• De som van GROUP_MAX_TEMPDB_DATA_MB waarden voor alle workloadgroepen kan de maximale tempdb grootte overschrijden. Als de maximale tempdb grootte bijvoorbeeld 100 GB is, kunnen de GROUP_MAX_TEMPDB_DATA_MB limieten voor workloadgroep A en workloadgroep B elk 80 GB zijn.

  Deze aanpak voorkomt nog steeds dat elke workloadgroep alle ruimte in tempdb beslag neemt door 20 GB voor andere workloadgroepen te verlaten. Tegelijkertijd vermijdt u onnodige afgebroken query's wanneer er nog steeds vrije tempdb ruimte beschikbaar is, omdat de werkbelastinggroepen A en B waarschijnlijk niet tegelijkertijd veel tempdb ruimte zullen verbruiken.

  Op dezelfde manier kan de som van GROUP_MAX_TEMPDB_DATA_PERCENT waarden voor alle workloadgroepen groter zijn dan 100 procent. U kunt meer tempdb ruimte toewijzen aan elke groep als u weet dat meerdere groepen waarschijnlijk niet tegelijkertijd een hoog tempdb gebruik veroorzaken.

Examples

Configuratie van tempdb-gegevensbestand weergeven

De volgende query toont de huidige tempdb configuratie van het gegevensbestand:

SELECT file_id,
       name,
       size * 8. / 1024 AS size_mb,
       IIF (max_size = -1, NULL, max_size * 8. / 1024) AS maxsize_mb,
       IIF (is_percent_growth = 0, growth * 8. / 1024, NULL) AS filegrowth_mb,
       IIF (is_percent_growth = 1, growth, NULL) AS filegrowth_percent
FROM sys.master_files
WHERE database_id = 2
      AND type_desc = 'ROWS';

Voor een bepaald bestand in de resultatenset:

• Als de maxsize_mb kolom NULL is, dan is MAXSIZEUNLIMITED.
• Wanneer een van beide filegrowth_mb of filegrowth_percent nul is, is dat FILEGROWTH nul.

Effectieve tempdb-ruimtelimieten per workloadgroep weergeven

De volgende query toont de effectieve tempdb limiet voor ruimteverbruik voor elke workloadgroep. De limiet wordt geretourneerd in megabytes voor de configuratie van een vaste limiet of percentagelimiet .

Als de group_effective_limit_mb kolom is NULL, betekent dit een van de volgende:

• Er wordt geen vaste limiet of percentagelimiet geconfigureerd.
• Er wordt niet voldaan aan de vereisten voor het gebruik van de configuratie van de procentlimiet.

SELECT wg.group_id,
       wg.name,
       tf.tempdb_max_size_mb,
       CASE
           WHEN wg.group_max_tempdb_data_mb IS NOT NULL
               THEN wg.group_max_tempdb_data_mb
           WHEN wg.group_max_tempdb_data_percent IS NOT NULL AND tf.tempdb_max_size_mb IS NOT NULL
               THEN 0.01 * wg.group_max_tempdb_data_percent * tf.tempdb_max_size_mb
       ELSE NULL END AS group_effective_limit_mb
FROM sys.resource_governor_workload_groups AS wg
CROSS APPLY (
    SELECT IIF (SUM(IIF (max_size <> -1
        AND growth > 0, 1, 0)) = COUNT(1) /* autogrow up to the maxsize */
        OR SUM(IIF (max_size = -1
        AND growth = 0, 1, 0)) = COUNT(1), /* pregrown and fixed */
        SUM(IIF (growth = 0, size, max_size)) * 8 / 1024., NULL) AS tempdb_max_size_mb
    FROM sys.master_files
    WHERE database_id = 2
        AND type_desc = 'ROWS'
) AS tf;

Volgende stap

Verwante inhoud

• Resourcebeheerder
• Zelfstudie: Voorbeelden van de configuratie van Resource Governor en aanbevolen procedures
• ALTER RESOURCE GOVERNOR (Transact-SQL)
• CREATE WORKLOAD GROUP (Transact-SQL)
• ALTER WORKLOAD GROUP (Transact-SQL)
• DROP WORKLOAD GROUP (Transact-SQL)
• sys.resource_governor_workload_groups (Transact-SQL)
• sys.dm_resource_governor_workload_groups (Transact-SQL)