Spills und Spools: Was SQL Server in TempDB zwischenlagert

Ein Spool ist ein geplanter Operator. Ein Spill ist ein Ereignis während der Ausführung. Ein Spool kann Arbeit sparen. Ein passender Index kann diese Arbeit ganz überflüssig machen. Ein Spill zeigt dagegen, dass eine Sortierung oder ein Hash Join mit zu wenig Arbeitsspeicher auskommen musste.

Dieser Beitrag zeigt beide Fälle an eigenen Labmessungen auf SQL Server 2022 und 2025: wie Sie sie im Plan erkennen, wann ein Index hilft und was er kostet.

Zwei Begriffe, ein Ort

Beide nutzen TempDB: Spools legen dort Arbeitstabellen oder einen temporären Index an, Spills lagern Teile der Sortier- oder Hashdaten dorthin aus. Ein Plan kann einen Spool ohne Spill enthalten und einen Spill ohne Spool. Für beide Fälle liegen in unserem Lab tatsächliche Ausführungspläne vor.

Sort und Hash: wenn die Speicherzusage nicht reicht

Bevor eine Abfrage startet, sagt SQL Server ihr Arbeitsspeicher für Sortierungen und Hash-Tabellen zu (Memory Grant). Grundlage ist die geschätzte Zeilenzahl und Zeilenbreite. Reicht die Zusage während der Ausführung nicht, lagert der Operator Teile seiner Daten in TempDB aus und liest sie später wieder ein. Das ist der Spill.

Im Lab haben wir die Zusage absichtlich begrenzt, um den Effekt isoliert zu messen:

Auf SQL Server 2022 erhielt die Sortierung ohne Spill 65.912 KB Speicher, mit begrenzter Zusage nur 560 KB. Der Plan meldet dann je 19.362 Seiten, die in TempDB geschrieben und wieder gelesen wurden. Beim Hash Join waren es 125.000 KB gegenüber dem Mindestbedarf von 1.024 KB und je 36.160 Seiten in TempDB.

Der Hash-Spill lief auf 2025 schneller als auf 2022. Das ist keine Versionsrangliste: Die Läufe fanden in getrennten Zeitfenstern mit unterschiedlichem Kompatibilitätsgrad auf derselben gemeinsam genutzten SSD statt, die Ursache haben wir nicht untersucht. Belegt ist der Unterschied zwischen ausreichender und knapper Zusage auf derselben Version.

Was Sie bei einem Spill prüfen:

• Geschätzte gegenüber tatsächlichen Zeilen am Sort- oder Hash-Operator und an den Operatoren davor. Eine große Abweichung ist die häufigste Ursache.
• Zugesagter gegenüber genutztem Speicher in den Planeigenschaften (MemoryGrantInfo: GrantedMemory, MaxUsedMemory).
• Ob sich die Arbeit vermeiden lässt. Eine Sortierung, die ein Index bereits liefert, braucht keinen Speicher.
• Ab SQL Server 2019 (Kompatibilitätsgrad 150) kann Memory Grant Feedback auch im Row Mode die Zusage bei wiederholten Ausführungen anpassen. Das hilft bei wiederkehrenden Abfragen, nicht bei der ersten Ausführung.

Der Index, der die Sortierung erspart

Mit einem Index, der die Zeilen bereits in der gewünschten Reihenfolge liefert, entfällt der Sort-Operator. Bei derselben knappen Zusage sank die Laufzeit auf 58 ms auf beiden Versionen, ohne Sort und ohne Spill. Der Preis: Dieser breite Index belegt rund 40 MB und ist damit fast so groß wie die Tabelle selbst. Ob sich das lohnt, entscheidet die gesamte Arbeitslast, nicht diese eine Abfrage.

Der nützliche Spool: einmal lesen, mehrfach verwenden

Ein Lazy Table Spool speichert ein Zwischenergebnis beim ersten Bedarf und gibt es bei weiteren Durchläufen erneut aus. In unserem Beispiel liefert ein Nested Loops Join für 50 Kunden jeweils dieselbe gefilterte Menge von Bestellungen (Amount < 10). Der Spool wird einmal befüllt und 49-mal wiederverwendet.

Hier spart der Spool die wiederholte Arbeit. Ihn per Hint zu entfernen macht die Abfrage langsamer.

Anders wird es, wenn ein schmaler Index auf Amount hinzukommt: Dann ist der wiederholte Zugriff selbst günstig. Der Optimizer behielt den Spool trotzdem (29,7 ms), ohne Spool lief es mit 25,5 ms etwas schneller. Diese Differenz von wenigen Millisekunden ist kein belastbarer Vorteil. Sie zeigt aber: Nach einer Indexänderung sollten Sie einen Spool neu beurteilen, statt ihn als gesetzt zu betrachten.

Eager Index Spool: SQL Server baut sich einen Index, bei jeder Ausführung

Ein Eager Index Spool ist meist ein deutliches Signal für einen fehlenden Index. Fehlt ein Zugriffsweg, liest SQL Server die Tabelle vollständig, baut daraus in TempDB einen temporären Index und sucht dann darin. Nach der Abfrage ist dieser Index wieder weg. Bei der nächsten Ausführung beginnt alles von vorn.

Unser Beispiel sucht für 200 Kunden jeweils die letzte Bestellung:

SELECT COUNT_BIG(*) AS n, SUM(CONVERT(bigint, x.Amount)) AS summe
FROM dbo.Customers AS c
CROSS APPLY (
    SELECT TOP (1) o.Amount
    FROM dbo.Orders AS o
    WHERE o.CustomerId = c.Id
    ORDER BY o.Stamp DESC, o.Id DESC
) AS x
WHERE c.Id <= 200;

Ohne passenden Index steht im Plan ein Eager Index Spool: einmal aufgebaut, 199-mal wiederverwendet, insgesamt 80.000 ausgegebene Zeilen. Mit einem Index, der zur Suche, zur Sortierung und zur Ergebnisspalte passt, verschwindet der Spool:

CREATE INDEX IX_Orders_Customer_Stamp
ON dbo.Orders (CustomerId, Stamp DESC, Id DESC)
INCLUDE (Amount);

Das Ergebnis blieb identisch, der Indexzugriff ist im tatsächlichen Plan nachgewiesen. Der Hint NO_PERFORMANCE_SPOOL entfernte den Eager Index Spool auf keiner der beiden Versionen. Das deckt sich mit der Analyse von Paul White (siehe Quellen): Gegen einen Eager Index Spool hilft der fehlende Index, kein Hint.

Der Schutzspool: notwendig, nicht schädlich

Nicht jeder Spool ist ein Tuningkandidat. Ändert ein UPDATE eine Spalte, über deren Index es zugleich die betroffenen Zeilen findet, könnte eine Zeile mehrfach erfasst werden („Halloween-Problem“). Ein Eager Table Spool trennt dann das Lesen vom Ändern.

In unserem Lab erhöhte ein UPDATE den Wert Score und las dabei über den Index auf Score. Der Plan enthielt einen Eager Table Spool, auch mit NO_PERFORMANCE_SPOOL. Genau 500 Zeilen wurden je einmal erhöht. Dieser Spool sorgt für ein korrektes Ergebnis. „Eager“ beschreibt nur, wie er befüllt wird, nicht seinen Zweck: Ein Eager Index Spool und ein Schutzspool sind zwei verschiedene Fälle.

Was der passende Index kostet

Ein Index spart Lesearbeit und kostet bei jeder Änderung. Für den Index aus dem Eager-Index-Spool-Beispiel haben wir beides gemessen:

Die Schreibmessung ist ein kleiner Block in einer Transaktion, kein Durchsatztest. Sie zeigt den Preis dieses einen Index, keine allgemeine Grenze. Ob er sich lohnt, hängt davon ab, wie oft die Abfrage läuft und wie viel auf der Tabelle geschrieben wird.

So gehen Sie vor

1. Tatsächlichen Plan holen. Nur er zeigt Spills. In SSMS „Tatsächlichen Ausführungsplan einschließen“, alternativ aus dem Query Store die Planform und mit der Abfrage unten den TempDB-Verbrauch.
2. Spool oder Spill? Spool: eigener Operator. Spill: Warnung am Sort- oder Hash-Operator.
3. Beim Spill: geschätzte und tatsächliche Zeilen vergleichen, Speicherzusage prüfen. Die Schätzung verbessern (Statistiken, Abfrageform) oder die Sortierung durch einen passenden Index vermeiden.
4. Beim Eager Index Spool: den Index ableiten, den SQL Server sich jedes Mal selbst baut. Gleichheitsspalten zuerst, dann die Sortierung, benötigte Spalten als INCLUDE.
5. Beim Lazy Spool oder Schutzspool: verstehen, welche Arbeit er spart oder welche Korrektheit er sichert. Nicht per Hint entfernen, ohne die Wirkung gemessen zu haben.
6. Gesamtkosten messen. Lesezeit der Abfrage, Schreibkosten und Platz des Index, Wirkung auf die übrige Last.

Abfrage: Welche Abfragen brauchen am meisten TempDB?

Der Query Store speichert ab SQL Server 2017 je Ausführung, wie viele TempDB-Seiten eine Abfrage belegt hat. Damit finden Sie die Kandidaten für Spills und Spools, ohne auf den nächsten Vorfall zu warten.

/* PSG Stack · SQL Server TempDB · Abfrage 3
   Query Store: Welche Abfragen brauchen am meisten TempDB?

   - Nur lesend. In der Benutzerdatenbank ausführen, deren Query Store
     Sie auswerten möchten (nicht in TempDB).
   - Voraussetzung: Query Store ist für die Datenbank eingeschaltet.
   - avg/max_tempdb_space_used zählt 8-KB-Seiten je Ausführung
     (Spills, Spools, #temp-Tabellen, Sortierungen).
   - Zeitfenster unten anpassen: @von und @bis in UTC.
   - Rechte: VIEW DATABASE STATE
     (ab SQL Server 2022 genügt VIEW DATABASE PERFORMANCE STATE).
     Ohne dieses Recht scheitert die Abfrage mit Fehler 262.
   - Ist der Query Store der Datenbank aus (oder läuft die Abfrage in master oder TempDB),
     ist das Ergebnis leer, ohne Fehler.
   - Ab SQL Server 2017. Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025.
*/
DECLARE @von datetimeoffset = DATEADD(HOUR, -24, SYSUTCDATETIME());
DECLARE @bis datetimeoffset = SYSUTCDATETIME();

SELECT TOP (25)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions)                                                        AS ausfuehrungen,
    CAST(SUM(rs.avg_tempdb_space_used * rs.count_executions) * 8 / 1024.0
         / NULLIF(SUM(rs.count_executions), 0) AS decimal(12,2))                    AS tempdb_mb_je_ausfuehrung,
    CAST(MAX(rs.max_tempdb_space_used) * 8 / 1024.0 AS decimal(12,2))               AS tempdb_mb_max,
    CAST(SUM(rs.avg_tempdb_space_used * rs.count_executions) * 8 / 1024.0 AS decimal(14,1)) AS tempdb_mb_summe,
    CAST(SUM(rs.avg_duration * rs.count_executions) / 1000.0
         / NULLIF(SUM(rs.count_executions), 0) AS decimal(12,1))                    AS dauer_ms_je_ausfuehrung,
    MAX(rs.last_execution_time)                                                     AS zuletzt_ausgefuehrt,
    OBJECT_NAME(q.object_id)                                                        AS modul,
    LEFT(qt.query_sql_text, 4000)                                                   AS sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE rsi.start_time < @bis
  AND rsi.end_time   > @von
GROUP BY q.query_id, p.plan_id, q.object_id, qt.query_sql_text
HAVING SUM(rs.avg_tempdb_space_used * rs.count_executions) > 0
ORDER BY SUM(rs.avg_tempdb_space_used * rs.count_executions) DESC
OPTION (MAXDOP 1);

Sortieren Sie bei Bedarf nach tempdb_mb_je_ausfuehrung statt nach der Summe: Die Summe zeigt, was TempDB insgesamt belastet, der Wert je Ausführung zeigt einzelne große Verbraucher. Öffnen Sie für die auffälligen Pläne den gespeicherten Plan im Query Store und prüfen Sie, ob ein Spool oder ein Sort- bzw. Hash-Operator die Ursache ist. Ob ein Spill tatsächlich stattfand, zeigt erst der tatsächliche Plan einer erneuten Ausführung.

Quellen

• Microsoft: Showplan-Operatoren (Spool, Sort, Hash Match)
• Microsoft: Probleme mit Memory Grants untersuchen
• Microsoft: Abfragehinweise MAX_GRANT_PERCENT und NO_PERFORMANCE_SPOOL
• Microsoft: Leitfaden zum Indexdesign
• Paul White: The Eager Index Spool and The Optimizer
• Microsoft Tech Community: Fixing problematic index spools

Weitere TempDB-Themen