SQL-Server-Speicherzuteilungen unter Last untersuchen

Ein Bericht kann einzeln schnell laufen und bei mehreren gleichzeitigen Benutzern eine Warteschlange auslösen. Eine mögliche Ursache ist Arbeitsspeicher für Sortierungen und Hash-Operationen. Wer nur die Dauer einer einzelnen Ausführung optimiert, übersieht möglicherweise, wie viel Speicher sie reserviert und wie lange andere Anfragen deshalb warten.

Während des Vorfalls beobachten

Erfassen Sie die Situation, solange die Berichte noch laufen. Die folgende reine Leseabfrage verbindet aktive Speicheranforderungen mit laufenden Requests. Für serverweite Sicht benötigen Sie passende Rechte: Bei SQL Server 2022 und neuer verwendet diese DMV VIEW SERVER PERFORMANCE STATE, bei früheren Versionen normalerweise VIEW SERVER STATE.

SELECT
    mg.session_id,
    mg.request_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

Der angezeigte Text gehört zum gesamten Batch. In einer Prozedur kann daher mehr SQL stehen als die verantwortliche Anweisung. Zur Eingrenzung helfen die Anweisungspositionen des Requests oder ein Ausführungsplan. Speichern Sie Sitzungs- und Request-ID gemeinsam; dieselbe Sitzung kann später bereits eine andere Anfrage ausführen.

Ein fehlender grant_time kennzeichnet eine noch wartende Anforderung. Vergleichen Sie angeforderte und zugeteilte Kilobytes mit aktueller und maximal bisher verwendeter Menge. Das sind Momentaufnahmen, keine abgeschlossene Historie. Kurz nach dem Start darf die tatsächliche Nutzung noch deutlich niedriger sein. Erfassen Sie mehrere Stichproben mit Zeitstempel und ordnen Sie sie dem Vorfall zu.

RESOURCE_SEMAPHORE verweist auf die Zulassung zur Ausführung mit Arbeitsspeicher. RESOURCE_SEMAPHORE_QUERY_COMPILE betrifft dagegen die Kompilierung. Untersuchen Sie auch bereits laufende Besitzer großer Reservierungen. Die wartende Abfrage kann Opfer einer anderen, speicherbelegenden Abfrage sein.

Drei Ursachen auseinanderhalten

Eine übergroße Zuteilung reserviert deutlich mehr als benötigt. Eine zu kleine kann Zwischenergebnisse von Sortierung oder Hash-Verarbeitung nach tempdb auslagern. Eine Warteschlange kann aber auch bei vernünftigen Einzelzuteilungen entstehen, wenn zu viele große Operationen gleichzeitig laufen. Eine einzige Kennzahl zur gesamten Speicherauslastung trennt diese Fälle nicht.

Fordert ein Beispielbericht 600 MB an und nutzt wiederholt nur 25 MB, prüfen Sie Zeilenschätzung und Breite der Daten vor den speicherintensiven Operatoren. Eine frühe Stichprobe allein beweist keine Verschwendung. Bestätigen Sie die Beobachtung im abgeschlossenen tatsächlichen Plan und mit Parametern, die große Ergebnisse erzeugen.

Bei einem Spill vergleichen Sie geschätzte und tatsächliche Eingabezeilen des betreffenden Operators. Eine unterschätzte Join-Ausgabe kann die Hash-Eingabe massiv vergrößern. Auch viele unnötige Ausgabespalten können eine Sortierung aufblähen. Eine schmalere Projektion hilft nur dann, wenn die Bedeutung der Abfrage erhalten bleibt.

Ein Index mit passender Reihenfolge kann unter Umständen die Sortierung vermeiden. Eine fachlich korrekte Voraggregation kann die Zeilenmenge vor einem Join reduzieren. Prüfen Sie dabei Schreibkosten und Ergebnisgleichheit. Eine falsche Aggregation liefert häufig plausible Summen und bleibt deshalb leicht unentdeckt.

Mit realistischer Gleichzeitigkeit prüfen

Testen Sie den tatsächlichen Berichtsmix parallel und mit repräsentativen Parametern. Messen Sie abgeschlossene Anfragen pro Minute und hohe Latenzperzentile zusätzlich zur Einzellaufzeit. Eine kleinere Reservierung kann mehr Anfragen zulassen, aber durch zusätzliche tempdb-Arbeit den Gesamtdurchsatz verschlechtern.

Memory Grant Feedback kann in unterstützten Versionen und Ausführungsmodi Zuteilungen anhand früherer Ausführungen anpassen. Verhalten und Persistenz hängen von Version und Konfiguration ab. Beobachten Sie die Wirkung, ohne daraus eine Garantie für erste Ausführungen, andere Parameterverteilungen oder neu kompilierte Pläne abzuleiten.

Beginnen Sie nicht mit pauschalen Grant-Hints oder einer allgemeinen Speichererhöhung. Klären Sie zuerst, ob Schätzfehler, Zeilenbreite, Sortierung oder übermäßige Überlappung den Engpass verursachen. Zeitlich versetzte große Exporte können wirksamer sein als kleinere Reservierungen für jede einzelne Abfrage.

Vergleichen Sie abschließend dasselbe Lastfenster: weniger wartende Grants, vertretbare Spills, stabiler Durchsatz und korrekte Ergebnisse. Bewahren Sie Pläne und Messzeitpunkte auf. Damit lässt sich nachvollziehen, ob die Änderung tatsächlich Bedarf reduziert oder das Warten nur an eine andere Stelle verschoben hat.

Technische Referenzen: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.