Freigeben über

tempdb-Datenbank

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

In diesem Artikel wird die tempdb Systemdatenbank beschrieben, eine globale Ressource, die für alle Benutzer verfügbar ist, die mit einer Datenbankmodulinstanz in SQL Server, Azure SQL-Datenbank oder der von Azure SQL verwalteten Instanz verbunden sind.

Übersicht

Die tempdb-Systemdatenbank ist eine globale Ressource, die Folgendes umfasst:

  • Benutzerobjekte , die explizit erstellt werden. Dazu gehören:

    • Globale oder lokale temporäre Tabellen und Indizes für diese Tabellen
    • Temporäre gespeicherte Prozeduren
    • Große Variablen, einschließlich Tabellenvariablen
    • In Tabellenwertfunktionen zurückgegebene Tabellen
    • Cursoren

    Benutzerobjekte, die in einer Benutzerdatenbank erstellt werden können, können auch in tempdb erstellt werden. Sie werden jedoch ohne Dauerhaftigkeitsgarantie erstellt und beim Neustart der Datenbankmodulinstanz verworfen.

  • Interne Objekte, die von der Datenbank-Engine erstellt werden. Dazu gehören:

    • Arbeitstabellen, in denen direkte Ergebnisse für Spools, Cursor, Sortierungen und temporäre große Objektspeicher (LOB) gespeichert werden.
    • Arbeitsdateien für Hash-Übereinstimmungs- oder Hashaggregatvorgänge.
    • Zwischenergebnisse von Sortierungen bei Vorgängen wie z. B. dem Erstellen oder Neuerstellen von Indizes (wenn SORT_IN_TEMPDB angegeben ist) oder bei bestimmten GROUP BY-, ORDER BY- oder UNION-Abfragen.

    Jedes interne Objekt verwendet mindestens neun Seiten: eine IAM-Seite (Index Allocation Map) und eine achtseitige Erweiterung. Weitere Informationen zu Seiten und Erweiterungen finden Sie unter Seiten und Blöcke.

  • Versionsspeicher, bei denen es sich um Sammlungen von Datenseiten handelt, die die Datenzeilen enthalten, die die Zeilenversionsverwaltung unterstützen. Die Versionsspeicher beinhalten Folgendes:

    • Zeilenversionen, die von Datenänderungstransaktionen in einer Datenbank generiert werden, die Zeilenversionsbasierte READ COMMITTED oder SNAPSHOT Isolationstransaktionen verwenden.
    • Zeilenversionen, die von Datenänderungstransaktionen für Features wie z. B. die folgenden generiert werden: Online-Indexvorgänge, mehrere aktive Resultsets (MARS) und AFTER-Trigger.

    Ab SQL Server 2025 (17.x) Preview, wenn die beschleunigte Datenbankwiederherstellung (ADR) in tempdb aktiviert ist, enthält tempdb zwei verschiedene und unabhängige Versionsspeicher:

    • Der herkömmliche Versionsspeicher, der für Zeilenversionen verwendet wird, die von Transaktionen in Benutzerdatenbanken generiert werden, für die kein ADR aktiviert ist.
    • Der persistente Versionsspeicher (PVS), der für Zeilenversionen verwendet wird, die von Transaktionen tempdbgeneriert werden.

    Achten Sie darauf, ausreichend Speicherplatz für tempdb Datendateien zuzuweisen, damit beide Versionsspeicher enthalten sind, falls ADR tempdb aktiviert ist. Je nach Workload muss die Größe der tempdb Datendateien möglicherweise erhöht werden, um PVS-Daten zu enthalten.

    Weitere Informationen zur tempdb Speicherplatznutzung durch den herkömmlichen Versionsspeicher finden Sie unter "Space used in tempdb". Weitere Informationen zum von PVS verwendeten Raum finden Sie unter "Space used by the persistent version store (PVS)".

Vorgänge innerhalb tempdb werden minimal protokolliert. tempdb wird jedes Mal neu erstellt, wenn das Datenbankmodul gestartet wird, sodass das System immer mit einer leeren tempdb Datenbank beginnt. Temporäre gespeicherte Prozeduren und lokale temporäre Tabellen werden automatisch gelöscht, wenn die Sitzung, die sie erstellt hat, getrennt wird.

tempdb Es muss nie etwas aus einem Uptime-Zeitraum des Datenbankmoduls in einem anderen gespeichert werden. Sicherungs- und Wiederherstellungsvorgänge sind für tempdb nicht zulässig.

Physische Eigenschaften von tempdb in SQL Server

In der folgenden Tabelle sind die anfänglichen Konfigurationswerte der Daten- und Protokolldateien von tempdb in SQL Server aufgelistet. Diese Werte basieren auf den Standardwerten für die model-Datenbank. Die Größe dieser Dateien kann sich in den verschiedenen Editionen von SQL-Server geringfügig unterscheiden.

Datei Logischer Name Physikalischer Name Ursprüngliche Größe Dateivergrößerung (file growth)
Primäre Daten tempdev tempdb.mdf 8 Megabytes Automatische Vergrößerung um 64 MB, bis der Speicherplatz auf dem Datenträger erschöpft ist
Sekundäre Datendateien temp# tempdb_mssql_#.ndf 8 Megabytes Automatische Vergrößerung um 64 MB, bis der Speicherplatz auf dem Datenträger erschöpft ist
Protokoll templog templog.ldf 8 Megabytes Automatische Vergrößerung um 64 MB, bis der Maximalwert von 2 TB erreicht wird

Alle tempdb Datendateien sollten immer die gleichen Anfangsgrößen und Wachstumsparameter aufweisen.

Anzahl der tempdb-Datendateien

Je nach Version des Datenbankmoduls, seiner Konfiguration und der Workload sind möglicherweise mehrere Datendateien erforderlich, tempdb um Zuordnungsengpässe zu verringern.

Die empfohlene Gesamtanzahl der Datendateien hängt von der Anzahl der logischen Prozessoren auf dem Computer ab. Als allgemeine Richtlinie:

  • Wenn die Anzahl der logischen Prozessoren kleiner oder gleich acht ist, verwenden Sie dieselbe Anzahl von Datendateien.
  • Wenn mehr als acht logische Prozessoren vorhanden sind, verwenden Sie acht Datendateien.
  • Wenn tempdb der Zuweisungskonflikt weiterhin beobachtet wird, erhöhen Sie die Anzahl der Datendateien um Vielfache von vier, bis sich der Konflikt auf ein akzeptables Niveau reduziert, oder nehmen Sie Änderungen an der Arbeitslast vor.

Weitere Informationen finden Sie unter Empfehlungen zur Verringerung von Speicherplatzkonflikten in der TempDB-Datenbank von SQL Server.

Verwenden Sie zum Überprüfen der aktuellen Größen- und Wachstumsparameter die tempdbsys.database_files Katalogansicht in tempdb.

Verschieben der tempdb-Daten- und -Protokolldateien in SQL Server

Informationen zum Verschieben der Daten- und Protokolldateien von tempdb finden Sie unter Verschieben von Systemdatenbanken.

Datenbankoptionen für tempdb in SQL Server

In der folgenden Tabelle werden die Standardwerte für alle einzelnen Datenbankoptionen der Datenbank tempdb aufgeführt und, ob die Option geändert werden kann. Zum Anzeigen der aktuellen Einstellungen dieser Optionen verwenden Sie die Katalogsicht sys.databases .

Datenbankoption Standardwert Kann geändert werden.
ACCELERATED_DATABASE_RECOVERY OFF Ja1
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 Nein
AUTO_CREATE_STATISTICS ON Ja
AUTO_SHRINK OFF Nein
AUTO_UPDATE_STATISTICS ON Ja
AUTO_UPDATE_STATISTICS_ASYNC OFF Ja
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) OFF Nein
CHANGE_TRACKING OFF Nein
COMPATIBILITY_LEVEL Hängt von der Datenbankmodul-Version ab.

Weitere Informationen finden Sie unter ALTER DATABASE-(Transact-SQL) Kompatibilitätsgrad.		 Ja
CONCAT_NULL_YIELDS_NULL OFF Ja
CONTAINMENT NONE Nein
CURSOR_CLOSE_ON_COMMIT OFF Ja
CURSOR_DEFAULT GLOBAL Ja
Datenbankstatus ONLINE Nein
Datenbankaktualisierung READ_WRITE Nein
Datenbankbenutzerzugriff MULTI_USER Nein
DATE_CORRELATION_OPTIMIZATION OFF Ja
DB_CHAINING ON Nein
DELAYED_DURABILITY DISABLED

Unabhängig von dieser Option ist die verzögerte Haltbarkeit immer aktivierttempdb.		 Ja
ENCRYPTION OFF Nein
MIXED_PAGE_ALLOCATION OFF Nein
NUMERIC_ROUNDABORT OFF Ja
PAGE_VERIFY CHECKSUM für neue Installationen von SQL Server

Ein vorhandener PAGE_VERIFY Wert kann beibehalten werden, wenn eine Instanz von SQL Server vor Ort aktualisiert wird.		 Ja
PARAMETERIZATION SIMPLE Ja
QUOTED_IDENTIFIER OFF Ja
READ_COMMITTED_SNAPSHOT OFF Nein
RECOVERY SIMPLE Nein
RECURSIVE_TRIGGERS OFF Ja
Dienstvermittler ENABLE_BROKER Ja
TARGET_RECOVERY_TIME 60 Ja
TEMPORAL_HISTORY_RETENTION ON Ja
TRUSTWORTHY OFF Nein

1 Einstellung ACCELERATED_DATABASE_RECOVERY für ON in tempdb wird ab SQL Server 2025 (17.x) Preview unterstützt. In früheren Versionen von SQL Server ist das Ändern der ACCELERATED_DATABASE_RECOVERY Option für die tempdb Datenbank nicht zulässig.

Eine Beschreibung dieser Datenbankoptionen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

tempdb in Azure SQL-Datenbank

In der Azure SQL-Datenbank unterscheiden sich einige Aspekte des Verhaltens und der tempdb Konfiguration von SQL Server.

Für jede einzelne Datenbank auf einem logischen Server gibt es ein eigenes tempdb. In einem elastischen Pool ist eine gemeinsam genutzte Ressource für alle Datenbanken im selben Pool, tempdb aber temporäre Objekte, die von einer Datenbank erstellt wurden, sind für andere Datenbanken im selben elastischen Pool nicht sichtbar.

Objekte in tempdb, einschließlich Katalogansichten und dynamischen Verwaltungsansichten (DYNAMIC Management Views, DMVs), können über einen datenbankübergreifenden Verweis auf die tempdb Datenbank zugänglich sein. Sie können z. B. die sys.database_files Ansicht abfragen:

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

Globale temporäre Tabellen in der Azure SQL-Datenbank sind datenbankbereichsbezogene. Weitere Informationen finden Sie unter "Datenbankbereichsbezogene temporäre Tabellen" in der Azure SQL-Datenbank.

Informationen zu tempdb-Größen in Azure SQL-Datenbank finden Sie hier:

„tempdb“ in SQL Managed Instance

In azure SQL Managed Instance unterscheiden sich einige Aspekte des Verhaltens und der tempdb Standardkonfiguration von SQL Server.

Sie können die Anzahl der tempdb-Dateien, deren Vergrößerungsinkremente und deren maximale Größe konfigurieren. Weitere Informationen zum Konfigurieren von tempdb-Einstellungen in Azure SQL Managed Instance finden Sie unter Konfigurieren von tempdb-Einstellungen für Azure SQL Managed Instance.

Azure SQL Managed Instance unterstützt temporäre Objekte auf die gleiche Weise wie SQL Server, auf die alle globalen temporären Tabellen und globalen temporären gespeicherten Prozeduren von allen Benutzersitzungen innerhalb derselben SQL-verwalteten Instanz zugreifen können.

Weitere Informationen zu tempdb-Größen in Azure SQL Managed Instance finden Sie unter Ressourcenlimits.

tempdb in SQL-Datenbank in Fabric

Weitere Informationen zu tempdb Größen in der SQL-Datenbank in Microsoft Fabric finden Sie im Abschnitt "Ressourcenbeschränkungen" im Featurevergleich: Azure SQL-Datenbank und SQL-Datenbank in Microsoft Fabric.

Ähnlich wie in der Azure SQL-Datenbank sind globale temporäre Tabellen in der SQL-Datenbank in Microsoft Fabric datenbankbereichsbezogener Art. Weitere Informationen finden Sie unter "Datenbankbereichsbezogene temporäre Tabellen" in der Azure SQL-Datenbank.

Beschränkungen

Die folgenden Vorgänge können in der tempdb-Datenbank nicht ausgeführt werden:

  • Hinzufügen von Dateigruppen.
  • Sichern und Wiederherstellen der Datenbank.
  • Ändern der Sortierung. Die Standardsortierung entspricht der Serversortierung.
  • Ändern des Datenbankbesitzers tempdb befindet sich im Besitz von sa.
  • Erstellen einer Datenbankmomentaufnahme.
  • Löschen der Datenbank.
  • Löschen des guest -Benutzers aus der Datenbank.
  • Aktivieren von Change Data Capture.
  • Teilnehmen an der Datenbankspiegelung.
  • Entfernen der primären Dateigruppe, der primären Datendatei oder der Protokolldatei.
  • Umbenennen der Datenbank oder der primären Dateigruppe.
  • Ausführen von DBCC CHECKALLOC.
  • Ausführen von DBCC CHECKCATALOG.
  • Festlegen der Datenbank auf OFFLINE.
  • Festlegen der Datenbank oder primären Dateigruppe auf READ_ONLY.

Berechtigungen

Jeder Benutzer kann temporäre Objekte in tempdb erstellen.

Benutzer können nur auf ihre eigenen nicht temporären Objekte tempdbzugreifen, es sei denn, sie erhalten zusätzliche Berechtigungen.

Es ist möglich, die Berechtigung auf zu widerrufen, damit ein Datenbankbenutzer oder eine Datenbankrolle CONNECT nicht verwenden kann. Dies wird nicht empfohlen, da bei vielen Vorgängen die Verwendung von tempdb erforderlich ist.

Optimieren der tempdb-Leistung in SQL Server

Die Größe und physische Platzierung von tempdb Dateien kann sich auf die Leistung auswirken. Wenn die Anfangsgröße von tempdb zum Beispiel zu klein ist, können Zeit und Ressourcen benötigt werden, um tempdb auf die Größe zu bringen, die erforderlich ist, um die Arbeitsauslastung jedes Mal zu bewältigen, wenn die Datenbankmodulinstanz neu gestartet wird.

  • Verwenden Sie nach Möglichkeit die sofortige Dateiinitialisierung , um die Leistung der Wachstumsvorgänge für Datendateien zu verbessern.
  • Weisen Sie allen tempdb-Dateien im Voraus Speicherplatz zu, indem Sie die Dateigröße auf einen Wert festlegen, der hoch genug ist, um der üblichen Arbeitsauslastung in der Umgebung gerecht zu werden. Preallocation verhindert die zu häufige automatische Vergrößerung von tempdb, was sich negativ auf die Leistung auswirken kann.
  • Die Dateien in der tempdb Datenbank sollten so festgelegt werden, dass sie automatisch wächst, um Platz bei ungeplanten Wachstumsereignissen bereitzustellen.
  • Das Aufteilen von tempdb in mehrere Datendateien gleicher Größe kann die Effizienz von Vorgängen verbessern, die tempdb verwenden.
    • Um ein Ungleichgewicht bei der Datenzuordnung zu vermeiden, sollten Datendateien die gleichen Anfangs- und Wachstumsparameter aufweisen, da das Datenbankmodul einen Proportionalfüllalgorithmus verwendet, der Zuordnungen in Dateien mit mehr freiem Speicherplatz bevorzugt.
    • Legen Sie das Dateiwachstumsinkrement auf eine angemessene Größe fest, z. B. 64 MB, und machen Sie das Wachstumsinkrement für alle Datendateien gleich, um Wachstumsungleichgewichte zu verhindern.
  • Ab SQL Server 2025 (17.x) Preview sollten Sie in Betracht ziehen, die beschleunigte Datenbankwiederherstellung zu aktivieren, um die Vorteile des sofortigen Transaktionsrollbacks und aggressiver Protokollkürzung für Transaktionen in tempdb zu nutzen. Weitere Informationen finden Sie unter ADR in tempdb.
    • Das Aktivieren oder Deaktivieren von ADR in tempdb erfordert einen Neustart des Datenbankmoduls, um wirksam zu werden.

Verwenden Sie die folgende Abfrage, um die aktuelle Größe und die Vergrößerungsparameter von tempdb zu überprüfen:

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;

Platzieren Sie die tempdb-Datenbank auf einem schnellen E/A-Subsystem. Einzelne Datendateien oder Gruppen von tempdb Datendateien müssen nicht unbedingt auf unterschiedlichen Datenträgern gespeichert sein, es sei denn, Sie stoßen auf E/A-Engpässe auf Datenträgerebene.

Wenn es einen E/A-Konflikt zwischen tempdb und Benutzerdatenbanken gibt, legen Sie die tempdb-Dateien auf andere Datenträger als die, die von den Benutzerdatenbanken verwendet werden.

Hinweis

Um die Leistung zu verbessern, ist die verzögerte Haltbarkeit immer aktiviert tempdb , auch wenn die Datenbankoption DELAYED_DURABILITY auf DISABLED festgelegt ist. Da tempdb beim Start neu erstellt wird, durchläuft er keinen Wiederherstellungsvorgang und bietet keine Dauerhaftigkeitsgarantie.

Verbesserungen in tempdb für SQL Server

In SQL Server 2025 (17.x) Preview eingeführt

  • Tempdb Die Speicherressourcenverwaltung erzwingt einen Grenzwert für die Gesamtmenge des tempdb Speicherplatzes, der von einer Anwendung oder Benutzerbelastung verbraucht wird. Dies verbessert die Zuverlässigkeit und vermeidet Ausfälle, indem verhindert wird, dass auslaufende Abfragen oder Workloads einen großen Speicherplatz in tempdbAnspruch nehmen. Weitere Informationen finden Sie unter Tempdb Space Resource Governance.
  • Die beschleunigte Datenbankwiederherstellung wird in tempdb unterstützt und ermöglicht ein sofortiges Rückgängigmachen von Transaktionen sowie eine aggressive Protokollverkürzung für Transaktionen in tempdb.
  • Optimierter Halloween-Schutz reduziert die Platznutzung in tempdb, indem bei DML-Anweisungen (Data Modification Language) kein Halloween-Schutzspool verwendet wird.

Eingeführt in SQL Server 2022 (16.x)

Eingeführt in SQL Server 2019 (15.x) eingeführt.

  • Das Datenbankmodul verwendet beim Öffnen von FILE_FLAG_WRITE_THROUGH Dateien nicht die tempdb Option, um den maximalen Datenträgerdurchsatz zu ermöglichen. Da tempdb beim Start neu erstellt wird, ist diese Option nicht erforderlich, um die Datenbeständigkeit bereitzustellen. Weitere Informationen zu FILE_FLAG_WRITE_THROUGH finden Sie unter Protokollierungs- und Datenspeicheralgorithmen zur Erweiterung der Datenzuverlässigkeit in SQL Server.
  • Speicheroptimierte TempDB-Metadaten entfernt vorübergehende Objektmetadatenverknüpfungen in tempdb.
  • Gleichzeitige Aktualisierungen der Seitenfreiräume (PFS) reduzieren die Seiten-Latch-Konkurrenz in allen Datenbanken, ein Problem, das am häufigsten in tempdb. Diese Verbesserung ändert die Verwaltung der Gleichzeitigkeit von PFS-Seitenaktualisierungen, sodass sie unter einem gemeinsamen Latch und nicht unter einem exklusiven Latch aktualisiert werden können. Dieses Verhalten ist ab SQL Server 2019 (15.x) in allen Datenbanken (tempdb eingeschlossen) standardmäßig aktiviert. Weitere Informationen zu PFS-Seiten finden Sie Hintergrundinformationen: GAM-, SGAM- und PFS-Seiten.
  • Standardmäßig erstellt eine neue Installation von SQL Server für Linux mehrere tempdb-Datendateien, deren Anzahl sich nach der Anzahl von logischen Kernen richtet (bis zu acht Datendateien). Dies gilt nicht für direkte Upgrades der Neben- oder Hauptversion. Jede tempdb Datendatei beträgt 8 MB, mit einem automatischen Wachstum von 64 MB. Dieses Verhalten ähnelt dem der SQL Server-Standardinstallation unter Windows.

Eingeführt in SQL Server 2017 (14.x) eingeführt.

  • Die SQL-Setup-Erfahrung bietet eine verbesserte Anleitung bei der anfänglichen tempdb-Dateizuweisung. SQL Setup warnt Kunden, wenn die anfängliche Dateigröße auf einen Wert von mehr als 1 GB festgelegt ist und wenn die Schnelle Dateiinitialisierung nicht aktiviert ist, um Instanz-Startup-Verzögerungen zu verhindern.
  • Die sys.dm_tran_version_store_space_usage dynamische Verwaltungsansicht verfolgt die Versionsspeichernutzung pro Datenbank. Dieser DMV ist nützlich für DBAs, die basierend auf der Anforderung des Versionsspeichers pro Datenbank die Größenanpassung proaktiv planen möchten tempdb.
  • Intelligente Abfrageverarbeitungsfeatures wie adaptive Verknüpfungen und Feedback zur Speicherzuteilung reduzieren Speicherüberläufe bei aufeinanderfolgenden Ausführungen einer Abfrage und verringern tempdb Auslastung.

Eingeführt in SQL Server 2016 (13.x)

  • Temporäre Tabellen und Tabellenvariablen werden zwischengespeichert. Das Zwischenspeichern ermöglicht eine sehr schnelle Ausführung der Vorgänge zum Löschen und Erstellen der temporären Objekte. Zudem reduziert es Konflikte bei Seitenzuordnung und Metadaten.
  • Das Latchprotokoll für Zuordnungsseiten wurde verbessert, um die Anzahl der verwendeten UP-Latches (Updatelatches) zu verringern.
  • Der Protokollierungsaufwand für tempdb wurde verringert, um die E/A-Bandbreite des Datenträgers für die tempdb-Protokolldatei zu reduzieren.
  • SQL Setup fügt während einer neuen Instanzinstallation mehrere tempdb Datendateien hinzu. Überprüfen Sie die Empfehlungen und konfigurieren Sie Ihr tempdb auf der Seite Datenbankmodulkonfiguration von SQL Setup, oder verwenden Sie den Befehlszeilenparameter /SQLTEMPDBFILECOUNT. Sql Setup fügt standardmäßig so viele tempdb Datendateien wie die Anzahl der logischen Prozessoren oder acht hinzu, je nachdem, welcher Wert niedriger ist.
  • Wenn mehrere tempdb-Datendateien vorhanden sind, werden alle Dateien je nach Wachstumseinstellungen automatisch gleichzeitig und um denselben Wert vergrößert. Ablaufverfolgungsflag 1117 ist nicht mehr erforderlich. Weitere Informationen finden Sie unter -T1117- und -T1118-Änderungen für TEMPDB und Benutzerdatenbanken.
  • Alle Zuordnungen in tempdb verwenden einheitliche Erweiterungen. Ablaufverfolgungsflag 1118 ist nicht mehr erforderlich. Weitere Informationen zu Leistungsverbesserungen in tempdb finden Sie im Blogbeitrag TEMPDB - Files and Trace Flags and Updates, Oh My! (TEMPDB – Dateien und Ablaufverfolgungsflags und Updates, o je!).
  • Die AUTOGROW_ALL_FILES Eigenschaft ist für die PRIMARY Dateigruppe immer aktiviert.

Speicheroptimierte TempDB-Metadaten

Historisch gesehen war der Wettstreit um Objektmetadaten ein Engpass bei der Skalierbarkeit für viele der SQL Server-Workloads. Um dies zu beheben, hat SQL Server 2019 (15.x) ein Feature eingeführt, das Teil der In-Memory-Datenbankfeaturefamilie ist: Speicheroptimierte TempDB-Metadaten.

Durch die Aktivierung des speicheroptimierten TempDB-Metadatenfeatures wird dieser Engpass für Arbeitslasten entfernt, die zuvor durch Konflikte der temporären Objektmetadaten innerhalb tempdb eingeschränkt wurden. Ab SQL Server 2019 (15.x) können die Systemtabellen, die zur Verwaltung temporärer Objektmetadaten verwendet werden, zu latenzfreien, nicht dauerhaften und speicheroptimierten Tabellen werden.

Tipp

Aufgrund der aktuellen Einschränkungen wird empfohlen, speicheroptimierte TempDB-Metadaten nur zu aktivieren, wenn die Objektmetadatenkonflikt auftritt und sich erheblich auf Ihre Workloads auswirkt.

Die folgende Diagnoseabfrage gibt eine oder mehrere Zeilen zurück, wenn ein temporärer Objektmetadatenkonflikt auftritt. Jede Zeile stellt eine Systemtabelle dar und gibt die Anzahl der Sitzungen zurück, die zum Zeitpunkt der Ausführung dieser Diagnoseabfrage für den Zugriff auf diese Tabelle kämpfen.

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;

Schauen Sie sich dieses siebenminütige Video an, um einen Überblick darüber zu erfahren, wie und wann Sie die speicheroptimierte TempDB-Metadatenfunktion verwenden:

Hinweis

Derzeit ist das speicheroptimierte TempDB-Metadatenfeature in Azure SQL-Datenbank, SQL-Datenbank in Microsoft Fabric und azure SQL Managed Instance nicht verfügbar.

Konfigurieren und Verwenden von speicheroptimierten TempDB-Metadaten

Die folgenden Abschnitte enthalten Schritte zum Aktivieren, Konfigurieren, Prüfen und Deaktivieren der speicheroptimierten TempDB-Metadatenfunktion.

Aktivieren

Verwenden Sie zum Aktivieren dieses Features das folgende Skript:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Weitere Informationen finden Sie unter ALTER SERVER. Damit diese Konfigurationsänderung wirksam wird, muss der Dienst neu gestartet werden.

Mit dem folgenden T-SQL-Befehl können Sie überprüfen, ob tempdb speicheroptimiert ist:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Wenn der zurückgegebene Wert 1 ist und ein Neustart nach dem Aktivieren des Features aufgetreten ist, ist das Feature aktiviert.

Wenn der Server aus irgendeinem Grund nicht gestartet werden kann, nachdem Sie speicheroptimierte TempDB-Metadaten aktiviert haben, können Sie das Feature umgehen, indem Sie die Datenbankmodulinstanz mit minimaler Konfiguration mithilfe der -f Startoption starten. Anschließend können Sie das Feature deaktivieren und die -f Option zum Neustarten des Datenbankmoduls im normalen Modus entfernen.

Binden an den Ressourcenpool, um die Speicherauslastung zu begrenzen

Um den Server vor potenziellen Out-of-Memory-Bedingungen zu schützen, empfiehlt es sich, tempdb an einen Ressourcenpool des Ressourcengovernors zu binden, der den von speicheroptimierten TempDB-Metadaten verbrauchten Speicher begrenzt. Das folgende Beispielskript erstellt einen Ressourcenpool und legt den maximalen Speicher auf 20%fest, ermöglicht ressourcenverwaltung und bindet an tempdb den Ressourcenpool.

In diesem Beispiel werden 20% als Speicherlimit für Demonstrationszwecke verwendet. Der optimale Wert in Ihrer Umgebung kann je nach Workload größer oder kleiner sein und kann sich im Laufe der Zeit ändern, wenn sich die Workload ändert.

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');

Diese Änderung erfordert auch einen Dienstneustart, um wirksam zu werden, auch wenn speicheroptimierte TempDB-Metadaten bereits aktiviert sind.

Überprüfen der Ressourcenpoolbindung und Überwachen der Speicherauslastung

Verwenden Sie die folgende Abfrage, um zu überprüfen, ob tempdb diese an einen Ressourcenpool gebunden ist, und um die Speicherauslastungsstatistiken für den Pool zu überwachen:

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';

Ressourcenpoolbindung entfernen

Um die Ressourcenpoolbindung zu entfernen, während speicheroptimierte TempDB-Metadaten aktiviert bleiben, führen Sie den folgenden Befehl aus, und starten Sie den Dienst neu:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Deaktivieren

Führen Sie den folgenden Befehl aus, und starten Sie den Dienst neu, um speicheroptimierte TempDB-Metadaten zu deaktivieren:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Einschränkungen von speicheroptimierten TempDB-Metadaten

  • Das Aktivieren oder Deaktivieren des speicheroptimierten TempDB-Metadatenfeatures erfordert einen Neustart.

  • In bestimmten Fällen können Sie eine hohe Speicherauslastung durch den MEMORYCLERK_XTP Speicherverwalter beobachten, was zu Out-of-Memory-Fehlern in Ihrer Workload führt.

    Führen Sie die folgende Abfrage aus, um die Speicherauslastung des MEMORYCLERK_XTP Mitarbeiters relativ zu allen anderen Speicherbearbeitern und relativ zum Zielserverspeicher anzuzeigen:

    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;

    Wenn MEMORYCLERK_XTP der Arbeitsspeicher hoch ist, können Sie das Problem wie folgt beheben:

    Weitere Informationen finden Sie unter speicheroptimierte tempdb-Metadaten (HkTempDB) außerhalb des Arbeitsspeichers.

  • Wenn Sie In-Memory OLTP verwenden, darf eine einzelne Transaktion nicht auf speicheroptimierte Tabellen in mehreren Datenbanken zugreifen. Aus diesem Fall kann jede Lese- oder Schreibtransaktion, die eine speicheroptimierte Tabelle in einer Benutzerdatenbank umfasst, nicht auch auf Systemansichten in derselben Transaktion zugreifen tempdb . Wenn dies geschieht, erhalten Sie den Fehler 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.

    Diese Einschränkung gilt auch für andere Szenarien, in denen eine einzelne Transaktion versucht, auf speicheroptimierte Tabellen in mehr als einer Datenbank zuzugreifen.

    Beispielsweise erhalten Sie möglicherweise Fehler 41317, wenn Sie die Sys.stats-Katalogansicht in einer Benutzerdatenbank abfragen, die speicheroptimierte Tabellen enthält. Dies geschieht, weil die Abfrage versucht, auf Statistikdaten in einer speicheroptimierten Tabelle in der Benutzerdatenbank und in den speicheroptimierten Metadaten zuzugreifen tempdb.

    Das folgende Beispielskript erzeugt diesen Fehler, wenn speicheroptimierte TempDB-Metadaten aktiviert sind:

    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;

    Hinweis

    Diese Einschränkung gilt nicht für temporäre Tabellen. Sie können eine temporäre Tabelle in derselben Transaktion erstellen, die auf eine speicheroptimierte Tabelle in einer Benutzerdatenbank zugreift.

  • Abfragen für Systemkatalogansichten verwenden immer die READ COMMITTED Isolationsstufe. Wenn die speicheroptimierten TempDB-Metadaten aktiviert sind, verwenden Abfragen für Systemkatalogansichten tempdb die SNAPSHOT Isolationsstufe. In beiden Fällen werden Sperrhinweise nicht berücksichtigt.

  • Columnstore-Indizes können nicht für temporäre Tabellen erstellt werden, wenn speicheroptimierte TempDB-Metadaten aktiviert sind.

    • Daher wird die Verwendung der sp_estimate_data_compression_savings gespeicherten Systemprozedur mit dem Parameter für COLUMNSTORE oder COLUMNSTORE_ARCHIVE Datenkomprimierung nicht unterstützt, wenn speicheroptimierte TempDB-Metadaten aktiviert sind.

Kapazitätsplanung für tempdb in SQL Server

Die Bestimmung der geeigneten Größe tempdb hängt von vielen Faktoren ab. Zu diesen Faktoren gehören die Arbeitslast und die verwendeten Funktionen des Datenbankmoduls.

Es wird empfohlen, den Speicherplatzverbrauch zu analysieren tempdb , indem Sie die folgenden Aufgaben in einer Testumgebung ausführen, in der Sie Ihre typische Workload reproduzieren können:

  • Automatische Vergrößerung für tempdb Dateien aktivieren. Alle tempdb Datendateien sollten die gleiche Anfangsgröße und automatische Vergrößerungskonfiguration aufweisen.
  • Reproduzieren Sie die Arbeitsauslastung, und überwachen Sie tempdb die Raumnutzung.
  • Wenn Sie regelmäßige Indexwartung verwenden, führen Sie Ihre Wartungsaufträge aus und überwachen Sie den Speicherplatz.
  • Verwenden Sie die maximal benutzten Werte aus den vorherigen Schritten, um die Gesamtarbeitslast vorherzusagen. Passen Sie diesen Wert an die veranschlagten gleichzeitigen Aktivitäten an, und legen Sie dann die Größe von tempdb entsprechend fest.

Überwachen der tempdb-Nutzung

Bei knappem Speicherplatz auf tempdb kann es zu erheblichen Unterbrechungen und zu Ausfallzeiten von Anwendungen kommen. Sie können die sys.dm_db_file_space_usage dynamische Verwaltungsansicht verwenden, um den in den tempdb Dateien verwendeten Speicherplatz zu überwachen.

Das folgende Beispielskript findet z. B. Folgendes:

  • Freier Speicherplatz in tempdb (ohne Berücksichtigung des freien Speicherplatzes, der möglicherweise für die Erweiterung von tempdb verfügbar ist).
  • Speicherplatz, der vom herkömmlichen Versionsspeicher verwendet wird.
    • Informationen zum Überwachen der Größe des persistenten Versionsspeichers (PVS), wenn die beschleunigte Datenbankwiederherstellung (ADR) aktivierttempdbist, finden Sie unter Untersuchen der Größe der PVS.
  • Von internen Objekten verwendeter Platz.
  • Von Benutzerobjekten verwendete Platz.
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;

Um die Seitenzuordnungs- oder Aufhebungsaktivität tempdb auf Sitzungs- oder Aufgabenebene zu überwachen, können Sie die dynamischen Verwaltungsansichten sys.dm_db_session_space_usage und sys.dm_db_task_space_usage verwenden. Diese Ansichten können Ihnen helfen, Abfragen, temporäre Tabellen oder Tabellenvariablen zu identifizieren, die große Speicherplatzmengen tempdb verwenden.

Verwenden Sie beispielsweise das folgende Beispielskript, um den von internen Objekten zugewiesenen und freigegebenen Speicherplatz in allen derzeit ausgeführten Aufgaben in jeder Sitzung abzurufen.

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;

Verwenden Sie das folgende Beispielskript, um den tempdb zugewiesenen und aktuell verbrauchten Speicherplatz nach internen und Benutzerobjekten für jede Sitzung und Anforderung zu finden, sowohl für die Ausführung als auch für erledigte Aufgaben:

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;

Zugehöriger Inhalt