Kapazitätsplanung für tempdb
Dieses Thema enthält Richtlinien zur Ermittlung der für tempdb erforderlichen Menge an Speicherplatz. In diesem Thema sind außerdem Empfehlungen zum Konfigurieren von tempdb für die optimale Leistung in einer Produktionsumgebung enthalten sowie Informationen zum Überwachen der Speicherplatzverwendung in tempdb.
Verwendung von tempdb
Die tempdb-Systemdatenbank ist eine globale Ressource, die für alle mit einer Instanz von SQL Server verbundenen Benutzer verfügbar ist. Die tempdb-Datenbank wird zum Speichern folgender Objekte verwendet: Benutzerobjekte, interne Objekte und Versionsspeicher.
Benutzerobjekte
Benutzerobjekte werden explizit vom Benutzer erstellt. Diese Objekte können sich im Bereich einer Benutzersitzung oder im Bereich der Routine befinden, in der das Objekt erstellt wurde. Bei einer Routine handelt es sich um eine gespeicherte Prozedur, einen Trigger oder eine benutzerdefinierte Funktion. Benutzerobjekte können Folgendes sein:
Benutzerdefinierte Tabellen und Indizes
Systemtabellen und -indizes
Globale temporäre Tabellen und Indizes
Lokale temporäre Tabellen und Indizes
Tabellenvariablen
In Tabellenwertfunktionen zurückgegebene Tabellen
Interne Objekte
Interne Objekte werden von SQL Server Database Engine (Datenbankmodul) ggf. zum Verarbeiten von SQL Server-Anweisungen erstellt. Interne Objekte werden innerhalb des Bereichs einer Anweisung erstellt und gelöscht. Interne Objekte können Folgendes sein:
Arbeitstabellen für Cursor- oder Spoolvorgänge und temporäre LOB-Speicher (Large Object).
Arbeitsdateien für Hashverknüpfungs- oder Hashaggregatvorgänge.
Zwischenergebnisse von Sortierungen für Vorgänge, wie z. B. das Erstellen oder Neuerstellen von Indizes (wenn SORT_IN_TEMPDB angegeben ist) oder bestimmte GROUP BY-, ORDER BY- oder UNION-Abfragen.
Jedes interne Objekt verwendet mindestens neun Seiten: eine IAM-Seite (Index Allocation Map) und einen achtseitigen Block. Weitere Informationen zu Seiten und Blöcken finden Sie unter Grundlegendes zu Seiten und Blöcken.
Versionsspeicher
Ein Versionsspeicher ist eine Sammlung von Datenseiten, in denen die Datenzeilen enthalten sind, die zur Unterstützung der Funktionen, die die Zeilenversionsverwaltung verwenden, erforderlich ist. Es gibt zwei Versionsspeicher: ein allgemeiner Versionsspeicher und ein Onlineindexerstellungs-Versionsspeicher. Der Versionsspeicher kann Folgendes beinhalten:
Zeilenversionen, die von Datenänderungstransaktionen in einer Datenbank erstellt wurden, für die die Momentaufnahme- oder READ COMMITTED-Isolationsstufen mit Zeilenversionsverwaltung verwendet wird.
Zeilenversionen, die von Datenänderungstransaktionen für Funktionen, wie z. B. Onlineindexvorgänge, Multiple Active Result Sets (MARS) und AFTER-Trigger, generiert wurden.
In der folgenden Tabelle finden Sie eine Auflistung der in SQL Server enthaltenen Funktionen, die Benutzerobjekte, interne Objekte und Zeilenversionen in tempdb erstellen. Nach Möglichkeit werden die Methoden zum Schätzen der Speicherplatzverwendung bereitgestellt.
Funktion |
Verwendung von tempdb |
Zusätzliche Informationen |
|---|---|---|
Massenladevorgänge mit aktivierten Triggern |
Massenimportoptimierungen sind verfügbar, wenn Trigger aktiviert sind. SQL Server verwendet für Trigger, die Transaktionen aktualisieren oder löschen, eine Zeilenversionsverwaltung. Dem Versionsspeicher wird jeweils eine Kopie der gelöschten oder aktualisierten Zeilen hinzugefügt. Weitere Informationen finden Sie weiter unten in dieser Tabelle unter "Trigger". |
|
Abfragen für allgemeine Tabellenausdrücke |
Ein allgemeiner Tabellenausdruck kann als temporäres Resultset betrachtet werden, das im Ausführungsbereich einer einzelnen SELECT-, INSERT-, UPDATE-, DELETE- oder CREATE VIEW-Anweisung definiert wird. Wenn im Abfrageplan für einen allgemeinen Tabellenausdruck zum Speichern von Zwischenergebnissen ein Spooloperator verwendet wird, wird von Database Engine (Datenbankmodul) zur Unterstützung dieses Vorgangs in tempdb eine Arbeitstabelle erstellt. |
|
Cursor |
Keysetgesteuerte und statische Cursor verwenden in tempdb erstellte Arbeitstabellen. Ein keysetgesteuerter Cursor verwendet die Arbeitstabellen, um das Keyset zu speichern, das die Zeilen des zugehörigen Cursors identifiziert. Ein statischer Cursor verwendet eine Arbeitstabelle zum Speichern des vollständigen Resultsets des Cursors. Die Speicherplatzverwendung für Cursor kann je nach ausgewähltem Abfrageplan variieren. Wenn der Abfrageplan dem Abfrageplan früherer SQL Server-Versionen entspricht, ist die Speicherplatzverwendung ungefähr dieselbe. |
|
Datenbank-E-Mail |
Weitere Informationen finden Sie weiter unten in dieser Tabelle unter "Service Broker". |
|
DBCC CHECKDB |
DBCC CHECKDB verwendet tempdb-Arbeitstabellen zum Speichern von Zwischenergebnissen und für Sortiervorgänge. Führen Sie DBCC CHECKDB WITH ESTIMATEONLY aus, um die tempdb-Speicherplatzanforderungen für den Vorgang zu bestimmen. |
|
Ereignisbenachrichtigungen |
Weitere Informationen finden Sie weiter unten in dieser Tabelle unter "Service Broker". |
|
Indizes |
Wenn Sie einen Index erstellen oder neu erstellen (offline oder online) und die SORT_IN_TEMPDB-Option auf ON festlegen, wird Database Engine (Datenbankmodul) angewiesen, zum Speichern der Zwischenergebnisse der Sortierung zum Erstellen eines Indexes tempdb zu verwenden. Wenn SORT_IN_TEMPDB angegeben und eine Sortierung erforderlich ist, muss tempdb über genügend Speicherplatz verfügen, um den größten Index zu speichern, und zusätzlich über einen Speicherplatz, der dem Wert der index create memory-Option entspricht. Weitere Informationen finden Sie unter Beispiel für den zum Speichern eines Indexes belegten Speicherplatz. Tabellen und Indizes können partitioniert werden. Wenn die SORT_IN_TEMPDB-Indexoption bei partitionierten Indizes angegeben ist, und der Index ist an der Basistabelle ausgerichtet, muss in tempdb genügend Speicherplatz vorhanden sein, um die Zwischensortierläufe der größten Partition zu speichern. Ist der Index nicht ausgerichtet, muss in tempdb genügend Speicherplatz vorhanden sein, um die Zwischensortierläufe aller Partitionen zu speichern. Weitere Informationen finden Sie unter Spezielle Richtlinien für partitionierte Indizes. Bei Onlineindexvorgängen wird die Zeilenversionsverwaltung verwendet, um den Indexvorgang von den Auswirkungen der von anderen Transaktionen vorgenommenen Änderungen zu isolieren. Bei der Zeilenversionsverwaltung ist das Anfordern von freigegebenen Sperren für gelesene Zeilen nicht erforderlich. Für gleichzeitige Update- und Löschvorgänge, die vom Benutzer während Onlineindexvorgängen durchgeführt werden, ist ein entsprechender Speicherplatz für Versionsdatensätze in tempdb erforderlich. Verwenden Onlineindexvorgänge SORT_IN_TEMPDB, und ist die Sortierung erforderlich, muss tempdb ebenfalls über zusätzlichen Speicherplatz für die zuvor beschriebenen Zwischensortierergebnisse verfügen. Onlineindexvorgänge, die einen gruppierten Index erstellen, löschen oder neu erstellen, erfordern ebenfalls zusätzlichen Speicherplatz zum Erstellen und Verwalten eines temporären Zuordnungsindexes. CREATE- und UPDATE STATISTICS-Vorgänge können mithilfe von tempdb die Stichprobenzeilen zum Erstellen von Statistiken sortieren. Weitere Informationen finden Sie unter Speicherplatzanforderungen für Index-DDL-Vorgänge. |
Spezielle Richtlinien für partitionierte Indizes Speicherplatzanforderungen für Index-DDL-Vorgänge Beispiel für den zum Speichern eines Indexes belegten Speicherplatz |
Variablen und Parameter des LOB-Datentyps (Large Object) |
Zu den LOB-Datentypen zählen varchar(max), nvarchar(max), varbinary(max)text, ntext, image und xml. Diese Typen können bis zu 2 GB groß sein und können als Variablen oder Parameter in gespeicherten Prozeduren, benutzerdefinierten Funktionen, Batches oder Abfragen verwendet werden. Als LOB-Datentypen definierte Parameter und Variablen verwenden den Hauptspeicher zum Speichern kleiner Werte. Große Werte werden hingegen in tempdb gespeichert. In tempdb gespeicherte LOB-Variablen und -Parameter werden als interne Objekte behandelt. Sie können die dynamische Verwaltungssicht sys.dm_db_session_space_usage abfragen, um die Seiten, die internen Objekten einer bestimmten Sitzung zugewiesen sind, zu melden. Einige systeminterne Zeichenfolgenfunktionen, z. B. SUBSTRING oder REPLICATE, können die temporäre Zwischenspeicherung in tempdb erfordern, wenn sie LOB-Werte verarbeiten. Wenn eine auf Zeilenversionsverwaltung basierte Transaktionsisolationsstufe in der Datenbank aktiviert wird, und an LOB-Objekten wurden Änderungen vorgenommen, wird das geänderte Fragment des LOB-Objekts gleichermaßen in tempdb in den Versionsspeicher kopiert. |
|
Multiple Active Result Sets (MARS) |
Es ist möglich, dass mehrere aktive Resultsets unter einer einzelnen Verbindung auftreten. Dies wird als MARS (Multiple Active Result Sets) bezeichnet. Wenn eine MARS-Sitzung bei einem aktiven Resultset eine Datenänderungsanweisung ausgibt (z. B. INSERT, UPDATE oder DELETE), werden die von der Änderungsanweisung betroffenen Zeilen in tempdb im Versionsspeicher gespeichert. Weitere Informationen finden Sie weiter unten in dieser Tabelle unter "Zeilenversionsverwaltung". |
|
Abfragebenachrichtigungen |
Weitere Informationen finden Sie weiter unten in dieser Tabelle unter "Service Broker". |
|
Abfragen |
Abfragen mit SELECT-, INSERT-, UPDATE- und DELETE-Anweisungen können interne Objekte verwenden, um Zwischenergebnisse für Hashjoins, Hashaggregate und Sortierungen zu speichern. Wenn ein Abfrageausführungsplan zwischengespeichert wird, werden die vom Plan benötigten Arbeitstabellen ebenfalls zwischengespeichert. Beim Zwischenspeichern einer Arbeitstabelle wird die Tabelle abgeschnitten, und im Cache verbleiben neun Seiten zur Wiederverwendung. Auf diese Weise wird die Leistung der nächsten Abfrageausführung verbessert. Verfügt das System über sehr wenig freien Arbeitsspeicher, kann Database Engine (Datenbankmodul) den Ausführungsplan entfernen und die zugehörigen Arbeitstabellen löschen. |
|
Zeilenversionsverwaltung |
Die Zeilenversionsverwaltung stellt ein allgemeines Framework zur Unterstützung folgender Funktionen dar:
Zeilenversionen werden im Versionsspeicher von tempdb für die Zeit gespeichert, in der eine aktive Transaktion auf sie zugreifen muss. Der Inhalt des aktuellen Versionsspeichers wird in sys.dm_tran_version_store zurückgegeben. Versionsspeicherseiten werden auf der Dateiebene nachverfolgt, da es sich bei ihnen um globale Ressourcen handelt. Sie können die version_store_reserved_page_count-Spalte in sys.dm_db_file_space_usage verwenden, um die aktuelle Größe des Versionsspeichers anzuzeigen. Beim Cleanup des Versionsspeichers muss die am längsten ausgeführte Transaktion, die Zugriff auf die bestimmte Version benötigt, berücksichtigt werden. Die im Zusammenhang mit dem Cleanup des Versionsspeichers am längsten ausgeführte Transaktion kann durch Anzeigen der elapsed_time_seconds-Spalte in sys.dm_tran_active_snapshot_database_transactions ermittelt werden. Die im Transactions-Objekt enthaltenen Leistungsindikatoren Freier Speicherplatz in tempdb (KB) und Versionsspeichergröße (KB) können zum Überwachen der Größe und Wachstumsrate des Zeilenversionsspeichers in tempdb verwendet werden. Weitere Informationen finden Sie unter SQL Server, Transaktionen-Objekt. Sie müssen zunächst sicherstellen, dass alle Änderungen einer aktiven Transaktion im Versionsspeicher enthalten sind, um den in tempdb für die Zeilenversionsverwaltung erforderlichen Speicherplatz zu schätzen. Das bedeutet, dass eine später gestartete Momentaufnahmetransaktion auf die älteren Versionen zugreifen kann. Außerdem müssen bei einer aktiven Momentaufnahmetransaktion alle durch Transaktionen generierten Versionsspeicherdaten, die beim Starten der Momentaufnahme aktiviert sind, ebenfalls beibehalten werden. Hierbei gilt eine Grundformel: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
|
Service Broker |
Service Broker unterstützt die Entwickler beim Erstellen von asynchronen, lose verbundenen Anwendungen, bei denen unabhängige Komponenten zusammenwirken, um eine Aufgabe durchzuführen. Diese Anwendungskomponenten tauschen Nachrichten aus, die die zum Abschließen der Aufgabe erforderlichen Informationen enthalten. Service Broker verwendet tempdb explizit, wenn vorhandener Dialogkontext, der nicht im Speicher bleiben kann, erhalten bleiben soll. Die Größe pro Dialog beträgt ungefähr 1 KB. Service Broker verwendet außerdem tempdb implizit, indem Objekte im Kontext der Abfrageausführung zwischengespeichert werden, z. B. Arbeitstabellen, die für Zeitgeberereignisse und im Hintergrund gelieferte Konversationen verwendet werden. Datenbank-E-Mail, Ereignisbenachrichtigungen und Abfragebenachrichtigungen verwenden implizit Service Broker. |
|
Gespeicherte Prozeduren |
Gespeicherte Prozeduren können Benutzerobjekte erstellen, wie z. B. globale oder lokale temporäre Tabellen und die zugehörigen Indizes sowie Variablen und Parameter. Temporäre Objekte gespeicherter Prozeduren können zwischengespeichert werden, um die Vorgänge zum Löschen und Erstellen dieser Objekte zu optimieren. Aufgrund dieses Verhaltens können die Speicherplatzanforderungen von tempdb zunehmen. Für jedes temporäre Objekt werden bis zu neun Seiten zur Wiederverwendung gespeichert. Weitere Informationen finden Sie nachstehend in dieser Tabelle unter "Temporäre Tabellen und table-Variablen". |
|
Temporäre Tabellen und table-Variablen
|
Temporäre Tabellen und table-Variablen werden in tempdb gespeichert. Die Speicherplatzanforderungen für temporäre Tabellenobjekte sind mit den Speicherplatzanforderungen früherer SQL Server-Versionen identisch. Die zum Schätzen der Größe einer temporären Tabelle angewandte Methode ist mit der zum Schätzen der Größe einer Standardtabelle verwendeten Methode identisch. Weitere Informationen finden Sie unter Schätzen der Größe einer Tabelle. Eine table-Variable verhält sich wie eine lokale Variable. Eine table-Variable weist den table-Typ auf und wird hauptsächlich für die temporäre Speicherung einer Zeilengruppe verwendet, die als Resultset einer Tabellenwertfunktion zurückgegeben wird. Der für eine table-Variable erforderliche Speicherplatz hängt von der Größe der deklarierten Variable und vom Wert der Variable ab. Lokale temporäre Tabellen und Variablen werden zwischengespeichert, wenn die folgenden Bedingungen erfüllt sind:
Beim Zwischenspeichern einer temporären Tabelle oder einer table-Variablen wird das temporäre Objekt nach Erfüllung seines Zwecks nicht gelöscht. Stattdessen wird das temporäre Objekt abgeschnitten. Bis zu neun Seiten werden gespeichert, die bei der nächsten Ausführung des aufrufenden Objekts wiederverwendet werden. Das Zwischenspeichern ermöglicht das sehr schnelle Ausführen von Vorgängen zum Löschen und Erstellen der Objekte und reduziert das Auftreten von Seitenzuordnungskonflikten. Um die optimale Leistung sicherzustellen, sollten Sie den in tempdb erforderlichen Speicherplatz für zwischengespeicherte lokale temporäre Tabellen oder table-Variablen mithilfe der folgenden Formel ermitteln: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
Trigger |
Die in AFTER-Triggern verwendeten Tabellen inserted und deleted werden in tempdb erstellt. Das heißt, dass bei allen vom Trigger aktualisierten oder gelöschten Zeilen eine Versionsverwaltung durchgeführt wird. Hierzu gehören alle von der Anweisung, die den Trigger ausgelöst hat, geänderten Zeilen. Bei allen vom Trigger eingefügten Zeilen wird die Versionsverwaltung nicht durchgeführt. INSTEAD OF-Trigger verwenden tempdb gleichermaßen wie Abfragen. Die Speicherplatzverwendung von INSTEAD OF-Triggern ist die gleiche wie in SQL Server-Versionen. Weitere Informationen finden Sie weiter oben in dieser Tabelle unter "Abfragen". Beim Massenladen von Daten mit aktivierten Triggern wird dem Versionsspeicher jeweils eine Kopie der gelöschten oder aktualisierten Seiten hinzugefügt. |
|
Benutzerdefinierte Funktionen |
Benutzerdefinierte Funktionen können temporäre Benutzerobjekte erstellen, wie z. B. globale oder lokale temporäre Tabellen und die zugehörigen Indizes, Variablen oder Parameter. Beispielsweise wird die Rückgabetabelle einer Tabellenwertfunktion in tempdb gespeichert. Die für Parameter und Rückgabewerte in skalaren Funktionen und Tabellenwertfunktionen zulässigen Datentypen umfassen auch die meisten LOB-Datentypen. Beispielsweise kann ein Rückgabewert den xml-Typ oder varchar(max)-Typ aufweisen. Weitere Informationen finden Sie weiter oben in dieser Tabelle unter "Variablen und Parameter des LOB-Datentyps (Large Object)". Temporäre Objekte von benutzerdefinierten Tabellenwertfunktionen können zwischengespeichert werden, um die Vorgänge zum Löschen und Erstellen dieser Objekte zu optimieren. Weitere Informationen finden Sie weiter oben in dieser Tabelle unter "Temporäre Tabellen und table-Variablen". |
|
XML |
Variablen und Parameter des xml-Typs können eine Größe von bis zu 2 GB annehmen. Zum Speichern kleiner Werte wird der Hauptspeicher verwendet. Große Werte werden hingegen in tempdb gespeichert. Weitere Informationen finden Sie weiter oben in dieser Tabelle unter "Variablen und Parameter des LOB-Datentyps (Large Object)". Die gespeicherte Systemprozedur sp_xml_preparedocument erstellt eine Arbeitstabelle in tempdb. Der Microsoft XML-Parser verwendet die Arbeitstabelle zum Speichern des analysierten XML-Dokuments. Die Speicherplatzanforderungen für tempdb sind beim Ausführen der gespeicherten Prozedur fast proportional zur Größe des angegebenen XML-Dokuments. |
Implementieren von XML in SQL Server |
Kapazitätsplanung für Updates auf SQL Server
Das Festlegen der angemessenen Größe von tempdb in einer Produktionsumgebung hängt von vielen Faktoren ab. Wie bereits zuvor in diesem Thema beschrieben, schließen diese Faktoren die vorhandene Arbeitsauslastung und die verwendeten SQL Server-Funktionen ein. Es wird empfohlen, die vorhandene Arbeitsauslastung durch Ausführen folgender Aufgaben in einer SQL Server-Testumgebung zu analysieren:
Festlegen der automatischen Vergrößerung für tempdb.
Ausführen einzelner Abfragen oder einzelner Arbeitsauslastungsdateien sowie Überwachen der Speicherplatzverwendung in tempdb.
Ausführen von Indexverwaltungsvorgängen, wie z. B. Neuerstellen von Indizes, und Überwachen des Speicherplatzes in tempdb.
Verwenden der Werte über die Speicherplatzverwendung aus den vorigen Schritten, um die Gesamtarbeitsauslastung vorherzusagen; Anpassen dieses Werts für prognostizierte gleichzeitige Aktivitäten und anschließend Festlegen der entsprechenden Größe von tempdb.
Weitere Informationen zum Überwachen des Speicherplatzes in tempdb finden Sie unter Problembehandlung bei unzureichendem Speicherplatz in tempdb. Weitere Informationen zum Schätzen der Speicherplatzverwendung in tempdb bei Indexvorgängen finden Sie unter Beispiel für den zum Speichern eines Indexes belegten Speicherplatz.
Konfigurieren von tempdb für Produktionsumgebungen
Befolgen Sie die unter Optimieren der Leistung von 'tempdb' bereitgestellten Richtlinien und Empfehlungen für eine optimale tempdb-Leistung.
Überwachen der Speicherplatzverwendung in tempdb
Wenn nicht mehr genügend Speicherplatz in tempdb vorhanden ist, kann das erhebliche Störungen in der SQL Server-Produktionsumgebung verursachen und dazu führen, dass ausgeführte Anwendungen Vorgänge nicht abschließen können. Sie können mit der dynamischen Verwaltungssicht sys.dm_db_file_space_usage den von diesen Funktionen in den tempdb-Dateien verwendeten Speicherplatz überwachen. Um außerdem die Aktivität für die Seitenzuordnung und die Zuordnungsaufhebung in tempdb auf der Sitzungs- oder Aufgabenebene zu überwachen, können Sie die dynamischen Verwaltungssichten sys.dm_db_session_space_usage und sys.dm_db_task_space_usage verwenden. Mithilfe dieser Sichten können große Abfragen, temporäre Tabellen oder Tabellenvariablen identifiziert werden, die große Mengen von Speicherplatz in tempdb belegen. Es gibt ebenfalls mehrere Leistungsindikatoren, die zum Überwachen des in tempdb verfügbaren freien Speicherplatzes verwendet werden können. Diese können auch verwendet werden, um die Ressourcen, die tempdb verwenden, zu überwachen. Weitere Informationen finden Sie unter Problembehandlung bei unzureichendem Speicherplatz in tempdb.
Siehe auch