Ein Thema, was auch für mich nicht ganz einfach zu verstehen ist und zu dem es bei Microsoft aber auch einiges an Konfusion und Erklärungsbedarf gibt, ist die Verwaltung des Speichers für SQL Server.

Angefangen hat das Thema mit dieser Fehlermeldung: A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Sucht man nun hiermit im Web, so findet man unzählige Seiten mit Erklärungsversuchen und Workarounds. Das Problem tritt vornehmlich auf 64-Bit-Systemen auf und was oft nicht erwähnt wird, wird Windows 2003 als Betriebssystem verwendet. Sucht man in der KB, so findet man schnell diesen Artikel: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server.

Der dort vorgeschlagene Workaround, nämlich das Recht Lock pages in memory zu vergeben, führt zu weiterer Konfusion.
Einhergehend hiermit sollte bei Windows 2003 auch noch erwähnt werden, dass die Einstellung für max server memory auf jeden Fall gesetzt werden sollte, da es sonst zu Problemen kommen kann. Bei Windows 2008 scheint das Zusammenspiel von OS und SQL Server zu funktionieren, so dass hier diese Einstellung nicht notwendig ist. Leider differenziert der KB-Artikel in der aktuellen Version aber nicht ausreichend zwischen den OS und was genau bei Lock pages in memory passiert, ist hier auch nicht erklärt.

Die Details stammen von Bob Ward und erklären die Reaktionen des SQL Server.
"Locked pages" bedeutet, dass die Speicherverwendung von SQL Server (für den Buffer-Pool und in SQL 2008 R2 für die Log Buffer) nicht Bestandteil des "working set" sind und demzufolge das Betriebssystem diese Seiten nicht auf Platte auslagern kann. Das soll der Zusatz "locked" bedeuten. "Locked" bedeutet also, dass das OS diese Seiten nicht auslagern kann. Aber natürlich ist der SQL Server so designed, dass er auf die Anforderungen des Betriebssystems reagiert, wenn es Memory-Notstand (memory pressure) auf der Maschine gibt. Wenn SQL Server benachrichtigt wird, fährt er seine Anforderungen zurück, was auch Speicher aus dem Buffer-Pool betreffen kann. Das Verhalten ist identisch zu dem, wenn das Limit von einem manuell gesetzten Wert für "max server memory" erreicht wird. SQL Server schraubt einfach seinen Bedarf zurück.
Wie funktioniert das nun? Es werden ungenutzte Datenbank-Seiten freigegeben oder "Dirty-Pages" auf Platte geschrieben und danach freigegeben. Es werden auch Teile des Procedure-Cache freigegeben. Das alles funktioniert auch dann, wenn der Buffer Pool "locked pages" verwendet. "Lock" bedeutet also nicht, dass der SQL Server seinen Speicher nicht freigibt wenn der Memory-Pressure auftritt. Es bedeutet nur, dass das OS diese Seiten nicht auf Platte auslagern kann, da sie nicht Bestandteil des "Working-Sets" sind.

Verwendet man aber mehrere Instanzen auf einem Server, so ist auch bei Windows 2008 natürlich die Einstellung für "max server memory" vorzunehmen, um die Instanzen individuell zu konfigurieren.

Wie kann ich den Page-File beobachten?
Ich verwende hierzu den Performance-Monitor und dort die Counter:

• Paging File - % Usage
• Paging File - % Usage Peak

Für den Speicher verwende ich auf 64-Bit-Systemen in der Regel die Einstellung
"Max Server Memory " = Physical Memory - 4 GB
Damit bleibt noch genügend Speicher für das OS und weitere Prozesse, wie z. B. zusätzliche Sicherungssoftware für den Server und Virenscanner.
Auch nach einer Umkonfiguration der "max server memory" Option SQL Servers, dauert es durchaus noch eine ganze Weile, bis die Verwendung des Page-Files wieder zurückgeht.