Wer die tempdb füllt, steht in einer einzigen Abfrage

Drei Verbraucher, ein Datenbereich

Benutzerobjekte sind alles, was jemand ausdrücklich anlegt: temporäre Tabellen mit einer oder zwei Rauten, Tabellenvariablen, Cursor und die Ergebnisse von Tabellenwertfunktionen. Der verbreitete Glaube, eine Tabellenvariable liege im Arbeitsspeicher und eine temporäre Tabelle auf der Platte, stimmt nicht. Beide werden in der tempdb angelegt, die Unterschiede liegen im Transaktionsverhalten, in der Statistikpflege und in der Parallelisierbarkeit.

Interne Objekte legt der Server selbst an, ohne dass jemand sie sieht. Dazu gehören Arbeitstabellen für Spools und Cursor, Arbeitsdateien für Hashvorgänge und Zwischenergebnisse von Sortierungen, etwa beim Erstellen eines Index mit SORT_IN_TEMPDB oder bei einer Abfrage mit GROUP BY, deren Zwischenergebnis nicht in den zugeteilten Speicher passt.

Der Versionsspeicher hält Zeilenversionen fest, die für Leseoperationen unter Zeilenversionierung gebraucht werden, außerdem für Onlinevorgänge an Indizes, für mehrere aktive Ergebnismengen auf einer Verbindung und für Trigger, die nach der Änderung ausgeführt werden. Er ist der einzige der drei, dessen Größe nicht von der aktuellen Last abhängt, sondern von der ältesten noch offenen Transaktion.

Den Verursacher in drei Abfragen einkreisen

Die erste Abfrage geht auf sys.dm_db_file_space_usage in der tempdb und summiert user_object_reserved_page_count, internal_object_reserved_page_count, version_store_reserved_page_count und unallocated_extent_page_count. Multipliziert mit acht Kilobyte je Seite ergibt das vier Zahlen, die zusammen den gesamten Datenbereich beschreiben. Welche der ersten drei die größte ist, bestimmt den weiteren Weg.

Dominieren Benutzerobjekte, geht es weiter mit sys.dm_db_session_space_usage und sys.dm_db_task_space_usage. Beide führen Zuweisungen und Freigaben getrennt, sodass sich der aktuelle Verbrauch je Sitzung ausrechnen lässt. Über sys.dm_exec_requests und sys.dm_exec_sql_text kommst du von der Sitzungsnummer zum tatsächlichen Anweisungstext.

Dominiert der Versionsspeicher, ist sys.dm_tran_active_snapshot_database_transactions die richtige Sicht, weil sie zu jeder Transaktion die verstrichene Zeit liefert. Sortier nach dieser Spalte, die oberste Zeile ist in der Regel die Ursache. Ab SQL Server 2017 zeigt sys.dm_tran_version_store_space_usage zusätzlich, aus welcher Datenbank die Versionen stammen, und das ist auf einer Instanz mit vielen Datenbanken die entscheidende Information.

Interne Objekte sind der Abdruck einer zu kleinen Speicherzuteilung

Für jede Abfrage, die sortieren oder hashen muss, reserviert SQL Server vor dem Start eine Speichermenge. Die Größe beruht auf der geschätzten Zeilenzahl. Schätzt der Optimierer zu niedrig, reicht die Zuteilung nicht, und der Rest der Arbeit wandert als internes Objekt in die tempdb. sys.dm_exec_query_memory_grants stellt für laufende Abfragen angeforderten, tatsächlich zugeteilten und idealerweise benötigten Speicher nebeneinander. Liegt der ideale Wert weit über dem zugeteilten, hast du den Kandidaten gefunden.

Im Ausführungsplan hinterlässt derselbe Vorgang eine Warnung am Sortier- oder Hashoperator. Wer nicht danebensitzen will, während es passiert, schneidet die Ereignisse sort_warning und hash_warning über eine Sitzung der erweiterten Ereignisse mit und hat danach eine Liste der betroffenen Anweisungen. Die Ursache ist fast immer eine Schätzung, die weit danebenliegt, meist wegen veralteter Statistiken oder wegen eines Filters, dessen Selektivität sich nicht abschätzen lässt.

Dateien, Autogrowth und die Ablaufverfolgungsflags, die man nicht mehr braucht

Für die Anzahl der Datendateien gilt eine einfache Regel: so viele wie logische Prozessoren, höchstens jedoch acht. Bleibt danach noch Zuweisungskonkurrenz messbar, erhöht man in Vierer-Schritten. Alle Dateien brauchen dieselbe Anfangsgröße und dasselbe Wachstum, sonst greift die proportionale Verteilung und die größte Datei bekommt fast alles ab.

Zwei Ablaufverfolgungsflags tauchen in älteren Anleitungen immer noch auf. Flag 1117 sorgte dafür, dass alle Dateien einer Dateigruppe gemeinsam wachsen, Flag 1118 dafür, dass Zuweisungen in vollen Blöcken erfolgen. Beides ist seit SQL Server 2016 für die tempdb das Standardverhalten, die Flags sind dort ohne Wirkung. Wer sie noch gesetzt hat, kann sie entfernen.

Das Wachstum selbst gehört auf einen festen Wert in Megabyte, nicht auf einen Prozentsatz, denn prozentuales Wachstum wird mit jeder Runde größer und blockiert entsprechend länger. Wichtiger ist ohnehin, dass es gar nicht erst dazu kommt: Setz die Anfangsgröße auf den gemessenen Höchstbedarf. Damit das Wachstum der Datendateien im Ernstfall nicht den Betrieb anhält, braucht das Dienstkonto das Recht zum sofortigen Initialisieren von Dateien, sonst wird jeder neu belegte Bereich vor der Nutzung mit Nullen überschrieben. Für die Protokolldatei gilt das nur eingeschränkt: Bis SQL Server 2019 wurde sie immer vollständig genullt, ab SQL Server 2022 gilt die schnelle Initialisierung dort für Wachstumsschritte bis 64 Megabyte.

Die dauerhaften Maßnahmen liegen meist außerhalb der tempdb

Wenn interne Objekte den Platz füllen, ist die Ursache eine Abfrage, deren Speicherbedarf falsch geschätzt wurde. Aktuelle Statistiken, ein passender Index und das Vermeiden von Funktionen auf der Filterspalte wirken hier stärker als jede Vergrößerung der Dateien. Dasselbe gilt für Sortierungen: Ein Index, der die Sortierreihenfolge bereits liefert, macht die Auslagerung überflüssig.

Wenn Benutzerobjekte dominieren, lohnt der Blick in die gespeicherten Prozeduren. Häufig wird eine große Tabelle vollständig in eine temporäre Tabelle kopiert, um danach darauf zu filtern. Das Filtern vor dem Kopieren spart den Platz vollständig. Ebenso häufig bleiben temporäre Tabellen in langlaufenden Prozeduren bis zum Ende bestehen, obwohl sie nach dem dritten von zwanzig Schritten nicht mehr gebraucht werden.

Beim Versionsspeicher ist die Maßnahme fast immer eine Änderung in der Anwendung, nämlich Transaktionen nur so lange offen zu halten, wie sie wirklich brauchen. Eine Transaktion, die auf eine Benutzereingabe oder auf eine Antwort eines fremden Dienstes wartet, ist der klassische Fall. SQL Server 2025 bringt hier zwei Ergänzungen, die den Betrieb entspannen: eine Begrenzung des tempdb-Verbrauchs je Arbeitslast über die Ressourcenverwaltung und die beschleunigte Datenbankwiederherstellung auch für die tempdb selbst. Letztere bringt allerdings einen zusätzlichen dauerhaften Versionsspeicher mit, dessen Platzbedarf in die Auslegung gehört.