Tempdb-Platzressourcenverwaltung

Gilt für: SQL Server 2025 (17.x) und höhere Versionen

Wenn Sie die Ressourcenverwaltung tempdb aktivieren, verbessern Sie die Zuverlässigkeit und vermeiden Ausfälle, indem Sie verhindern, dass ungebremste Abfragen oder Workloads einen großen Speicherplatz in tempdb beanspruchen.

Ab SQL Server 2025 (17.x) können Sie den Ressourcenhauptwert verwenden, um einen Grenzwert für die Gesamtmenge des tempdb von einer Workloadgruppe verbrauchten Speicherplatzes zu erzwingen. Wenn eine Anforderung (eine Abfrage) versucht, den Grenzwert zu überschreiten, bricht die Ressourcenverwaltung sie mit einem eindeutigen Fehler ab, der angibt, dass die Workloadgruppengrenze erzwungen wird.

Tatsächlich können Sie den freigegebenen tempdb Speicherplatz unter verschiedenen Workloads partitionieren. Sie können beispielsweise einen höheren Grenzwert für eine Workloadgruppe festlegen, die von einer unternehmenskritischen Anwendung verwendet wird, und einen niedrigeren Grenzwert für die Workloadgruppe festlegen, die default von allen anderen Workloads verwendet wird.

Beispiele für eine schrittweise Konfiguration finden Sie unter Tutorial: Beispiele für die Konfiguration der Governance von Tempdb-Speicherressourcen.

Erste Schritte mit Ressourcenverwaltung

Die Ressourcenkontrolle bietet ein flexibles Framework, um unterschiedliche tempdb Platzbeschränkungen für verschiedene Anwendungen, Benutzer, Benutzergruppen usw. festzulegen. Sie können auch Grenzwerte basierend auf benutzerdefinierter Logik festlegen.

Wenn Sie noch nicht mit dem Ressourcengouverneur in SQL Server vertraut sind, finden Sie informationen zu den Konzepten und Funktionen des Ressourcengouverneurs.

Für eine exemplarische Konfiguration des Ressourcen-Governors und bewährte Methoden, siehe Lernprogramm: Beispiele und bewährte Methoden zur Konfiguration des Ressourcen-Governors.

Festlegen von Grenzwerten für den tempdb-Speicherplatzverbrauch

Sie können den Speicherplatzverbrauch durch eine Workloadgruppe auf eine von zwei Arten einschränken tempdb :

• Legen Sie einen festen Grenzwert mithilfe des GROUP_MAX_TEMPDB_DATA_MB Arguments fest.

  Verwenden Sie den festen Grenzwert, wenn Sie die Arbeitsauslastungsanforderungen tempdb im Voraus kennen oder wenn sich die tempdb Größe nicht ändert.

• Legen Sie mithilfe des Arguments einen GROUP_MAX_TEMPDB_DATA_PERCENT fest.

  Verwenden Sie die Prozentgrenze, wenn Sie die maximale Größe von tempdb im Laufe der Zeit ändern könnten und möchten, dass sich der für jede Workloadgruppe verfügbare tempdb-Speicherplatz proportional ändert, ohne die Workloadgruppenkonfiguration zu ändern. Wenn Sie z. B. eine Azure-VM mit SQL Server skalieren und die maximale tempdb Größe erhöhen, steigt auch der tempdb für jede Workloadgruppe verfügbare Speicherplatz mit einem Prozentgrenzwert.

Weitere Informationen zu den Argumenten GROUP_MAX_TEMPDB_DATA_MB und GROUP_MAX_TEMPDB_DATA_PERCENT finden Sie unter CREATE WORKLOAD GROUP oder ALTER WORKLOAD GROUP.

Wenn Sie sowohl feste als auch Prozentgrenzen für dieselbe Workloadgruppe angeben, hat das feste Limit Vorrang vor dem Prozentgrenzwert.

Bei einer bestimmten SQL Server-Instanz können Sie eine Mischung aus Workloadgruppen mit festen Grenzwerten, Prozentgrenzwerten oder keinen Grenzwerten für tempdb den Speicherplatzverbrauch haben. Informationen zum Anzeigen effektiver Grenzwerte finden Sie im Beispiel für effektive tempdb-Speicherplatzbeschränkungen pro Workloadgruppe .

Prozentgrenzwertkonfiguration

Wenn Sie die ALTER RESOURCE GOVERNOR RECONFIGURE Anweisung ausführen, werden die Prozentgrenzwerte gemäß der folgenden Tabelle wirksam:

Zum Anzeigen Ihrer tempdb Konfiguration siehe das Beispiel Tempdb-Datendateikonfiguration anzeigen.

Berücksichtigen Sie bei der Verwendung von Prozentgrenzwerten Folgendes:

• Wenn Sie die ALTER RESOURCE GOVERNOR festlegen und ausführen, die Datendateikonfiguration jedoch nicht den Anforderungen entspricht, wird die Anweisung erfolgreich abgeschlossen, und die Prozentgrenzwerte werden gespeichert, aber sie werden nicht erzwungen. In diesem Fall erhalten Sie warnmeldung 10989, Schweregrad 10, der auch im Fehlerprotokoll protokolliert wird:

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

• Um die Prozentgrenzwerte effektiv zu machen, konfigurieren Sie die tempdb-Datendateien neu, um die Anforderungen zu erfüllen, und führen Sie ALTER RESOURCE GOVERNOR RECONFIGURE erneut aus. Weitere Informationen zum Konfigurieren von SIZE, FILEGROWTH und MAXSIZE finden Sie unter ALTER DATABASEDatei- und Dateigruppenoptionen.

• Wenn ein Prozentlimit in Kraft ist und Sie tempdb Datendateien hinzufügen, entfernen oder die Größe ändern, müssen Sie ALTER RESOURCE GOVERNOR RECONFIGURE ausführen, um den Ressourcen-Manager mit der neuen maximalen Größe von tempdb (100%) zu aktualisieren.

Funktionsweise

In diesem Abschnitt wird die Governance von Weltraumressourcen detailliert beschrieben tempdb.

• Da Datenseiten tempdb zugeordnet und freigegeben werden, führt der Resource Governor Buch über den von jeder Workloadgruppe verbrauchten Speicherplatz.

  Wenn die Ressourcensteuerung aktiviert ist und für eine Workloadgruppe ein tempdb Speicherplatzverbrauchslimit festgelegt ist, und eine Anforderung (eine Abfrage), die in der Workloadgruppe ausgeführt wird, versucht, den Gesamtspeicherverbrauch tempdb der Gruppe über dem Grenzwert zu bringen, wird die Anforderung mit Fehler 1138, Schweregrad 17 abgebrochen:

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

  Wenn eine Anforderung mit Fehler 1138 abgebrochen wird, wird der Wert in der total_tempdb_data_limit_violation_count Spalte der sys.dm_resource_governor_workload_groups dynamischen Verwaltungsansicht (DYNAMIC Management View, DMV) um eins erhöht, und das tempdb_data_workload_group_limit_reached erweiterte Ereignis wird ausgelöst.

• Die Ressourcenkontrolle verfolgt alle tempdb Verwendungen, die einer Workloadgruppe zugeordnet werden können, einschließlich temporärer Tabellen, Variablen (einschließlich Tabellenvariablen), tabellenwertiger Parameter, nicht temporärer Tabellen, Cursors und Verwendungen von tempdb während der Abfrageverarbeitung, wie Spools, Überläufe, Arbeitstabellen und Arbeitsdateien.

  Der Speicherplatzverbrauch für globale temporäre Tabellen und nichttemporäre Tabellen wird tempdb unter der Workloadgruppe berücksichtigt, die die erste Zeile in die Tabelle einfügt, auch wenn Sitzungen in anderen Workloadgruppen Zeilen in derselben Tabelle hinzufügen, ändern oder entfernen.

• Die konfigurierten tempdb Verbrauchsgrenzwerte für jede Workloadgruppe werden in der sys.resource_governor_workload_groups Katalogansicht in den group_max_tempdb_data_mb und group_max_tempdb_data_percent spalten verfügbar gemacht.

  Der aktuelle Verbrauch und der Spitzenverbrauch von tempdb-Speicherplatz durch eine Workload-Gruppe werden in der DMV sys.dm_resource_governor_workload_groups in den Spalten tempdb_data_space_kb bzw. peak_tempdb_data_space_kb zur Verfügung gestellt.

  Tipp

  tempdb_data_space_kb und peak_tempdb_data_space_kb Spalten in sys.dm_resource_governor_workload_groups werden beibehalten, auch wenn keine Grenzwerte für den tempdb Platzverbrauch festgelegt werden.

  Sie können die Klassifiziererfunktion und Arbeitsauslastungsgruppen erstellen, ohne zunächst Einschränkungen festzulegen. Überwachen Sie tempdb die Verwendung durch jede Gruppe im Laufe der Zeit, um repräsentative Nutzungsmuster zu identifizieren, und legen Sie dann nach Bedarf Grenzwerte fest.

• tempdb Die Nutzung der Versionsspeicher, einschließlich des persistenten Versionsspeichers (PVS), wird nicht gesteuert, wenn die beschleunigte Datenbankwiederherstellung (ADR) aktiviert ist tempdb, da Zeilenversionen möglicherweise von Anforderungen in mehreren Workloadgruppen verwendet werden.

• Der Speicherplatzverbrauch in tempdb wird als die Anzahl der verwendeten 8-KB-Datenseiten gerechnet. Selbst wenn eine Seite nicht vollständig mit Daten gefüllt ist, fügt sie dem tempdb Verbrauch einer Workloadgruppe 8 KB hinzu.

• tempdb Die Speicherplatzverwaltung wird über die gesamte Lebensdauer der Workload-Gruppe beibehalten. Wenn eine Workloadgruppe verworfen wird, während globale temporäre Tabellen oder nichttemporäre Tabellen mit den Daten, die dieser Workloadgruppe zugeordnet sind, in tempdb verbleiben, wird der von diesen Tabellen verwendete Speicherplatz nicht unter einer anderen Workloadgruppe berücksichtigt.

• tempdb Data-Governance steuert den Speicherplatz in tempdb-Datendateien, aber nicht den Speicherplatz auf den zugrunde liegenden Volumes. Sofern Sie tempdb Datendateien nicht vorab auf ihre vorgesehene Größe vergrößern, könnte der Speicherplatz auf den Volumes, auf denen sich tempdb befindet, von anderen Dateien belegt werden. Wenn kein Platz mehr für tempdb-Datendateien zum Vergrößern vorhanden ist, könnte tempdb keinen Platz mehr haben, bevor das Workload-Gruppenlimit für den tempdb-Speicherverbrauch erreicht ist.

• Die Space Resource Governance gilt für Datendateien in tempdb, jedoch nicht für die Transaktionsprotokolldatei. Um sicherzustellen, dass das Transaktionsprotokoll tempdb keinen großen Speicherplatz belegt, aktivieren Sie ADR in tempdb.

Unterschiede bei der Raumnachverfolgung auf Sitzungsebene

Die sys.dm_db_session_space_usage DMV stellt tempdb für jede Sitzung Raumzuweisungs- und Deallocation-Statistiken bereit. Selbst wenn es nur eine Sitzung in einer Workloadgruppe gibt, stimmen die Statistiken zur Raumnutzung aus diesem DMV möglicherweise nicht exakt mit den Statistiken aus der ansicht sys.dm_resource_governor_workload_groups überein, aus den folgenden Gründen:

• Im Gegensatz zu sys.dm_resource_governor_workload_groups: sys.dm_db_session_space_usage
  • Spiegelt die Speicherplatznutzung durch die derzeit ausgeführten Aufgaben nicht wider tempdb . Die Statistiken in sys.dm_db_session_space_usage werden aktualisiert, sobald eine Aufgabe abgeschlossen ist. Statistiken in sys.dm_resource_governor_workload_groups werden kontinuierlich aktualisiert.
  • Verfolgt keine Indexzuordnungsseiten (IAM) nach. Weitere Informationen finden Sie im Leitfaden zur Seiten- und Erweiterungsarchitektur.
• Nachdem Zeilen gelöscht wurden oder wenn eine Tabelle, ein Index oder eine Partition gelöscht oder geleert wird, gibt die Datenbank-Engine die Datenseiten frei. Die Deallocation kann synchron oder von einem asynchronen Hintergrundprozess ausgeführt werden. sys.dm_resource_governor_workload_groups spiegelt diese Seitenfreigaben wider, selbst wenn die Sitzung, die diese Freigaben verursacht hat, abgeschlossen wurde und in sys.dm_db_session_space_usage nicht mehr vorhanden ist.

Bewährte Verfahren für die Ressourcenverwaltung von Tempdb

Bevor Sie die Governance für tempdb-Speicherplatzressourcen konfigurieren, sollten Sie die folgenden bewährten Verfahren berücksichtigen:

• Überprüfen Sie die allgemeinen bewährten Methoden für den Ressourcengouverneur.

• Vermeiden Sie für die meisten Szenarien das Festlegen des Grenzwerts für den tempdb Speicherplatzverbrauch auf einen kleinen Wert oder null, insbesondere für die default Workloadgruppe. Wenn Sie diesen Grenzwert auf einen kleinen Wert oder null festlegen, können viele häufige Aufgaben möglicherweise fehlschlagen, wenn sie Speicherplatz tempdbzuweisen müssen. Wenn Sie z. B. entweder das feste oder das Prozentlimit für die default Workloadgruppe auf 0 festlegen, können Sie den Objekt-Explorer möglicherweise nicht in SQL Server Management Studio (SSMS) öffnen.

• Wenn Sie keine benutzerdefinierten Workloadgruppen und keine Klassifizierungsfunktion erstellen, die Workloads ihren jeweils zugewiesenen Gruppen zuordnet, sollten Sie die Verwendung der Workloadgruppe default nicht tempdb einschränken. Wenn Sie den Speicherplatzverbrauch durch die tempdb Workloadgruppe begrenzendefault, geben Abfragen möglicherweise Fehler 1138 zurück. Dieser Fehler tritt auf, wenn tempdb noch ungenutzten Speicherplatz hat, der von keiner Benutzer-Workload belegt werden kann.

• Die Summe der GROUP_MAX_TEMPDB_DATA_MB Werte für alle Workloadgruppen kann die maximale tempdb Größe überschreiten. Wenn die maximale tempdb Größe beispielsweise 100 GB beträgt, können die GROUP_MAX_TEMPDB_DATA_MB Grenzwerte für Arbeitsauslastungsgruppe A und Workloadgruppe B jeweils 80 GB betragen.

  Dieser Ansatz verhindert weiterhin, dass jede Workloadgruppe den gesamten Speicherplatz tempdb verbraucht, indem sie 20 GB für andere Workloadgruppen verlassen. Gleichzeitig vermeiden Sie unnötige Abfrageabbrüche, wenn noch freier tempdb Speicherplatz verfügbar ist, da Arbeitsauslastungsgruppen A und B nicht gleichzeitig einen großen tempdb Speicherplatz verbrauchen.

  Ebenso kann die Summe der GROUP_MAX_TEMPDB_DATA_PERCENT Werte für alle Workloadgruppen 100 Prozent überschreiten. Sie können jeder Gruppe mehr tempdb Speicherplatz zuweisen, wenn Sie wissen, dass es unwahrscheinlich ist, dass mehrere Gruppen gleichzeitig eine hohe tempdb Nutzung verursachen.

Examples

Tempdb-Datendateikonfiguration anzeigen

Die folgende Abfrage zeigt die aktuelle tempdb Konfiguration der Datendatei:

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

Für eine bestimmte Datei im Resultset:

• Wenn die maxsize_mb-Spalte NULL ist, dann ist MAXSIZEUNLIMITED.
• Wenn entweder filegrowth_mb oder filegrowth_percent null ist, ist FILEGROWTH null.

Anzeigen effektiver tempdb-Speicherplatzbeschränkungen pro Workloadgruppe

Die folgende Abfrage zeigt den effektiven tempdb Speicherplatzverbrauchsgrenzwert für jede Workloadgruppe. Das Limit wird bei einer Konfiguration als festes Limit oder prozentuales Limit in Megabyte zurückgegeben.

Wenn die group_effective_limit_mb Spalte lautet NULL, bedeutet dies eine der folgenden Optionen:

• Weder ein fester noch ein prozentualer Grenzwert ist konfiguriert.
• Die Anforderungen für die Verwendung der Prozentgrenzwertkonfiguration sind nicht erfüllt.

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

Nächster Schritt

Verwandte Inhalte

• Ressourcenkontrolle
• Lernprogramm: Konfigurationsbeispiele für Ressourcenverwaltung und bewährte Methoden
• ALTER RESOURCE GOVERNOR (Transact-SQL)
• CREATE WORKLOAD GROUP (Transact-SQL)
• ALTER WORKLOAD GROUP (Transact-SQL)
• DROP WORKLOAD GROUP (Transact-SQL)
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups