TempDB:

Die fleißigste Datenbank — und die am meisten vernachlässigte

Das Lagerzimmer des SQL Servers

TempDB ist die einzige Datenbank in SQL Server, die sich alle Sessions, alle Abfragen und alle internen Prozesse gleichzeitig teilen. Nicht eine pro Benutzer, nicht eine pro Datenbank — eine für alle. Temporäre Tabellen, Sortierpuffer, Hash-Zwischenergebnisse, Row-Version-Daten für Snapshot Isolation, Online-Index-Operationen, XML-Variablen: alles landet hier.

TempDB ist wie das Lagerzimmer im Büro — jeder wirft seinen Kram rein, aber aufräumen tut keiner. Bei einem Neustart von SQL Server wird TempDB vollständig neu erstellt: Inhalt weg, Dateigröße auf die konfigurierte Initialgröße zurückgesetzt. Das klingt erst mal nach sauberem Neuanfang. Ist es auch. Aber, wenn die Initialgröße zu klein konfiguriert ist, fängt TempDB sofort an zu wachsen — mit allen damit verbundenen Autogrowth-Ereignissen, die wir in Kapitel 10 ausführlich besprochen haben.

TempDB ist in fast jedem Performance-Problem irgendwie beteiligt. Sort Spills aus Kapitel 12 landen hier. Der Row-Version-Store für RCSI, den wir in Kapitel 29 behandeln, lebt hier. Und, wenn bei Sparfuchs & Partner (Kapitel 33) TempDB, Datenbankdaten, Transaktionslog, OS und Backup-Dateien auf derselben Festplatte liegen, dann ist TempDB der erste Zeuge, wenn das ganze System einbricht. Spoiler: Es bricht ein.

 

Definition: TempDB

System-Datenbank die bei jedem SQL Server Start vollständig neu erstellt wird. Enthält temporäre Tabellen (#temp, ##global), Tabellenvariablen (@table), Work Tables für Sort- und Hash-Spills, den Row-Version-Store für RCSI und Snapshot Isolation sowie interne Zwischenergebnisse des Abfrageprocessors. Alle Sessions teilen sich dieselbe TempDB-Instanz — sie ist damit die am stärksten geteilte Ressource im gesamten SQL Server.

 

Was landet alles in TempDB?

Die Antwort ist: mehr als die meisten DBAs denken. TempDB ist nicht nur der Abstellplatz für "#temp Tabellen". Schauen wir uns systematisch an, was dort landet — und was das für die Dimensionierung bedeutet.

 

Objekt-Typ	Erstellt durch	Typische Größe	Wann relevant
#temp Tabellen	Explizit im T-SQL: CREATE TABLE #t	KB bis GB	Fast überall
##global temp	CREATE TABLE ##t (sessionsübergreifend)	KB bis GB	Selten, aber riskant
Tabellenvariablen (@table)	DECLARE @t TABLE(…)	Klein (keine Statistiken!)	Entwickler-Code
Work Tables Sort	Intern bei Sort Spill (→ Kap 12)	MB bis GB	Bei Speichermangel
Work Tables Hash	Intern bei Hash Spill (→ Kap 12)	MB bis GB	Bei Speichermangel
Spool Tables	Intern bei komplexen Ausführungsplänen	Variabel	Eager/Lazy Spool
Row-Version Store	RCSI / Snapshot Isolation (→ Kap 29)	Kann sehr groß werden	Sobald RCSI aktiv
Online Index Ops	ALTER INDEX … REBUILD ONLINE	Abhängig von Index-Größe	Wartungsfenster
Service Broker	Interne Nachrichtenverarbeitung	Gering	Service Broker Instanzen
MARS (Multiple Active Result Sets)	Gleichzeitige Result Sets je Verbindung	Gering pro Session	MARS-Verbindungen
XML-Variablen	DECLARE @x XML; SET @x = …	Kann erheblich wachsen	XML-intensive Queries
Cursor-Zwischenergebnisse	Deklarierte Cursor mit Keyset/Static	Kopie des Result Sets	Cursor-Verwendung

Tabelle 13.1: Was in TempDB landet — und warum das die Dimensionierung kompliziert macht

 

Der wichtige Punkt: Nicht alle diese Nutzungsarten sind für den DBA direkt sichtbar. Sort Spills passieren intern, der Row-Version Store wächst ohne explizite Aktion des Entwicklers, und Online-Index-Rebuilds im Wartungsfenster können TempDB kurzzeitig auf ein Vielfaches der normalen Betriebsgröße treiben. Wer TempDB nicht überwacht, erlebt Überraschungen zur Unzeit.

 

Hinweis: Querverweise: TempDB ist überall
Sort Spills und Hash Spills als Hauptverursacher von TempDB-IO: Kapitel 12 (Memory Grants und Spills). Row-Version Store als TempDB-Konsument bei RCSI und Snapshot Isolation: Kapitel 29 (Row Versioning). Autogrowth-Mechanismus und Instant File Initialization: Kapitel 10 (IO-Performance). TempDB als Schauplatz der Sparfuchs-Katastrophe: Kapitel 33 (Fallstudie Sparfuchs).

 

#temp vs. @table — der ewige Religionskrieg

Kaum ein Thema wird in der SQL Server Community heißer diskutiert als die Frage: Temporäre Tabelle oder Tabellenvariable? Die Antwort ist nicht "kommt drauf an" — sie ist konkret und messbar.

Was #temp-Tabellen richtig machen

Temporäre Tabellen (#temp) verhalten sich wie echte Tabellen. Sie haben Statistiken, die SQL Server automatisch pflegt. Der Abfrageoptimierer kann daraus korrekte Kardinalitätsschätzungen ableiten — entscheidend für den richtigen Join-Algorithmus und den richtigen Ausführungsplan. Du kannst Indizes auf #temp-Tabellen erstellen, Constraints hinzufügen und mit TRUNCATE den Inhalt schnell leeren. Für Datenmengen ab ein paar hundert Zeilen ist #temp fast immer die bessere Wahl.

Was @table-Variablen falsch machen

Tabellenvariablen (@table) haben keine Statistiken. Der Abfrageoptimierer geht davon aus, dass eine Tabellenvariable genau eine Zeile enthält — egal wie viele tatsächlich drin sind. Bei 10 Zeilen ist das egal. Bei 100.000 Zeilen entsteht ein katastrophal schlechter Plan: Nested Loop statt Hash Join, keine Parallelität, wiederholte Tabellen-Scans. Der Optimizer ist nicht dumm — er hat schlicht keine Information.

Hinzu kommt: Tabellenvariablen erzwingen Row-Level Locking, unterstützen kein TRUNCATE (nur DELETE), und bei Änderungen rollback-t SQL Server die Variable mit — was in bestimmten Szenarien unerwünschtes Verhalten erzeugt. In Kapitel 26 gehen wir auf die Implikationen für Stored Procedures genauer ein.

 

Warnung: @table bei großen Datenmengen — ein teures Missverständnis

Der Mythos "Tabellenvariablen sind schneller als #temp-Tabellen" stammt aus einer Zeit, in der die Schreibkosten für Transaktionslog-Einträge ein Unterschied machten. Heute ist dieser Unterschied vernachlässigbar. Der durch fehlende Statistiken erzeugte Fehler im Ausführungsplan kostet bei großen Datenmengen um Größenordnungen mehr als jeder potenzielle Schreibkostenvorteil. Faustregel: @table für kleine, kurzlebige Mengen (< 100 Zeilen) und, wenn der Code innerhalb einer Transaktion rollback-sicher sein muss. Für alles andere: #temp.

 

 

Merkmal	#temp Tabelle	@table Variable
Statistiken	Ja — Optimizer kann korrekte Pläne erstellen	Nein — Optimizer nimmt immer 1 Zeile an
Indizes	Ja — inklusive Covering Indexes	Eingeschränkt (nur PK/Unique bei Deklaration)
TRUNCATE	Ja — schnell	Nein — nur DELETE (langsamer)
Sichtbarkeit	Nur aktuelle Session	Nur aktuelle Session (## für global)
Transaktionslog	Minimal (TempDB)	Minimal (TempDB)
Caching	Ja — in Stored Procedures (Deferred Drop)	Nein
Ideal für	Mittlere bis große Datenmengen (>100 Zeilen)	Kleine, kurzlebige Mengen (<100 Zeilen)
Risiko	Vergessener DROP bei dynamischem Code	Falsche Pläne bei großen Datenmengen

Tabelle 13.2: #temp vs. @table — die ehrliche Gegenüberstellung

 

TempDB-Caching: Warum #temp in Stored Procedures schneller ist als du denkst

SQL Server hat eine clevere Optimierung für temporäre Tabellen in Stored Procedures: Deferred Drop, auch bekannt als TempDB-Caching. Wenn eine Stored Procedure eine #temp-Tabelle erstellt und am Ende der Prozedur wieder dropped (explizit oder implizit durch Session-Ende), behält SQL Server die Tabellenstruktur im Hintergrund — nur die Daten werden geleert. Beim nächsten Aufruf derselben Prozedur bekommt die neue #temp-Tabelle die gecachte Struktur: kein erneuter Allokations-Overhead, keine neue Seite in PFS registrieren, keine neue Metadaten-Anlage.

Das klingt nach einem kleinen Detail — ist es aber nicht. Bei Stored Procedures die pro Sekunde hunderte Male aufgerufen werden und jedes Mal eine #temp-Tabelle anlegen, kann TempDB-Caching den Allokations-Overhead erheblich reduzieren. Der Mechanismus funktioniert automatisch, solange die Prozedur die Tabelle nicht mittels explizitem DROP TABLE entfernt und sofort neu anlegt (das leert den Cache). Auch DDL-Änderungen an der Tabelle (ALTER TABLE) während der Prozedur deaktivieren das Caching.

-- TempDB-Cache-Treffer überprüfen

-- Gecachte TempDB-Objekte sind in sys.objects unter tempdb sichtbar

USE tempdb;

 

SELECT

    name AS tabellenname,

    object_id,

    -- Negative Object-IDs signalisieren gecachte (Deferred Drop) Objekte

    CASE WHEN object_id < 0 THEN 'Gecacht (Deferred Drop)' ELSE 'Aktiv' END AS status,

    create_date,

    modify_date

FROM sys.objects

-- Nur temporäre Tabellen (Name beginnt mit #)

WHERE name LIKE '#%'

ORDER BY create_date DESC;

 

-- Hinweis: Gecachte Objekte haben negative object_id — das ist normal und gewollt.

-- Sie belegen Speicher aber verursachen keinen Allokations-Overhead beim nächsten Aufruf.

Wann greift das Caching nicht? Drei häufige Situationen: Erstens, wenn DDL nach dem initialen CREATE TABLE ausgeführt wird (ALTER TABLE, CREATE INDEX nach der Deklaration). Zweitens, wenn Named Constraints in der Tabellendefinition verwendet werden — SQL Server kann den Cache nicht eindeutig zuordnen. Drittens bei globalen ##temp-Tabellen, die sessions-übergreifend sichtbar sein müssen und daher nicht gecacht werden können.

PFS-Contention: Wenn die Verwaltungsseiten zum Engpass werden

Vor SQL Server 2016 war das häufigste TempDB-Performance-Problem nicht zu wenig IO, nicht zu wenig RAM — es war ein internes Serialisierungsproblem: PAGELATCH_UP auf PFS-Seiten. Das Problem ist so alt wie SQL Server unter Last, und das Verständnis warum es passiert, ist entscheidend für die richtige Konfiguration.

PFS, GAM und SGAM — die Buchhalter von SQL Server

SQL Server verwaltet den freien Speicher innerhalb jeder Datenbankdatei über drei Typen von Systemseiten. Sie sind keine Daten — sie sind Metadaten über Daten. Und genau das macht sie zum potenziellen Engpass bei vielen gleichzeitigen Allokationen:

PFS (Page Free Space): Enthält den Füllstand jeder einzelnen Datenseite, kodiert in 8 Stufen (0%, 1–50%, 51–80%, 81–95%, 96–100%). Jede PFS-Seite verwaltet 8.088 Datenseiten — das entspricht etwa 64 MB. Bei jeder Allokation und Deallokation muss die zugehörige PFS-Seite aktualisiert werden.

GAM (Global Allocation Map): Protokolliert welche Extents (8 × 8 KB = 64 KB) bereits vergeben sind. Jede GAM-Seite deckt 64.000 Extents ab, also etwa 4 GB. GAM-Seiten werden bei Extent-Allokationen aktualisiert.

SGAM (Shared Global Allocation Map): Verfolgt Mixed Extents — Extents die noch nicht vollständig von einem einzigen Objekt belegt sind und für neue kleine Objekte verwendet werden können. SGAM ist besonders bei vielen kleinen Temp-Tabellen-Allokationen betroffen.

Das kritische Detail: Jede dieser Seiten kann gleichzeitig nur von einem Thread modifiziert werden. Das schützt die Konsistenz der Metadaten — ist aber ein klassisches Serialisierungsproblem. Bei 32 Sessions die gleichzeitig in TempDB allokieren, wollen alle 32 dieselbe PFS-Seite aktualisieren. Thread 1 hält den Latch, Threads 2–32 warten auf PAGELATCH_UP. Die Wait Statistics (Kapitel 9) zeigen dann PAGELATCH_UP mit resource_description "2:1:1" oder "2:1:2" — Datenbankdatei 2, Seite 1 ist die erste PFS-Seite in TempDB.

 

Abb. 13.1: PFS-Seiten-Contention: Viele Threads konkurrieren auf dieselbe Verwaltungsseite

Trace Flags 1118 und 1117 — und warum du sie heute nicht mehr brauchst

Früher war die Standardlösung für TempDB-Contention: Trace Flag 1118 aktivieren, damit SQL Server für neue Objekte immer volle Extents allokiert statt Mixed Extents (reduziert SGAM-Contention), plus Trace Flag 1117 um sicherzustellen, dass alle Dateien in einer Dateigruppe gleichzeitig wachsen. Ab SQL Server 2016 ist dieses Verhalten für TempDB der Standard — ohne Trace Flags, ohne manuelle Konfiguration. Wenn du noch ältere SQL Server Versionen (2014 oder früher) betreibst, sind diese Trace Flags nach wie vor relevant und sollten über den Startup Parameters des SQL Server Dienstes gesetzt werden.

 

Hinweis: Trace Flags auf modernen Instanzen
Auf SQL Server 2016 und neuer haben Trace Flag 1118 und 1117 keinen Effekt mehr auf TempDB — das Verhalten ist Standard. Das Setzen dieser Flags ist nicht schädlich aber überflüssig. Die eigentliche Lösung für verbleibende PFS-Contention auf modernen Instanzen ist die korrekte Anzahl und Größe der TempDB-Datendateien, die wir im nächsten Abschnitt besprechen.

 

Mehrere gleichgroße Datendateien: Die elegante Lösung

Die elegante Lösung für PFS-Contention ist: Mehr Datendateien. Jede Datendatei hat ihre eigenen PFS-, GAM- und SGAM-Seiten. Vier Datendateien bedeuten vier separate Sätze von Allokationsseiten. SQL Server verteilt neue Allokationen über den Proportional Fill-Algorithmus: Jede Datei bekommt Allokationen proportional zu ihrem aktuell verfügbaren freien Speicher.

Haben alle Dateien exakt gleich viel freien Speicher — was der Fall ist, wenn sie gleich groß und gleich befüllt sind — wird gleichmäßig über alle Dateien verteilt. Bei vier gleich großen Dateien landet ungefähr jede vierte Allokation in derselben PFS-Seite statt jede erste. Der Contention-Druck sinkt auf etwa ein Viertel.

 

Warnung: Gleiche Größe ist Pflicht — kein "ungefähr"

Wenn du vier TempDB-Dateien hast, aber eine davon ist 8 GB groß und drei sind je 4 GB, bekommt die große Datei proportional doppelt so viele Allokationen wie jede der kleinen. Du hast dann faktisch eine Datei die den Löwenanteil trägt — und ihre PFS-Seiten werden wieder zum Engpass. Alle Dateien müssen exakt dieselbe SIZE und exakt denselben FILEGROWTH haben. Nicht "ungefähr gleich". Exakt gleich.

 

Die Faustregel: min(logische CPU-Kerne, 8)

Microsoft empfiehlt seit Jahren: Anzahl TempDB-Datendateien = min(Anzahl logischer CPU-Kerne, 8). Bei einem 4-Kern-Server: 4 Dateien. Bei einem 8-Kern-Server: 8 Dateien. Bei einem 32-Kern-Server: ebenfalls 8 Dateien. Mehr als 8 Datendateien bringt in den meisten Fällen keinen messbaren Benefit mehr, weil der Proportional-Fill-Algorithmus bei acht Dateien bereits sehr fein verteilt. Ausnahme: Systeme mit exzessiver Temp-Objekt-Erstellungsrate und messbarer PAGELATCH_UP-Contention trotz 8 Dateien — dann hilft manchmal ein weiteres Erhöhen.

Ab SQL Server 2016 setzt das Setup-Programm automatisch die richtige Anzahl — wenn du es lässt. Ältere Instanzen haben häufig noch die Default-Konfiguration: genau eine Datendatei. Das war vor 2005 ausreichend. Heute ist es ein Performance-Risiko auf jedem Multi-Core-System.

 

Logische CPU-Kerne	Empfohlene TempDB-Datendateien	Bemerkung
1–4	= Anzahl CPUs	Kleinstserver oder Dev-VMs
5–8	= Anzahl CPUs	Standard für mittelgroße Systeme
9–16	8	Obergrenze in den meisten Fällen ausreichend
17–64	8	Selten mehr nötig, erst bei gemessenem Contention erhöhen
>64	8 bis maximal 16	Nur bei bewiesener PAGELATCH_UP-Contention trotz 8 Dateien

Tabelle 13.3: Anzahl TempDB-Datendateien nach CPU-Ausstattung

 

-- TempDB korrekt konfigurieren: 8 gleichgroße Datendateien auf dediziertem Volume

-- Vorausgesetzt: Ziel-Größe pro Datei = 4 GB (je nach Workload anpassen)

 

-- Schritt 1: Bestehende primäre Datei (tempdev/tempdb.mdf) auf Zielgröße bringen

ALTER DATABASE tempdb

MODIFY FILE (

    NAME = N'tempdev',

    -- Initialgröße auf den erwarteten Spitzenbedarf setzen — nicht kleiner!

    SIZE = 4096MB,

    -- Autogrowth als Sicherheitsnetz: groß genug um selten zu greifen

    FILEGROWTH = 512MB

);

 

-- Schritt 2: Weitere gleichgroße Datendateien hinzufügen

-- ALLE auf dasselbe Volume, EXAKT gleiche SIZE und FILEGROWTH!

ALTER DATABASE tempdb ADD FILE (

    NAME = N'tempdev2',

    FILENAME = N'T:TempDBempdev2.ndf',   -- Dediziertes TempDB-Volume

    SIZE = 4096MB,                             -- Exakt wie tempdev!

    FILEGROWTH = 512MB                         -- Exakt wie tempdev!

);

ALTER DATABASE tempdb ADD FILE (

    NAME = N'tempdev3',

    FILENAME = N'T:TempDBempdev3.ndf',

    SIZE = 4096MB,

    FILEGROWTH = 512MB

);

-- ... (tempdev4 bis tempdev8 analog) ...

 

-- Schritt 3: Log-Datei separat anpassen

-- TempDB benötigt NUR EINE Log-Datei — Log-Schreibzugriffe sind seriell

ALTER DATABASE tempdb

MODIFY FILE (

    NAME = N'templog',

    SIZE = 1024MB,

    FILEGROWTH = 256MB

);

 

-- WICHTIG: Änderungen an TempDB-Dateinamen/-Pfaden gelten erst nach Neustart!

-- Die Größenänderung (MODIFY FILE SIZE) greift sofort ohne Neustart.

-- Nach der Konfiguration: SQL Server Dienst neu starten, um die neuen Dateien zu aktivieren.

 

Abb. 13.2: Proportional Fill mit gleich großen Dateien: Allokationen verteilen sich optimal auf alle PFS-Seiten

SQL Server 2019: In-Memory TempDB Metadata

SQL Server 2019 bringt eine elegante Ergänzung zur Dateistrategie: Die internen Systemtabellen die TempDB-Objekte verwalten — sys.sysobjects, sys.syscolumns, sys.sysschobjs und verwandte Strukturen innerhalb von TempDB — können als In-Memory-optimierte Tabellen angelegt werden. Das eliminiert Latch-Contention auf diesen Verwaltungsstrukturen vollständig, weil In-Memory-Tabellen ohne traditionelles Locking auskommen.

In-Memory TempDB Metadata ist kein Ersatz für korrekte Dateianzahl — es ist eine Ergänzung. PFS-Contention durch viele gleichzeitige Allokationen wird durch mehrere Datendateien gelöst. Metadata Contention durch viele gleichzeitige Erstellungen und Drops von Temp-Objekten (z.B. Stored Procedures die pro Aufruf eine #temp-Tabelle anlegen und sofort droppen) wird durch In-Memory Metadata reduziert. Beide Maßnahmen zusammen ergeben die optimale Lösung.

-- In-Memory TempDB Metadata: Status prüfen und aktivieren (SQL Server 2019+)

 

-- Ist das Feature bereits aktiv?

SELECT SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS metadata_optimized;

-- 0 = deaktiviert, 1 = aktiv

 

-- Aktivieren — erfordert Neustart des SQL Server Diensts

ALTER SERVER CONFIGURATION

SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

 

-- Voraussetzungen prüfen: In-Memory OLTP muss verfügbar sein

-- Nicht verfügbar auf Express Edition!

SELECT

    SERVERPROPERTY('edition') AS edition,

    -- Weitere Voraussetzung: Enterprise oder Developer Edition für vollen Nutzen

    CASE

        WHEN SERVERPROPERTY('EngineEdition') IN (3, 8) THEN 'Vollständig unterstützt'

        ELSE 'Eingeschränkt oder nicht verfügbar'

    END AS support_status;

TempDB auf dediziertem Volume: Pflicht, keine Option

TempDB ist IO-intensiv. Sort Spills aus Kapitel 12, der Row-Version Store aus Kapitel 29, temporäre Tabellen, Online-Index-Operationen — all das erzeugt IO auf dem Volume auf dem TempDB liegt. Wenn TempDB mit den Datenbankdaten, dem Transaktionslog oder gar dem Betriebssystem auf demselben Volume liegt, konkurrieren alle Schreibvorgänge um dieselbe IO-Kapazität. Das Ergebnis: höhere Latenzen für alle, unvorhersehbares Performance-Verhalten je nach Auslastungsmix.

 

Praxisbeispiel: Sparfuchs & Partner: Die Einfach-Spindel-Katastrophe

Bei Sparfuchs & Partner (Kapitel 33) teilen sich TempDB, OS-Laufwerk, alle Datenbankdateien, das Transaktionslog und die Backup-Dateien eine einzige Spindel. Gemessene IO-Latenz p95: 312 ms Write. Auf einer modernen NVMe-SSD: 0,03 ms. Das ist Faktor 10.000. Nicht Faktor 10, nicht Faktor 100 — Faktor 10.000. Jeder Sort Spill auf dieser Konfiguration kostet Sekunden statt Millisekunden. Jeder Autogrowth-Event blockiert alle anderen IO-Operationen auf der Spindel. Das System läuft — irgendwie. Aber es läuft am Limit jeder einzelnen Komponente gleichzeitig.

 

Die Anforderungen an das TempDB-Volume sind spezifisch: NVMe-SSD ist die erste Wahl, weil TempDB write-intensiv ist und von niedrigen Schreib-Latenzen überproportional profitiert. Für kleinere Systeme tut es auch eine schnelle SATA-SSD — Hauptsache kein Teilen mit anderen Datenbankdateien. Auf Systemen mit mehreren NVMe-Drives empfiehlt sich das schnellste für TempDB zu reservieren: Write-Workload ist hier vorhersehbarer als auf den Datenbankdaten-Volumes.

Auf virtualisierten Systemen (VMware, Hyper-V) gelten dieselben Prinzipien: Ein dedizierter virtueller Datenträger auf einem NVMe-Pool für TempDB, getrennt vom virtuellen Datenträger für Datenbankdaten und Log. Kapitel 3 zur Virtualisierung erklärt die Implikationen von VMDK-Platzierung und Storage-Tiering für SQL Server IO-Workloads.

Version Store: Der stille TempDB-Fresser

Wenn Read Committed Snapshot Isolation (RCSI) oder Snapshot Isolation aktiv ist, schreibt SQL Server für jede Datenänderung eine Kopie der alten Zeilenversion in den Version Store in TempDB. Gleichzeitig laufende Transaktionen können damit konsistente Lesevorgänge ohne Sperren durchführen — sehr elegant, aber TempDB-Kosten inklusive.

Wie groß kann der Version Store werden? So groß wie die kumulative Datenmenge aller Änderungen die von noch laufenden Transaktionen benötigt werden. Eine Reporting-Transaktion die um 08:00 Uhr startet und bis 10:00 Uhr läuft, während in dieser Zeit 5 GB Daten geändert werden, hält den Version Store für diese 5 GB offen. SQL Server kann alte Versionen erst dann bereinigen, wenn keine aktive Transaktion sie mehr benötigt. Wer lange Reporting-Queries unter RCSI laufen lässt und sich wundert warum TempDB immer größer wird: Das ist die Antwort. Kapitel 29 behandelt den Version Store und seine Auswirkungen ausführlich.

-- Version Store: Größe und Wachstum überwachen

SELECT

    -- Gesamtgröße des Version Stores in KB und MB

    reserved_page_count * 8 / 1024 AS version_store_mb,

    -- Wie schnell wird er aktuell befüllt?

    version_store_start_time,

    -- Älteste Transaktion die den Store offen hält

    oldest_active_transaction_id,

    -- Wie lange läuft die älteste Transaktion bereits?

    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS alter_minuten

FROM sys.dm_tran_version_store_space_usage;

 

-- Welche Datenbank verursacht den meisten Version-Store-Verbrauch?

SELECT

    DB_NAME(database_id) AS datenbank,

    reserved_page_count * 8 / 1024 AS verbrauch_mb

FROM sys.dm_tran_version_store_space_usage

ORDER BY reserved_page_count DESC;

 

-- Älteste Transaktion finden die den Version Store blockiert

-- Diese Transaktion verhindert Cleanup und lässt den Store wachsen!

SELECT TOP 5

    at.transaction_id,

    at.transaction_begin_time,

    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS alter_minuten,

    es.login_name,

    es.program_name,

    es.host_name

FROM sys.dm_tran_active_transactions at

JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id

JOIN sys.dm_exec_sessions es ON st.session_id = es.session_id

-- Nur Transaktionen mit Snapshot-Isolation-Relevanz

WHERE at.transaction_type IN (1, 2)  -- 1=Read-Write, 2=Read-Only

ORDER BY at.transaction_begin_time ASC;  -- Älteste zuerst

 

Tipp: Version Store explodiert — was tun?

Wenn der Version Store unkontrolliert wächst, ist fast immer eine lange laufende Transaktion der Übeltäter. Die Query oben findet sie. Mögliche Maßnahmen: Reporting-Queries mit kürzeren Snapshots strukturieren (häufiger committen), langläufige Analyse-Jobs außerhalb der Hauptlastzeiten planen, oder — wenn der Wachstum akut kritisch wird — die blockierende Session mit KILL beenden. Das ist keine elegante Lösung, aber manchmal notwendig um TempDB-Vollaufen zu verhindern. Kapitel 29 diskutiert strukturelle Lösungsansätze.

 

TempDB-Nutzung überwachen — bevor es brennt

TempDB-Probleme kündigen sich an. Wer regelmäßig überwacht, sieht Autogrowth-Häufungen, steigenden Version-Store-Verbrauch und wachsende PAGELATCH-Waits lange, bevor Nutzer etwas merken. Die folgenden DMVs sind die wichtigsten Werkzeuge im TempDB-Monitoring-Arsenal.

sys.dm_db_session_space_usage — Wer braucht wie viel?

-- TempDB-Verbrauch auf Session-Ebene: Top-Verbraucher identifizieren

SELECT TOP 20

    s.session_id,

    s.login_name,

    -- Programm und Host für die Zuordnung zum Verursacher

    LEFT(s.program_name, 40) AS programm,

    LEFT(s.host_name, 30) AS host,

    -- Explizite temporäre Objekte: #temp Tabellen, Tabellenvariablen

    tsu.user_objects_alloc_page_count * 8 / 1024.0 AS user_obj_allok_mb,

    tsu.user_objects_dealloc_page_count * 8 / 1024.0 AS user_obj_frei_mb,

    -- Interne Objekte: Sort-Puffer, Hash-Puffer, Spool-Tabellen

    tsu.internal_objects_alloc_page_count * 8 / 1024.0 AS intern_allok_mb,

    tsu.internal_objects_dealloc_page_count * 8 / 1024.0 AS intern_frei_mb,

    -- Netto-Verbrauch: noch belegte Seiten (Allokiert minus Freigegeben)

    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count

     + tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)

     * 8 / 1024.0 AS netto_mb

FROM sys.dm_db_session_space_usage tsu

JOIN sys.dm_exec_sessions s ON tsu.session_id = s.session_id

-- Nur Sessions die tatsächlich TempDB nutzen

WHERE (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) > 0

ORDER BY netto_mb DESC;

sys.dm_db_task_space_usage — laufende Tasks

-- TempDB-Verbrauch auf Task-Ebene: Welche laufenden Operationen verursachen gerade Last?

-- Besonders nützlich während eines Performance-Problems

SELECT

    wt.session_id,

    wt.request_id,

    -- Interne TempDB-Nutzung des Tasks (Sort, Hash, Spool)

    ttu.internal_objects_alloc_page_count * 8 / 1024.0 AS intern_mb,

    ttu.user_objects_alloc_page_count * 8 / 1024.0 AS user_mb,

    -- Aktuell ausgeführte Query (erste 200 Zeichen reichen meist)

    LEFT(qt.text, 200) AS query_anfang

FROM sys.dm_db_task_space_usage ttu

JOIN sys.dm_os_waiting_tasks wt

    ON ttu.session_id = wt.session_id AND ttu.request_id = wt.request_id

CROSS APPLY sys.dm_exec_sql_text(wt.sql_handle) qt

WHERE (ttu.internal_objects_alloc_page_count + ttu.user_objects_alloc_page_count) > 0

ORDER BY (ttu.internal_objects_alloc_page_count + ttu.user_objects_alloc_page_count) DESC;

TempDB-IO-Latenzen überwachen

-- IO-Latenzen für TempDB-Dateien (database_id = 2)

-- Hohe Latenzen = entweder zu langsames Volume oder zu viele Sort/Hash Spills

SELECT

    mf.name AS dateiname,

    mf.physical_name AS pfad,

    -- Durchschnittliche Lese-Latenz in ms

    CASE WHEN vfs.num_of_reads = 0 THEN 0

         ELSE CAST(vfs.io_stall_read / vfs.num_of_reads AS DECIMAL(10,2))

    END AS avg_read_ms,

    -- Durchschnittliche Schreib-Latenz in ms

    CASE WHEN vfs.num_of_writes = 0 THEN 0

         ELSE CAST(vfs.io_stall_write / vfs.num_of_writes AS DECIMAL(10,2))

    END AS avg_write_ms,

    -- Absoluter Schreib-Stall in Sekunden seit letztem SQL Server Start

    vfs.io_stall_write / 1000 AS write_stall_sek_gesamt

FROM sys.dm_io_virtual_file_stats(2, NULL) vfs  -- 2 = TempDB

JOIN sys.master_files mf

    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

ORDER BY avg_write_ms DESC;

 

-- Richtwerte: < 5 ms Schreib-Latenz = gut. 5–20 ms = akzeptabel.

-- > 20 ms = das TempDB-Volume ist ein Engpass. > 100 ms = kritisch.

PAGELATCH-Contention auf TempDB identifizieren

-- Aktive PAGELATCH-Waits auf TempDB-Systemseiten erkennen

-- Diese Query direkt während einer Langsamkeit ausführen

SELECT

    wt.session_id,

    wt.wait_type,

    wt.wait_duration_ms,

    wt.blocking_session_id,

    wt.resource_description,

    -- Seiten-Nummer extrahieren und klassifizieren

    CASE

        -- PFS: Seiten 1, 8088, 16176, ... (alle 8.088 Seiten)

        WHEN CAST(SUBSTRING(wt.resource_description,

             CHARINDEX(':', wt.resource_description, 3) + 1, 20) AS BIGINT) % 8088 IN (0, 1)

        THEN 'PFS-Seite (Allokations-Contention!)'

        ELSE 'Normale Datenseite'

    END AS seitentyp

FROM sys.dm_os_waiting_tasks wt

WHERE wt.wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX', 'PAGELATCH_SH')

  -- Nur TempDB: database_id = 2

  AND wt.resource_description LIKE '2:%'

ORDER BY wt.wait_duration_ms DESC;

TempDB richtig dimensionieren: Messen statt Raten

Die häufigste Frage zur TempDB-Dimensionierung: "Wie groß soll sie sein?" Die ehrliche Antwort: So groß wie der Spitzenbedarf, plus 20–30% Puffer. Aber du musst den Spitzenbedarf erst messen — nicht schätzen, nicht raten. Messen.

Miss den TempDB-Verbrauch via sys.dm_db_session_space_usage über mehrere Tage, bevorzugt über einen vollständigen Geschäftszyklus (Monatsabschluss, Jahresabschluss, besondere Lastspitzen). Sammle den Spitzenwert. Addiere 30% Puffer. Das ist deine Zielgröße für jede Datendatei — bei 8 Dateien entsprechend geteilt.

Autogrowth für TempDB ist kein Feature — es ist ein Sicherheitsnetz das du selten brauchen solltest. Wenn TempDB regelmäßig wächst, ist die Initialgröße zu klein konfiguriert. Autogrowth unter Last kostet IO und Zeit, selbst mit Instant File Initialization (→ Kapitel 10), weil der Log-Anteil von TempDB nicht von IFI profitiert.

-- Gesamten TempDB-Verbrauch pro Datei überwachen

-- Ideal als Job alle 15 Minuten ausführen und Spitzenwerte protokollieren

SELECT

    mf.name AS dateiname,

    mf.physical_name AS pfad,

    mf.size * 8 / 1024 AS konfigurierte_mb,

    -- Tatsächlich belegter Speicher

    (mf.size - fsu.unallocated_extent_page_count) * 8 / 1024 AS belegt_mb,

    fsu.unallocated_extent_page_count * 8 / 1024 AS frei_mb,

    -- Füllgrad: Wenn > 80% → Initialgröße erhöhen!

    CAST(100.0 * (mf.size - fsu.unallocated_extent_page_count)

         / NULLIF(mf.size, 0) AS DECIMAL(5,1)) AS fuellgrad_pct

FROM sys.dm_db_file_space_usage fsu

JOIN sys.master_files mf

    ON fsu.file_id = mf.file_id AND mf.database_id = 2

WHERE mf.database_id = 2  -- 2 = TempDB

ORDER BY mf.file_id;

Diagnose-Kästen: TempDB schnell analysieren

 

Hinweis: Symptome — woran merkst du ein TempDB-Problem?
Allgemeine Verlangsamung des gesamten SQL Servers, nicht nur einer einzelnen Abfrage — das ist das Erkennungsmerkmal von TempDB-Contention: sie trifft alle gleichzeitig.
In den Wait Statistics (Kapitel 9): PAGELATCH_UP oder PAGELATCH_EX dominieren, und die resource_description verweist auf "2:1:1", "2:1:2" oder "2:1:3" (Datenbank 2 = TempDB, Seite 1–3 = erste PFS/GAM/SGAM-Seiten).
TempDB-Volume zeigt hohe IO-Last (sys.dm_io_virtual_file_stats für database_id = 2), während die Datenbankdaten-Volumes unauffällig sind.
sys.dm_db_file_space_usage zeigt Füllgrad > 80% — Autogrowth-Ereignisse drohen oder finden bereits statt (im SQL Error Log erkennbar als "Autogrowth … completed").
Nach Serverneustarts dauert es mehrere Minuten bis das System normal läuft — TempDB wächst auf Betriebsgröße und verursacht dabei hohe IO-Last.
Version Store wächst kontinuierlich (sys.dm_tran_version_store_space_usage) bei aktivem RCSI, ohne, dass der Verbrauch zwischen den Transaktionen zurückgeht.

 

 

Hinweis: So misst du das — TempDB-Schnelldiagnose
Die folgende Query gibt in einem Ergebnis alle kritischen TempDB-Kennzahlen:

 

-- TempDB-Schnelldiagnose: alles auf einen Blick

SELECT

    -- 1. Anzahl Datendateien (sollte = min(CPU-Kerne, 8) sein)

    (SELECT COUNT(*)

     FROM sys.master_files

     WHERE database_id = 2 AND type = 0) AS datendateien_anzahl,

 

    -- 2. Sind alle Datendateien exakt gleich groß? (1 = ja, 0 = Problem!)

    CASE WHEN (SELECT COUNT(DISTINCT size)

               FROM sys.master_files

               WHERE database_id = 2 AND type = 0) = 1

         THEN 1 ELSE 0 END AS alle_gleich_gross,

 

    -- 3. Gesamter TempDB-Verbrauch in MB

    (SELECT SUM((size - unallocated_extent_page_count) * 8) / 1024

     FROM sys.dm_db_file_space_usage) AS gesamt_belegt_mb,

 

    -- 4. Laufende PAGELATCH-Waits auf TempDB-Seiten

    (SELECT COUNT(*)

     FROM sys.dm_os_waiting_tasks

     WHERE wait_type IN ('PAGELATCH_UP', 'PAGELATCH_EX')

       AND resource_description LIKE '2:%') AS pagelatch_waits_tempdb,

 

    -- 5. Version Store Größe (nur relevant, wenn RCSI/Snapshot aktiv)

    (SELECT SUM(reserved_page_count) * 8 / 1024

     FROM sys.dm_tran_version_store_space_usage) AS version_store_mb,

 

    -- 6. In-Memory TempDB Metadata aktiv? (SQL 2019+)

    SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') AS memory_metadata_aktiv;

 

Warnung: Typische Fehlinterpretationen
Fehlinterpretation 1: "TempDB ist zu groß — ich mache SHRINK." Niemals. SHRINK fragmentiert massiv, das dabei entstehende IO-Chaos ist schlimmer als die ursprüngliche Größe. TempDB darf groß sein. Beim nächsten Neustart startet sie wieder auf die konfigurierte Initialgröße zurück.
Fehlinterpretation 2: "Ich habe 8 Datendateien, also kein Contention-Problem möglich." Falsch, wenn die Dateien unterschiedlich groß sind. Proportional Fill verteilt dann ungleichmäßig — eine Datei trägt den Löwenanteil, und deren PFS-Seiten sind wieder der Engpass. Gleiche Größe ist keine Empfehlung sondern Voraussetzung.
Fehlinterpretation 3: "Tabellenvariablen sind schneller als #temp-Tabellen." Mythos aus vergangenen Zeiten. Bei kleinen Mengen (<100 Zeilen) egal. Bei großen Mengen produzieren Tabellenvariablen durch fehlende Statistiken katastrophal schlechte Ausführungspläne — der Overhead dafür ist um Größenordnungen höher als jeder theoretische Logging-Vorteil.
Fehlinterpretation 4: "PAGELATCH_UP auf TempDB bedeutet immer PFS-Contention." Nicht zwingend. PAGELATCH-Waits können auf beliebigen Seiten auftreten. Die resource_description "2:1:1" oder "2:1:2" identifiziert konkret PFS/GAM/SGAM-Seiten. Andere Seitennummern bedeuten Contention auf normalen Datenseiten — anderes Problem, andere Lösung.
Fehlinterpretation 5: "TempDB-Contention ist ein Speicherproblem." Nein. Es ist ein Allokations-Serialisierungsproblem. Mehr RAM hilft nur indirekt (weniger Spills → weniger TempDB-Nutzung). Das direkte Mittel sind mehr gleich große Dateien.

 

 

Tipp: Erste Gegenmaßnahmen — Triage, nicht Volltherapie
1. Datendatei-Anzahl prüfen: SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0. Wenn kleiner als min(logische CPU-Kerne, 8), sofort weitere gleichgroße Dateien hinzufügen.
2. Dateigrößen angleichen: SELECT DISTINCT size FROM sys.master_files WHERE database_id = 2 AND type = 0. Mehr als eine eindeutige Größe = Problem. Alle Dateien auf dieselbe SIZE setzen.
3. TempDB auf dediziertes Volume: Falls TempDB noch mit anderen Dateien auf demselben Volume liegt — dieser Schritt erfordert einen Neustart, bringt aber sofort messbare IO-Entlastung für alle anderen Volumes.
4. Initialgröße erhöhen: Wenn Autogrowth-Events im Error Log erscheinen, ist die Initialgröße zu klein. Spitzenbedarf messen, Initialgröße großzügig setzen.
5. SQL Server 2019+: In-Memory TempDB Metadata prüfen und aktivieren — kostet nichts, hilft bei hohem Temp-Objekt-Erstellungsrate.
6. Version Store prüfen: Falls RCSI aktiv und TempDB wächst kontinuierlich, lange Transaktionen via sys.dm_tran_active_transactions identifizieren.

 

Zusammenfassung

TempDB ist die am stärksten geteilte Ressource in SQL Server — und genau deshalb der häufigste Engpass unter Last. Fast jede Performance-Analyse stößt irgendwann auf TempDB: als Ziel von Sort Spills, als Heimat des Version Stores, als Opfer schlechter Konfiguration.

TempDB speichert mehr als #temp-Tabellen: Work Tables für Sort und Hash Spills, den Row-Version Store für RCSI und Snapshot Isolation, Online-Index-Operationen, Cursor-Ergebnisse und XML-Variablen.

PFS-Contention (PAGELATCH_UP auf Seite 2:1:1 bis 2:1:3) ist das klassische TempDB-Bottleneck: Viele gleichzeitige Allokationen serialisieren auf wenigen Systemseiten.

Die Lösung ist elegant: min(logische CPU-Kerne, 8) Datendateien, alle exakt gleich groß. Nur dann verteilt Proportional Fill gleichmäßig.

#temp-Tabellen haben Statistiken — Tabellenvariablen nicht. Bei mehr als 100 Zeilen ist #temp fast immer die bessere Wahl für den Ausführungsplan.

TempDB-Caching in Stored Procedures (Deferred Drop) reduziert Allokations-Overhead bei häufig aufgerufenen Prozeduren automatisch — solange keine DDL-Änderungen innerhalb der Prozedur erfolgen.

SQL Server 2019 bietet In-Memory TempDB Metadata als Ergänzung zur Dateistrategie — eliminiert Latch-Contention auf internen Systemtabellen.

TempDB auf dediziertem, schnellem Volume ist Pflicht: Sharing mit Datenbankdaten, Log oder OS erzeugt IO-Konkurrenz bei genau den Operationen die am latenzempfindlichsten sind.

Den Version Store bei aktivem RCSI im Auge behalten: Lange Transaktionen verhindern Cleanup und lassen TempDB unkontrolliert wachsen.

Dimensionierung basiert auf gemessenem Spitzenbedarf plus 30% Puffer — nicht auf Schätzungen. Autogrowth ist das Netz, nicht der Plan.

Die vier Diagnose-Kästen dieses Kapitels sind der schnelle Einstieg, wenn TempDB brennt: Symptome erkennen, messen, Fehlinterpretationen vermeiden, sofort gegensteuern.

Im nächsten Kapitel wenden wir uns einem Thema zu das Nutzer direkt und unmittelbar spüren: Blocking und Deadlocks. Wenn eine Transaktion eine andere blockiert, bleibt die Applikation stehen — nicht langsam, sondern komplett. Der Helpdesk-Anruf folgt zuverlässig. Wir schauen uns an wie Blocking entsteht, wie man es mit dem Deadlock-Graphen und den Wait Statistics aus Kapitel 9 diagnostiziert, und welche Konfigurationsmaßnahmen und Isolation-Level-Strategien das Problem strukturell lösen statt nur kaschieren.

 

Kapitel 14