Memory Grants und Spills:
Wenn SQL Server seinen eigenen RAM nicht mehr findet
Was ist eine Memory Grant — und warum existiert sie überhaupt?
SQL Server ist nicht naiv. Bevor er eine komplexe Abfrage ausführt, schaut er sich den Ausführungsplan an und fragt: "Wie viel RAM brauche ich wahrscheinlich für diese Operation?" Sorts und Hash Joins können nicht einfach loslegen — sie müssen Daten im Speicher puffern, bevor sie ein Ergebnis liefern können. Dieser vorab reservierte Arbeitsspeicher heißt Memory Grant.
Das klingt vernünftig. Das Problem: Die Schätzung basiert auf Statistiken und Kardinalitätsschätzungen des Query Optimizers. Und wer schon mal erlebt hat, wie SQL Server 200 Zeilen schätzt und 2 Millionen bekommt, weiß, wie das endet. Falsche Schätzung → falscher Grant → Spill. Den Rest des Kapitels verbringen wir damit, genau diesen Teufelskreis zu verstehen, zu messen und zu brechen.
Erinnerst du dich an den Buffer Pool aus Kapitel 11? Der ist für Datenseiten zuständig. Memory Grants kommen aus einem anderen Pool — dem Query Execution Memory — und werden vom Resource Broker verwaltet. Diese strikte Trennung sorgt dafür, dass eine speicherhungrige Abfrage nicht einfach den Buffer Pool leerfrisst. Aber sie sorgt auch dafür, dass Abfragen in der Warteschlange landen können, wenn der Execution Memory knapp wird.
|
Definition: Memory Grant |
|---|
|
Vorab reservierter Arbeitsspeicher für Abfragen, die Sort- oder Hash-Operationen durchführen. Die Größe wird vom Query Optimizer geschätzt und vor Abfragestart zugeteilt. Eine Abfrage startet erst, wenn der Grant gewährt wurde — oder bis zum Timeout wartet. |
Abb. 12.1: Memory Grant Lifecycle: Von der Anforderung bis zum Spill
Memory Grant Lifecycle: Schritt für Schritt
Der Weg einer Memory Grant ist vorhersehbar — und an jedem Schritt kann etwas schiefgehen. Schauen wir uns den Ablauf konkret an.
Schritt 1: Optimizer schätzt den Bedarf
Während der Query Optimizer den Ausführungsplan erstellt, berechnet er für jede Sort- und Hash-Operation den erwarteten Speicherbedarf. Grundlage sind die Statistiken der beteiligten Tabellen: Wie viele Zeilen werden erwartet? Wie groß ist eine durchschnittliche Zeile? Das Ergebnis ist der "Required Memory" — der Wunschbetrag des Optimizers.
Hier lauert das erste Problem: Sind die Statistiken veraltet oder schlicht falsch (→ Kapitel 16), schätzt der Optimizer möglicherweise 500 KB wo eigentlich 2 GB gebraucht werden. Oder umgekehrt: 8 GB anfordern, weil ein Parameter-Sniffing-Problem einen generischen Plan erzwungen hat (→ Kapitel 18). Beides ist schlechter als eine gute Schätzung.
Schritt 2: Resource Broker entscheidet
Der Resource Broker ist die Instanz, die den verfügbaren Execution Memory verwaltet. Er empfängt die Grant-Anforderung und prüft, ob genug freier Speicher da ist. Wenn ja — Grant gewährt, Abfrage startet. Wenn nein — Abfrage landet in der Wait Queue. Der zugehörige Wait Type heißt RESOURCE_SEMAPHORE (→ Kapitel 9 für die vollständige Behandlung von Wait Types).
Wie viel Execution Memory steht zur Verfügung? Ungefähr 75% des "max server memory"-Werts werden für den Buffer Pool reserviert; der Rest ist für Execution Memory und andere Zwecke verfügbar. Bei einem Server mit 128 GB RAM und max server memory = 120 GB sind das grob 30 GB für Execution Memory — aber eben geteilt durch alle gleichzeitig laufenden Abfragen.
Schritt 3: Ausführung und Realitätscheck
Die Abfrage läuft los und verbraucht Speicher. Irgendwann zeigt sich, ob die Schätzung stimmt. Drei Szenarien:
Memory Spills: Wenn der RAM nicht reicht
Ein Spill ist kein Crash und keine Fehlermeldung. SQL Server schweigt und schaufelt Daten in die TempDB — du merkst es erst, wenn der Benutzer fragt warum seine Auswertung plötzlich 90 Sekunden statt 3 Sekunden braucht. Das macht Spills so tückisch: Sie sind unsichtbar ohne explizites Monitoring.
Sort Spill
Eine Sort-Operation braucht alle zu sortierenden Daten im Speicher. Reicht der Grant nicht, schreibt SQL Server Teile der Daten als temporäre "Run Files" in die TempDB, sortiert sie dort, und führt am Ende einen Merge durch. Das nennt sich External Sort oder Multi-Pass Sort. Jeder zusätzliche Pass kostet IO und Zeit. Bei einem Level-1-Spill auf NVMe sind das realistische 10–20× längere Ausführungszeiten; auf einem SAN mit HDD-Backend können daraus leicht 50–100× werden.
Hash Spill
Hash Joins und Hash Aggregates bauen eine Hash-Tabelle aus der Probe-Seite auf. Passt diese nicht in den Grant, wird sie in Partitionen aufgeteilt und in die TempDB geschrieben — ebenfalls ein mehrstufiger Prozess. In sys.dm_exec_query_stats kannst du über die Spalten total_spills und max_spills sehen, ob eine Query historisch gesehen gespillt hat.
|
Warnung: Spills auf Sparfuchs-Hardware |
|---|
|
Bei Sparfuchs & Partner (Kapitel 33) liegen TempDB, Datenbankdaten, Transaktionslog und OS alle auf einer einzigen Spindel mit gemessenen p95-Schreiblatenzen von 312 ms. Ein Sort Spill bedeutet dort nicht "etwas langsamer" — er bedeutet "die Abfrage konkurriert mit dem Checkpoint, dem Log-Flush und dem Backup gleichzeitig um die einzige Schreibköpf-Position auf der Platte". Faktor 100× ist da keine Übertreibung, sondern Messpunkt. |
Abb. 12.2: Performance-Vergleich: In-Memory vs. TempDB-Spill nach Storage-Tier
Diagnose: Extended Events für Spills
Der direkteste Weg, Spills zu erkennen, sind Extended Events. SQL Server hat zwei dedizierte Events genau dafür — und sie sind in keiner Default-Session aktiv. Du musst sie explizit einschalten.
sort_warning und hash_warning
-- Extended Event Session für Memory-Grant-Probleme
-- Läuft dauerhaft im Hintergrund, wenig Overhead
CREATE EVENT SESSION [MemoryGrantWatch] ON SERVER
ADD EVENT sqlserver.sort_warning(
-- sort_warning feuert, wenn SQL Server in TempDB sortieren muss
ACTION(sqlserver.sql_text, sqlserver.plan_handle,
sqlserver.session_id, sqlserver.database_name)
WHERE ([sqlserver].[database_name] <> N'tempdb')
),
ADD EVENT sqlserver.hash_warning(
-- hash_warning: Hash Join / Hash Aggregat spills
-- warning_type: 0=recursion, 1=bailout (schlimmer!)
ACTION(sqlserver.sql_text, sqlserver.plan_handle,
sqlserver.session_id, sqlserver.database_name)
WHERE ([sqlserver].[database_name] <> N'tempdb')
),
ADD EVENT sqlserver.query_memory_grant_usage(
-- feuert nach Abfrage-Ende: granted vs. used Memory
-- ideal um over- und underestimated Grants zu erkennen
ACTION(sqlserver.sql_text, sqlserver.plan_handle)
WHERE (granted_memory_kb > 102400) -- nur Grants > 100 MB protokollieren
)
ADD TARGET package0.ring_buffer(SET max_memory = 51200) -- 50 MB Ringpuffer
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
ALTER EVENT SESSION [MemoryGrantWatch] ON SERVER STATE = START;
Das query_memory_grant_usage-Event ist besonders wertvoll: Es zeigt dir nach Abfrage-Ende wie viel RAM angefordert wurde (granted_memory_kb) vs. wie viel wirklich verbraucht wurde (used_grant_kb). Eine Abfrage mit granted = 4 GB und used = 12 MB ist ein klarer Overestimate — die Statistiken stimmen nicht (→ Kapitel 16).
sys.dm_exec_query_memory_grants: Wer wartet, wer bekommt, wer verschwendet
Diese DMV zeigt alle Abfragen, die gerade auf einen Memory Grant warten oder bereits einen erhalten haben — in Echtzeit. Das ist dein Sichtfenster auf den aktuellen Druck im Execution Memory.
-- Aktuelle Memory Grant Situation auf dem Server
-- Zeigt wartende und laufende Abfragen mit Grant-Details
SELECT
r.session_id,
r.status,
-- Wie lange wartet/läuft diese Abfrage schon?
r.wait_time / 1000.0 AS wait_sek,
r.total_elapsed_time / 1000.0 AS laufzeit_sek,
mg.request_time,
-- Angefordert: was der Optimizer glaubte zu brauchen
mg.requested_memory_kb / 1024.0 AS requested_mb,
-- Gewährt: was der Resource Broker tatsächlich gegeben hat
mg.granted_memory_kb / 1024.0 AS granted_mb,
-- Verbraucht: Realität (nur, wenn Abfrage schon läuft)
mg.used_memory_kb / 1024.0 AS used_mb,
-- Ideal: was die Abfrage idealerweise hätte haben wollen
mg.ideal_memory_kb / 1024.0 AS ideal_mb,
-- grant_time NULL = wartet noch, NOT NULL = bekommt RAM
mg.grant_time,
-- queue_id: in welcher Warteschlange steckt die Abfrage?
mg.queue_id,
-- Abfragetext für Identifikation
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2 + 1,
128) AS abfragetext
FROM sys.dm_exec_query_memory_grants mg
JOIN sys.dm_exec_requests r ON mg.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
-- Abfragen die warten zuerst anzeigen
ORDER BY mg.grant_time ASC, mg.requested_memory_kb DESC;
Wichtige Spalten im Ergebnis: Wenn grant_time NULL ist, wartet die Abfrage auf ihren Grant. Das ist der erste Hinweis auf Memory Pressure. Wenn used_mb deutlich unter granted_mb liegt, verschwendet der Grant Platz — andere Abfragen könnten davon profitieren.
RESOURCE_SEMAPHORE: Wenn die Warteschlange zum Stau wird
In Kapitel 9 haben wir Wait Types als diagnostisches Instrument kennengelernt. RESOURCE_SEMAPHORE ist einer der Wait Types, der klare Aussagen erlaubt: Er zeigt, dass Abfragen auf ihren Memory Grant warten — der Execution Memory ist ausgeschöpft. Das ist kein gelegentliches Ruckeln, das ist struktureller Memory-Druck.
-- RESOURCE_SEMAPHORE Situation auswerten
-- Wie viele Abfragen warten, wie lange im Schnitt?
SELECT
wait_type,
waiting_tasks_count,
-- Gesamte Wait Time in Minuten
wait_time_ms / 60000.0 AS wait_time_min,
-- Durchschnittliche Wartezeit pro Abfrage
CASE WHEN waiting_tasks_count > 0
THEN wait_time_ms / waiting_tasks_count
ELSE 0 END AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
'RESOURCE_SEMAPHORE', -- Warten auf Memory Grant
'RESOURCE_SEMAPHORE_QUERY_COMPILE' -- Warten auf Compile-Memory
)
ORDER BY wait_time_ms DESC;
-- Zusätzlich: Aktuelle Semaphore-Zustand
SELECT
pool_id,
-- Wie viele Abfragen warten gerade?
pending_request_count,
-- Gesamtspeicher des Pools in MB
target_memory_kb / 1024 AS target_mb,
-- Aktuell gewährter Speicher
granted_memory_kb / 1024 AS granted_mb,
-- Wie viele Grants laufen gerade?
grantee_count,
-- Wie viele warten?
waiter_count
FROM sys.dm_exec_query_resource_semaphores;
Ein pending_request_count > 0 kombiniert mit einer RESOURCE_SEMAPHORE Wait Time, die in eurer Baseline (→ Kapitel 9) nicht auftaucht, ist ein klares Signal: Entweder laufen zu viele speicherhungrige Abfragen gleichzeitig, oder einzelne Abfragen requieren zu viel RAM wegen schlechter Schätzungen.
|
Hinweis: Zwei Semaphoren, nicht eine |
|---|
|
SQL Server hat zwei Resource Semaphores: pool_id = 1 ist der "big gateway" für Abfragen die mehr als 5 MB benötigen, pool_id = 2 der "small gateway" für kleinere Grants. Wenn ihr großer Gateway voll ist, landen neue große Abfragen in der Warteschlange — selbst, wenn noch RAM frei wäre. |
Falsche Grants: Statistiken und Parameter Sniffing als Ursache
Spills entstehen fast nie aus dem Nichts. Die häufigste Ursache ist eine fehlerhafte Kardinalitätsschätzung, die zu einem zu kleinen Grant führt. Und Kardinalitätsschätzungen werden aus Statistiken abgeleitet — veraltete oder fehlende Statistiken bedeuten falsche Grants.
In Kapitel 16 werden wir Statistiken und den Cardinality Estimator detailliert durchleuchten. Hier die Kurzversion: Wenn SQL Server glaubt, eine Tabelle hat 1.000 Zeilen, aber sie hat 10 Millionen, plant er einen Grant für 1.000 Zeilen. Der Spill folgt mit hoher Wahrscheinlichkeit.
Parameter Sniffing (→ Kapitel 18) kann das Problem auf die Spitze treiben: Ein Plan wird mit einer "kleinen" Parameterversion gecacht und bekommt einen winzigen Grant. Die nächste Abfrage mit "großen" Parameterwerten benutzt denselben Plan — und spillt garantiert.
-- Historische Spill-Statistiken aus dem Plan Cache
-- Zeigt welche Abfragen in der Vergangenheit gespillt haben
SELECT TOP 20
qs.total_spills,
qs.max_spills,
-- Spills pro Ausführung: > 0 = regelmäßiges Problem
qs.total_spills / qs.execution_count AS spills_pro_exec,
qs.execution_count,
qs.total_elapsed_time / 1000 AS total_ms,
qs.total_worker_time / 1000 AS cpu_ms,
SUBSTRING(st.text, 1, 200) AS abfragetext_kurz,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_spills > 0
-- Schlimmste Spiller zuerst
ORDER BY qs.total_spills DESC;
Resource Governor: Wer darf wie viel RAM haben?
Wenn einzelne Workloads den Execution Memory dominieren — typischerweise Reporting-Abfragen die riesige Grants requieren und alle anderen in RESOURCE_SEMAPHORE treiben — ist Resource Governor das richtige Mittel. Er erlaubt es, verschiedene Workloads in Pools zu isolieren und Memory-Limits pro Pool zu setzen.
-- Resource Governor: Reporting-Pool mit Memory-Begrenzung
-- Verhindert, dass SSRS-Reports den OLTP-Betrieb abwürgen
-- Erst den Pool definieren
CREATE RESOURCE POOL ReportingPool
WITH (
-- Maximaler Grant pro Abfrage: 25% des Execution Memory
MAX_MEMORY_GRANT_PERCENT = 25,
-- Maximale CPU-Nutzung für diesen Pool
MAX_CPU_PERCENT = 30
);
-- Workload Group im Pool
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool
WITH (
-- Einzelner Grant darf max 15% des Pool-Memory nutzen
REQUEST_MAX_MEMORY_GRANT_PERCENT = 15,
-- Timeout: nach 60 Sekunden Warten → Abbruch
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60
);
-- Klassifizierungsfunktion: Reporting-User in die Group
CREATE FUNCTION dbo.RG_Klassifizierung()
RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
-- Alle Verbindungen vom SSRS-Service-Account gehen in den Reporting-Pool
IF SUSER_NAME() = 'DOMAIN\SSRS_SVC'
RETURN 'ReportingGroup';
RETURN 'default';
END;
-- Resource Governor neu konfigurieren
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RG_Klassifizierung);
ALTER RESOURCE GOVERNOR RECONFIGURE;
Resource Governor ist kein Allheilmittel — er löst nicht das eigentliche Problem (schlechte Schätzungen oder zu kleine Hardware), er begrenzt nur den Schaden. Aber in Mixed-Workload-Umgebungen, wo OLTP und Reporting sich eine Instanz teilen, kann er den Unterschied zwischen "läuft halbwegs" und "totale Blockade" ausmachen.
MIN_GRANT_PERCENT und MAX_GRANT_PERCENT: Notfallkosmetik
SQL Server 2012 SP3 / 2014 SP2 und neuer kennen zwei Query Hints, mit denen du den Grant direkt beeinflussen kannst. Nutze sie mit Bedacht — sie sind Pflaster, keine Heilung.
-- MIN_GRANT_PERCENT: Mindestens diesen Anteil des Pool-Memory reservieren
-- Nützlich, wenn eine Abfrage chronisch zu wenig Grant bekommt
SELECT *
FROM dbo.VerkaufsDaten v
JOIN dbo.Produkte p ON v.ProduktID = p.ID
ORDER BY v.Umsatz DESC
-- 5% des Pool-Memory als Minimum garantieren
OPTION (MIN_GRANT_PERCENT = 5);
-- MAX_GRANT_PERCENT: Grant nach oben deckeln
-- Nützlich, wenn ein Overestimate Speicher blockiert
SELECT /*+ kein echter Hint-Syntax, nur Kommentar */
KundenID,
SUM(Betrag) AS Gesamtumsatz
FROM dbo.Bestellungen
GROUP BY KundenID
-- Nicht mehr als 10% des Pool-Memory nehmen
OPTION (MAX_GRANT_PERCENT = 10);
|
Tipp: Wann Hints sinnvoll sind |
|---|
|
MIN_GRANT_PERCENT ist sinnvoll, wenn eine Abfrage regelmäßig spillt, weil die Kardinalitätsschätzung chronisch zu niedrig ist und du die Statistiken nicht schnell fixen kannst. MAX_GRANT_PERCENT hilft, wenn ein Overestimate zu großen Grants führt, die andere Abfragen blockieren. Langfristig löse die eigentliche Ursache: Statistiken aktualisieren, Plan-Regression beheben (→ Kapitel 16). |
Diagnose-Kästen: Memory Grants und Spills
|
Hinweis: Symptome — woran merkst du ein Memory Grant Problem? |
|---|
|
Nutzer berichten über Abfragen die "manchmal normal schnell, manchmal ewig langsam" sind. Im Wait Statistics Dashboard (→ Kapitel 9) taucht RESOURCE_SEMAPHORE prominent auf. sys.dm_exec_query_memory_grants zeigt pending_request_count > 0. TempDB-IO steigt ohne erkennbaren Grund — das ist der Spill-Verkehr. In sys.dm_exec_query_stats findest du total_spills > 0 bei den langsamen Abfragen. |
-- Schnelldiagnose Memory Grant Druck
-- Alles in einem Query — für den ersten Überblick
SELECT
-- Wie viele Abfragen warten gerade auf einen Grant?
(SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NULL) AS wartende_abfragen,
-- Wie viel Memory ist vergeben vs. verfügbar?
(SELECT SUM(granted_memory_kb)/1024
FROM sys.dm_exec_query_memory_grants) AS granted_mb,
-- Wie ist der RESOURCE_SEMAPHORE Wait?
(SELECT wait_time_ms FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE') AS semaphore_wait_ms,
-- Wie viele Spills in letzter Stunde (Query Store)?
(SELECT COUNT(*) FROM sys.query_store_runtime_stats
WHERE last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
AND avg_num_physical_io_reads > 1000) AS io_intensive_queries;
|
Warnung: Typische Fehlinterpretationen |
|---|
|
Fehlinterpretation 1: "RESOURCE_SEMAPHORE ist selten, also alles gut." — Falsch. Schau auf die avg_wait_time. Wenn jede wartende Abfrage 30 Sekunden wartet, ist das ein Problem — auch, wenn es selten passiert. Fehlinterpretation 2: "Viele Spills = zu wenig RAM kaufen." — Häufig falsch. Oft liegt die Ursache bei schlechten Statistiken oder Parameter Sniffing, nicht bei hardware-seitigem RAM-Mangel. Fehlinterpretation 3: "granted_memory_kb ist hoch, also braucht diese Abfrage viel RAM." — Nicht zwingend. Prüfe used_memory_kb. Wenn der Unterschied riesig ist, liegt ein Overestimate vor. |
|
Tipp: Erste Gegenmaßnahmen |
|---|
|
1. Sofort: sys.dm_exec_query_memory_grants öffnen und wartende Abfragen identifizieren. Wenn ein einzelner Query den Löwenanteil des Grants hält, ist er der Täter. 2. Statistiken für beteiligte Tabellen aktualisieren: UPDATE STATISTICS dbo.GroßeTabelle WITH FULLSCAN. 3. Wenn Spills auf TempDB enden: TempDB auf separates, schnelles Volume legen (→ Kapitel 13). 4. Wenn RESOURCE_SEMAPHORE dominiert: MAX_MEMORY_GRANT_PERCENT im Resource Governor setzen, damit eine Abfrage nicht alles blockiert. 5. Langfristig: Statistik-Aktualisierung automatisieren und Cardinality Estimator prüfen (→ Kapitel 16). |
Resource Governor: Workload-Isolation im Detail
Das Resource-Governor-Beispiel aus dem vorherigen Abschnitt hat die Grundstruktur gezeigt. In der Praxis ist die Konfiguration etwas differenzierter — besonders, wenn du mehrere Workload-Typen hast, die alle unterschiedliche Prioritäten und Memory-Budgets bekommen sollen.
Ein typisches Szenario in mittelgroßen Unternehmen: OLTP-Verbindungen aus der Hauptapplikation, Reporting-Abfragen aus SSRS oder Power BI, und nächtliche Batch-Jobs für ETL und Aggregation. Alle drei haben unterschiedliche Anforderungen — und ohne Resource Governor konkurrieren sie ungefiltert um denselben Execution Memory.
-- Resource Governor: Drei-Pool-Szenario
-- OLTP bekommt Priorität, Reporting wird gebremst,
-- Batch-Jobs bekommen was übrig bleibt
-- Pool 1: OLTP — wenig Memory pro Query, aber hohe Verfügbarkeit
CREATE RESOURCE POOL OLTPPool
WITH (
MIN_CPU_PERCENT = 20, -- mindestens 20% CPU garantiert
MAX_CPU_PERCENT = 80, -- nie mehr als 80% (Rest für Housekeeping)
MIN_MEMORY_PERCENT = 10, -- 10% des Server-Memory immer verfügbar
MAX_MEMORY_GRANT_PERCENT = 15 -- einzelne OLTP-Query max 15% des Grants
);
-- Pool 2: Reporting — darf viel, aber nicht alles
CREATE RESOURCE POOL ReportingPool
WITH (
MIN_CPU_PERCENT = 5,
MAX_CPU_PERCENT = 50,
MAX_MEMORY_GRANT_PERCENT = 25 -- ein Report darf max 25% bekommen
);
-- Pool 3: Batch-Jobs — bewusst gedrosselt
CREATE RESOURCE POOL BatchPool
WITH (
MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 30,
-- Batch-Jobs bekommen max 25% Memory
-- so stören sie OLTP selbst bei vollem Lauf nicht
MAX_MEMORY_GRANT_PERCENT = 25
);
-- Workload Groups mit REQUEST-Limits
CREATE WORKLOAD GROUP OLTPGroup
USING OLTPPool
WITH (
IMPORTANCE = HIGH, -- hohe Scheduler-Priorität
REQUEST_MAX_MEMORY_GRANT_PERCENT = 5, -- OLTP-Queries brauchen wenig
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 5 -- kurzer Timeout: lieber Fehler als warten
);
CREATE WORKLOAD GROUP ReportingGroup
USING ReportingPool
WITH (
IMPORTANCE = MEDIUM,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 20,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120 -- Reports dürfen 2 Min warten
);
CREATE WORKLOAD GROUP BatchGroup
USING BatchPool
WITH (
IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 300 -- Batch kann 5 Min warten
);
-- Klassifizierungsfunktion — bestimmt welche Group eine Verbindung bekommt
CREATE FUNCTION dbo.RG_MultiPool_Klassifizierung()
RETURNS SYSNAME WITH SCHEMABINDING
AS BEGIN
DECLARE @gruppe SYSNAME;
-- App-Login für OLTP-Applikation
IF SUSER_NAME() IN ('DOMAIN\AppSvc', 'DOMAIN\WebSvc')
SET @gruppe = 'OLTPGroup';
-- SSRS und Power BI Service Accounts
ELSE IF SUSER_NAME() LIKE 'DOMAIN\SSRS%'
OR SUSER_NAME() = 'DOMAIN\PowerBI_SVC'
SET @gruppe = 'ReportingGroup';
-- ETL und Batch-Prozesse
ELSE IF SUSER_NAME() LIKE 'DOMAIN\ETL%'
OR APP_NAME() LIKE 'BatchJob%'
SET @gruppe = 'BatchGroup';
-- Alles andere: Default
ELSE SET @gruppe = 'default';
RETURN @gruppe;
END;
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.RG_MultiPool_Klassifizierung);
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- Überprüfung: Wer ist wo eingestuft?
SELECT session_id, login_name, group_id, g.name AS workload_group
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups g
ON s.group_id = g.group_id
WHERE s.is_user_process = 1
ORDER BY g.name, s.session_id;
Die IMPORTANCE-Einstellung innerhalb einer Workload Group beeinflusst, wie der SQL Server Scheduler CPU-Zeit und Memory-Grant-Priorität zwischen Gruppen aufteilt, wenn Ressourcen knapp werden. HIGH bedeutet: Diese Abfragen kommen bei Konflikten zuerst. Das ist der entscheidende Hebel für OLTP-Latenz in gemischten Umgebungen.
|
Tipp: Resource Governor erst auf einem Testsystem einrichten |
|---|
|
Klassifizierungsfunktionen können bei Fehler die gesamte Login-Verarbeitung blockieren. Teste die Funktion immer zuerst mit SELECT dbo.RG_MultiPool_Klassifizierung() in der Sitzung des jeweiligen Service-Accounts — bevor du ALTER RESOURCE GOVERNOR RECONFIGURE ausführst. Ein Fehler in der Klassifizierungsfunktion schmeißt alle neuen Verbindungen in die default-Gruppe, was im besten Fall nur unbemerkt bleibt und im schlechtesten Fall dein sorgfältig konfiguriertes Limit aushebelt. |
MIN_GRANT_PERCENT und MAX_GRANT_PERCENT: Notfall-Eingriff mit Skalpell
Manchmal ist die Ursache klar — der Optimizer schätzt chronisch falsch, die Statistiken lassen sich nicht kurzfristig reparieren, und der Rollout eines Fixes dauert noch zwei Wochen. Genau für diese Situation gibt es die Grant-Hints. Sie sind kein Ersatz für ordentliche Statistiken, aber ein legitimes Werkzeug zur Überbrückung.
-- Szenario 1: Abfrage spillt chronisch — Grant zu klein
-- Symptom: sort_warning oder hash_warning taucht regelmäßig auf,
-- Statistiken sind korrekt aber Join-Kardinalität wird systematisch unterschätzt
SELECT
k.KundenName,
COUNT(b.BestellID) AS AnzahlBestellungen,
SUM(b.Betrag) AS Gesamtumsatz
FROM dbo.Kunden k
JOIN dbo.Bestellungen b ON k.KundenID = b.KundenID
JOIN dbo.BestellPositionen bp ON b.BestellID = bp.BestellID
WHERE b.BestellDatum >= DATEADD(YEAR, -1, GETDATE())
GROUP BY k.KundenName
ORDER BY Gesamtumsatz DESC
-- 8% des Pool-Memory mindestens reservieren
-- verhindert Spill, wenn Kardinalitätsschätzung 5x zu niedrig ist
OPTION (MIN_GRANT_PERCENT = 8);
-- Szenario 2: Overestimate blockiert andere Abfragen
-- Parameter Sniffing hat einen Plan mit riesigem Grant erzeugt
-- der für 95% aller Ausführungen völlig überdimensioniert ist
EXEC dbo.GetKundenUmsatz @Jahr = 2024
-- Falls direkte Hints in SPs nötig: Plan Guide oder Query Store nutzen
-- Für Ad-hoc-Abfragen direkt:
SELECT KundenID, SUM(Betrag)
FROM dbo.Bestellungen
WHERE YEAR(BestellDatum) = 2024
GROUP BY KundenID
-- Grant deckeln: diese Abfrage braucht nie mehr als 5%
-- Optimizer schätzt wegen fehlerhaftem Parameter-Plan 40%
OPTION (MAX_GRANT_PERCENT = 5);
-- Kombination: Minimum sichern, Maximum begrenzen
-- Nützlich, wenn Schätzung stark streut (manchmal 1%, manchmal 50%)
SELECT *
FROM dbo.SalesHistory sh
JOIN dbo.Products p ON sh.ProductID = p.ProductID
WHERE sh.SaleDate BETWEEN @vonDatum AND @bisDatum
ORDER BY sh.Amount DESC
OPTION (
MIN_GRANT_PERCENT = 3, -- immer mindestens 3% — kein Spill unter Normallast
MAX_GRANT_PERCENT = 15 -- nie mehr als 15% — kein Blockieren bei Extremlast
);
|
Warnung: Risiken der Grant-Hints |
|---|
|
MIN_GRANT_PERCENT zu hoch setzen bedeutet: Auch kleine Abfragen reservieren viel Speicher. Bei 50 gleichzeitigen Abfragen mit MIN_GRANT_PERCENT = 10 brauchst du theoretisch 500% des verfügbaren Execution Memory — was in RESOURCE_SEMAPHORE-Warteschlangen endet, die noch länger sind als vorher. Faustregel: MIN_GRANT_PERCENT nie über 10 setzen, außer für explizit serialisierte Batch-Jobs die nie gleichzeitig laufen. MAX_GRANT_PERCENT zu niedrig erzwingt den Spill aktiv — du tauschst Speicherdruck gegen IO-Last aus. Nur sinnvoll, wenn TempDB auf sehr schnellem Storage liegt. |
Adaptive Memory Grants: SQL Server lernt aus seinen Fehlern
SQL Server 2019 hat eine der nützlichsten neuen Funktionen im Bereich Memory Grants eingeführt: Memory Grant Feedback. Die Idee ist so simpel wie elegant — SQL Server merkt sich, ob der letzte Grant für eine Abfrage zu groß oder zu klein war, und korrigiert beim nächsten Mal.
Konkret: Wenn eine Abfrage nur 10% des gewährten Grants verbraucht hat, reduziert SQL Server den Grant beim nächsten Aufruf. Wenn ein Spill aufgetreten ist, erhöht er ihn. Das passiert automatisch, ohne manuellen Eingriff — und wird im Query Store persistent gespeichert, sodass das Feedback auch nach einem Neustart erhalten bleibt (→ Kapitel 19).
Batch Mode vs. Row Mode Feedback
Memory Grant Feedback gibt es in zwei Varianten. Batch Mode Feedback ist seit SQL Server 2017 verfügbar und funktioniert nur bei Columnstore-Abfragen, die den Batch Execution Mode nutzen. Row Mode Feedback wurde mit SQL Server 2019 eingeführt und deckt den deutlich häufigeren Fall ab: normale zeilenbasierte Abfragen auf Rowstore-Tabellen.
Row Mode Memory Grant Feedback ist beim Datenbank-Kompatibilitätslevel 150 (SQL Server 2019) standardmäßig aktiv. Wer noch auf Kompatibilitätslevel 140 oder niedriger läuft — wie Sparfuchs mit Level 110 (Kapitel 33) — bekommt dieses Feature nicht, selbst, wenn der Server SQL 2019 ist. Ein weiterer guter Grund, den Kompatibilitätslevel zu aktualisieren.
-- Überprüfen ob Memory Grant Feedback aktiv ist
-- (Voraussetzung: Kompatibilitätslevel >= 150)
SELECT
name,
compatibility_level,
-- Ab Level 150 ist Row Mode Memory Grant Feedback verfügbar
CASE WHEN compatibility_level >= 150
THEN 'Row Mode Feedback aktiv'
WHEN compatibility_level >= 140
THEN 'Nur Batch Mode Feedback'
ELSE 'Kein Memory Grant Feedback'
END AS feedback_status
FROM sys.databases
WHERE database_id > 4; -- User-Datenbanken
-- Memory Grant Feedback in Query Stats überwachen
-- grant_feedback_count zeigt wie oft SQL Server den Grant korrigiert hat
SELECT TOP 20
qs.execution_count,
-- Wie oft wurde der Grant durch Feedback angepasst?
qs.grant_feedback_count,
-- Verhältnis: hohe Rate = aktive Selbstkorrektur
CASE WHEN qs.execution_count > 0
THEN CAST(qs.grant_feedback_count AS FLOAT)
/ qs.execution_count * 100
ELSE 0
END AS feedback_rate_pct,
qs.total_spills,
qs.min_grant_kb / 1024.0 AS min_grant_mb,
qs.max_grant_kb / 1024.0 AS max_grant_mb,
-- Große Streuung zwischen min und max deutet auf instabiles Feedback hin
(qs.max_grant_kb - qs.min_grant_kb) / 1024.0 AS grant_varianz_mb,
SUBSTRING(st.text, 1, 150) AS abfragetext
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
-- Nur Abfragen die überhaupt Feedback bekommen haben
WHERE qs.grant_feedback_count > 0
ORDER BY qs.grant_feedback_count DESC;
Eine hohe grant_feedback_count bei niedriger execution_count bedeutet: SQL Server korrigiert aggressiv hin und her — ein Zeichen für instabile Parameterwerte oder hohe Datenskew. Das ist kein Problem per se, aber ein Hinweis, dass Parameter Sniffing oder Statistik-Qualität untersucht werden sollte (→ Kapitel 18 für Parameter Sniffing, → Kapitel 16 für Statistiken).
Query Store als Feedback-Persistenz
Das elegante an Memory Grant Feedback in SQL Server 2019 ist die Integration mit dem Query Store: Die gelernten Grant-Korrekturen werden im Query Store persistent gespeichert und überleben damit Neustarts und Plan-Cache-Flushes. In sys.query_store_plan findest du die Spalte has_compile_replay_script — wenn diese true ist, enthält der gespeicherte Plan explizite Memory-Grant-Korrekturen aus dem Feedback-Mechanismus.
Wer schon mit dem Query Store arbeitet (→ Kapitel 19) kann das Feedback auch manuell kontrollieren: Forced Plans im Query Store überschreiben das automatische Feedback. Das gibt dir die Möglichkeit, für bekannte Problemabfragen einen Plan mit explizit korrigiertem Grant zu erzwingen — und das Adaptive Feedback für diese Abfrage effektiv "einzufrieren".
|
Hintergrund: Warum Adaptive Memory Grants kein Allheilmittel sind |
|---|
|
Memory Grant Feedback lernt aus der Vergangenheit — das funktioniert gut für stabile Workloads mit ähnlichen Parameterwerten. Bei Abfragen mit stark variierenden Parametern (z.B. Datumsbereich heute vs. letztes Jahrzehnt) kann das Feedback zwischen den Extremen schwanken und nie zu einer stabilen Einstellung konvergieren. In solchen Fällen ist ein Plan Guide mit explizitem MIN_GRANT_PERCENT / MAX_GRANT_PERCENT oft stabiler als adaptives Feedback. Außerdem: Feedback funktioniert nur für wiederholte Abfragen im selben Plan. Bei Ad-hoc-SQL das jedes Mal anders aussieht, lernt SQL Server gar nichts. |
Fallstudie: Trendforge Digital GmbH — Der Entwickler-Albtraum
Trendforge Digital GmbH (Instanz: TFSQL01) ist das Gegenstück zu Sparfuchs. Die Hardware ist tadellos: 32 Kerne, 256 GB RAM, All-Flash-Storage mit NVMe. Der DBA hat alles richtig konfiguriert. Und trotzdem: RESOURCE_SEMAPHORE-Waits dominieren das Wait Statistics Dashboard, TempDB-IO läuft dauerhaft auf 80% Auslastung, und die Entwickler fragen verwundert warum der "neue Server" nicht schneller ist als der alte.
Die Analyse zeigt das klassische Trendforge-Muster (das wir in Kapitel 34 vollständig aufdröseln): Fehlende Indizes führen zu Full Table Scans in Join-Operationen. Der Optimizer unterschätzt die Join-Kardinalität systematisch, weil er ohne passende Indexstatistiken keine genauen Selektivitäten berechnen kann. Das Ergebnis: Memory Grants die um Faktor 20 zu klein sind — und garantierte Spills.
|
Praxisbeispiel: Trendforge: Konkrete Messwerte |
|---|
|
Bei der Analyse von TFSQL01 im Peak (12–14 Uhr) wurden folgende Werte gemessen: 847 gleichzeitig laufende User-Queries, davon 312 in RESOURCE_SEMAPHORE wartend. Durchschnittliche Grant-Wartezeit: 8,3 Sekunden. Maximum: 47 Sekunden. sys.dm_exec_query_memory_grants zeigte granted_memory_kb-Summe von 68 GB — bei theoretisch verfügbaren 90 GB Execution Memory. Der Server war schlicht zu 75% seiner Execution-Memory-Kapazität ausgelastet, weil jede der 847 Queries einen überdimensionierten Grant requirierte. Die Ursache: Genau drei Stored Procedures ohne passende Covering-Indizes, die zusammen 71% aller Grants generierten. |
Die Diagnose-Query mit sys.dm_exec_query_memory_grants hat in diesem Fall sofort die Täter geliefert: Drei Queries mit granted_memory_kb zwischen 4 GB und 18 GB pro Ausführung — bei used_memory_kb von 200 MB bis 800 MB. Die Grants waren also 5x bis 20x zu groß. Das ist kein Schätzbaufehler, das ist das Signal für einen grundlegend falschen Ausführungsplan.
-- Überestimierte Grants aufspüren: Verhältnis granted vs. used
-- Alle laufenden Queries mit starker Diskrepanz
SELECT
mg.session_id,
mg.requested_memory_kb / 1024 AS requested_mb,
mg.granted_memory_kb / 1024 AS granted_mb,
mg.used_memory_kb / 1024 AS used_mb,
-- Effizienz: wie viel des Grants wird tatsächlich genutzt?
CASE WHEN mg.granted_memory_kb > 0
THEN CAST(mg.used_memory_kb AS FLOAT)
/ mg.granted_memory_kb * 100
ELSE 100
END AS grant_effizienz_pct,
-- Verschwendeter Speicher in MB
(mg.granted_memory_kb - mg.used_memory_kb) / 1024 AS verschwendet_mb,
r.wait_type,
r.wait_time / 1000.0 AS wait_sek,
SUBSTRING(st.text, 1, 200) AS abfragetext
FROM sys.dm_exec_query_memory_grants mg
JOIN sys.dm_exec_requests r ON mg.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE mg.granted_memory_kb > 0
AND mg.used_memory_kb IS NOT NULL
-- Effizienz unter 20%: Grant ist mindestens 5x zu groß
AND (CAST(mg.used_memory_kb AS FLOAT) / mg.granted_memory_kb) < 0.2
ORDER BY verschwendet_mb DESC;
Bei Trendforge hat diese Query sofort die drei Problemkandidaten gezeigt. Der Fix war in zwei Phasen: Kurzfristig hat der DBA im Query Store einen Forced Plan mit explizitem MAX_GRANT_PERCENT = 8 aktiviert — das hat die RESOURCE_SEMAPHORE-Wartezeiten von 8,3 Sekunden auf unter 0,4 Sekunden reduziert. Nicht schön, aber sofort wirksam.
Die langfristige Lösung waren drei neue Covering-Indizes, die die Entwickler nach intensivem Gespräch mit dem DBA erstellt haben. Mit den richtigen Indizes sank die Join-Kardinalitätsschätzung von "komplett falsch" auf "akzeptabel", die Grants von 4–18 GB auf 80–400 MB, und die RESOURCE_SEMAPHORE-Waits verschwanden aus dem Top-10-Dashboard. Die vollständige Fallstudie — inklusive aller identifizierten Probleme, dem Massnahmenplan und den gemessenen Verbesserungen — findest du in Kapitel 34.
|
Hinweis: Warum der DBA hier nicht alleine helfen kann |
|---|
|
Das Trendforge-Problem ist kein Konfigurations- oder Hardware-Problem — es ist ein Entwickler-Problem. Fehlende Indizes kann der DBA zwar selber anlegen, aber in einer Umgebung mit wöchentlichen Deployments und Schema-Migrationen müssen die Entwickler die Indexstrategie mitdenken. Der DBA kann Symptome kurzfristig lindern (Grant Hints, Query Store Forced Plans), aber die eigentliche Lösung liegt im Code. Das ist der zentrale Unterschied zwischen Trendforge und den anderen beiden Fallstudien: Musterwerk und Sparfuchs kann ein einzelner DBA in den Griff bekommen. Trendforge braucht einen Kulturwandel im Entwicklungsteam. |
Zusammenfassung
Memory Grants sind SQL Servers Weg, Sort- und Hash-Operationen mit ausreichend RAM zu versorgen. Sie werden vorab reserviert, basierend auf Kardinalitätsschätzungen des Optimizers — und genau das ist der Schwachpunkt: Falsche Statistiken führen zu falschen Grants, falsche Grants führen zu Spills, Spills führen zu TempDB-IO, und TempDB-IO kostet je nach Storage-Tier zwischen 10× und 100× mehr als die In-Memory-Alternative.
Die wichtigsten Diagnose-Instrumente sind sys.dm_exec_query_memory_grants für den Live-Blick, sys.dm_exec_query_stats für historische Spill-Daten und Extended Events (sort_warning, hash_warning) für die lückenlose Aufzeichnung. RESOURCE_SEMAPHORE als Wait Type signalisiert Memory Pressure auf Systemebene.
Resource Governor ermöglicht die Isolierung verschiedener Workloads — sinnvoll, wenn Reporting und OLTP um denselben Execution Memory kämpfen. Grant Hints (MIN_GRANT_PERCENT) sind ein valides Mittel zur Schadensbegrenzung, lösen aber nicht das eigentliche Problem.
Im nächsten Kapitel schauen wir uns TempDB im Detail an — die Datenbank, in die alle Spills landen, und die gleichzeitig für temporäre Tabellen, Row-Versioning und interne Workloads zuständig ist. Wenn TempDB unter Druck steht, spürt das der gesamte SQL Server.
Kapitel 13