Freigeben über

tempdb-Datenbank

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Dieser Artikel beschreibt die tempdb-Systemdatenbank, eine globale Ressource, die allen Benutzern zur Verfügung steht, die mit einer Instanz von SQL-Server, Azure SQL-Datenbank oder Azure SQL Managed Instance verbunden sind.

Übersicht

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

  • Temporäre Benutzerobjekte, die explizit erstellt werden. Hierzu gehören globale oder lokale temporäre Tabellen und Indizes, temporäre gespeicherte Prozeduren, Tabellenvariablen, in Tabellenwertfunktionen zurückgegebene Tabellen und Cursor.

  • 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 Hashjoin- 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. Dies sind Sammlungen von Datenseiten, in denen die Datenzeilen zur Unterstützung von Features für die Zeilenversionsverwaltung gespeichert werden. Es gibt zwei Speichertypen: einen allgemeinen Versionsspeicher und einen Versionsspeicher für die Online-Indexerstellung. Die Versionsspeicher beinhalten Folgendes:

    • Zeilenversionen, die von Datenänderungstransaktionen in einer Datenbank generiert werden, die READ COMMITTED durch Isolation der Zeilenversionsverwaltung oder durch Transaktionen der Momentaufnahmeisolation verwendet.
    • 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.

Vorgänge in tempdb werden minimal protokolliert, sodass ein Rollback für Transaktionen ausgeführt werden kann. tempdb wird bei jedem Start von SQL-Server neu erstellt, sodass das System immer mit einer bereinigten Kopie der Datenbank startet. Temporäre Tabellen und gespeicherte Prozeduren werden beim Trennen der Verbindung automatisch gelöscht; es sind keine Verbindungen aktiv, wenn das System heruntergefahren wird.

tempdb muss zwischen einzelnen SQL-Server-Sitzungen niemals etwas speichern. 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

Die Anzahl von sekundären Datendateien richtet sich nach der Anzahl der (logischen) Prozessoren auf dem Computer. Als allgemeine Regel gilt: Verwenden Sie die Anzahl von Datendateien, die der Anzahl von logischen Prozessoren entspricht, falls die Anzahl von logischen Prozessoren acht oder weniger beträgt. Wenn mehr als acht logische Prozessoren vorhanden sind, verwenden Sie acht Datendateien. Sollte weiterhin ein Konflikt bestehen, erhöhen Sie die Anzahl von Datendateien um ein Vielfaches von vier, bis der Konflikt auf ein akzeptables Ausmaß reduziert ist. Alternativ dazu können Sie auch die Arbeitsauslastung oder den Code ändern.

Der Standardwert für die Anzahl der Datendateien basiert auf den allgemeinen Richtlinien in KB 2154845.

Fragen Sie die Sicht tempdb.sys.database_files ab, um die aktuelle Größe und die Vergrößerungsparameter von tempdb zu überprüfen.

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.
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 No
AUTO_CREATE_STATISTICS ON Ja
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS ON Ja
AUTO_UPDATE_STATISTICS_ASYNC OFF Ja
CHANGE_TRACKING OFF No
CONCAT_NULL_YIELDS_NULL OFF Ja
CURSOR_CLOSE_ON_COMMIT OFF Ja
CURSOR_DEFAULT GLOBAL Ja
Datenbankverfügbarkeitsoptionen ONLINE

MULTI_USER

READ_WRITE		 No

Nr.

No
DATE_CORRELATION_OPTIMIZATION OFF Ja
DB_CHAINING ON No
ENCRYPTION OFF No
MIXED_PAGE_ALLOCATION OFF No
NUMERIC_ROUNDABORT OFF Ja
PAGE_VERIFY CHECKSUM für neue Installationen von SQL-Server

NONE für Upgrades von SQL Server		 Ja
PARAMETERIZATION SIMPLE Ja
QUOTED_IDENTIFIER OFF Ja
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SIMPLE No
RECURSIVE_TRIGGERS OFF Ja
Service Broker-Optionen ENABLE_BROKER Ja
TRUSTWORTHY OFF No

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

„tempdb“ in Azure SQL

Das Verhalten von tempdb in Azure SQL-Datenbank unterscheidet sich vom Verhalten in SQL Server, Azure SQL Managed Instance und SQL Server auf Azure-VMs.

„tempdb“ in SQL-Datenbank

Einzel- und Pooldatenbanken in Azure SQL-Datenbank unterstützen globale temporäre Tabellen und globale temporäre gespeicherte Prozeduren, die auf die Datenbankebene beschränkt sind und in tempdb gespeichert werden. Globale temporäre Tabellen und globale temporäre gespeicherte Prozeduren sind für alle Benutzersitzungen innerhalb derselben Datenbank freigegeben. Benutzersitzungen von anderen Datenbanken können nicht auf globale temporäre Tabellen zugreifen. Weitere Informationen finden Sie unter Database scoped global temporary tables (Azure SQL-Datenbank) (Globale temporäre Tabellen auf Datenbankebene (Azure SQL-Datenbank)).

Bei Singletons verfügt jeder einzelne Singleton auf einem logischen Server über einen eigenen tempdb. In einem Pool für elastische Datenbanken ist tempdb eine freigegebene Ressource für alle Datenbanken desselben Pools, aber in einer Datenbank erstellte temporäre Objekte sind nicht in den anderen Datenbanken desselben Pools sichtbar.

Bei Einzel- und Pooldatenbanken in Azure SQL-Datenbank sind von allen Systemdatenbanken nur die master-Datenbank und die tempdb-Datenbanken zugänglich. Weitere Informationen finden Sie unter Was ist ein logischer Server in Azure?

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

„tempdb“ in SQL Managed Instance

Azure SQL Managed Instance unterstützt temporäre Objekte auf dieselbe Weise wie SQL Server, wobei alle globalen temporären Tabellen und globalen temporären gespeicherten Prozeduren für alle Benutzersitzungen innerhalb derselben verwalteten Instanz zugänglich sind. Ebenso ist der Zugriff auf alle Systemdatenbanken möglich.

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.

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

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 haben nur Zugriff auf ihre eigenen Objekte, es sei denn, ihnen wurden zusätzliche Berechtigungen zugewiesen. Es ist möglich, die Berechtigung zum Herstellen einer Verbindung mit tempdb zu widerrufen, um einen Benutzer an der Verwendung von tempdb zu hindern. Dies wird jedoch nicht empfohlen, da die Verwendung von tempdb für einige Routinevorgänge erforderlich ist.

Optimieren der tempdb-Leistung in SQL Server

Die Größe und die physische Platzierung der tempdb-Datenbank kann sich auf die Leistung eines Systems auswirken. Wenn z. B. eine zu geringe Größe für tempdb definiert wurde, muss bei jedem Neustart der SQL-Server-Instanz möglicherweise ein Teil der Systemverarbeitungslast dafür aufgewendet werden, die tempdb-Datenbank automatisch auf den Umfang zu vergrößern, der für den anfallenden Workload erforderlich ist.

Verwenden Sie nach Möglichkeit die schnelle Dateiinitialisierung, um die Leistung von Vergrößerungsvorgängen 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. Durch die Vorabzuordnung wird verhindert, dass tempdb zu häufig vergrößert und die Leistung dadurch beeinträchtigt wird. Für die tempdb-Datenbank sollte die automatische Vergrößerung festgelegt werden, um den Speicherplatz für nicht geplante Ausnahmen zu erhöhen.

Die Datendateien müssen in jeder Dateigruppe gleich groß sein, da SQL Server einen Algorithmus zum proportionalen Füllen verwendet, der Zuweisungen in Dateien mit mehr freiem Speicherplatz bevorzugt. Ein Aufteilen von tempdb in mehrere Datendateien gleicher Größe bietet einen hohen Grad an paralleler Effizienz in Vorgängen, in denen tempdb verwendet wird.

Setzen Sie das Vergrößerungsinkrement der Datei auf einen angemessenen Wert, der in allen Datendateien gleich groß ist, um zu verhindern, dass die tempdb-Datenbankdateien um einen zu kleinen Wert anwachsen. Wenn das Wachstum der Datei im Vergleich zur in tempdb geschrieben Datenmenge zu gering ist, muss tempdb möglicherweise häufig über automatische Vergrößerungs-Events erweitert werden. Autogrowth-Ereignisse wirken sich negativ auf die Leistung aus.

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

Platzieren Sie die tempdb-Datenbank auf einem schnellen E/A-Subsystem. Verwenden Sie Datenträgerstriping, wenn viele Datenträger direkt angeschlossen sind. Einzelne oder Gruppen von tempdb-Datendateien müssen nicht unbedingt auf verschiedenen Datenträgern oder Spindeln gespeichert sein, es sei denn, Sie stellen außerdem E/A-Engpässe fest.

Platzieren Sie die tempdb-Datenbank nicht auf denselben Datenträgern, die auch von Benutzerdatenbanken genutzt werden.

Hinweis

Obwohl die Datenbankoption DELAYED_DURABILITY auf DEAKTIVIERT tempdb festgelegt ist, verwendet SQL Server faule Commits zum Leeren von tempdb-Protokolländerungen auf dem Datenträger, da tempdb beim Start erstellt wird und den Wiederherstellungsvorgang nicht ausführen muss.

Leistungsverbesserungen in tempdb für SQL Server

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.
  • Das Setup fügt während der Installation einer neuen Instanz mehrere tempdb-Datendateien hinzu. Für diese Aufgabe können Sie die neue Eingabesteuerung der Benutzeroberfläche im Abschnitt Datenbank-Engine-Konfiguration und den Befehlszeilenparameter /SQLTEMPDBFILECOUNT verwenden. Standardmäßig fügt das Setup die Anzahl von tempdb-Datendateien hinzu, die der Anzahl von logischen Prozessoren entspricht, höchstens jedoch acht.
  • 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!).
  • Für die primäre Dateigruppe ist die Eigenschaft AUTOGROW_ALL_FILES aktiviert. Diese kann nicht geändert werden.

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.
  • SQL-Server 2017 führt eine neue DMV sys.dm_tran_version_store_space_usage ein, um die Versionspeichernutzung nach Datenbank nachzuverfolgen. Diese neue DMV wird der Überwachung der tempdb-Versionsspeichernutzung für DBAs dienen, welche die tempdb-Größenanpassung basierend auf der Anforderung bei der Versionsspeichernutzung pro Datenbank proaktiv planen können.
  • Neue Features zur intelligenten Abfrageverarbeitung wie z B. adaptive Joins, und Feedback zur Speicherzuweisung reduzieren Speicherüberläufe bei wiederholten Ausführungen einer Abfrage, wodurch unnötige tempdb-Auslastung reduziert wird.

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

  • Ab SQL Server 2019 (15.x) verwendet SQL Server die FILE_FLAG_WRITE_THROUGH-Option nicht beim Öffnen von Dateien für tempdb, um maximalen Datenträgerdurchsatz zu ermöglichen. Da tempdb beim Starten von SQL Server neu erstellt wird, sind diese Optionen nicht wie für für andere Systemdatenbanken und Benutzerdatenbanken zur Konsistenz von Daten erforderlich. Weitere Informationen zu FILE_FLAG_WRITE_THROUGH finden Sie unter Protokollierungs- und Datenspeicheralgorithmen zur Erweiterung der Datenzuverlässigkeit in SQL Server.
  • Speicheroptimierte TempDB-Metadaten entfernen einen Engpass bei PAGELATCH-Wartevorgängen in tempdb und erschließen eine neue Ebene der Skalierbarkeit. Weitere Informationen hierzu finden Sie im Demo-Video zu „How (and When) To: Speicheroptimierte TempDB-Metadaten. Weitere Informationen finden Sie unter Überwachung und Problembehandlung für speicheroptimierte tempdb-Metadaten.
  • Aktualisierungen beim gleichzeitigen Page Free Space (PFS) reduzieren Seitenlatchkonflikte in allen Datenbanken, ein in tempdb sehr häufig auftretendes Problem. Diese Verbesserung ändert die Verwaltung paralleler Vorgänge bei PFS-Aktualisierungen, sodass die Aktualisierung nicht mit einem exklusiven Latch, sondern über einen gemeinsamen Latch erfolgen kann. 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-Datei ist 8 MB groß und wird automatisch um 64 MB vergrößert. Dieses Verhalten ähnelt dem der SQL Server-Standardinstallation unter Windows.

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

Speicheroptimierte tempdb-Metadaten

Bislang stellten Metadatenkonflikte in tempdb einen Engpass für die Skalierbarkeit vieler Workloads auf SQL-Server dar. SQL-Server 2019 (15.x) führt ein neues Feature ein, das zur In-Memory Database-Featurefamilie gehört: Speicheroptimierte tempdb-Metadaten.

Dieses Feature beseitigt diesen Engpass und ermöglicht ein neues Maß an Skalierbarkeit für tempdb-intensive Workloads. In SQL Server 2019 (15.x) können die an der Verwaltung von Metadaten temporärer Tabellen beteiligten Systemtabellen in nicht dauerhafte speicheroptimierte Tabellen ohne Latches verschoben werden.

Hinweis

Derzeit ist das speicheroptimierte TempDB-Metadatenfeature in Azure SQL-Datenbank oder Azure SQL Managed Instance nicht verfügbar.

In diesem siebenminütigen Video erhalten Sie einen Überblick darüber, wann und wie speicheroptimierte TempDB-Metadaten verwendet werden sollten:

Konfigurieren und Verwenden von speicheroptimierten tempdb-Metadaten

Mit dem folgenden Skript können Sie dieses neue Feature aktivieren:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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 nach dem Aktivieren von speicheroptimierten TempDB-Metadaten der Server aus irgendeinem Grund nicht startet, können Sie das Feature umgehen, indem Sie die SQL Server-Instanz in der Minimalkonfiguration über die Startoption -f starten. Dann können Sie das Feature deaktivieren und SQL Server im normalen Modus neu starten.

Zum Schutz des Servers vor potenziellen Bedingungen mit nicht genügendem Arbeitsspeicher können Sie tempdb an einen Ressourcenpool binden. Dies erfolgt über den Befehl ALTER SERVER anstelle der Schritte, die Sie normalerweise befolgen, um einen Ressourcenpool an eine Datenbank zu binden.

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

Diese Änderung erfordert außerdem einen Neustart, um wirksam zu werden, selbst dann, wenn die speicheroptimierten TempDB-Metadaten bereits aktiviert sind.

Einschränkungen von speicheroptimierten tempdb-Metadaten

  • Das Ein- und Ausschalten dieser Funktion ist nicht dynamisch. Aufgrund der systeminternen Änderungen, die an der Struktur von tempdb vorgenommen werden müssen, ist ein Neustart erforderlich, um das Feature zu aktivieren oder zu deaktivieren.

  • Eine einzelne Transaktion darf nicht auf speicheroptimierte Tabellen in mehreren Datenbanken zugreifen. Bei Transaktionen, an denen eine speicheroptimierte Tabelle in einer Benutzerdatenbank beteiligt ist, ist ein Zugriff auf tempdb-Systemsichten nicht innerhalb derselben Transaktion möglich. Wenn Sie versuchen, in derselben Transaktion auf tempdb-Systemsichten zuzugreifen, wird die folgende Fehlermeldung angezeigt:

    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.

    Beispiel:

    BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;

  • Abfragen in speicheroptimierten Tabellen unterstützen keine Sperr- und Isolationshinweise, daher werden diese Hinweise bei Abfragen in speicheroptimierten tempdb-Katalogsichten nicht berücksichtigt. Ebenso wie bei anderen Systemkatalogsichten in SQL-Server erfolgen alle Transaktionen für Systemsichten in READ COMMITTED-Isolation (bzw. in diesem Fall in READ COMMITTED SNAPSHOT-Isolation).

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

  • Aufgrund der Einschränkung für Columnstore-Indizes wird die Verwendung der gespeicherten Systemprozedur sp_estimate_data_compression_savings mit dem Datenkomprimierungsparameter COLUMNSTORE oder COLUMNSTORE_ARCHIVE nicht unterstützt, wenn speicheroptimierte TempDB-Metadaten aktiviert sind.

  • Eine gespeicherte Systemprozedur ist verfügbar, um manuell zu bewirken, dass die In-Memory-Engine Speicher im Zusammenhang mit gelöschten Zeilen von In-Memory-Daten freigibt, die zur automatischen Speicherbereinigung freigegeben sind. Dies kann bei der Problembehandlung bestimmter Fehler bei speicheroptimierten tempdb-Metadaten (HkTempDB) aufgrund ungenügenden Arbeitsspeichers helfen. Weitere Informationen finden Sie unter sys.sp_xtp_force_gc (Transact-SQL).

Hinweis

Diese Einschränkungen kommen nur beim Verweisen auf tempdb-Systemsichten zum Tragen. Sie können bei Bedarf eine temporäre Tabelle in derselben Transaktion erstellen, wenn Sie auf eine speicheroptimierte Tabelle in einer Benutzerdatenbank zugreifen.

Kapazitätsplanung für tempdb in SQL Server

Das Festlegen der angemessenen Größe von tempdb in einer SQL Server-Produktionsumgebung hängt von vielen Faktoren ab. Wie bereits erläutert, gehören die vorhandene Arbeitsauslastung und die verwendeten SQL-Server-Features zu diesen Faktoren.

Es wird empfohlen, die vorhandene Workload durch Ausführen folgender Aufgaben in einer SQL Server-Testumgebung zu analysieren:

  • Legen Sie automatische Vergrößerung ein für tempdb fest.
  • Führen Sie einzelne Abfragen oder Ablaufverfolgungsdateien für die Arbeitsauslastung aus, und überwachen Sie die Speicherplatzbelegung von tempdb.
  • Führen Sie Indexverwaltungsvorgänge aus – z. B. die Neuerstellung von Indizes –, und überwachen Sie den tempdb-Speicherplatz.
  • Verwenden Sie die Werte der Speicherplatzbelegung aus den vorherigen Schritten, um die gesamte Arbeitsauslastung zu prognostizieren. 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

Unzureichender Speicherplatz in tempdb kann erhebliche Unterbrechungen in der -SQL-Server-Produktionsumgebung verursachen. Dieses Problem kann auch dazu führen, dass Anwendungen Vorgänge nicht abschließen können. Mit der dynamischen Verwaltungssicht sys.dm_db_file_space_usage können Sie den in den tempdb-Dateien verwendeten Speicherplatz überwachen:

Die folgenden vier Beispielskripts ermitteln beispielsweise den freien Speicherplatz in tempdb, den vom Versionsspeicher verwendeten Speicherplatz, den von internen Objekten verwendeten Speicherplatz und den von Benutzerobjekten verwendeten Speicherplatz:

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Darüber hinaus können Sie die dynamischen Verwaltungssichten sys.dm_db_session_space_usage und sys.dm_db_task_space_usage verwenden, um die Seitenzuordnung und die Zuordnungsaufhebung in tempdb auf der Sitzungs- oder Taskebene zu überwachen. Mit diesen Sichten können Sie umfangreiche Abfragen, temporäre Tabellen oder Tabellenvariablen identifizieren, die sehr viel Speicherplatz in tempdb belegen. Sie können auch verschiedene Leistungsindikatoren verwenden, um den in tempdb verfügbaren freien Speicherplatz sowie die Ressourcen zu überwachen, die tempdb verwenden.

Verwenden Sie beispielsweise das folgende Skript, um den von internen Objekten verbrauchten tempdb-Speicherplatz in allen derzeit ausgeführten Aufgaben in jeder Sitzung zu erhalten:

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
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 Skript, um den von internen Objekten verbrauchten tempdb-Speicherplatz sowohl für derzeit ausgeführte wie für beendete Aufgaben in jeder Sitzung zu erhalten:

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

Zugehöriger Inhalt