dtcSoftware - powerShellDba

📌 Was bedeutet „Memory Grant“?

GRUNDLAGEN

Jede Abfrage in SQL Server benötigt Arbeitsspeicher (RAM), um Operationen wie Sortierungen (ORDER BY, GROUP BY), Hash-Joins, Hash-Aggregate oder parallele Abfragen durchzuführen. Bevor die Abfrage ausgeführt wird, fordert der Optimizer einen sogenannten Memory Grant an – eine Reservierung von Arbeitsspeicher. Wenn aktuell nicht genug freier Speicher für diese Anforderung verfügbar ist, muss die Abfrage warten. Dieses Warten wird als RESOURCE_SEMAPHORE in den Wartestatistiken protokolliert. Je länger die Wartezeit, desto schlechter die Performance – und desto häufiger sehen Benutzer „Timeouts“.

💡 Wichtig: Ein hoher RESOURCE_SEMAPHORE‑Wert ist ein klares Zeichen für Speicherkonflikte. Das Problem kann sowohl durch unzureichend dimensionierten Arbeitsspeicher als auch durch ineffiziente Abfragen (die zu viel Speicher anfordern) verursacht werden.

⏱️ Der Wartetyp RESOURCE_SEMAPHORE

WAIT STATISTICS

Der Wartetyp RESOURCE_SEMAPHORE tritt auf, wenn eine Abfrage auf die Zuteilung von Arbeitsspeicher wartet. SQL Server verwendet ein Semaphor‑System, um die Speicherzuteilungen zu steuern. Jede Abfrage erhält einen bestimmten Memory Grant; wenn die Summe aller Grants das verfügbare Limit (begrenzt durch max server memory und die aktuelle Auslastung) überschreitet, müssen neue Abfragen warten.

Typische Ursachen:

🖥️ Zu geringer Arbeitsspeicher für die Workload (RAM knapp bemessen).
📈 Falsche Speichereinstellungen (max server memory zu niedrig).
🧠 Übermäßige Parallelität (MAXDOP zu hoch → jeder parallele Thread fordert eigenen Speicher an).
📉 Fehlende Indizes → Sortierungen oder Hash-Joins sind unnötig groß.
📊 Veraltete Statistiken → Der Optimizer schätzt Zeilenanzahlen falsch und beantragt zu viel oder zu wenig Speicher. Zu wenig Speicher führt zu Spills, zu viel Speicher blockiert andere Abfragen.

-- RESOURCE_SEMAPHORE Wartezeiten anzeigen
SELECT 
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  max_wait_time_ms,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE'
ORDER BY wait_time_ms DESC;

⚠️ Interpretation: Wenn die durchschnittliche Wartezeit deutlich über 100–200 ms liegt und viele wartende Tasks existieren, ist ein Speicherproblem akut. Die Signal Wait Time (die Zeit, die der Thread auf die CPU wartet) ist hier meist niedrig, weil das Problem im Speicher‑Subsystem liegt.

🔍 Aktuelle Speicherzuteilungen und Wartende finden

DMVS

Nutzen Sie die DMV sys.dm_exec_query_memory_grants, um zu sehen, welche Abfragen gerade Speicher erhalten haben und welche warten. Eine Zeile mit grant_time IS NULL bedeutet, dass die Abfrage auf einen Memory Grant wartet (RESOURCE_SEMAPHORE).

-- Alle wartenden Memory Grants
SELECT 
  session_id,
  requested_memory_kb,
  granted_memory_kb,
  required_memory_kb,
  query_cost,
  CASE WHEN grant_time IS NULL THEN 'WARTEND' ELSE 'BEWILLIGT' END AS GrantStatus
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

-- SQL-Text der wartenden Abfragen
SELECT 
  mg.session_id,
  mg.requested_memory_kb,
  st.text
FROM sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) st
WHERE mg.grant_time IS NULL;

💡 Profi-Tipp: Notieren Sie sich die session_id der wartenden Abfragen. Sie können dann den Ausführungsplan (sys.dm_exec_query_plan) abrufen und nach großen Sortier‑ oder Hash‑Operationen suchen.

🔎 Hauptursachen für Memory Grant‑Engpässe

URSACHEN

Unzureichender Arbeitsspeicher: Der physische RAM ist zu knapp bemessen. SQL Server kann nicht genug Arbeitsspeicher für alle Abfragen bereitstellen.
max server memory zu niedrig: Selbst wenn genug physischer RAM vorhanden ist, kann die Einstellung max server memory zu restriktiv sein.
Übermäßige Parallelität: Jeder parallele Thread benötigt ein eigenes Memory Grant (z. B. bei MAXDOP 8 fordert die Abfrage bis zu 8× den Speicher einer seriellen Abfrage).
Fehlende Indizes: Sortierungen oder Hash‑Joins werden notwendig, die mit einem Index vermeidbar wären.
Veraltete Statistiken: Der Optimizer unterschätzt die Zeilenanzahl massiv, bewilligt einen zu kleinen Grant → Spill in TempDB. Oder er überschätzt stark und bewilligt einen riesigen Grant, der unnötig Speicher blockiert.
Datenbankdesign: VARCHAR(max) oder sehr breite Spalten blähen die Speicheranforderung auf.

📌 Besonders kritisch: Abfragen, die große Mengen sortieren oder Hash‑Joins durchführen. Eine einzelne solche Abfrage kann den gesamten verfügbaren Speicher für sich beanspruchen und alle anderen blockieren.

🛠️ Lösung 1: max server memory richtig einstellen

KONFIGURATION

Die Einstellung max server memory ist oft die erste Stellschraube. Berechnen Sie einen sicheren Wert: Lassen Sie dem Betriebssystem mindestens 4–8 GB plus Puffer für andere Anwendungen. Eine Formel für dedizierte SQL Server: MaxServerMemory = GesamtRAM - (4 GB + (RAM * 0,05)). Beispiel bei 64 GB RAM: 64 - (4 + 3,2) = 56,8 → 56800 MB.

-- Aktuelle Speichereinstellungen anzeigen
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name = 'max server memory (MB)';

-- Anpassen (z. B. auf 32 GB)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 32768;
RECONFIGURE;

💡 Tipp: Beobachten Sie nach der Änderung die RESOURCE_SEMAPHORE-Wartezeiten. Oft verbessert sich das Bild sofort.

🔄 Lösung 2: Max Degree of Parallelism (MAXDOP) anpassen

PARALLELISMUS

Hohe Parallelität kann den Speicherbedarf einer Abfrage um ein Vielfaches erhöhen. Setzen Sie MAXDOP nicht höher als die Anzahl der logischen Kerne pro NUMA‑Knoten, aber oft ist ein Wert zwischen 4 und 8 ideal. Vermeiden Sie MAXDOP = 0 (alle Kerne) auf Systemen mit mehr als 16 Kernen.

-- MAXDOP global setzen (z. B. auf 4)
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Für eine bestimmte Abfrage (Query Hint)
SELECT ... OPTION (MAXDOP 2);

⚠️ Wichtig: Ein zu niedriger MAXDOP kann ebenfalls schaden (wenn z. B. ein Hash‑Join auf einen großen Arbeitsspeicher angewiesen ist, aber nur einen Thread nutzt). Finden Sie die Balance durch Testen.

📊 Lösung 3: Indizes und Statistiken aktualisieren

OPTIMIERUNG

Oft sind Memory Grant‑Probleme die Folge schlechter Ausführungspläne. Fehlende Indizes erzwingen Sortierungen oder Hash‑Joins. Veraltete Statistiken führen zu falschen Schätzungen und damit zu überhöhten Memory Grants.

-- Veraltete Statistiken finden
SELECT 
  OBJECT_NAME(s.object_id) AS TableName,
  s.name AS StatName,
  STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats s
WHERE STATS_DATE(s.object_id, s.stats_id) < DATEADD(day, -30, GETDATE())
ORDER BY LastUpdated;

-- Alle Statistiken aktualisieren
EXEC sp_updatestats;

-- Fehlende Indizes aus dem Plan Cache ermitteln
SELECT 
  migs.avg_total_user_cost,
  migs.avg_user_impact,
  mid.statement AS TableName,
  mid.equality_columns,
  mid.inequality_columns,
  mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

💡 Ergebnis: Bessere Pläne führen zu kleineren Memory Grants, reduzieren RESOURCE_SEMAPHORE und verbessern die Antwortzeiten.

✍️ Lösung 4: Abfragen gezielt optimieren

QUERY TUNING

Wenn Sie die speicherhungrigste Abfrage identifiziert haben (z. B. aus sys.dm_exec_query_memory_grants), versuchen Sie, sie umzuschreiben:

Vermeiden Sie SELECT * – reduzieren Sie die Spalten auf das Nötigste.
Splitten Sie große Sortierungen auf (z. B. durch Verwendung einer temporären Tabelle mit Index).
Ersetzen Sie DISTINCT und GROUP BY durch Fensterfunktionen, wenn möglich.
Setzen Sie OPTION (HASH JOIN, MERGE JOIN, LOOP JOIN), um den Join‑Typ zu steuern.
Nutzen Sie den Query Store, um frühere, bessere Pläne zu erzwingen (Plan Forcing).

-- Beispiel: Große Sortierung mit Top N begrenzen
SELECT TOP 1000 ... ORDER BY expensive_column;

-- Erzwingen eines Loop-Joins statt Hash-Join (reduziert Speicherbedarf)
SELECT ... 
FROM SmallTable s
INNER LOOP JOIN BigTable b ON s.Key = b.Key;

🚨 Vorsicht: Ein zu kleiner Memory Grant kann Spills in die TempDB verursachen, die ebenfalls Performance kosten. Die beste Lösung ist ein ausgeglichener Grant – weder zu groß noch zu klein.

⚙️ Lösung 5: Resource Governor einsetzen

ISOLIERUNG

Mit dem Resource Governor können Sie verschiedene Workloads voneinander isolieren. Sie können z. B. eine Workload-Gruppe für Ad‑hoc‑Abfragen erstellen, die nur maximal 20 % des Arbeitsspeichers erhält, um zu verhindern, dass diese den Buffer Pool leer räumen. Der Resource Governor ist in der Enterprise Edition verfügbar.

-- Ressourcenpool mit 30 % max Arbeitsspeicher
CREATE RESOURCE POOL Pool_Reporting
WITH (
  MEMORY_PERCENT = 30,
  MAX_CPU_PERCENT = 40
);
CREATE WORKLOAD GROUP Group_Reporting USING Pool_Reporting;
ALTER RESOURCE GOVERNOR RECONFIGURE;

💡 Hinweis: Nutzen Sie die Klassifizierungsfunktion, um bestimmte Anwendungen oder Benutzer in diesen Pool zu leiten.

📈 Monitoring – Frühwarnung einrichten

PRÄVENTION

Richten Sie Alerts ein, bevor RESOURCE_SEMAPHORE zum Problem wird:

Überwachen Sie den Performance‑Counter „Memory Grants Pending“ (SQLServer:Memory Manager). Ist dieser dauerhaft > 0, haben Sie aktuelle Memory Grant‑Konflikte.
Nutzen Sie das Extended Event sql_statement_recompile oder query_memory_grant_warning, um große Grants zu erkennen.
Erstellen Sie einen SQL Agent‑Job, der alle 5 Minuten prüft, ob mehr als 5 Abfragen auf einen Memory Grant warten, und senden Sie eine E‑Mail.

-- Alert: Wartende Memory Grants abfragen
IF (SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL) > 5
BEGIN
  PRINT 'Mehrere Abfragen warten auf Memory Grant';
  -- hier E-Mail-Versand einfügen
END;

📌 Best Practice: Kombinieren Sie Memory Grant‑Überwachung mit der Überwachung von RESOURCE_SEMAPHORE in sys.dm_os_wait_stats. Ein Anstieg der Wartezeit um mehr als 10 % innerhalb von 24 Stunden sollte untersucht werden.

✅ Checkliste zur Behebung von Memory Grant‑Problemen

AKTIONSPLAN

Wartestatistiken prüfen – RESOURCE_SEMAPHORE aus sys.dm_os_wait_stats auslesen.
Aktive Memory Grants analysieren – Wartende Abfragen identifizieren (grant_time IS NULL).
max server memory überprüfen und ggf. erhöhen.
MAXDOP anpassen – Testen mit niedrigeren Werten (z. B. 4 statt 0).
Statistiken aktualisieren – Besonders für Tabellen, die in den wartenden Abfragen vorkommen.
Fehlende Indizes ergänzen – Nutzen Sie die Missing Index DMVs oder den Query Store.
Abfragen umschreiben – Große Sortierungen und Hash-Joins vermeiden.
Query Store nutzen – Plan Forcing für stabile Pläne.
Resource Governor konfigurieren (falls Enterprise Edition).
Alerts für „Memory Grants Pending“ einrichten.

💡 Abschlussgedanke: Ein gut abgestimmter SQL Server sollte nur selten RESOURCE_SEMAPHORE-Wartezeiten aufweisen. Wenn doch, liegt meist ein grundlegendes Problem vor: zu wenig Arbeitsspeicher für die Workload oder massive Planregressionen.