SQL_Kurs

TempDB in der Krise – So erkennen und beheben Sie Performance-Flaschenhälse

Kaum eine Systemdatenbank in SQL Server sorgt für so viele spontane Performance-Einbrüche wie die TempDB. Hier erfahren Sie, wie Sie Probleme frühzeitig erkennen, lösen und einen kompletten Stillstand verhindern.

🔍 Typische Symptome – Wann die TempDB schmerzt

PAGELATCH_UP / PAGELATCH_SH – Hohe Wartezeiten auf Seiten-Latches
Langsame Abfragen, die plötzlich unter normaler Last extrem reagieren
TempDB-Datenbank wächst rasant und schrumpft nicht zurück
Fehler wie „Insufficient disk space in tempdb“
Hohe Write-Aktivität auf der TempDB-Log-Datei
Der verfügbare Speicherplatz auf dem Laufwerk geht gegen 0 Byte

💡 Merke: Wenn eines oder mehrere dieser Symptome auftreten – handeln Sie, bevor die Produktion stillsteht.

🔎 Identifizierung – Mit diesen DMVs kommen Sie dem Übeltäter auf die Spur

1. Aktuelle TempDB-Auslastung messen
Zeigt, welche Sessions gerade massiv Seiten in der TempDB belegen.

SELECT 
  DB_NAME(database_id) AS DBName,
  user_objects_alloc_page_count,
  internal_objects_alloc_page_count
FROM sys.dm_db_task_space_usage
WHERE session_id > 50
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

2. Latches & Seitenkonflikte erkennen
Hohe Werte bei PAGELATCH_UP auf GAM/SGAM-Seiten deuten auf Allokationskontention hin.

SELECT 
  wait_type,
  waiting_tasks_count,
  wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%_%'
ORDER BY wait_time_ms DESC;

3. Wer blockiert die TempDB?

SELECT 
  session_id,
  wait_type,
  wait_resource,
  wait_time
FROM sys.dm_exec_requests
WHERE wait_type LIKE 'PAGE%LATCH%'
  AND database_id = 2;

📊 Tipp: Speichern Sie die Ergebnisse dieser Abfragen in einer Tabelle, um Trends über die Zeit zu erkennen.

🆘 NOTFALL: Auf der TempDB-Platte sind 0 Byte frei – Sofortmaßnahmen

Wenn der Speicherort der TempDB-Dateien (tempdb.mdf, tempdb.ndf, templog.ldf) keinen freien Platz mehr hat, kann keine neue Seite mehr allokiert werden. Neue Abfragen schlagen mit folgendem Fehler fehl: „Could not create tempdb. You may not have enough disk space available.“. Das ist ein echter Produktions-Notfall.

🚨 Sofortmaßnahmen (in dieser Reihenfolge):

1️⃣ SQL Server Dienst neu starten – Das ist oft die schnellste Lösung, weil TempDB beim Neustart mit der zuletzt konfigurierten Größe neu erstellt wird und überschüssiger, ungenutzter Speicherplatz freigegeben wird. In einer 24/7-Umgebung ist das der letzte Ausweg, aber bei 0 Byte Freiheit bleibt oft keine Alternative.

2️⃣ Manuell andere Dateien auf der Platte löschen – So schaffen Sie sofort ein paar Gigabyte Luft, um den SQL Server überhaupt wieder zu starten.

3️⃣ Problem-Session identifizieren und killen – Sobald wieder Platz vorhanden ist, finden Sie die Verursacher und beenden die entsprechenden Sessions:

SELECT session_id, internal_objects_alloc_page_count, user_objects_alloc_page_count
FROM sys.dm_db_task_space_usage
ORDER BY (internal_objects_alloc_page_count + user_objects_alloc_page_count) DESC;

KILL ; -- Problem-Session beenden

⚠️ Prävention: Nachdem der Notfall behoben ist, sollten Sie die Ursache ergründen (z. B. fehlende Indizes, riesige Hash-Joins, lange Transaktionen im Version Store) und die TempDB-Größe dauerhaft angemessen dimensionieren.

📉 TempDB verkleinern – Methoden, Risiken und Best Practices

Wichtig vorweg: Die TempDB wächst bei normalem Betrieb wieder auf die benötigte Größe. Ein sehr großes TempDB ist kein Performance-Problem – häufiges Schrumpfen fragmentiert die Dateien und führt zu unnötigen I/O-Operationen. Sie sollten die TempDB daher nur in zwei Situationen verkleinern:

Nach einer einmaligen, ungewöhnlich großen Operation (z. B. riesiger Indexrebuild oder Reporting-Job).
Im Notfall (siehe oben), um wieder Platz auf der Platte zu schaffen.

Methode 1: DBCC SHRINKFILE (empfohlen)
Verkleinert einzelne Dateien und bietet mehr Flexibilität als DBCC SHRINKDATABASE.

USE [tempdb];
GO
DBCC SHRINKFILE (N'tempdev', 'ZIELGRÖSSE_IN_MB');
DBCC SHRINKFILE (N'templog', 'ZIELGRÖSSE_IN_MB');
GO

💡 Tipp: Verwenden Sie DBCC SHRINKFILE mit der Option TRUNCATEONLY, um ungenutzten Speicherplatz am Ende der Datei freizugeben, ohne die Datei neu zu organisieren: DBCC SHRINKFILE (N'tempdev', TRUNCATEONLY);

Methode 2: SQL Server Neustart
Wenn Sie zusätzliche Dateien hinzugefügt haben oder ein kompletter Reset gewünscht ist, können Sie diese nach einem Dienstneustart verkleinern. Die TempDB wird dann mit ihrer zuletzt konfigurierten Größe neu erstellt.

🛑 Achtung – Shrink schlägt fehl, wenn:
• Noch aktive Transaktionen in der TempDB laufen.
• Worktables oder temporäre Objekte die zu verschiebenden Seiten belegen.
• Die Datei bereits auf ihre Minimalgröße komprimiert ist.

In diesen Fällen hilft oft nur ein Neustart des SQL Server-Dienstes.

⚙️ Dauerhafte Lösungen – Mehrere Dateien, schneller Speicher und richtige Konfiguration

✔️ Mehrere TempDB-Dateien anlegen
Die wichtigste Maßnahme gegen Allokations-Kontention: Eine Datei pro logischem CPU-Kern (bis maximal 8 Dateien).

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (
  NAME = N'tempdev2', 
  FILENAME = N'D:\TempDB\tempdb2.ndf', 
  SIZE = 8192KB, 
  FILEGROWTH = 65536KB
);
GO

🔧 Wichtige Regeln:

Alle Datendateien müssen die gleiche Anfangsgröße und gleichen Autogrowth haben, damit der proportionale Füllalgorithmus optimal arbeitet.
Verwenden Sie MB-Werte für Autogrowth (keine Prozentangaben), um häufige Wachstumsschübe zu vermeiden.
Lagern Sie die TempDB auf schnellen separaten Datenträgern (SSD/NVMe) aus, idealerweise nicht auf demselben Laufwerk wie die User-Datenbanken.

✔️ Abfragen optimieren, um TempDB-Last zu reduzieren
Viele TempDB-Probleme sind die Folge schlecht geschriebener Abfragen. Prüfen Sie:

Fehlende Indizes, die zu Sortierungen oder Hash-Joins zwingen.
Vermeidung von SELECT * und übermäßigen ORDER BY-Klauseln.
Verwendung von @table-Variablen anstelle von #temporären Tabellen, wenn möglich.

📌 Version Store – Wenn READ COMMITTED SNAPSHOT die TempDB füllt

Unter READ COMMITTED SNAPSHOT oder SNAPSHOT ISOLATION speichert SQL Server alle Zeilenversionen in der TempDB. Wenn eine Transaktion sehr lange läuft (z. B. ein vergessenes COMMIT im Anwendungscode), kann der Version Store explodieren und die Platte füllen.

SELECT 
  version_store_reserved_page_count * 8 / 1024 AS VersionStoreMB
FROM sys.dm_db_file_space_usage;

-- Lange Transaktionen finden
SELECT session_id, transaction_id, transaction_begin_time
FROM sys.dm_tran_active_transactions
ORDER BY transaction_begin_time;

🔥 Lösung: Die älteste aktive Transaktion beenden (KILL ) oder den Anwendungscode korrigieren, damit Transaktionen immer geschlossen werden. Wenn das nicht möglich ist, müssen Sie die Isolationsstufe überdenken oder den SQL Server neu starten, um den Version Store komplett zu bereinigen.

📈 Monitoring & Frühwarnung einrichten

Richten Sie Alerts ein, sobald folgende Schwellwerte überschritten werden:

Freier Speicher auf der TempDB-Platte < 20 %
Log-Datei > 80 % der Datendatei(en)
Hohe Latch-Wartezeiten (> 100 ms über 5 Minuten)

-- Freien Platz in TempDB ermitteln
SELECT 
  SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB
FROM sys.dm_db_file_space_usage;

🛠️ Empfehlung: Automatisieren Sie diese Prüfungen mit einem regelmäßigen SQL Server-Agent-Job oder Ihrer Monitoring-Lösung.

✅ Abschließende Checkliste für Ihre TempDB

Mindestens 4 Daten-Dateien (je nach CPU-Kernen, maximal 8).
Alle Daten-Dateien gleich groß initialisieren + identische Auto-Wachstums-Einstellungen.
Auto-Wachstum in MB festlegen (z. B. 512 MB oder 1024 MB), keine Prozentangaben.
TempDB auf schneller, separater SSD/NVMe (eigene LUN oder Partition).
Auto-Shrink deaktivieren (Systemwartung vermeidet unnötige I/O-Last).
Regelmäßige Kontrolle über DMVs (sys.dm_db_file_space_usage, sys.dm_os_wait_stats).
Keine unnötigen großen temporären Objekte im Code.
Lange Transaktionen vermeiden, insbesondere unter READ COMMITTED SNAPSHOT.
Nach Notfällen die Ursache analysieren, nicht einfach nur die TempDB vergrößern.