Konfigurationsoptionen für den Serverarbeitsspeicher

Anwendungsbereich: JaSQL Server (alle unterstützten Versionen)

Konfigurieren Sie die von einer Instanz von SQL Server verwendete Arbeitsspeichermenge (in Megabyte) für einen SQL Server-Prozess neu. Es gibt die beiden Serverarbeitsspeicheroptionen min server memory und max server memory. Durch diese Optionen wird die Arbeitsspeichermenge geändert, die SQL Server-Prozessen vom Speicher-Manager von SQL Server zugewiesen wird.

Die Standardeinstellungen und die minimal zulässigen Werte für diese Optionen sind folgende:

Option Standard Minimal zulässig
Min. Serverarbeitsspeicher 0 0
Max. Serverarbeitsspeicher 2.147.483.647 Megabytes (MB). 128 MB

Standardmäßig können die Arbeitsspeicheranforderungen von SQL Server anhand der verfügbaren Systemressourcen dynamisch geändert werden. Weitere Informationen finden Sie unter Dynamische Arbeitsspeicherverwaltung.

Wichtig

Die Festlegung von max server memory auf einen zu hohen Wert kann dazu führen, dass eine einzelne Instanz von SQL Server möglicherweise mit anderen SQL Server-Instanzen um Arbeitsspeicher konkurrieren muss, die auf dem gleichen Host aufgeführt werden. Die Festlegung auf einen zu niedrigen Wert kann jedoch zu erheblichem Arbeitsspeichermangel und entsprechenden Leistungsproblemen führen. Das Festlegen von Max. Serverarbeitsspeicher auf den Minimalwert kann sogar den Start von SQL Server verhindern. Wenn SQL Server nach dem Ändern dieser Option nicht gestartet werden kann, müssen Sie den Start mithilfe der Startoption -f durchführen und die Option Max. Serverarbeitsspeicher auf ihren vorherigen Wert zurücksetzen. Weitere Informationen finden Sie unter Startoptionen für den Datenbank-Engine-Dienst.

SQL Server kann Arbeitsspeicher dynamisch verwenden. Sie können die Speicheroptionen jedoch auch manuell festlegen und den Umfang des für SQL Server zugreifbaren Arbeitsspeichers einschränken. Bevor Sie den Umfang des Arbeitsspeichers für SQL Server festlegen, sollten Sie die geeignete Arbeitsspeichereinstellung ermitteln. Ziehen Sie dazu vom gesamten physischen Speicher den Arbeitsspeicher ab, der für das Betriebssystem, für nicht durch die Einstellung „max_server_memory“ gesteuerte Speicherbelegungen und für alle weiteren Instanzen von SQL Server erforderlich ist. (Falls der Computer nicht vollständig für SQL Server reserviert ist, müssen Sie zusätzlich auch den für andere Verwendungen des Systems benötigten Arbeitsspeicher abziehen.) Die Differenz entspricht der maximalen Arbeitsspeichergröße, die Sie der aktuellen SQL Server-Instanz zuweisen können.

Manuelles Festlegen der Optionen

Sie können die Serveroptionen Min. Serverarbeitsspeicher und Max. Serverarbeitsspeicher so festlegen, dass ein großer Bereich von Arbeitsspeicherwerten überdeckt wird. Diese Methode ist vor allem dann sinnvoll, wenn der System- oder Datenbankadministrator eine Instanz von SQL Server in Abhängigkeit von den Arbeitsspeicheranforderungen anderer Anwendungen oder weiterer Instanzen von SQL Server, die auf demselben Computer ausgeführt werden, konfigurieren möchte.

Hinweis

Bei min server memory und max server memory handelt es sich um erweiterte Optionen. Wenn Sie diese Einstellungen mithilfe der gespeicherten Systemprozedur sp_configure ändern, können Sie diese nur ändern, wenn Erweiterte Optionen anzeigen auf 1 festgelegt ist. Diese Einstellungen treten sofort ohne Neustart des Servers in Kraft.

Mithilfe der Konfigurationsoption min_server_memory wird sichergestellt, dass für den SQL Server-Speicher-Manager einer Instanz von SQL Server eine Mindestmenge an Arbeitsspeicher verfügbar ist. SQL Server Allerdings wird die unter Min. Serverarbeitsspeicher angegebene Arbeitsspeichermenge von nicht gleich beim Start zugeordnet. Sobald der Wert für die Speicherauslastung aufgrund der Clientauslastung erreicht ist, kann SQL Server nur dann Arbeitsspeicher freigeben, wenn der Wert für Min. Serverarbeitsspeicher reduziert wird. Wenn beispielsweise mehrere Instanzen von SQL Server gleichzeitig auf dem gleichen Host ausgeführt werden können, legen Sie den Parameter „min_server_memory“ anstelle von „max_server_memory“ fest, um Arbeitsspeicher für eine Instanz zu reservieren. Ferner ist das Festlegen eines Werts für „min_server_memory“ in einer virtualisierten Umgebung entscheidend, um sicherzustellen, dass Arbeitsspeichermangel beim zugrundeliegenden Host nicht zu dem Versuch führt, Arbeitsspeicher aus dem Pufferpool eines virtuellen SQL Server-Gastcomputers jenseits dessen abzuzweigen, was für eine vertretbare Leistung erforderlich ist.

Hinweis

Allerdings kann nicht sichergestellt werden, dass SQL Server die in min server memory angegebene Arbeitsspeichermenge zuordnet. Wenn die in min server memory angegebene Arbeitsspeichermenge aufgrund der Serverlast zu keinem Zeitpunkt zugeordnet werden muss, wird SQL Server mit weniger Arbeitsspeicher ausgeführt.

Verwenden Sie max_server_memory, um sicherzustellen, dass beim Betriebssystem kein nachteiliger Arbeitsspeichermangel eintritt. Um den maximalen Serverarbeitsspeicher zu konfigurieren, überwachen Sie den Gesamtverbrauch des SQL Server-Prozesses, um die Arbeitsspeicheranforderungen zu bestimmen. Hier folgen genauere Angaben für diese Berechnungen für eine Einzelinstanz:

  • Reservieren Sie vom gesamten Arbeitsspeicher des Betriebssystems 1 GB–4 GB für das Betriebssystem selbst.
  • Subtrahieren Sie anschließend das Äquivalent der potenziellen SQL Server-Arbeitsspeicherbelegungen außerhalb des Steuerelements Max. Serverarbeitsspeicher, bestehend aus Stapelgröße 1 * max. Anzahl von berechneten Arbeitsthreads 2 . Der Rest sollte die Einstellung „max_server_memory“ für die Einrichtung einer einzelnen Instanz bilden.

1 Informationen zu den Threadstapelgrößen der einzelnen Architekturen finden Sie im Handbuch zur Architektur der Speicherverwaltung.

2 Informationen zu den standardmäßig berechneten Arbeitsthreads für eine bestimmte Anzahl kategorisierter CPUs auf dem aktuellen Host finden Sie auf der Dokumentationsseite zum Konfigurieren der Serverkonfigurationsoption Maximale Anzahl von Arbeitsthreads.

Verwenden Sie SQL Server Management Studio

Mit den beiden Arbeitsspeicheroptionen für den Server, Min. Serverarbeitsspeicher und Max. Serverarbeitsspeicher, können Sie den vom SQL Server-Speicher-Manager für eine Instanz von SQL Server verwalteten Umfang des Arbeitsspeichers (in MB) umkonfigurieren. Standardmäßig können die Arbeitsspeicheranforderungen von SQL Server anhand der verfügbaren Systemressourcen dynamisch geändert werden.

So legen Sie eine feste Arbeitsspeichergröße fest:

  1. Klicken Sie im Objekt-Explorer mit der rechten Maustaste auf einen Server, und wählen Sie Eigenschaften aus.

  2. Klicken Sie auf den Speicher -Knoten.

  3. Geben Sie unter Arbeitsspeicheroptionen für den Server den gewünschten Wert für Minimaler Serverarbeitsspeicher und Maximaler Serverarbeitsspeicher ein.

    Verwenden Sie die Standardeinstellungen, damit SQL Server die Arbeitsspeicheranforderungen auf der Grundlage der verfügbaren Systemressourcen dynamisch ändert. Es wird empfohlen, einen Wert für Max. Serverarbeitsspeicher festzulegen, wie oben ausführlich beschrieben.

Der folgende Screenshot zeigt alle drei Schritte:

Konfigurieren des Arbeitsspeichers in SSMS

Sperren von Seiten im Speicher (LPIM)

Mit dieser Windows-Richtlinie werden die Konten bestimmt, die einen Prozess zum Speichern von Daten im physischen Speicher verwenden können, um das systemgesteuerte Auslagern der Daten in den virtuellen Arbeitsspeicher zu vermeiden. Durch Sperren von Seiten im Arbeitsspeicher können Sie die Reaktionsfähigkeit des Servers möglicherweise auch nach Auslagerung von Arbeitsspeicherdaten auf die Festplatte aufrechterhalten. Die Option Sperren von Seiten im Speicher wird für Instanzen der SQL Server Standard Edition und höher auf ON gesetzt, wenn dem Konto mit den Privilegien zum Ausführen von "sqlserver.exe" das Windows-Benutzerrecht Sperren von Seiten im Speicher (Lock Pages in Memory, LPIM) erteilt wurde.

Entfernen Sie zum Deaktivieren der Option Sperren von Seiten im Speicher für SQL Server das Benutzerrecht Sperren von Seiten im Speicher für das Konto mit der Ausführungsberechtigung für sqlserver.exe (das SQL Server-Startkonto).

Das Festlegen dieser Option wirkt sich nicht auf die dynamische Arbeitsspeicherverwaltung von SQL Server aus und ermöglicht ein Erweitern oder Verkleinern aufgrund der Anforderungen anderer Arbeitsspeicherclerks. Bei der Verwendung des Benutzerrechts Sperren von Seiten im Speicher empfiehlt es sich, einen oberen Grenzwert für Max. Serverarbeitsspeicher festzulegen, wie oben ausführlich beschrieben.

Wichtig

Das Festlegen dieser Option sollte nur bei Bedarf erfolgen, nämlich wenn es Anzeichen gibt, dass der sqlservr-Prozess ausgelagert wird. In diesem Fall wird im Fehlerprotokoll der Fehler 17890 gemeldet, ähnlich wie im folgenden Beispiel: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%. Ab SQL Server 2012 (11.x) wird das Ablaufverfolgungsflag 845 nicht von der Standard Edition benötigt, um gesperrte Seiten zu verwenden.

Aktivieren des Sperrens von Seiten im Speicher

So aktivieren Sie die Option "Sperren von Seiten im Speicher":

  1. Klicken Sie im Menü Start auf Ausführen. Geben Sie gpedit.msc im Feld Öffnen ein.

    Das Dialogfeld Gruppenrichtlinie wird geöffnet.

  2. Erweitern Sie in der Konsole Gruppenrichtlinie die Option Computerkonfiguration und dann Windows-Einstellungen.

  3. Erweitern Sie Sicherheitseinstellungen und dann Lokale Richtlinien.

  4. Wählen Sie den Ordner Zuweisen von Benutzerrechten aus.

    Die Richtlinien werden im Detailbereich angezeigt.

  5. Doppelklicken Sie im Detailbereich auf Sperren von Seiten im Speicher.

  6. Fügen Sie im Dialogfeld Lokale Sicherheitseinstellung das Konto mit Privilegien zum Ausführen von „sqlservr.exe“ (das SQL Server-Startkonto) hinzu.

Mehrere Instanzen von SQL Server

Wenn Sie mehrere Instanzen von Datenbank-Engineausführen, stehen Ihnen zum Verwalten des Arbeitsspeichers drei Möglichkeiten zur Verfügung:

  • Verwenden Sie Max. Serverarbeitsspeicher, um die Speicherbelegung zu steuern, wie oben ausführlich dargestellt. Richten Sie für jede Instanz Maximaleinstellungen ein, und achten Sie darauf, dass der gesamte zugeordnete Arbeitsspeicher nicht größer ist als der insgesamt auf dem Computer verfügbare physische Speicher. Es empfiehlt sich, den jeder Instanz zugeordneten Arbeitsspeicher proportional zur erwarteten Arbeitsauslastung oder Datenbankgröße zu bemessen. Dieser Ansatz hat den Vorteil, dass beim Starten neuer Prozesse oder Instanzen sofort freier Arbeitsspeicher für die Prozesse oder Instanzen zur Verfügung steht. Der Nachteil ist, wenn nicht alle Instanzen ausgeführt werden, dass keine der laufenden Instanzen den verbleibenden freien Arbeitsspeicher nutzen kann.

  • Verwenden Sie Min. Serverarbeitsspeicher, um die Speicherbelegung zu steuern, wie oben ausführlich dargestellt. Richten Sie für jede Instanz Minimaleinstellungen ein, sodass die Summe dieser Mindestwerte 1 bis 2 GB unterhalb des gesamten physischen Speichers auf dem Computer liegt. Auch bei dieser Methode empfiehlt es sich, die Werte proportional zu der für die jeweilige Instanz erwarteten Arbeitsauslastung zu bemessen. Dieser Ansatz hat den Vorteil, dass die laufenden Instanzen den verbleibenden freien Arbeitsspeicher nutzen können, wenn nicht alle Instanzen gleichzeitig ausgeführt werden. Diese Vorgehensweise ist auch dann sinnvoll, wenn auf dem Computer ein weiterer speicherintensiver Prozess vorhanden ist, da sichergestellt ist, dass SQL Server zumindest eine angemessene Menge an Arbeitsspeicher erhält. Der Nachteil besteht darin, dass es beim Starten einer neuen Instanz (oder eines anderen Prozesses) ggf. etwas dauern kann, bis die laufenden Instanzen Speicher freigeben. Dies trifft vor allem dann zu, wenn die Instanzen zuerst noch geänderte Seiten in ihre jeweiligen Datenbanken zurückschreiben müssen.

  • Unternehmen Sie nichts (dies wird nicht empfohlen). Die ersten Instanzen, denen eine Arbeitslast zugewiesen wird, weisen sich den gesamten Arbeitsspeicher zu. Instanzen im Leerlauf oder Instanzen, die später gestartet werden, müssen in dieser Situation u. U. mit einer minimalen Menge an Arbeitsspeicher auskommen. SQL Server versucht nicht, die Speicherauslastung über mehrere Instanzen hinweg auszugleichen. Alle Instanzen antworten jedoch auf Signale der Windows-Arbeitsspeicherbenachrichtigung, um ihren Speicherbedarf anzupassen. Windows nimmt keinen Speicherausgleich bei Anwendungen vor, die über eine Arbeitsspeicherbenachrichtigungs-API verfügen. Es erfolgt lediglich eine globale Rückmeldung über die Verfügbarkeit von Arbeitsspeicher auf dem System.

Sie können diese Einstellungen ohne Neustart der Instanzen ändern. Dadurch können Sie problemlos mit verschiedenen Einstellungen experimentieren, um die für Ihr Nutzungsmuster am besten geeigneten Einstellungen herauszufinden.

Bereitstellen der maximalen Arbeitsspeichermenge

In allen Editionen von SQL Server kann der Arbeitsspeicher bis zum Speicherplatzlimit des virtuellen Adressraums des Prozesses konfiguriert werden. Weitere Informationen finden Sie unter Memory Limits for Windows and Windows Server Releases (Grenzwerte für den Arbeitsspeicher für Versionen von Windows und Windows Server).

Beispiele

Beispiel A: Festlegen der Option „Max. Serverarbeitsspeicher“ auf 4 GB.

Im folgenden Beispiel wird die Option max server memory auf 4 GB festgelegt. Beachten Sie, dass im Beispiel (MB) ausgelassen wird, obwohl sp_configure den Namen der Option als max server memory (MB) festlegt.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Dadurch wird eine Anweisung ausgegeben ähnlich wie:

Die Konfigurationsoption 'max server memory (MB)' (Max. Serverarbeitsspeicher (MB)) wurde von 2147483647 in 4096 geändert. Führen Sie zum Installieren die RECONFIGURE-Anweisung aus.

Beispiel B: Bestimmen der aktuellen Speicherbelegung

Mit der folgenden Abfrage werden Informationen zur aktuellen Speicherbelegung zurückgegeben.

SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage, 
   process_physical_memory_low AS sql_process_physical_memory_low, 
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

Beispiel C: Bestimmen eines Werts für die Option „Max. Serverarbeitsspeicher (MB)“

Die folgende Abfrage gibt Informationen zum aktuell konfigurierten Wert und dem von SQL Server verwendeten Wert zurück. Diese Abfrage gibt Ergebnisse zurück, unabhängig davon, ob für „Erweiterte Optionen anzeigen“ „true“ gilt.

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[name] = 'max server memory (MB)'

Nächste Schritte

Handbuch zur Architektur der Speicherverwaltung
Überwachen und Optimieren der Leistung
RECONFIGURE (Transact-SQL)
Serverkonfigurationsoptionen (SQL Server)
sp_configure (Transact-SQL)
Startoptionen für den Datenbank-Engine-Dienst
Editionen und unterstützten Funktionen von SQL Server 2016
Editionen und unterstützten Funktionen von SQL Server 2017
Editionen und unterstützte Funktionen von SQL Server 2017 unter Linux
Memory Limits for Windows and Windows Server Releases (Grenzwerte für den Arbeitsspeicher für Versionen von Windows und Windows Server).