Memory Management:
Wer bekommt was — und wer geht leer aus
SQL Server und Speicher: Eine Liebesgeschichte mit Schattenseiten
SQL Server verhält sich bei Speicher wie ein sehr gut organisierter Hamster: Er nimmt sich so viel RAM wie er kriegen kann, gibt ihn nicht freiwillig zurück, und nutzt den gesammelten Vorrat, um möglichst wenig IO machen zu müssen. Das ist kein Bug — das ist bewusstes Design. Wer dieses Design versteht, versteht auch, warum max server memory einer der wichtigsten Konfigurationsparameter überhaupt ist, und warum "SQL Server frisst den gesamten RAM" kein Problem ist — solange das Verhältnis zum Gesamtsystem stimmt.
In den vorangegangenen Kapiteln haben wir gesehen, wie IO-Performance (Kapitel 10) und CPU-Sättigung (Kapitel 9) diagnostiziert werden. Speicher ist die dritte große Diagnoseachse — und sie hängt eng mit den anderen beiden zusammen. Zu wenig RAM führt zu mehr IO: Der Buffer Pool ist zu klein, Seiten werden ständig verdrängt und müssen von der Disk nachgeladen werden. Zu viel RAM für SQL Server führt zu OS-Druck: Windows kämpft buchstäblich um Auslagerungsspeicher. Das Gleichgewicht zu finden, ist die eigentliche Aufgabe.
Dieses Kapitel baut auf dem Architekturverständnis aus Kapitel 4 auf, wo wir den Buffer Pool und die SQLOS-Speicherverwaltung zum ersten Mal eingeführt haben. Kapitel 3 (Virtualisierung) hat beschrieben, was Memory Ballooning für den Buffer Pool bedeutet — und warum Lock Pages in Memory auf VMs gefährlich sein kann. Kapitel 5 hat max server memory als Konfigurationsparameter eingeführt. Hier gehen wir tiefer, mit konkreten DMV-Queries, Diagnosetechniken und einer vollständigen Fallanalyse am Beispiel von Sparfuchs & Partner.
Der Buffer Pool: Das Herzstück der Speicherverwaltung
Abb. 11.1: SQL Server Memory-Aufteilung: Buffer Pool, Stolen Memory, Free Memory
Der Buffer Pool ist mit Abstand der größte Speicherverbraucher in SQL Server — in einem typisch konfigurierten System belegt er 80 bis 90 Prozent des für SQL Server verfügbaren Speichers. Er speichert Datenbankseiten (je 8 KB) im RAM, damit wiederholte Zugriffe auf dieselben Daten ohne Disk-IO auskommen. Die Grundlogik ist simpel: Was einmal gelesen wurde, bleibt im Buffer Pool, bis jemand anderes diesen Platz braucht. Jeder Cache-Hit spart einen Disk-Zugriff. Auf einer modernen NVMe-SSD kostet ein Lesezugriff etwa 0,05 ms, im RAM kostet er 0,0001 ms — Faktor 500.
Wie Seiten in den Buffer Pool kommen — und wie sie wieder rausfliegen
Wenn SQL Server eine Seite braucht, die noch nicht im Buffer Pool ist, liest er sie von der Disk. Diese Seite wird im Buffer Pool abgelegt. Beim nächsten Zugriff auf dieselbe Seite ist sie bereits im RAM — kein IO nötig. Je mehr des aktiven Arbeitsbereichs (Working Set) der Workload in den Buffer Pool passt, desto weniger IO ist insgesamt nötig.
Der Buffer Pool verwaltet seine Seiten mit einem Least Recently Used (LRU)-Algorithmus. Seiten, die lange nicht benutzt wurden, wandern auf die "Kandidaten für Freigabe"-Liste. Wenn neuer Speicher benötigt wird, werden die ältesten unbenutzten Seiten freigegeben. Bei Clean Pages geht das sofort — bei Dirty Pages erst nach dem Schreiben auf die Disk.
Clean vs. Dirty Pages: Der Unterschied, der über IO entscheidet
Eine Clean Page ist eine Seite im Buffer Pool, die identisch mit der Version auf der Disk ist. Sie kann jederzeit und ohne Vorwarnung freigegeben werden — wenn sie wieder gebraucht wird, liest SQL Server sie einfach erneut von der Disk. Kein Datenverlust möglich.
Eine Dirty Page ist eine Seite, die im Buffer Pool geändert wurde, aber noch nicht auf die Disk geschrieben wurde. Die Änderung steckt nur im RAM. Sie kann nicht einfach freigegeben werden — zuerst muss sie durch einen Checkpoint-Vorgang auf die Disk geschrieben werden. SQL Server betreibt dafür zwei Hintergrundprozesse: den Checkpoint-Prozess, der in konfigurierbaren Intervallen Dirty Pages wegschreibt, und den Lazy Writer, der kontinuierlich Free Buffer auffüllt, indem er die ältesten Clean und Dirty Pages freimacht.
|
Hintergrund: Lazy Writer vs. Checkpoint: Zwei verschiedene Ziele |
|---|
|
Der Checkpoint-Prozess schreibt regelmäßig alle Dirty Pages auf die Disk — sein Ziel ist es, die Recovery-Zeit zu begrenzen. Im Falle eines Absturzes muss SQL Server nur die Änderungen seit dem letzten Checkpoint wiederholen. Der Lazy Writer hat ein anderes Ziel: Er sorgt dafür, dass im Buffer Pool immer genug freie Seiten-Slots vorhanden sind, um neue Seiten ohne Wartezeit einlagern zu können. Wenn der Lazy Writer sehr aktiv wird und Page Faults ansteigen, ist das ein Hinweis auf Memory Pressure — der Buffer Pool ist zu klein für das aktive Working Set. |
Target Memory vs. Committed Memory: Was SQL Server will und was er hat
SQL Server unterscheidet intern zwischen zwei Speichergrößen: Target Memory ist die Menge, die SQL Server haben möchte — nach oben begrenzt durch max server memory. Committed Memory ist die Menge, die SQL Server aktuell tatsächlich belegt. Nach einem Neustart ist Committed Memory klein und wächst graduell, während der Buffer Pool mit Datenbankseiten befüllt wird. Wenn beide Werte annähernd gleich sind und Free Memory nahezu null ist, ist der Buffer Pool vollständig ausgelastet. Das ist der Normalzustand auf einem gut konfigurierten System — gewollt, nicht problematisch.
Problematisch wird es in zwei Szenarien: Erstens, wenn Target Memory größer ist als der physische RAM — dann versucht SQL Server, mehr Speicher zu allokieren als vorhanden ist, mit Paging als Folge. Zweitens, wenn Committed Memory wiederholt schrumpft, obwohl Queries Seiten laden wollen — dann hat Windows SQL Server gedrängt, Speicher freizugeben, weil andere Prozesse oder das OS selbst Speicher brauchen.
-- Buffer Pool Auslastung auf einen Blick
-- Vergleich von Committed vs. Target vs. Free — der erste Memory-Healthcheck
SELECT
physical_memory_in_use_kb / 1024 AS SQL_Committed_MB,
locked_page_allocations_kb / 1024 AS LPIM_Locked_MB,
-- Page Faults: deutet auf Paging hin — schlecht für Performance
page_fault_count AS PageFaults_Total,
memory_utilization_percentage AS Auslastung_Pct
FROM sys.dm_os_process_memory;
-- Detailliertere Sicht: Target, Committed, Database Pages, Free
SELECT
-- Target = wie viel SQL Server haben möchte (begrenzt durch max server memory)
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Target pages') * 8 / 1024 AS Target_MB,
-- Database pages = tatsächlich mit Datenbankseiten belegte Buffer Pool Slots
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Database pages') * 8 / 1024 AS DatabasePages_MB,
-- Free pages = leere Buffer Pool Slots, die sofort genutzt werden können
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Free pages') * 8 / 1024 AS Free_MB;
|
Hinweis: Wie du die Werte interpretierst |
|---|
|
Target_MB ≈ Database_MB + Free_MB: Alles normal, der Buffer Pool ist voll ausgelastet. Target_MB >> Database_MB: SQL Server wächst noch — normales Verhalten nach Neustart oder nach einem großen PLE-Drop. Database_MB sinkt unter Last: OS-Druck — SQL Server gibt Seiten frei, weil Windows Speicher zurückfordert. Ursache: max server memory zu hoch gesetzt. PageFaults_Total wächst schnell: Paging findet statt — sofortige Untersuchung nötig. |
Page Life Expectancy: Der Fiebermesser des Buffer Pools
Page Life Expectancy (PLE) ist die Antwort auf die Frage: Wie lange bleibt eine Seite durchschnittlich im Buffer Pool, bevor sie wieder freigegeben werden muss? Ein hoher PLE bedeutet: Seiten verbleiben lange im Cache, wenig IO ist nötig. Ein sinkender PLE bedeutet: Der Buffer Pool dreht sich schnell, Seiten werden häufig von der Disk nachgeladen.
Die alte Faustregel "PLE über 300 ist gut" stammt aus einer Zeit, als Datenbankserver 8 bis 16 GB RAM hatten und die meisten Working Sets damit locker abgedeckt wurden. Auf einem modernen Server mit 256 GB RAM und einem 500-GB-Working-Set ist PLE 300 ein Alarmzeichen, kein Ziel. Die sinnvollere Näherungsformel:
|
Definition: PLE-Zielwert — Näherungsformel |
|---|
|
Grobe Formel: (Buffer Pool Größe in GB / 4) × 300 Sekunden. Beispiel: 64 GB Buffer Pool → (64 / 4) × 300 = 4.800 Sekunden PLE-Ziel. 128 GB Buffer Pool → (128 / 4) × 300 = 9.600 Sekunden. Wichtiger als der Absolutwert ist aber immer der Vergleich mit der eigenen Baseline. Was hat das System im Normalzustand? Und wann weicht es davon ab? Ohne Baseline ist PLE 4.800 genau so wenig aussagekräftig wie PLE 300. |
-- Page Life Expectancy abfragen — aufgeteilt nach NUMA-Node!
-- Der Gesamtwert ist bei mehreren NUMA-Nodes oft irreführend:
-- Ein überlasteter Node kann den Gesamtwert nach unten ziehen,
-- während die anderen Nodes entspannt sind.
SELECT
object_name,
counter_name,
instance_name, -- NUMA-Node: '000', '001', etc. oder '_Total'
cntr_value AS PLE_Sekunden
FROM sys.dm_os_performance_counters
WHERE
(object_name LIKE '%Buffer Manager%'
OR object_name LIKE '%Buffer Node%')
AND counter_name = 'Page life expectancy'
ORDER BY object_name, instance_name;
Besonders wichtig ist der PLE pro NUMA-Node. Wenn ein Node einen wesentlich niedrigeren PLE aufweist als die anderen, ist dieser NUMA-Node überlastet: Seine Memory-Slots werden häufig ausgewechselt, während die anderen Nodes entspannt Seiten cachen. Das deutet auf NUMA-Imbalance hin — ein Thema, das wir im nächsten Abschnitt vertiefen. Der Zusammenhang zwischen NUMA-Topologie, Buffer Pool Partitionierung und MAXDOP-Konfiguration ist eng genug, dass Kapitel 3 (Virtualisierung) und Kapitel 1 (Hardware-Grundlagen) explizit darauf eingehen.
|
Warnung: PLE-Drops: Das Signal, das du nicht ignorieren kannst |
|---|
|
Ein PLE-Drop ist ein plötzlicher, starker Rückgang des PLE — zum Beispiel von 8.000 auf 200 innerhalb weniger Minuten. Das bedeutet: Der Buffer Pool wurde größtenteils geleert. Typische Auslöser sind ein großer Table Scan (der viele neue Seiten einliest und alte verdrängt), ein externes DBCC CHECKDB, ein Backup-Job, oder Memory Pressure von außen (Windows nimmt Speicher zurück). PLE-Drops beobachtet man am besten über Zeit im Windows Performance Monitor, Counter "Page life expectancy" im Objekt SQLServer:Buffer Manager. Ein regelmäßiger Drop zu einer bestimmten Uhrzeit? Welcher Job läuft dann? |
NUMA-Memory-Management: Local vs. Foreign Access
Abb. 11.2: NUMA-Node Memory-Lokalisierung — Local vs. Remote Access
Wir haben NUMA in Kapitel 1 (Hardware-Grundlagen) und Kapitel 3 (Virtualisierung) eingeführt. Jetzt betrachten wir die Memory-Dimension: Jeder NUMA-Node hat seinen eigenen physischen Speicher. Wenn ein Thread auf dem lokalen Speicher seines NUMA-Nodes zugreift, ist die Latenz niedrig — typisch 5 bis 10 Nanosekunden. Wenn er auf den Speicher eines anderen Nodes zugreift (Foreign Access oder Remote Access), kostet das zusätzlich 30 bis 40 Nanosekunden. Das klingt nach wenig, aber bei einer Millionen-Seiten-Abfrage addiert sich das zu messbaren Latenzen.
SQL Server partitioniert den Buffer Pool NUMA-aware: Jeder NUMA-Node verwaltet seinen eigenen Teilbereich des Buffer Pools. Threads allokieren Speicher bevorzugt lokal zu ihrem NUMA-Node. Wenn ein Node mehr Speicher benötigt als lokal verfügbar ist, greift er auf den Speicher der Nachbar-Nodes zu — das nennt sich NUMA-Imbalance. Dieser Zustand ist an der Metrik foreign_committed_kb in sys.dm_os_memory_nodes ablesbar.
-- NUMA-Node Speicherauslastung — Local vs. Foreign Access
-- foreign_committed_kb deutlich > 0 zeigt NUMA-Imbalance an
SELECT
memory_node_id,
-- Lokal auf diesem NUMA-Node allokierter Speicher (schnelle Zugriffe)
pages_kb / 1024 AS Local_MB,
-- Auf anderen Nodes allokierter Speicher (langsamere Zugriffe)
foreign_committed_kb / 1024 AS Foreign_MB,
-- Verhältnis: wie viel Prozent des Speichers ist "fremd"?
CASE WHEN pages_kb > 0
THEN CAST(100.0 * foreign_committed_kb / pages_kb AS DECIMAL(5,2))
ELSE 0 END AS Foreign_Pct,
-- Virtuell reservierter Speicher (committed, aber nicht unbedingt aktiv genutzt)
virtual_memory_committed_kb / 1024 AS VirtualCommitted_MB
FROM sys.dm_os_memory_nodes
-- Node 64 ist ein interner Knoten (DAC), kein physischer NUMA-Node
WHERE memory_node_id < 64
ORDER BY memory_node_id;
Wenn Foreign_Pct dauerhaft über 10 bis 15 Prozent liegt, lohnt sich eine tiefere Untersuchung der NUMA-Balance. Die häufigste Ursache: Ein zu hoher MAXDOP verteilt einen Query auf alle NUMA-Nodes, aber die benötigten Datenseiten befinden sich überwiegend im Buffer Pool eines einzigen Nodes. Die Worker-Threads der anderen Nodes müssen dann Foreign Memory allokieren, um die Daten zu verarbeiten. Lösung: MAXDOP an die NUMA-Topologie anpassen (Kapitel 5) und Soft-NUMA-Konfiguration überprüfen (Kapitel 3).
|
Hintergrund: Soft-NUMA und Buffer Pool Partitionierung |
|---|
|
SQL Server kann auch auf Systemen ohne physische NUMA-Architektur NUMA-ähnliche Partitionen erstellen — das nennt sich Soft-NUMA. Auf einem Server mit 32 CPU-Kernen und einem NUMA-Node könnte man 4 Soft-NUMA-Nodes mit je 8 Kernen konfigurieren. Vorteil: Der Buffer Pool wird in 4 Partitionen aufgeteilt, was Sperrkonflikte auf internen Buffer-Pool-Datenstrukturen reduziert. Nachteil: Mehr Komplexität bei MAXDOP-Konfiguration. Ab SQL Server 2014 wird Soft-NUMA automatisch konfiguriert, wenn mehr als 8 logische CPUs pro NUMA-Node vorhanden sind. |
max server memory: Das wichtigste Stellrad — richtig gesetzt
max server memory (in MB) begrenzt, wie viel SQL Server insgesamt für den Buffer Pool und andere interne Strukturen allokieren darf. Der Standardwert seit SQL Server 2014 ist 2.147.483.647 MB — also effektiv unbegrenzt. Auf einem dedizierten SQL Server-Host, auf dem keine anderen schwerwiegenden Prozesse laufen, ist das vertretbar. Auf einem geteilten Host ist es eine Katastrophe in Zeitlupe.
|
Warnung: Sparfuchs & Partner: max server memory auf Standard bei 8 GB RAM |
|---|
|
max server memory war auf dem Standardwert — also "alles". Der Server hatte 8 GB physischen RAM. SQL Server belegte nach einer Stunde Laufzeit 7,4 GB davon. Was das für Windows bedeutete: 600 MB für OS-Kernel, Antivirus, SQL Server Agent, SSRS-Berichtsdienst (der ebenfalls auf demselben Host lief — ja, wirklich), und alles was Windows selbst braucht. Das Ergebnis war vorhersehbar: Windows begann aktiv SQL Server-Speicherseiten in die Auslagerungsdatei zu pagen — auf dieselbe Festplatte, auf der auch die Datenbankdateien lagen. IO-Latenzen von 312 ms p95 (Kapitel 10) hatten diesen Effekt als Teil-Ursache. Die Sofortmaßnahme — max server memory auf 5.500 MB — ließ den PLE von 140 auf 620 Sekunden steigen, obwohl weniger RAM für SQL Server verfügbar war. Kein Paging mehr, kein Kampf um Speicher: Effizienz statt Ressourcengerangel. |
Die Faustformel für max server memory auf einem dedizierten SQL Server: RAM minus max(1 GB, RAM × 10 %) für das Betriebssystem. In der Praxis sieht das so aus:
|
Gesamter RAM |
Empfohlenes max server memory |
Für Windows reserviert |
|---|---|---|
|
8 GB |
6.000 MB |
~2 GB für OS |
|
16 GB |
13.500 MB |
~2,5 GB für OS |
|
32 GB |
28.000 MB |
~4 GB für OS |
|
64 GB |
58.000 MB |
~6 GB für OS |
|
128 GB |
118.000 MB |
~10 GB für OS |
|
256 GB |
240.000 MB |
~16 GB für OS |
|
512 GB |
490.000 MB |
~22 GB für OS |
Tabelle 11.1: max server memory Richtwerte für dedizierte SQL Server-Hosts
Diese Werte sind Richtwerte, keine absoluten Wahrheiten. Auf einem Server mit SSRS, Reporting Services oder anderen speicherhungrigen Diensten muss mehr für das OS reserviert werden. Auf einem Server, der ausschließlich SQL Server betreibt und keine anderen Anwendungen hat, kann man etwas enger planen. Grundregel: Lieber großzügiger reservieren und beobachten, als zu eng konfigurieren und dann rätseln, warum Windows instabil wird.
-- max server memory prüfen und konfigurieren — ohne Neustart wirksam
-- Erst schauen was aktuell konfiguriert ist:
SELECT
name,
value_in_use AS Aktueller_Wert_MB,
description
FROM sys.configurations
WHERE name = 'max server memory (MB)';
-- Auf z.B. 118.000 MB setzen (für einen 128-GB-Server)
-- sp_configure benötigt die CONTROL SERVER-Berechtigung
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 118000;
RECONFIGURE;
-- Der neue Wert gilt sofort — kein SQL Server Neustart erforderlich.
-- SQL Server beginnt unmittelbar, überschüssigen Speicher freizugeben,
-- wenn der aktuelle Verbrauch über dem neuen Limit liegt.
|
Tipp: min server memory: Das vergessene Gegenstück |
|---|
|
Neben max server memory gibt es auch min server memory. Dieser Parameter legt fest, wie viel Speicher SQL Server mindestens behält — auch, wenn Windows mehr anfordert. Standard: 0 MB (SQL Server kann auf null sinken). In Umgebungen mit schwankender Last kann es sinnvoll sein, min server memory auf 60 bis 70 Prozent des max-Werts zu setzen, damit SQL Server nach einem Lastpeak nicht seinen gesamten Buffer Pool freigeben muss und danach wieder aufwärmen muss. Auf VMs mit dynamischem Memory ist Vorsicht geboten — min server memory und Balloon Driver können sich beißen. |
Wer belegt den Buffer Pool? sys.dm_os_buffer_descriptors
Manchmal weiß man, dass der Buffer Pool zu klein ist für das Working Set — aber man weiß nicht, wer den Platz belegt. sys.dm_os_buffer_descriptors gibt Auskunft darüber, welche Datenbank wie viele Seiten im Buffer Pool hält. Das ist nützlich, wenn man herausfinden will, welche Datenbank auf einem Shared Server den meisten Buffer Pool "stiehlt".
-- Welche Datenbank belegt wie viel Buffer Pool?
-- Wichtig auf Shared Servern mit mehreren Datenbanken
SELECT
CASE database_id
WHEN 32767 THEN 'ResourceDB (System)'
ELSE DB_NAME(database_id)
END AS Datenbank,
COUNT(*) * 8 / 1024 AS BufferPool_MB,
-- Verhältnis zum gesamten Buffer Pool in Prozent
CAST(100.0 * COUNT(*)
/ SUM(COUNT(*)) OVER ()
AS DECIMAL(5,2)) AS Anteil_Pct
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT(*) DESC;
-- Feiner: welche Objekte in einer bestimmten Datenbank belegen den meisten Platz?
SELECT TOP 20
OBJECT_NAME(p.object_id) AS Tabelle,
i.name AS Index_Name,
i.type_desc AS Index_Typ,
COUNT(*) * 8 / 1024 AS BufferPool_MB
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
GROUP BY p.object_id, i.name, i.type_desc
ORDER BY COUNT(*) DESC;
Diese Abfrage ist besonders hilfreich, wenn ein PLE-Drop regelmäßig auftritt und man herausfinden will, welches Objekt dabei den Buffer Pool leert. Eine große Tabelle ohne passenden Index, die bei einem nächtlichen Report komplett gescannt wird, füllt den Buffer Pool mit Seiten, die danach nie mehr gebraucht werden — und verdrängt dabei die wertvollen Hot-Data-Seiten des OLTP-Betriebs.
Memory Clerks: Wer verbraucht was außerhalb des Buffer Pools?
Nicht alles, was SQL Server an Speicher verbraucht, ist Buffer Pool. Der sogenannte "Stolen Memory" umfasst alle Speicherallokationen außerhalb des Buffer Pool-Caches: Plan Cache, Lock Manager, CLR-Laufzeit, XML-Parser, Connection Memory und viele weitere Strukturen. sys.dm_os_memory_clerks zeigt alle diese Verbraucher mit ihrem aktuellen Speicherverbrauch:
-- Top Memory Clerks nach Speicherverbrauch
-- Zeigt wer außerhalb des Buffer Pools Speicher verbraucht
SELECT TOP 20
type AS MemoryClerk,
-- pages_kb = direkt allokierte Seiten (der Hauptverbraucher)
SUM(pages_kb) / 1024 AS Pages_MB,
-- virtual_memory = reservierter, aber nicht unbedingt belegter Adressraum
SUM(virtual_memory_committed_kb) / 1024 AS VirtualMem_MB,
COUNT(*) AS Anzahl_Instanzen
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(pages_kb) DESC;
|
Memory Clerk |
Was er verwaltet |
Alarm, wenn… |
|---|---|---|
|
MEMORYCLERK_SQLBUFFERPOOL |
Buffer Pool (Datenbankseiten) |
Liegt immer oben — das ist normal |
|
MEMORYCLERK_SQLQUERYPLAN |
Plan Cache (Ausführungspläne) |
Wächst unkontrolliert → Plan Cache Bloat |
|
OBJECTSTORE_LOCK_MANAGER |
Lock-Strukturen für Sperren |
Hoch bei sehr vielen gleichzeitigen Locks |
|
CACHESTORE_SQLCP |
Ad-hoc-Query-Pläne |
Groß bei vielen nicht-parametrisierten Queries |
|
CACHESTORE_OBJCP |
Stored Procedure Plan Cache |
Hoch bei sehr vielen verschiedenen Prozeduren |
|
MEMORYCLERK_CLR |
CLR-Assemblies (.NET-Code in SQL) |
Hoch, wenn CLR-Integration intensiv genutzt |
|
MEMORYCLERK_SQLGENERAL |
Allgemeine interne Strukturen |
Divers, meist harmlos unter 100 MB |
Tabelle 11.2: Wichtige Memory Clerks und ihre Bedeutung
Ein typisches Problemszenario: CACHESTORE_SQLCP wächst auf mehrere GB. Das passiert, wenn Applikationen viele Ad-hoc-Queries ohne Parameter senden — jede leicht unterschiedliche Query bekommt einen eigenen Plan-Cache-Eintrag. SQL Server cached diese Pläne, weil sie möglicherweise wiederverwendet werden. In der Praxis werden sie das nie. Das Plan-Cache-Aufblähproblem ist eng mit Kapitel 18 (Parameter Sniffing & Plan Cache) verbunden, wo wir auch "Optimize for Ad-hoc Workloads" als Gegenmittel vorstellen.
Memory Pressure erkennen: Die Warnsignale im Überblick
Memory Pressure ist kein binärer Zustand. Sie entwickelt sich graduell — und, wenn man die frühen Signale kennt, kann man eingreifen, bevor der Server in die Knie geht. Hier sind die wichtigsten Indikatoren, von offensichtlich bis subtil:
Signal 1: PLE fällt und fällt
Der deutlichste Indikator: PLE sinkt kontinuierlich oder zeigt regelmäßige Einbrüche. Wenn PLE dauerhaft unter einem Viertel des berechneten Zielwerts liegt, ist der Buffer Pool nicht groß genug für das Working Set. Mögliche Lösungen: mehr RAM, bessere Indizes (weniger Seiten müssen gelesen werden), oder Workload-Trennung via Resource Governor.
Signal 2: Buffer Pool schrumpft unter Last
Im Performance Monitor: der Counter "Database pages" (SQLServer:Buffer Manager) sinkt, obwohl der Server unter hoher Last steht. Das bedeutet: SQL Server gibt Seiten aus dem Buffer Pool zurück — weil Windows Speicher anfordert. Klassisches Symptom für zu hohes max server memory.
Signal 3: RESOURCE_SEMAPHORE in den Wait Stats
Den Wait Type RESOURCE_SEMAPHORE kennen wir aus Kapitel 9 bereits. Er entsteht, wenn Abfragen auf Memory Grants warten — auf den Speicher für Sortierungen und Hash Joins. Wenn dieser Wait Type prominent in sys.dm_os_wait_stats auftaucht, ist der Memory-Grant-Pool erschöpft. Das muss nicht bedeuten, dass zu wenig RAM vorhanden ist — oft liegt es an schlechten Kardinalitätsschätzungen, die zu viel Memory anfordern. Mehr dazu in Kapitel 12 (Memory Grants und Spills).
Signal 4: SQL Server Error Log und Ring Buffer
Im SQL Server Error Log taucht die Meldung "A significant part of sql server process memory has been paged out" auf — das ist ein sehr ernstes Signal. SQL Server hat intern einen Resource Monitor, der auf Windows Memory Notifications reagiert. Diese Meldung bedeutet: Windows hat aktiv SQL Server-Speicherseiten in die Auslagerungsdatei geschrieben. Die Auswirkungen auf die Performance sind dramatisch, besonders, wenn die Auslagerungsdatei auf langsamen Datenträgern liegt.
-- Memory Pressure Diagnose — kombinierte Sicht in einer Abfrage
SELECT
-- Aktueller PLE (Page Life Expectancy)
(SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Page life expectancy') AS PLE_Sekunden,
-- Buffer Pool Größe in MB
(SELECT cntr_value * 8 / 1024
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Database pages') AS BufferPool_MB,
-- Page Fault Count — wächst bei Paging
(SELECT page_fault_count
FROM sys.dm_os_process_memory) AS PageFaults,
-- Gibt an ob Windows SQL Server Memory-Druck signalisiert hat
(SELECT memory_utilization_percentage
FROM sys.dm_os_process_memory) AS Auslastung_Pct,
-- Wie viel SQL Server aktuell committet hat
(SELECT physical_memory_in_use_kb / 1024
FROM sys.dm_os_process_memory) AS Committed_MB;
Lock Pages in Memory (LPIM) und Large Pages
Lock Pages in Memory: Schild gegen Windows-Paging
Lock Pages in Memory (LPIM) ist eine Windows-Berechtigung (SE_LOCK_MEMORY_PRIVILEGE), die verhindert, dass das Betriebssystem SQL Server-Speicherseiten in die Auslagerungsdatei auslagert. Wenn LPIM aktiv ist, kann Windows den Buffer Pool nicht "wegpagen" — auch bei extremem OS-Druck nicht. SQL Server behält seinen Speicher unter allen Umständen.
Ab SQL Server 2012 aktiviert sich LPIM automatisch, wenn das SQL Server-Dienstkonto die SE_LOCK_MEMORY_PRIVILEGE-Berechtigung besitzt und AWE aktiviert ist (auf 64-Bit-Systemen standardmäßig der Fall). Man kann über sys.dm_os_process_memory prüfen, ob LPIM aktiv ist: locked_page_allocations_kb > 0 zeigt es an.
|
Warnung: LPIM auf VMs: Ein gefährliches Spiel |
|---|
|
Auf virtuellen Maschinen mit dynamischem Memory oder VMware Balloon Driver ist LPIM hochgefährlich. Normaler Ablauf ohne LPIM: Der Hypervisor fordert Speicher von der VM zurück, der Balloon Driver signalisiert Windows, Windows drängt SQL Server, SQL Server gibt Seiten frei. Alle arbeiten zusammen. Mit LPIM: SQL Server verweigert die Speicherfreigabe. Windows hat kein eigenes RAM mehr. Im Worst Case friert der gesamte VM-Host ein, weil der Hypervisor den Speicher nicht neu verteilen kann. LPIM auf VMs: Nur in Kombination mit statischem (reserviertem) VM-Memory sinnvoll. Kapitel 3 geht auf dieses Thema ausführlicher ein. |
Large Pages (Trace Flag 834)
Windows unterstützt neben Standard-4-KB-Speicherseiten auch Large Pages (2 MB auf x64). SQL Server kann Large Pages nutzen, wenn LPIM aktiviert ist und Trace Flag 834 gesetzt wurde. Der Vorteil: Der Translation Lookaside Buffer (TLB) muss weniger Page-Table-Einträge verwalten, was den TLB-Miss-Overhead reduziert. Auf speicherbandbreiten-intensiven Workloads (große In-Memory-Sorts, Buffer-Pool-intensive OLAP-Queries) ist ein Performance-Gewinn von 1 bis 3 Prozent messbar.
Einschränkung: Large Pages erfordern zusammenhängenden physischen Speicher in 2-MB-Blöcken. Nach längerer Laufzeit oder mit einem fragmentierten Adressraum kann die Allokation fehlschlagen — SQL Server fällt dann auf 4-KB-Seiten zurück, ohne Fehlermeldung. In der Praxis wird TF 834 selten eingesetzt: Der Nutzen ist gering, die Komplikationen durch Speicherfragmentierung können ärgerlich sein.
Buffer Pool Extension (BPE): SSD als verlängerter RAM
SQL Server 2014 führte die Buffer Pool Extension (BPE) ein: Eine SSD kann als erweiterter Buffer Pool verwendet werden. Clean Pages (also Seiten ohne ausstehende Änderungen), die aus dem RAM-Buffer-Pool verdrängt werden, werden dabei nicht auf die Datenbankdaten-Disk geschrieben, sondern auf die SSD-Extension. Bei erneutem Bedarf werden sie von der schnellen SSD statt von der langsamen Datenbankdisk gelesen.
-- Buffer Pool Extension konfigurieren
-- Voraussetzung: SQL Server Standard Edition (Enterprise profitiert durch mehr RAM)
-- Die BPE-Datei sollte auf einer dedizierten schnellen SSD liegen
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\BPE\sqlserver_bpe.BPE',
-- Empfehlung: 4-8x der aktuellen Buffer Pool Größe
SIZE = 32 GB);
-- Status prüfen
SELECT
path,
file_size_in_kb / 1024 AS Datei_MB,
state_description,
current_size_in_kb / 1024 AS Aktuell_MB
FROM sys.dm_os_buffer_pool_extension_configuration;
|
Hinweis: BPE: Wann sinnvoll, wann nicht? |
|---|
|
BPE ist sinnvoll für: SQL Server Standard Edition auf einem System mit zu wenig RAM, read-heavy OLTP-Workloads, Datenbanken mit kalten und heißen Datenbereichen (die kalten Seiten landen in BPE, die heißen bleiben im RAM). BPE ist nicht sinnvoll für: SQL Server Enterprise Edition (hier: einfach mehr RAM kaufen, das ist effizienter), write-heavy Workloads (Dirty Pages gehen nicht in die BPE), Systeme, auf denen die SSD bereits für Datenbankdateien stark ausgelastet ist. |
In-Memory OLTP (Hekaton): Wenn der Buffer Pool außen vor bleibt
Memory-optimierte Tabellen (In-Memory OLTP, intern bekannt als "Hekaton") folgen einer völlig anderen Speicherarchitektur als disk-basierte Tabellen. Ihre Daten werden nicht im Buffer Pool gehalten — sie liegen im XTP-Speicher, einem eigenen Memory-Bereich außerhalb des Buffer Pools. Das bedeutet: Der max server memory-Grenzwert schließt XTP-Speicher nicht ein. Auf einem Server mit vielen In-Memory-Tabellen kann der tatsächliche Speicherverbrauch von SQL Server die max server memory-Einstellung deutlich überschreiten.
In-Memory OLTP ist ein umfangreiches Thema mit eigenen Trade-offs (keine ALTER TABLE ohne Neuerstellung der Tabelle, eingeschränkte T-SQL-Unterstützung in nativ kompilierten Prozeduren). Eine vollständige Behandlung würde den Rahmen dieses Kapitels sprengen. Was hier zählt: Wer In-Memory-Tabellen einsetzt, muss den XTP-Speicherverbrauch separat beobachten und in die Speicherplanung einbeziehen.
-- XTP (In-Memory OLTP) Speicherverbrauch prüfen
-- Wichtig: dieser Speicher wird NICHT durch max server memory begrenzt
SELECT
memory_consumer_type_desc,
-- Gesamter allokierter XTP-Speicher in MB
SUM(allocated_bytes) / 1048576 AS Allokiert_MB,
SUM(used_bytes) / 1048576 AS Genutzt_MB
FROM sys.dm_db_xtp_memory_consumers
GROUP BY memory_consumer_type_desc
ORDER BY SUM(allocated_bytes) DESC;
Fallstudie Musterwerk GmbH (MWSQL01): Der gut konfigurierte Normalfall
Musterwerk GmbH ist das Gegenstück zu Sparfuchs. Hier ist die Grundkonfiguration vernünftig: 64 GB RAM, max server memory auf 58.000 MB gesetzt, dedizierter SQL Server-Host ohne konkurrierende Dienste. Der Buffer Pool belegt stabil zwischen 50 und 56 GB, PLE liegt im Tagesbetrieb bei 2.000 bis 4.500 Sekunden — gut über dem Zielwert von 4.800 Sekunden (64 / 4 × 300) für diesen Server.
Das Problem bei Musterwerk ist subtiler: Jeden Nacht um 02:30 Uhr läuft ein Reportjob, der mehrere große Tabellen komplett scannt. In diesen 45 Minuten fällt der PLE von typisch 3.200 auf unter 400 — ein klassischer PLE-Drop durch einen Table Scan, der den Buffer Pool mit Seiten füllt, die nach dem Report nie mehr gebraucht werden. Am nächsten Morgen, wenn die OLTP-Benutzer einloggen, ist der Buffer Pool leer und muss sich erst wieder mit den Hot-Data-Seiten füllen. Die ersten 20 bis 30 Minuten nach Arbeitsbeginn sind für die Benutzer spürbar langsamer als gewohnt.
Die Diagnose war eindeutig: sys.dm_os_buffer_descriptors zeigte, dass der Reportjob eine einzige 40-GB-Tabelle fast vollständig in den Buffer Pool lud — und dabei die wertvollen OLTP-Seiten verdrängte. Eine vollständige Analyse mit Messungen und Maßnahmenplan findet sich in Kapitel 32.
Lösung bei Musterwerk: Zwei Maßnahmen in Kombination. Erstens: Resource Governor mit einem separaten Resource Pool für den Report-Workload, mit einer Memory-Obergrenze von 25 %. Der Reportjob bekommt damit weniger Buffer Pool-Zugriff und läuft etwas länger — aber der OLTP-Buffer-Pool bleibt intakt. Zweitens: Einen fehlenden Index auf der am häufigsten gescannten Reporttabelle hinzufügen, was den Table Scan in einen Index Seek verwandelt und die eingelesene Datenmenge um 90 % reduziert. Ergebnis: PLE-Drop von vormals 400 auf 1.800 — immer noch ein Drop, aber kein komplettes Leeren des Caches mehr.
|
Praxisbeispiel: Resource Governor für Memory-Trennung |
|---|
|
Resource Governor (verfügbar in Enterprise und Standard ab SQL Server 2016 SP1) ermöglicht es, Workloads verschiedenen Resource Pools zuzuweisen, die jeweils begrenzte Anteile an Memory, CPU und IO bekommen. Für das Musterwerk-Szenario: Ein "ReportPool" mit MAX_MEMORY_PERCENT = 25 verhindert, dass der Reportjob mehr als 25 % des Buffer Pools beanspruchen darf. Die heißen OLTP-Seiten bleiben im Cache. Kapitel 12 (Memory Grants und Spills) zeigt Resource Governor für Memory Grants im Detail. |
Diagnose-Kästen: Memory Management
Kasten 1 — Symptome
|
Hinweis: Woran erkennst du Memory-Probleme? |
|---|
|
PLE sinkt dauerhaft oder zeigt regelmäßige Einbrüche zu bestimmten Uhrzeiten |
|
PAGEIOLATCH_SH in den Wait Stats hoch trotz scheinbar genug RAM — Working Set zu groß für Buffer Pool |
|
RESOURCE_SEMAPHORE in den Wait Stats — Memory Grants im Stau (Details: Kapitel 12) |
|
Performance Monitor: "Database pages" sinkt unter hoher Last → Buffer Pool wird aktiv verkleinert |
|
page_fault_count in sys.dm_os_process_memory wächst schnell → Paging findet statt |
|
SQL Server Error Log: "A significant part of sql server process memory has been paged out" |
|
max server memory steht noch auf dem Standardwert (2.147.483.647 MB) |
|
Anwender berichten über langsame Performance vor allem am frühen Morgen (Buffer Pool nach Nachtjob leer) |
Kasten 2 — So misst du das
|
Tipp: Memory-Sofortdiagnose in drei Schritten |
|---|
|
Schritt 1 — PLE prüfen: SELECT aus sys.dm_os_performance_counters für "Page life expectancy" aufgeteilt nach NUMA-Node. Niedriger Wert auf einem einzelnen Node = NUMA-Imbalance. |
|
Schritt 2 — Memory Clerks: sys.dm_os_memory_clerks TOP-10 — unerwartete Speicherfresser identifizieren. CACHESTORE_SQLCP groß? Plan Cache aufgebläht. |
|
Schritt 3 — NUMA-Balance: sys.dm_os_memory_nodes — foreign_committed_kb deutlich > 0 auf einem Node? MAXDOP und Soft-NUMA-Konfiguration prüfen. |
-- Memory Health Snapshot — alles auf einmal
SELECT
-- PLE aus dem Buffer Manager Objekt (Gesamtwert)
(SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
AND counter_name = 'Page life expectancy') AS PLE_Gesamt,
-- max server memory Einstellung
(SELECT CAST(value_in_use AS BIGINT)
FROM sys.configurations
WHERE name = 'max server memory (MB)') AS MaxMem_MB,
-- Tatsächlich committed
physical_memory_in_use_kb / 1024 AS Committed_MB,
-- LPIM aktiv?
CASE WHEN locked_page_allocations_kb > 0
THEN 'Ja' ELSE 'Nein' END AS LPIM_Aktiv,
-- Page Faults (Paging-Indikator)
page_fault_count AS PageFaults
FROM sys.dm_os_process_memory;
Kasten 3 — Typische Fehlinterpretationen
|
Warnung: Memory-Diagnose-Fallen |
|---|
|
SQL Server verbraucht fast den gesamten RAM → Kein Problem. Das ist normales Verhalten. SQL Server gibt Speicher frei, wenn Windows ihn anfordert (solange LPIM nicht aktiv ist). Ein fast voller Buffer Pool ist ein effizient arbeitender Buffer Pool. |
|
PLE 300 ist der Schwellwert → Veraltete Faustregel aus der 8-GB-RAM-Ära. Auf einem 128-GB-System ist PLE 300 ein ernstes Problem. Die Formel (RAM_GB / 4) × 300 gibt einen besseren Richtwert. |
|
NUMA-PLE ist der Durchschnitt aller Nodes → Nein. Wenn Node 0 PLE 8.000 hat und Node 1 PLE 200, liegt ein ernstes Problem vor — das zeigt der Durchschnitt nicht. |
|
Mehr RAM löst alle Memory-Probleme → Nicht zwingend. Ein fehlender Index, der einen Full Table Scan auslöst, leert auch den Buffer Pool eines 512-GB-Servers. Erst Ursache analysieren, dann Maßnahme. |
|
RESOURCE_SEMAPHORE = zu wenig RAM → Nicht unbedingt. RESOURCE_SEMAPHORE wird auch durch schlechte Kardinalitätsschätzungen ausgelöst, die zu überhöhten Memory Grants führen. Kapitel 12 klärt das. |
Kasten 4 — Erste Gegenmaßnahmen
|
Tipp: Memory-Triage — sofort umsetzbar |
|---|
|
1. max server memory prüfen und korrekt setzen — das ist die erste und wichtigste Maßnahme bei jedem Memory-Problem. Tabelle 11.1 gibt Richtwerte. |
|
2. PLE-Baseline aufbauen: Performance Monitor für "Page life expectancy" aktivieren und 24 Stunden aufzeichnen. Muster erkennen: Wann sind die Einbrüche? Welcher Job läuft dann? |
|
3. Memory Clerks analysieren: Wenn Plan Cache (CACHESTORE_SQLCP) überproportional groß ist → "Optimize for Ad hoc Workloads" aktivieren (sp_configure, Kapitel 18). |
|
4. NUMA-Node PLE vergleichen: Wenn ein Node signifikant niedrigeren PLE hat → MAXDOP und CPU-Affinität prüfen (Kapitel 5), Soft-NUMA-Konfiguration überprüfen (Kapitel 3). |
|
5. Buffer Pool Inhalt analysieren: sys.dm_os_buffer_descriptors zeigt wer den Pool belegt. Großer Table Scan eines Nachtjobs? Resource Governor oder besser geeigneter Index als Lösung. |
Zusammenfassung
Memory Management ist die dritte große Diagnoseachse neben IO und CPU. Der Buffer Pool ist das Herzstück — er spart IO, indem er Datenbankseiten im RAM hält. Page Life Expectancy ist der wichtigste einzelne Indikator für die Buffer Pool-Gesundheit. Aber wie immer gilt: Ein Wert allein ohne Baseline sagt wenig. Ein PLE von 2.000 ist hervorragend für einen Server mit 16 GB RAM — und möglicherweise bedenklich für einen Server mit 256 GB, der eine 2-TB-Datenbank verwaltet.
Das nächste Kapitel behandelt Memory Grants und Spills — was passiert, wenn eine Abfrage mehr Speicher für Sortierungen und Hash Joins anfordert als SQL Server gewähren kann, wie sich das in RESOURCE_SEMAPHORE-Waits und TempDB-Spills äußert, und wie Resource Governor dabei helfen kann, die Situation zu kontrollieren. Ein Kapitel, das enger mit Memory Management zusammenhängt als es auf den ersten Blick scheint.
Kapitel 12