Konfigurationsoptionen für den Serverarbeitsspeicher

Gilt für:SQL Server

Die Speicherauslastung für das SQL Server-Datenbankmodul ist durch ein Paar von Konfigurationseinstellungen min server memory (MB) und max server memory (MB). Im Laufe der Zeit und unter normalen Umständen versucht SQL Server, Arbeitsspeicher bis zum festgelegten max server memory (MB)Grenzwert zu beanspruchen.

In älteren Versionen von SQL Server hatte die Speicherauslastung praktisch keine Obergrenze, wodurch für SQL Server der gesamte Systemarbeitsspeicher für die Verwendung verfügbar war. Es wird empfohlen, in allen Versionen von SQL Server eine Obergrenze für die SQL Server-Speicherauslastung zu konfigurieren, indem sie die max server memory (MB).

• Seit SQL Server 2019 (15.x) bietet SQL Setup in Windows-Servern eine Empfehlung für max server memory (MB) eine eigenständige SQL Server-Instanz basierend auf einem Prozentsatz des verfügbaren Systemspeichers zum Zeitpunkt der Installation.
• Sie können jederzeit die Grenzen des Arbeitsspeichers (in Megabyte) für einen SQL Server-Prozess neu konfigurieren, der von einer Instanz von SQL Server über die min server memory (MB) Konfigurationsoptionen max server memory (MB) verwendet wird.

Empfehlungen

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

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

• Das Festlegen max server memory (MB) des Werts zu hoch kann dazu führen, dass eine einzelne Instanz von SQL Server mit anderen SQL Server-Instanzen konkurrieren kann, die auf demselben Host gehostet werden.
• Die Einstellung max server memory (MB) zu niedrig ist jedoch eine verlorene Leistungschance und kann zu Speicherdruck und Leistungsproblemen in der SQL Server-Instanz führen.
• Das Festlegen max server memory (MB) auf den Minimalwert kann sogar verhindern, dass SQL Server gestartet wird. Wenn Sie SQL Server nach dem Ändern dieser Option nicht starten können, starten Sie sie mit der -f Startoption, und setzen Sie ihn auf den vorherigen Wert zurück max server memory (MB) . Weitere Informationen finden Sie unter Startoptionen für den Datenbank-Engine-Dienst.
• Es wird nicht empfohlen, denselben Wert oder in der Nähe der gleichen Werte festzulegen max server memory (MB) und min server memory (MB) zu verwenden.

SQL Server kann den 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 die Menge des Arbeitsspeichers für SQL Server festlegen, bestimmen Sie die entsprechende Speichereinstellung, indem Sie vom gesamten physischen Speicher den für das Betriebssystem (Betriebssystem) erforderlichen Arbeitsspeicher, die Speicherzuweisungen, die nicht durch die max server memory (MB) Einstellung gesteuert werden, sowie andere Instanzen von SQL Server (und andere Systemanwendungen subtrahieren, wenn der Server in anderen Anwendungen verwaltet wird, die Arbeitsspeicher verbrauchen, einschließlich anderer Instanzen von SQL Server). Die Differenz entspricht der maximalen Arbeitsspeichergröße, die Sie der aktuellen SQL Server-Instanz zuweisen können.

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).

Min. Serverarbeitsspeicher

Wird min server memory (MB) verwendet, um eine mindest verfügbare Arbeitsspeichermenge für den SQL Server-Speicher-Manager zu gewährleisten.

• SQL Server weist nicht sofort die menge an Arbeitsspeicher zu, die beim min server memory (MB) Start angegeben ist. Nachdem die Speicherauslastung diesen Wert aufgrund der Clientlast erreicht hat, kann SQL Server jedoch keinen Arbeitsspeicher freigeben, es sei denn, der Wert min server memory (MB) wird reduziert. Wenn beispielsweise mehrere Instanzen von SQL Server gleichzeitig auf demselben Server installiert werden, sollten Sie den min server memory (MB) Parameter so festlegen, dass der Speicher für eine Instanz reserviert wird.

• Das Festlegen eines min server memory (MB) Werts ist in einer virtualisierten Umgebung unerlässlich, um sicherzustellen, dass der Arbeitsspeicherdruck vom zugrunde liegenden Host nicht versucht, den Speicher aus dem Pufferpool auf einem virtuellen Gastcomputer (VM) zu behandeln, der über das hinausgeht, was für eine akzeptable Leistung erforderlich ist. Idealerweise müssen Instanzen von SQL Server auf einem virtuellen Computer nicht mit den proaktiven Prozessen für die Belegungsfreigabe von Speicher für den virtuellen Host konkurrieren.

• SQL Server ist nicht garantiert, die in der angegebenen min server memory (MB)Arbeitsspeichermenge zuzuweisen. Wenn für die Auslastung auf dem Server niemals die in der Datei angegebene min server memory (MB)Arbeitsspeichermenge zu verwenden ist, verwendet SQL Server weniger Arbeitsspeicher.

Max. Serverarbeitsspeicher

Wird max server memory (MB) verwendet, um sicherzustellen, dass das Betriebssystem und andere Anwendungen keinen schädlichen Arbeitsspeicherdruck von SQL Server haben.

• Bevor Sie die max server memory (MB) Konfiguration festlegen, überwachen Sie den gesamten Speicherverbrauch des Servers, auf dem die SQL Server-Instanz gehostet wird, während des normalen Vorgangs, um die Verfügbarkeit und Anforderungen des Arbeitsspeichers zu ermitteln. Verwenden Sie für eine erstkonfiguration oder wenn im Laufe der Zeit keine Möglichkeit zum Sammeln der Speicherauslastung von SQL Server-Prozessen besteht, den folgenden generalisierten Best Practice-Ansatz, um eine einzelne Instanz zu konfigurieren max server memory (MB) :
  • Subtrahieren Sie vom gesamten Betriebssystemspeicher das Äquivalent potenzieller SQL Server-Threadspeicherzuordnungen außerhalb max server memory (MB) der Kontrolle, was die Stapelgröße¹ multipliziert mit berechneten max. Workerthreads² multipliziert ist.
  • Subtrahieren Sie dann 25% für andere Speicherzuweisungen außerhalb max server memory (MB) der Kontrolle, z. B. Sicherungspuffer, erweiterte gespeicherte Prozedur-DLLs, Objekte, die mithilfe von Automatisierungsprozeduren (sp_OA Aufrufen) erstellt werden, und Zuordnungen von verknüpften Serveranbietern. Dies ist eine generische Annäherung, und Ihr tatsächlicher Bedarf kann variieren.
  • Was bleibt, sollte die max server memory (MB) Einstellung für ein einzelnes Instanzsetup sein.

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

² Weitere Informationen zu den berechneten Standardarbeitsthreads für eine bestimmte Anzahl von affinitierten CPUs im aktuellen Host finden Sie unter Serverkonfiguration: Max-Worker-Threads.

Manuelles Festlegen der Optionen

Die Serveroptionen min server memory (MB) und max server memory (MB) können auf einen Bereich von Speicherwerten festgelegt werden. Diese Methode ist vor allem dann sinnvoll, wenn der System- oder Datenbankadministrator eine Instanz von SQL Server mit den Arbeitsspeicheranforderungen anderer Anwendungen oder weiterer Instanzen von SQL Server, die auf demselben Computer ausgeführt werden, konfigurieren möchte.

Verwenden von Transact-SQL

Die min server memory (MB) Optionen sind max server memory (MB) 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. Weitere Informationen finden Sie unter sp_configure.

Im folgenden Beispiel wird die max server memory (MB) Option auf 12.288 MB oder 12 GB festgelegt. Obwohl sp_configure den Namen der Option als max server memory (MB) angibt, können Sie nur (MB) auslassen.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 12288;
GO

RECONFIGURE;
GO

Die folgende Abfrage gibt Informationen zum aktuell konfigurierten Wert und dem Wert zurück, der aktuell verwendet wird. Diese Abfrage gibt Ergebnisse zurück, unabhängig davon, ob die sp_configure-Option für „Erweiterte Optionen anzeigen“ aktiviert ist.

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

Verwenden Sie SQL Server Management Studio

Verwenden Und max server memory (MB) konfigurieren Sie min server memory (MB) die Vom SQL Server-Speicher-Manager verwaltete Speichermenge (in Megabyte) für eine Instanz von SQL Server neu.

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

2. Wählen Sie die Seite Arbeitsspeicher des Fensters Servereigenschaften aus. Die aktuellen Werte des min. Serverarbeitsspeichers und des max. Serverarbeitsspeichers werden angezeigt.

3. Geben Sie in Serverspeicheroptionen die gewünschten Zahlen für den min. Serverarbeitsspeicher und den max. Serverarbeitsspeicher ein. Empfehlungen finden Sie unter Min. Serverarbeitsspeicher (MB) und Max. Serverarbeitsspeicher (MB) in diesem Artikel.

Der folgende Screenshot zeigt alle drei Schritte:

Sperren von Seiten im Speicher (LPIM)

Windows-basierte Anwendungen können Windows-Adressfenstererweiterungen (AWE)-APIs verwenden, um physischen Speicher im Prozessadressraum zuzuweisen und zuzuordnen. Mit der LPIM Windows-Richtlinie werden die Konten bestimmt, welche auf die API zugreifen können, um Daten im physischen Speicher beizubehalten. Dadurch wird das systemgesteuerte Auslagern der Daten in den virtuellen Arbeitsspeicher verhindert. Der mit AWE zugewiesene Speicher wird gesperrt, bis die Anwendung ihn explizit freigibt oder sich beendet. Die Verwendung der AWE-APIs für die Speicherverwaltung in 64-Bit-SQL Server wird auch häufig als gesperrte Seiten bezeichnet. 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 Seiten im Arbeitsspeicher sperren wird für Instanzen der SQL Server Standard Edition und höher aktiviert, wenn dem Konto mit den Privilegien zum Ausführen von sqlservr.exe das Windows-Benutzerrecht Seiten im Arbeitsspeicher sperren (Lock Pages in Memory, LPIM) erteilt wurde.

Entfernen Sie zum Deaktivieren der Option Seiten im Arbeitsspeicher sperren für SQL Server das Benutzerrecht Seiten im Arbeitsspeicher sperren für das Konto mit der Ausführungsberechtigung für sqlservr.exe (das SQL Server-Startkonto).

Die Verwendung von LPIM wirkt sich nicht auf die dynamische Arbeitsspeicherverwaltung von SQL Server aus und ermöglicht ein Erweitern oder Verkleinern aufgrund der Anforderungen anderer Arbeitsspeicherclerks. Wenn Sie die Sperrseiten im Speicherbenutzer richtig verwenden, empfiehlt es sich dringend, einen oberen Grenzwert festzulegen max server memory (MB). Weitere Informationen finden Sie unter Max. Serverarbeitsspeicher (MB).

LPIM sollte verwendet werden, wenn es Hinweise auf eine Auslagerung des sqlservr-Prozesses gibt. In diesem Fall wird im Fehlerprotokoll der Fehler 17890 gemeldet, wie im folgenden Beispiel gezeigt:

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: ##%.

Die Verwendung von LPIM mit einer falsch konfigurierten max server memory (MB) Einstellung, die nicht für andere Speicherverbraucher im System verantwortlich ist, kann zu Instabilität führen, je nachdem, wie viel Arbeitsspeicher von anderen Prozessen benötigt wird, oder SQL Server-Speicheranforderungen außerhalb des Bereichs von max server memory (MB). Weitere Informationen finden Sie unter Max. Serverarbeitsspeicher. Wenn die Sperrseiten im Arbeitsspeicher (LPIM) berechtigungen gewährt werden (auf 32-Bit- oder 64-Bit-Systemen), empfehlen wir dringend, dass Sie einen bestimmten Wert festlegen, anstatt den Standardwert von 2.147.483.647 MB (MB) zu max server memory (MB) verlassen.

Aktivieren von Seiten im Arbeitsspeicher sperren

Wenn Sie die vorherigen Informationen berücksichtigt haben, können Sie die Option Seiten im Arbeitsspeicher sperren, indem Sie dem Dienstkonto für die SQL Server-Instanz die Berechtigung dafür erteilen. Weitere Informationen finden Sie unter Aktivieren der Option „Sperren von Seiten im Speicher“ (Windows).

Informationen zum Ermitteln des Dienstkontos für die Instanz von SQL Server finden Sie im SQL Server-Konfigurations-Manager. Sie können alternativ auch service_account über sys.dm_server_services abfragen. Weitere Informationen finden Sie unter sys.dm_server_services.

Anzeigen des Status von Seiten im Arbeitsspeicher sperren

Verwenden Sie die folgende Abfrage, um zu ermitteln, ob die Berechtigung Seiten im Arbeitsspeicher sperren dem Dienstkonto für die Instanz von SQL Server gewährt wird. Diese Abfrage wird in SQL Server 2016 (13.x) SP1 und höher unterstützt.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Die folgenden Werte von sql_memory_model_desc geben den Status von LPIM an:

• CONVENTIONAL. Die Berechtigung „Seiten im Arbeitsspeicher sperren“ wird nicht gewährt.
• LOCK_PAGES. Die Berechtigung „Seiten im Arbeitsspeicher sperren“ wird gewährt.
• LARGE_PAGES. Die Berechtigung „Seiten im Arbeitsspeicher sperren“ wird im Unternehmensmodus mit aktiviertem Ablaufverfolgungsflag 834 gewährt. Dies ist eine erweiterte Konfiguration und wird für die meisten Umgebungen nicht empfohlen. Weitere Informationen und wichtige Vorbehalte finden Sie unter Ablaufverfolgungsflag 834.

Verwenden Sie die folgenden Methoden, um zu bestimmen, ob die SQL Server Instanz gesperrte Seiten verwendet:

• Die Ausgabe der folgenden Transact-SQL-Abfrage zeigt nichtleere-Werte für locked_page_allocations_kb:

  SELECT osn.node_id,
         osn.memory_node_id,
         osn.node_state_desc,
         omn.locked_page_allocations_kb
  FROM sys.dm_os_memory_nodes AS omn
       INNER JOIN sys.dm_os_nodes AS osn
           ON (omn.memory_node_id = osn.memory_node_id)
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• Das aktuelle SQL Server-Fehlerprotokoll meldet die Meldung, Using locked pages in the memory manager während des Serverstarts.

• Der Abschnitt „Memory Manager“ der DBCC MEMORYSTATUS-Ausgabe zeigt einen nichtleeren Wert für das AWE Allocated Element an.

Mehrere Instanzen von SQL Server

Wenn Sie mehrere Instanzen der Datenbank-Engine ausführen, stehen Ihnen zum Verwalten des Arbeitsspeichers verschiedene Möglichkeiten zur Verfügung:

• Verwenden Sie max server memory (MB) in jeder Instanz, um die Speicherauslastung wie zuvor beschrieben zu steuern. 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. Wenn nicht alle Instanzen ausgeführt werden, ist der Nachteil, dass keine der laufenden Instanzen den verbleibenden freien Arbeitsspeicher nutzen kann.

• Verwenden Sie min server memory (MB) in jeder Instanz, um die Speicherauslastung wie zuvor beschrieben zu steuern. 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.

• Verwenden Sie sowohl als min server memory (MB) auch max server memory (MB) in jeder Instanz die Speicherauslastung, beobachten und optimieren Sie die maximale Auslastung der einzelnen Instanzen und den minimalen Speicherschutz innerhalb eines breiten Spektrums potenzieller Speicherauslastungsstufen.

• 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.

Beispiele

Ein. Festlegen der Option „Max. Serverarbeitsspeicher“ auf 4 GB.

Im folgenden Beispiel wird die max server memory (MB) Option auf 4096 MB oder 4 GB festgelegt. Obwohl sp_configure den Namen der Option als max server memory (MB) angibt, können Sie nur (MB) auslassen.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 4096;
GO

RECONFIGURE;
GO

Dadurch wird eine Anweisung ausgegeben, die Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. ähnelt. Das neue Speicherlimit tritt nach der Ausführung von RECONFIGURE sofort in Kraft. Weitere Informationen finden Sie unter sp_configure.

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;

C. Überprüfen des Werts von max server memory (MB)

Die folgende Abfrage gibt Informationen zum aktuell konfigurierten Wert und dem verwendeten Wert zurück. Diese Abfrage gibt Ergebnisse zurück, unabhängig davon, ob die sp_configure-Option für „Erweiterte Optionen anzeigen“ aktiviert ist.

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

Zugehöriger Inhalt

• Leitfaden zur Architektur der Speicherverwaltung
• Überwachen und Optimieren der Leistung
• RECONFIGURE (Transact-SQL)
• Serverkonfigurationsoptionen
• sp_configure (Transact-SQL)
• Startoptionen für den Datenbank-Engine-Dienst
• Memory Limits for Windows and Windows Server Releases (Grenzwerte für den Arbeitsspeicher für Versionen von Windows und Windows Server).