tempdb-database

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

In dit artikel worden de tempdb systeemdatabase beschreven, een globale resource die beschikbaar is voor alle gebruikers die zijn verbonden met een Database Engine-exemplaar in SQL Server, Azure SQL Database of Azure SQL Managed Instance.

Overzicht

De tempdb systeemdatabase is een globale resource die het volgende bevat:

• gebruikersobjecten die expliciet gemaakt zijn. Dit zijn onder andere:

  • Globale of lokale tijdelijke tabellen en indexen voor deze tabellen
  • Tijdelijke opgeslagen procedures
  • Tabelvariabelen
  • De tabellen die worden geretourneerd in tabelwaardefuncties
  • Aanwijzers

  Gebruikersobjecten die kunnen worden gemaakt in een gebruikersdatabase, kunnen ook worden gemaakt in tempdb, maar ze worden gemaakt zonder een duurzaamheidsgarantie en worden verwijderd wanneer het Database Engine-exemplaar opnieuw wordt opgestart.

• interne objecten die de database-engine maakt. Dit zijn onder andere:

  • Werktabellen om tussenliggende resultaten op te slaan voor spools, cursors, sorteringen en opslag van tijdelijke grote objecten (LOB).
  • Werkbestanden voor hash-join- of hash-aggregatiefuncties.
  • Tussenliggende sorteerresultaten voor bewerkingen zoals het maken of herbouwen van indexen (als SORT_IN_TEMPDB is opgegeven), of bepaalde GROUP BY, ORDER BYof UNION query's.

  Elk intern object maakt gebruik van minimaal negen pagina's: een IAM-pagina en een omvang van acht pagina's. Zie Pagina's en gebiedenvoor meer informatie over pagina's en gebieden.

• Versieopslagzijn collecties van gegevenspagina's die de gegevensrijen bevatten die ondersteuning bieden voor versiebeheer van rijen. Er zijn twee typen: een gewone versieopslag en een online indexopbouwversieopslag. De versiearchieven bevatten:

  • Rijversies die worden gegenereerd door transacties voor gegevenswijziging in een database die gebruikmaakt van op rijversies gebaseerde READ COMMITTED of SNAPSHOT isolatietransacties.
  • Rijversies die worden gegenereerd door transacties voor gegevenswijziging voor functies, zoals online indexbewerkingen, MARS (Multiple Active Result Sets) en AFTER triggers.

Bewerkingen binnen tempdb worden minimaal vastgelegd. tempdb wordt telkens opnieuw gemaakt wanneer de database-engine wordt gestart, zodat het systeem altijd begint met een lege tempdb database. Tijdelijke opgeslagen procedures en lokale tijdelijke tabellen worden automatisch verwijderd wanneer de sessie waarmee ze zijn gemaakt, wordt verbroken.

tempdb hoeft nooit iets op te slaan van de ene uptimeperiode van de database-engine naar een andere. Back-up- en herstelbewerkingen zijn niet toegestaan op tempdb.

Fysieke eigenschappen van tempdb in SQL Server

De volgende tabel bevat de eerste configuratiewaarden van de tempdb gegevens en logboekbestanden in SQL Server. De waarden zijn gebaseerd op de standaardwaarden voor de model-database. De grootte van deze bestanden kan enigszins variëren voor verschillende edities van SQL Server.

Bestand	Logische naam	Fysieke naam	Oorspronkelijke grootte	Bestandsgroei
Primaire gegevens	tempdev	tempdb.mdf	8 megabytes	Automatisch groeien met 64 MB totdat de schijf vol is
Secundaire gegevensbestanden	temp#	tempdb_mssql_#.ndf	8 megabytes	Automatisch groeien met 64 MB totdat de schijf vol is
Logboek	templog	templog.ldf	8 megabytes	Automatisch groeien met 64 megabytes tot maximaal 2 terabytes

Alle tempdb gegevensbestanden moeten altijd dezelfde initiële grootte en groeiparameters hebben.

Aantal tempdb-gegevensbestanden

Afhankelijk van de versie van de database-engine, de configuratie en de workload, heeft tempdb mogelijk meerdere gegevensbestanden nodig om de allocatieproblemen te verminderen.

Het aanbevolen totale aantal gegevensbestanden is afhankelijk van het aantal logische processors op de computer. Als algemene richtlijnen:

• Als het aantal logische processors kleiner is dan of gelijk is aan acht, gebruikt u hetzelfde aantal gegevensbestanden.
• Als het aantal logische processors groter is dan acht, gebruikt u acht gegevensbestanden.
• Als er nog steeds tempdb toewijzingsconflicten worden waargenomen, verhoog dan het aantal gegevensbestanden met veelvouden van vier totdat het conflict tot een acceptabel niveau is afgenomen, of pas de werkbelasting aan.

Zie Aanbevelingen voor het verminderen van toewijzingsconflicten in sql Server tempdb-databasevoor meer informatie.

Als u de huidige grootte- en groeiparameters voor tempdbwilt controleren, gebruikt u de sys.database_files catalogusweergave in tempdb.

De tempdb-gegevens en logboekbestanden verplaatsen in SQL Server

Om de tempdb-gegevens- en logboekbestanden te verplaatsen, zie Systeemdatabases verplaatsen.

Databaseopties voor tempdb in SQL Server

De volgende tabel bevat de standaardwaarde voor elke databaseoptie in de tempdb database en of de optie kan worden gewijzigd. Als u de huidige instellingen voor deze opties wilt weergeven, gebruikt u de sys.databases catalogusweergave.

Databaseoptie	Standaardwaarde	Kan worden gewijzigd
ACCELERATED_DATABASE_RECOVERY	OFF	Nee
ALLOW_SNAPSHOT_ISOLATION	OFF	Ja
ANSI_NULL_DEFAULT	OFF	Ja
ANSI_NULLS	OFF	Ja
ANSI_PADDING	OFF	Ja
ANSI_WARNINGS	OFF	Ja
ARITHABORT	OFF	Ja
AUTO_CLOSE	OFF	Nee
AUTO_CREATE_STATISTICS	ON	Ja
AUTO_SHRINK	OFF	Nee
AUTO_UPDATE_STATISTICS	ON	Ja
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Ja
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)	OFF	Nee
CHANGE_TRACKING	OFF	Nee
COMPATIBILITY_LEVEL	Is afhankelijk van de versie van de database-engine. Zie ALTER DATABASE (Transact-SQL) compatibiliteitsniveauvoor meer informatie.	Ja
CONCAT_NULL_YIELDS_NULL	OFF	Ja
CONTAINMENT	NONE	Nee
CURSOR_CLOSE_ON_COMMIT	OFF	Ja
CURSOR_DEFAULT	GLOBAL	Ja
Databasestatus	ONLINE	Nee
Database-update	READ_WRITE	Nee
Gebruikerstoegang tot de database	MULTI_USER	Nee
DATE_CORRELATION_OPTIMIZATION	OFF	Ja
DB_CHAINING	ON	Nee
DELAYED_DURABILITY	DISABLED Ongeacht deze optie is vertraagde duurzaamheid altijd ingeschakeld op tempdb.	Ja
ENCRYPTION	OFF	Nee
MIXED_PAGE_ALLOCATION	OFF	Nee
NUMERIC_ROUNDABORT	OFF	Ja
PAGE_VERIFY	CHECKSUM voor nieuwe installaties van SQL Server Bestaande PAGE_VERIFY-waarde kan worden bewaard wanneer een exemplaar van SQL Server wordt bijgewerkt.	Ja
PARAMETERIZATION	SIMPLE	Ja
QUOTED_IDENTIFIER	OFF	Ja
READ_COMMITTED_SNAPSHOT	OFF	Nee
RECOVERY	SIMPLE	Nee
RECURSIVE_TRIGGERS	OFF	Ja
Service Broker	ENABLE_BROKER	Ja
TARGET_RECOVERY_TIME	60	Ja
TEMPORAL_HISTORY_RETENTION	ON	Ja
TRUSTWORTHY	OFF	Nee

Zie ALTER DATABASE SET Options (Transact-SQL)voor een beschrijving van deze databaseopties.

tempdb in Azure SQL Database

In Azure SQL Database verschillen sommige aspecten van tempdb gedrag en configuratie van SQL Server.

Voor individuele databases heeft elke database op een logische server een eigen tempdb. In een elastische pool is tempdb een gedeelde resource voor alle databases in dezelfde pool, maar tijdelijke objecten die door de ene database zijn gemaakt, zijn niet zichtbaar voor andere databases in dezelfde elastische pool.

Objecten in tempdb, waaronder catalogusweergaven en dynamische beheerweergaven (DMV's), zijn toegankelijk via een verwijzing tussen databases naar de tempdb database. U kunt bijvoorbeeld een query uitvoeren op de sys.database_files weergave:

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Globale tijdelijke tabellen in Azure SQL Database- hebben een databasebereik. Voor meer informatie, zie Globale Tijdelijke Tabellen in Azure SQL Database.

Raadpleeg voor meer informatie over tempdb grootten in Azure SQL Database:

• vCore-aankoopmodel: individuele databases, gegroepeerde databases
• DTU-aankoopmodel: individuele databases, gegroepeerde databases

tempdb in SQL Managed Instance

In Azure SQL Managed Instance verschillen sommige aspecten van tempdb gedrag en standaardconfiguratie van SQL Server.

U kunt het aantal tempdb bestanden, de groeiverhogingen en de maximale grootte configureren. Zie Tempdb-instellingen configureren voor Azure SQL Managed Instancevoor meer informatie over het configureren van tempdb-instellingen in Azure SQL Managed Instance.

Azure SQL Managed Instance ondersteunt tijdelijke objecten op dezelfde manier als SQL Server, waarbij alle globale tijdelijke tabellen en globale tijdelijke opgeslagen procedures toegankelijk zijn voor alle gebruikerssessies binnen hetzelfde met SQL beheerde exemplaar.

Voor meer informatie over tempdb grootten in Azure SQL Managed Instance, raadpleeg resourcelimieten.

tempdb in SQL Database in Fabric

Voor meer informatie over tempdb-grootten in een SQL-database in Microsoft Fabric, raadpleeg de sectie resourcelimieten in functievergelijking: Azure SQL Database en SQL-database in Microsoft Fabric.

Net als Azure SQL Databasezijn globale tijdelijke tabellen in de SQL Database in de Microsoft Fabric beperkt tot de database. Voor meer informatie, zie globale tijdelijke tabellen in Azure SQL Database.

Beperkingen

De volgende bewerkingen kunnen niet worden uitgevoerd op de tempdb-database:

• Bestandsgroepen toevoegen.
• Back-ups maken of de database herstellen.
• Collatie wijzigen. De standaardsortering is de serversortering.
• De eigenaar van de database wijzigen. tempdb is eigendom van sa.
• Een momentopname van een database maken.
• De database verwijderen.
• De gast gebruiker uit de database verwijderen.
• Change Data Capture inschakelen.
• Deelnemen aan het spiegelen van databases.
• De primaire bestandsgroep, het primaire gegevensbestand of het logboekbestand verwijderen.
• Wijzig de naam van de database of primaire bestandsgroep.
• DBCC CHECKALLOCwordt uitgevoerd.
• DBCC CHECKCATALOGwordt uitgevoerd.
• De database instellen op OFFLINE.
• De database of primaire bestandsgroep instellen op READ_ONLY.

Machtigingen

Elke gebruiker kan tijdelijke objecten maken in tempdb.

Gebruikers hebben alleen toegang tot hun eigen niet-tijdelijke objecten in tempdb, tenzij ze extra machtigingen krijgen.

Het is mogelijk om de CONNECT machtiging voor tempdb in te trekken om te voorkomen dat een databasegebruiker of -rol tempdbgebruikt. Dit wordt niet aanbevolen omdat veel bewerkingen het gebruik van tempdbvereisen.

Tempdb-prestaties optimaliseren in SQL Server

De grootte en fysieke plaatsing van tempdb bestanden kunnen van invloed zijn op de prestaties. Als de initiële grootte van tempdb bijvoorbeeld te klein is, zullen tijd en middelen worden gebruikt om tempdb automatisch te laten groeien tot de grootte die nodig is om de werkbelasting te ondersteunen telkens wanneer de database-engineinstantie opnieuw wordt opgestart.

• Gebruik indien mogelijk directe initialisatie van bestanden om de prestaties van de groeibewerkingen voor gegevensbestanden te verbeteren.
  • Vanaf SQL Server 2022 (16.x) kunnen groei-gebeurtenissen voor transactielogboekbestanden tot 64 MB ook profiteren van instant bestandsinitialisatie. Zie Instant-bestands initialisatie en het transactielogboekvoor meer informatie.
• Maak een vooraf toegewezen ruimte voor alle tempdb bestanden door de bestandsgrootte in te stellen op een waarde die groot genoeg is om de typische werkbelasting in de omgeving aan te passen. Voorbezetting voorkomt dat tempdb te vaak automatisch groeit, wat de prestaties negatief kan beïnvloeden.
• De bestanden in de tempdb-database moeten worden ingesteld op automatisch groeien om ruimte te bieden tijdens niet-geplande groeigebeurtenissen.
• Het delen van tempdb in meerdere gegevensbestanden van gelijke grootte kan de efficiëntie van bewerkingen die gebruikmaken van tempdbverbeteren.
  • Om onevenwichtigheid van gegevenstoewijzing te voorkomen, moeten gegevensbestanden dezelfde initiële grootte en groeiparameters hebben, omdat de database-engine een proportioneel opvulalgoritme gebruikt dat de toewijzingen in bestanden met meer vrije ruimte bevordert.
  • Stel de toename van de bestandsgroei in op een redelijke grootte, bijvoorbeeld 64 MB, en zorg ervoor dat de groei voor alle gegevensbestanden hetzelfde is om onevenwichtige groei te voorkomen.

Gebruik de volgende query om de huidige grootte- en groeiparameters voor tempdbte controleren:

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Plaats de tempdb-database op een snel I/O-subsysteem. Afzonderlijke gegevensbestanden of groepen tempdb gegevensbestanden hoeven niet per se op verschillende schijven te staan, tenzij u I/O-knelpunten op schijfniveau tegenkomt.

Als er sprake is van I/O-conflicten tussen tempdb en gebruikersdatabases, plaatst u tempdb bestanden op schijven die verschillen van de schijven die gebruikersdatabases gebruiken.

Notitie

Om de prestaties te verbeteren, is vertraagde duurzaamheid altijd ingeschakeld op tempdb, zelfs als de databaseoptie DELAYED_DURABILITY is ingesteld op DISABLED. Omdat tempdb opnieuw wordt gemaakt bij het opstarten, wordt er geen herstelproces uitgevoerd en wordt er geen duurzaamheidsgarantie geboden.

Prestatieverbeteringen in tempdb voor SQL Server

Geïntroduceerd in SQL Server 2016 (13.x)

• Tijdelijke tabellen en tabelvariabelen worden in de cache opgeslagen. Met caching kunnen bewerkingen die de tijdelijke objecten verwijderen en maken, zeer snel worden uitgevoerd. Caching vermindert ook paginatoewijzing en metagegevensconflicten.
• Het toewijzingspaginavergrendelingsprotocol is verbeterd om het aantal UP (update)-latches te verminderen dat wordt gebruikt.
• De overhead voor logboekregistratie voor tempdb wordt verminderd om het schijf- en I/O-bandbreedteverbruik op het tempdb-logbestand te reduceren.
• SQL Setup voegt meerdere tempdb gegevensbestanden toe tijdens een installatie van een nieuw exemplaar. Bekijk de aanbevelingen en configureer uw tempdb op de pagina Database Engine Configuration van SQL Setup of gebruik de opdrachtregelparameter /SQLTEMPDBFILECOUNT. Sql Setup voegt standaard zoveel tempdb gegevensbestanden toe als het aantal logische processors of acht, afhankelijk van wat lager is.
• Wanneer er meerdere tempdb gegevensbestanden zijn, groeien alle bestanden automatisch op hetzelfde moment en in dezelfde mate, afhankelijk van de groei-instellingen. traceringsvlag 1117 is niet meer vereist. Lees -T1117 en -T1118 wijzigingen voor TEMPDB- en gebruikersdatabasesvoor meer informatie.
• Alle toewijzingen in tempdb uniforme gebieden gebruiken. traceringsvlag 1118 is niet meer vereist. Voor meer informatie over prestatieverbeteringen in tempdb, zie het blogartikel TEMPDB - Files and Trace Flags and Updates, Oh My!.
• De eigenschap AUTOGROW_ALL_FILES is altijd ingeschakeld voor de PRIMARY bestandsgroep.

Geïntroduceerd in SQL Server 2017 (14.x)

• De SQL Setup-ervaring verbetert de richtlijnen voor de eerste tempdb bestandstoewijzing. SQL Setup waarschuwt klanten als de oorspronkelijke bestandsgrootte is ingesteld op een waarde die groter is dan 1 GB en als directe initialisatie van bestanden niet is ingeschakeld, waardoor opstartvertragingen van exemplaren worden voorkomen.
• De sys.dm_tran_version_store_space_usage dynamische beheerweergave houdt het gebruik van het versiearchief per database bij. Deze DMV is handig voor DBA's die proactief de grootte van tempdb willen plannen op basis van de gebruiksvereiste voor het versiearchief per database.
• Intelligente queryverwerking functies, zoals adaptieve joins en geheugentoekenningen, verminderen geheugenlekkage bij opeenvolgende uitvoeringen van een query, waardoor tempdb gebruik wordt verminderd.

Geïntroduceerd in SQL Server 2019 (15.x)

• Database Engine gebruikt de optie FILE_FLAG_WRITE_THROUGH niet bij het openen van tempdb bestanden om maximale schijfdoorvoer mogelijk te maken. Omdat tempdb opnieuw wordt gemaakt bij het opstarten, is deze optie niet nodig om duurzaamheid van gegevens te bieden. Zie voor meer informatie over FILE_FLAG_WRITE_THROUGHalgoritmen voor logboekregistratie en gegevensopslag waarmee de betrouwbaarheid van gegevens in SQL Serverwordt uitgebreid.
• voor geheugen geoptimaliseerde TempDB-metagegevens tijdelijke metagegevensconflicten van objecten in tempdbverwijdert.
• Pagina-updates voor gelijktijdige pagina vrije ruimte (PFS) verminderen het aantal pagina-vergrendelingen in alle databases, een probleem dat het meest voorkomt in tempdb. Deze verbetering wijzigt het gelijktijdigheidsbeheer van PFS-pagina-updates, zodat ze kunnen worden bijgewerkt onder een gedeelde vergrendeling, in plaats van een exclusieve vergrendeling. Dit gedrag is standaard ingeschakeld in alle databases (inclusief tempdb) vanaf SQL Server 2019 (15.x). Lees Onder de covers: GAM-, SGAM- en PFS-pagina'svoor meer informatie over PFS-pagina's.
• Standaard maakt een nieuwe installatie van SQL Server op Linux meerdere tempdb gegevensbestanden op basis van het aantal logische kernen (met maximaal acht gegevensbestanden). Dit geldt niet voor ter plaatse kleine of grote versie-upgrades. Elk tempdb gegevensbestand is 8 MB, met een automatische groei van 64 MB. Dit gedrag is vergelijkbaar met de standaardinstallatie van SQL Server in Windows.

Geïntroduceerd in SQL Server 2022 (16.x)

• Geïntroduceerd verbeterde schaalbaarheid met verbeteringen in gelijktijdigheid van systeempagina's. Gelijktijdige updates aan de GAM-pagina's (Global Allocation Map) en gedeelde SGAM-pagina's (Shared Global Allocation Map) verminderen de paginavergrendeling tijdens het toewijzen/onttrekken van gegevenspagina's en gebieden. Deze verbeteringen zijn van toepassing op alle gebruikersdatabases en zijn met name van toepassing op zware workloads in tempdb. Lees voor meer informatie over GAM- en SGAM-pagina's Achter de schermen: GAM-, SGAM- en PFS-pagina's. Bekijk voor meer informatie Systeempagina Verbeteringen voor gelijktijdigheid van systeempagina's (Ep. 6) | Gegevens weergegeven.

Voor geheugen geoptimaliseerde TempDB-metagegevens

Tijdelijke conflicten tussen objectmetagegevens zijn in het verleden een knelpunt geweest in schaalbaarheid voor veel SQL Server-workloads. Hiervoor heeft SQL Server 2019 (15.x) een functie geïntroduceerd die deel uitmaakt van de in-memory database onderdelenfamilie: tempDB-metagegevens die zijn geoptimaliseerd voor geheugen.

Als u de geheugen-geoptimaliseerde TempDB-metagegevensfunctie inschakelt, wordt dit knelpunt verwijderd voor workloads die eerder zijn beperkt door tijdelijke objectmetagegevens in de tempdbomgeving. Vanaf SQL Server 2019 (15.x) kunnen de systeemtabellen die betrokken zijn bij het beheren van tijdelijke objectmetagegevens, worden vergrendelingsvrije, niet-duurzame, voor geheugen geoptimaliseerde tabellen.

Fooi

Vanwege de huidige beperkingen, raden we u aan om tempDB-metagegevens die zijn geoptimaliseerd voor geheugen alleen in te schakelen wanneer conflicten tussen objectmetagegevens optreden en aanzienlijk van invloed zijn op uw workloads.

De volgende diagnostische query retourneert een of meer rijen als er sprake is van een tijdelijke objectmetagegevensconflict. Elke rij vertegenwoordigt een systeemtabelen geeft het aantal sessies weer die strijden om toegang tot die tabel op het moment dat deze diagnostische query wordt uitgevoerd.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Bekijk deze video van zeven minuten voor een overzicht van hoe en wanneer u de functie Voor geheugen geoptimaliseerde TempDB-metagegevens gebruikt:

Notitie

Momenteel is de functie Voor geheugen geoptimaliseerde TempDB-metagegevens niet beschikbaar in Azure SQL Database, SQL-database in Microsoft Fabric en Azure SQL Managed Instance.

Met geheugen geoptimaliseerde TempDB-metagegevens configureren en gebruiken

De volgende secties bevatten stappen voor het inschakelen, configureren, verifiëren en uitschakelen van de functie voor tempDB-metagegevens die zijn geoptimaliseerd voor geheugen.

Inschakelen

Gebruik het volgende script om deze functie in te schakelen:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Zie ALTER SERVERvoor meer informatie. Voor deze configuratiewijziging moet de service opnieuw worden opgestart.

U kunt controleren of tempdb is geoptimaliseerd voor geheugen met behulp van de volgende T-SQL-opdracht:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Als de geretourneerde waarde 1 is en er opnieuw is opgestart nadat de functie is ingeschakeld, is de functie ingeschakeld.

Als de server om welke reden dan ook niet kan worden gestart nadat u voor geheugen geoptimaliseerde TempDB-metagegevens hebt ingeschakeld, kunt u de functie overslaan door het Database Engine-exemplaar te starten met minimale configuratie met behulp van de -f opstartoptie. Vervolgens kunt u de functie uitschakelen en de -f optie verwijderen om de database-engine opnieuw op te starten in de normale modus.

Binden aan resourcegroep om het geheugengebruik te beperken

Om de server te beschermen tegen mogelijke out-of-memory omstandigheden, raden we u aan om tempdb te binden aan een resource governor resourcepool die het geheugen beperkt dat wordt verbruikt door geheugen-geoptimaliseerde TempDB-metagegevens. Met het volgende voorbeeldscript wordt een resourcegroep gemaakt en wordt het maximale geheugen ingesteld op 20%, wordt resource governor-ingeschakeld en wordt tempdb gekoppeld aan de resourcegroep.

In dit voorbeeld wordt 20% gebruikt als de geheugenlimiet voor demonstratiedoeleinden. De optimale waarde in uw omgeving kan groter of kleiner zijn, afhankelijk van uw workload en kan na verloop van tijd veranderen als de werkbelasting verandert.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 
(RESOURCE_POOL = 'tempdb_resource_pool');

Deze wijziging vereist ook dat de service opnieuw wordt opgestart, zelfs als tempDB-metagegevens die zijn geoptimaliseerd voor geheugen al zijn ingeschakeld.

Resourcegroepbinding controleren en geheugengebruik controleren

Gebruik de volgende query om te controleren of tempdb is gebonden aan een resourcegroep en om statistieken over geheugengebruik voor de pool te controleren:

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Resourcegroepbinding verwijderen

Als u de binding van de resourcegroep wilt verwijderen terwijl de geheugen-geoptimaliseerde TempDB-metagegevens ingeschakeld blijven, moet u de volgende opdracht uitvoeren en de service opnieuw starten:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Uitschakelen

Als u voor geheugen geoptimaliseerde TempDB-metagegevens wilt uitschakelen, voert u de volgende opdracht uit en start u de service opnieuw op:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Beperkingen van voor geheugen geoptimaliseerde TempDB-metagegevens

• Voor het in- of uitschakelen van de functie voor geheugen-geoptimaliseerde TempDB-metagegevens moet het systeem opnieuw worden opgestart.

• In bepaalde gevallen kunt u een hoog geheugengebruik door de MEMORYCLERK_XTP-geheugenbediende waarnemen, wat geheugenfouten in uw werklast veroorzaakt.

  Voer de volgende query uit om het geheugengebruik door de MEMORYCLERK_XTP clerk te zien ten opzichte van alle andere geheugenmedewerkers en ten opzichte van het geheugen van de doelserver:

  SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
         SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
         SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
  FROM sys.dm_os_memory_clerks
  CROSS JOIN sys.dm_os_sys_info;
  

  Als MEMORYCLERK_XTP geheugen hoog is, kunt u het probleem als volgt verhelpen:

  • Bind de tempdb-database aan een resourcegroep die het geheugenverbruik beperkt door tempDB-metagegevens die zijn geoptimaliseerd voor geheugen. Zie Met geheugen geoptimaliseerde tempdb-metagegevens configureren en gebruikenvoor meer informatie.
  • Een systeem opgeslagen procedure kan periodiek worden uitgevoerd om MEMORYCLERK_XTP geheugen vrij te geven dat niet meer nodig is. Zie sys.sp_xtp_force_gc (Transact-SQL)voor meer informatie.

  Zie voor geheugen geoptimaliseerde tempdb-metagegevens (HkTempDB) uit geheugenfoutenvoor meer informatie.

• Wanneer u In-Memory OLTP-gebruikt, heeft één transactie geen toegang tot tabellen die zijn geoptimaliseerd voor geheugen in meer dan één database. Als gevolg hiervan heeft elke lees- of schrijftransactie die betrekking heeft op een tabel die is geoptimaliseerd voor geheugen in een gebruikersdatabase, geen toegang tot tempdb systeemweergaven in dezelfde transactie. Als dit gebeurt, ontvangt u fout 41317:

  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  

  Deze beperking geldt ook voor andere scenario's waarbij één transactie probeert toegang te krijgen tot tabellen die zijn geoptimaliseerd voor geheugen in meer dan één database.

  U krijgt bijvoorbeeld fout 41317 als u een query uitvoert op de sys.stats catalogusweergave in een gebruikersdatabase met geheugen-geoptimaliseerde tabellen. Dit gebeurt omdat de query probeert toegang te krijgen tot statistieken gegevens in een tabel die is geoptimaliseerd voor geheugen in de gebruikersdatabase en de metagegevens die zijn geoptimaliseerd voor geheugen in tempdb.

  Het volgende voorbeeldscript produceert deze fout wanneer voor geheugen geoptimaliseerde TempDB-metagegevens zijn ingeschakeld:

  BEGIN TRAN;
  
  -- Create an In-memory OLTP transaction that accesses a system view in tempdb
  SELECT name
  FROM tempdb.sys.tables;
  
  -- An attempt to create an In-memory OLTP transaction in the user database fails
  INSERT INTO <user database>.<schema>.<memory-optimized table>
  VALUES (1);
  
  COMMIT TRAN;
  

  Notitie

  Deze beperking geldt niet voor tijdelijke tabellen. U kunt een tijdelijke tabel maken in dezelfde transactie die toegang heeft tot een tabel die is geoptimaliseerd voor geheugen in een gebruikersdatabase.

• Query's voor systeemcatalogusweergaven gebruiken altijd het READ COMMITTED isolatieniveau. Wanneer de voor geheugen geoptimaliseerde TempDB-metagegevens zijn ingeschakeld, gebruiken query's op systeemcatalogusweergaven in tempdb het isolatieniveau SNAPSHOT. In beide gevallen worden vergrendelingshints niet gehonoreerd.

• Columnstore-indexen kunnen niet worden gemaakt voor tijdelijke tabellen wanneer voor geheugen geoptimaliseerde TempDB-metagegevens zijn ingeschakeld.

  • Als gevolg hiervan wordt het gebruik van de sp_estimate_data_compression_savings door het systeem opgeslagen procedure met de parameter COLUMNSTORE of COLUMNSTORE_ARCHIVE gegevenscompressie niet ondersteund wanneer voor geheugen geoptimaliseerde TempDB-metagegevens zijn ingeschakeld.

Capaciteitsplanning voor tempdb in SQL Server

Het bepalen van de juiste grootte voor tempdb is afhankelijk van veel factoren. Deze factoren omvatten de workload en de functies van de database-engine die worden gebruikt.

We raden u aan tempdb ruimteverbruik te analyseren door de volgende taken uit te voeren in een testomgeving waar u uw typische workload kunt reproduceren:

• Schakel automatisch groeien in voor tempdb bestanden. Alle tempdb gegevensbestanden moeten dezelfde initiële grootte en automatische groeiconfiguratie hebben.
• Reproduceer de werkbelasting en bewaak tempdb ruimtegebruik.
• Als u periodiek indexonderhoud gebruikt, voert u uw onderhoudstaken uit en bewaakt u tempdb ruimte.
• Gebruik de maximale hoeveelheid gebruikte ruimte uit de vorige stappen om het totale workloadgebruik te voorspellen. Pas deze waarde aan voor geprojecteerde gelijktijdige activiteit en stel vervolgens de grootte van tempdb dienovereenkomstig in.

Tempdb-gebruik bewaken

Onvoldoende schijfruimte in tempdb kan aanzienlijke onderbrekingen en downtime van toepassingen veroorzaken. U kunt de sys.dm_db_file_space_usage dynamische beheerweergave gebruiken om de ruimte te bewaken die wordt gebruikt in de tempdb bestanden.

Met het volgende voorbeeldscript wordt gezocht naar:

• Vrije ruimte in tempdb (zonder rekening te houden met vrije schijfruimte die beschikbaar kan zijn voor tempdb groei)
• Ruimte gebruikt door de versieopslag
• Ruimte die wordt gebruikt door interne objecten
• Ruimte die wordt gebruikt door gebruikersobjecten

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Als u de paginatoewijzing of deallocatieactiviteit in tempdb op sessie- of taakniveau wilt bewaken, kunt u de sys.dm_db_session_space_usage en sys.dm_db_task_space_usage dynamische beheerweergaven gebruiken. Met deze weergaven kunt u query's, tijdelijke tabellen of tabelvariabelen identificeren die grote hoeveelheden tempdb ruimte gebruiken.

Gebruik bijvoorbeeld het volgende voorbeeldscript om de toegewezen en vrijgegeven tempdb-ruimte door interne objecten te verkrijgen in alle momenteel lopende taken in elke sessie.

SELECT session_id,
       SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Gebruik het volgende voorbeeldscript om te zoeken naar de tempdb toegewezen en momenteel verbruikte ruimte door interne en gebruikersobjecten voor elke sessie en aanvraag, voor zowel actieve als voltooide taken:

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Verwante inhoud

• SORT_IN_TEMPDB optie voor indexen
• Systeemdatabases
• sys.databases
• sys.master_files
• databasebestanden verplaatsen