TempDB einrichten: Dateien, Ablage und Größe

Dieser Beitrag beantwortet die drei Fragen, die bei jeder Installation anstehen: wie viele Datendateien, wohin mit Daten und Protokoll und wie groß. Grundlage sind eigene Messungen auf SQL Server 2017, 2019, 2022 und 2025.

Mehrere Datendateien: gegen welches Problem?

Jede Datendatei verwaltet ihren Platz über wenige besondere Seiten: PFS-Seiten vermerken, wie voll eine Seite ist, GAM- und SGAM-Seiten, welche Bereiche frei sind. Legt eine Sitzung eine temporäre Tabelle an, muss SQL Server diese Seiten kurz sperren. Tun das sehr viele Sitzungen gleichzeitig, stehen sie vor denselben wenigen Seiten Schlange. Sichtbar wird das als Wartetyp PAGELATCH_UP oder PAGELATCH_EX auf Ressourcen, die mit 2: beginnen, der Datenbank-ID von TempDB.

Mehrere gleich große Datendateien verteilen diese Arbeit: Jede Datei hat eigene Verwaltungsseiten, und SQL Server füllt die Dateien reihum. Das hilft nur gegen diese Art des Wartens. Gegen volle Platten, langsamen Storage oder Spills hilft es nicht.

SQL Server hat hier in mehreren Schritten nachgebessert:

Im Lab legten 64 Sitzungen gleichzeitig in einer Schleife kleine temporäre Tabellen an, füllten, lasen und löschten sie, mit 1, 4 und 8 gleich großen Datendateien. Durchsatz in Batches pro Sekunde:

Drei Befunde:

• Solange die Metadaten der Engpass sind, ändert die Anzahl der Dateien nichts, auf keiner Version. Fast alle Wartenden standen vor Seiten der Systemtabellen, in denen SQL Server temporäre Objekte verzeichnet, kaum vor den Verwaltungsseiten.
• Mit speicheroptimierten Metadaten kommt der klassische Engpass zum Vorschein. Auf SQL Server 2019 stieg der Durchsatz deutlich, und nun wurde auf GAM-Seiten gewartet. Jetzt lieferten 8 Dateien 72 % mehr als eine.
• SQL Server 2022 und 2025 haben auch diesen Engpass entschärft. Mit speicheroptimierten Metadaten lief es mit einer Datei so schnell wie mit acht.

Welchen Engpass Sie haben, zeigt der Wartetyp PAGELATCH_* zusammen mit der Seite in resource_description: Liegt sie auf einer PFS-, GAM- oder SGAM-Seite, helfen mehr Dateien. Liegt sie auf Seiten von Systemtabellen, helfen speicheroptimierte Metadaten (ab SQL Server 2019) oder weniger Anlegen und Löschen temporärer Objekte. Microsoft beschreibt auf der TempDB-Seite eine Abfrage, die diese Metadaten-Contention erkennt (siehe Quellen).

Was daraus folgt:

• Beginnen Sie mit so vielen gleich großen Datendateien wie logischen Prozessoren, höchstens 8. Auf Instanzen, die mit älteren Versionen eingerichtet und später aktualisiert wurden, steht oft noch eine einzige Datei.
• Bleibt Warten auf Verwaltungsseiten in TempDB, erhöhen Sie die Anzahl schrittweise um 4. Mehr Dateien ohne nachgewiesene Contention bringen nichts.
• Alle Dateien gleich groß und mit gleichem Wachstum. Wächst eine Datei allein, bekommt sie den größten Teil der neuen Zuweisungen, und die Verteilung ist dahin.

Wohin mit Daten und Protokoll?

TempDB gehört auf schnellen Storage und getrennt von den Datendateien und Protokollen der Benutzerdatenbanken. Spills, große temporäre Tabellen und der Version Store erzeugen Last, die sonst mit den Lese- und Schreibzugriffen der Anwendung konkurriert. In virtuellen Maschinen in der Cloud bietet sich dafür oft ein lokaler, flüchtiger Datenträger an: TempDB wird bei jedem Start ohnehin neu angelegt.

Beim TempDB-Protokoll lohnt ein genauer Blick. In einer Benutzerdatenbank wartet jeder Commit, bis seine Protokolleinträge auf dem Datenträger stehen; nur so lässt sich die Änderung nach einem Absturz wiederherstellen. TempDB wird nach einem Neustart aber nie wiederhergestellt, sondern neu angelegt. Microsoft schreibt dazu auf der TempDB-Seite, dass verzögerte Dauerhaftigkeit (Delayed Durability) in TempDB immer aktiv ist, auch wenn die Datenbankoption auf DISABLED steht; eine Versionsangabe nennt die Seite nicht. Ein Commit mit verzögerter Dauerhaftigkeit wartet nicht auf das Schreiben des Protokolls. Ob sich das auf allen Versionen so zeigt, haben wir gemessen: Die Empfehlung von Microsoft lautet entsprechend, TempDB auf schnellen Storage zu legen und bei Engpässen von den Benutzerdatenbanken zu trennen; eine Trennung von TempDB-Daten und -Protokoll schreibt sie nicht vor.

Im Lab schrieben 16 Sitzungen kleine Transaktionen mit je einer Zeile, einmal in eine #temp-Tabelle, einmal in eine Tabelle einer Benutzerdatenbank auf demselben Storage:

Obwohl sys.databases für TempDB bei der Option delayed_durability den Wert DISABLED meldet, warteten die Commits in TempDB auf allen vier Versionen praktisch nie auf das Protokoll und liefen 10- bis 15-mal so schnell.

In einem zweiten Versuch mit protokollintensiver Last (1.000 Zeilen je Transaktion) lag das TempDB-Protokoll einmal auf einer eigenen SSD, einmal auf derselben SSD wie die TempDB-Daten: 980 gegenüber 996 Transaktionen pro Sekunde. Die einzelnen Schreibvorgänge waren auf der eigenen SSD etwas schneller (0,57 statt 0,88 ms), am Durchsatz änderte das nichts.

Was daraus folgt: Ein eigener Datenträger für das TempDB-Protokoll ist keine Grundregel, sondern eine Maßnahme für Instanzen, auf denen sehr viel in TempDB geändert wird, etwa beim Befüllen großer temporärer Tabellen oder bei langen Rollbacks. Wichtiger ist, dass TempDB insgesamt nicht mit den Benutzerdatenbanken um denselben Storage konkurriert und dass auch das Protokoll von Anfang an ausreichend groß angelegt ist.

Wie groß muss TempDB sein?

Die verbreitetste Faustregel lautet: TempDB so groß wie die größte Tabelle. Als allgemeine Regel stimmt das nicht. TempDB füllt sich durch gleichzeitige Arbeit: Sortierungen und Hash Joins, die auslagern, temporäre Tabellen vieler Sitzungen, den Version Store und offene Transaktionen. Mit der Größe einer einzelnen Tabelle hat das zunächst nichts zu tun.

Im Lab sortierte eine Abfrage eine 196 MB große Tabelle mit knapper Speicherzusage, sodass die Sortierung in TempDB auslagerte:

Die größte Tabelle dieser Datenbank war knapp 2 GB groß. Zwanzig gleichzeitige Ausführungen einer Abfrage über eine kleine Tabelle brauchten doppelt so viel TempDB. Die Speicherzusage haben wir als Labinstrument begrenzt; im Betrieb entsteht dasselbe Bild durch Schätzfehler oder knappen Arbeitsspeicher, typischerweise in Lastspitzen.

Einen wahren Kern hat die Regel trotzdem. Einige Wartungsaufgaben brauchen TempDB tatsächlich in der Größenordnung eines Objekts:

Das Anlegen eines Index mit SORT_IN_TEMPDB brauchte tatsächlich ungefähr die Größe des Index. Ohne die Option sortiert SQL Server in der Zieldatenbank, TempDB bleibt leer. Der REBUILD des vorhandenen Index belegte keine TempDB, vermutlich weil er die Zeilen bereits sortiert aus dem alten Index liest. Und mit reichlich Arbeitsspeicher sortierten beide Vorgänge vollständig im Speicher; Microsoft beschreibt, dass SORT_IN_TEMPDB dann ignoriert wird. Die Schätzung von CHECKDB lag um ein Vielfaches über dem tatsächlichen Bedarf.

Das Objekt gibt also für einzelne Wartungsschritte eine Obergrenze vor, kein Maß für TempDB insgesamt.

Für die Größe von TempDB heißt das:

1. Messen statt schätzen. Beobachten Sie die Belegung über einen Zeitraum, der alle typischen Lasten enthält: Tagesgeschäft, Monatsabschluss, Wartungsfenster mit Indexpflege und CHECKDB. Das Lagebild TempDB zeigt, welcher Bereich wie viel belegt; regelmäßig ausgeführt und gespeichert, ergibt es den Verlauf.
2. Spitze plus bewusst gewählter Puffer. Legen Sie die Datendateien gleich groß und vorab in der Größe an, die die gemessene Spitze mit Reserve abdeckt.
3. Automatisches Wachstum als Notbremse. Ein festes Wachstum in MB, für alle Dateien gleich, verhindert den Stillstand bei einer unerwarteten Spitze. Es ersetzt nicht die geplante Größe.
4. Die geplante Größe ist die konfigurierte Größe. Nach einem Neustart legt SQL Server TempDB in der konfigurierten Größe neu an. Im Lab waren die Datendateien unter Last von 256 auf 768 MB gewachsen; nach dem Neustart waren sie wieder 256 MB groß. Was durch automatisches Wachstum entstanden ist, muss nach jedem Neustart erneut wachsen, mitten in der Last.
5. Wartung einplanen. Für Indexpflege mit SORT_IN_TEMPDB und für DBCC CHECKDB den Bedarf vorher schätzen, statt TempDB pauschal am größten Objekt auszurichten.

Checkliste

Quellen

• Microsoft: Die TempDB-Datenbank: Konfiguration, Größe und Leistungsverbesserungen je Version
• Microsoft: DBCC CHECKDB mit ESTIMATEONLY
• Microsoft: SORT_IN_TEMPDB beim Indexaufbau

Weitere TempDB-Themen