Opties voor servergeheugenconfiguratie

Van toepassing op:SQL Server

Geheugengebruik voor de SQL Server-database-engine wordt gebonden door een paar configuratie-instellingen en min server memory (MB)max server memory (MB). In de loop van de tijd en onder normale omstandigheden probeert SQL Server geheugen te claimen tot de limiet die is ingesteld door max server memory (MB).

In oudere versies van SQL Server is het geheugengebruik vrijwel niet opgecapt, wat aan SQL Server aangeeft dat alle systeemgeheugens beschikbaar waren voor gebruik. Het wordt aanbevolen in alle versies van SQL Server om een bovengrens voor het geheugengebruik van SQL Server te configureren door de max server memory (MB).

• Sinds SQL Server 2019 (15.x) biedt SQL Setup in Windows-servers een aanbeveling voor max server memory (MB) een zelfstandig SQL Server-exemplaar op basis van een percentage van het beschikbare systeemgeheugen op het moment van de installatie.
• U kunt op elk gewenst moment de grenzen van het geheugen (in megabytes) opnieuw configureren voor een SQL Server-proces dat wordt gebruikt door een exemplaar van SQL Server via de min server memory (MB) en max server memory (MB) configuratieopties.

Aanbevelingen

De standaardinstellingen en minimaal toegestane waarden voor deze opties zijn:

Binnen deze grenzen kan SQL Server de geheugenvereisten dynamisch wijzigen op basis van beschikbare systeembronnen. Zie dynamisch geheugenbeheer voor meer informatie.

• Als max server memory (MB) u de waarde te hoog instelt, kan één exemplaar van SQL Server concurreren voor geheugen met andere SQL Server-exemplaren die op dezelfde host worden gehost.
• Het instellen van max server memory (MB) te weinig prestaties is echter een verloren prestatiekans en kan leiden tot geheugenbelasting en prestatieproblemen in het SQL Server-exemplaar.
• Als u de max server memory (MB) minimumwaarde instelt, kan zelfs voorkomen dat SQL Server wordt gestart. Als u SQL Server niet kunt starten nadat u deze optie hebt gewijzigd, start u deze met behulp van de opstartoptie en stelt u de -f vorige waarde opnieuw max server memory (MB) in. Zie Database Engine Service-opstartoptiesvoor meer informatie.
• Het is niet raadzaam om dezelfde waarde in te stellen en max server memory (MB) in te stellen min server memory (MB) of bijna dezelfde waarden te hebben.

SQL Server kan dynamisch geheugen gebruiken. U kunt de geheugenopties echter handmatig instellen en de hoeveelheid geheugen beperken waartoe SQL Server toegang heeft. Voordat u de hoeveelheid geheugen voor SQL Server instelt, moet u de juiste geheugeninstelling bepalen door af te trekken van het totale fysieke geheugen, het geheugen dat nodig is voor het besturingssysteem (OS), geheugentoewijzingen die niet worden beheerd door de max server memory (MB) instelling, en andere exemplaren van SQL Server (en andere systeemtoepassingen, als de server de thuisbasis is van andere toepassingen die geheugen verbruiken, inclusief andere exemplaren van SQL Server). Dit verschil is de maximale hoeveelheid geheugen die u kunt toewijzen aan het huidige SQL Server-exemplaar.

Geheugen kan worden geconfigureerd tot de limiet voor de virtuele adresruimte van het proces in alle SQL Server-edities. Zie Geheugenlimieten voor Windows- en Windows Server-releases voor meer informatie.

Minimale servergeheugen

Gebruik min server memory (MB) dit om een minimale hoeveelheid geheugen te garanderen die beschikbaar is voor SQL Server Memory Manager.

• SQL Server wijst niet onmiddellijk de hoeveelheid geheugen toe die is opgegeven bij min server memory (MB) het opstarten. Nadat het geheugengebruik deze waarde echter heeft bereikt vanwege de belasting van de client, kan SQL Server geen geheugen vrij maken, tenzij de waarde ervan min server memory (MB) wordt verminderd. Wanneer bijvoorbeeld meerdere exemplaren van SQL Server gelijktijdig op dezelfde server worden geïnstalleerd, kunt u overwegen om de min server memory (MB) parameter in te stellen om geheugen voor een exemplaar te reserveren.

• Het instellen van een min server memory (MB) waarde is essentieel in een gevirtualiseerde omgeving om ervoor te zorgen dat geheugendruk van de onderliggende host niet probeert de toewijzing van geheugen uit de buffergroep op een virtuele gastmachine (VM) ongedaan te maken dan nodig is voor acceptabele prestaties. In het ideale geval hoeven exemplaren van SQL Server op een virtuele machine niet te concurreren met de proactieve processen voor de deallocatie van het geheugen van de virtuele host.

• SQL Server is niet gegarandeerd om de hoeveelheid geheugen toe te wijzen die is opgegeven in min server memory (MB). Als de belasting op de server nooit de hoeveelheid geheugen vereist die is opgegeven, min server memory (MB)gebruikt SQL Server minder geheugen.

Maximaal servergeheugen

Gebruik max server memory (MB) dit om te garanderen dat het besturingssysteem en andere toepassingen geen nadelige geheugenbelasting ondervinden die afkomstig is van SQL Server.

• Voordat u de max server memory (MB) configuratie instelt, moet u het totale geheugenverbruik bewaken van de server die als host fungeert voor het SQL Server-exemplaar, tijdens de normale werking, om de beschikbaarheid en vereisten van het geheugen te bepalen. Voor een eerste configuratie of wanneer er geen kans was om het geheugengebruik van SQL Server-processen in de loop van de tijd te verzamelen, gebruikt u de volgende gegeneraliseerde best practice om te configureren max server memory (MB) voor één exemplaar:
  • Trek uit het totale geheugen van het besturingssysteem het equivalent af van mogelijke SQL Server-threadgeheugentoewijzingen buiten max server memory (MB) beheer. Dit is de stackgrootte¹ vermenigvuldigd met berekende maximale werkrolthreads².
  • Trek vervolgens 25% af voor andere geheugentoewijzingen buiten max server memory (MB) beheer, zoals back-upbuffers, uitgebreide opgeslagen procedure-DLL's, objecten die worden gemaakt met behulp van Automation-procedures (sp_OA aanroepen) en toewijzingen van gekoppelde serverproviders. Dit is een algemene benadering en uw kilometerstand kan variëren.
  • Wat er overblijft, moet de max server memory (MB) instelling zijn voor één exemplaarinstallatie.

¹ Raadpleeg de handleiding voor de architectuur voor geheugenbeheer voor informatie over threadstackgrootten per architectuur.

² Zie Serverconfiguratie: max worker threads voor een bepaald aantal geaffineerde CPU's in de huidige host voor meer informatie over de berekende standaardwerkthreads.

Opties handmatig instellen

De serveropties min server memory (MB) en max server memory (MB) kunnen worden ingesteld om een bereik van geheugenwaarden te omvatten. Deze methode is handig voor systeem- of databasebeheerders om een exemplaar van SQL Server te configureren met de geheugenvereisten van andere toepassingen of andere exemplaren van SQL Server die op dezelfde host worden uitgevoerd.

Gebruik Transact-SQL

De min server memory (MB) opties en max server memory (MB) opties zijn geavanceerde opties. Wanneer u de door het sp_configure systeem opgeslagen procedure gebruikt om deze instellingen te wijzigen, kunt u deze alleen wijzigen wanneer geavanceerde opties worden weergegeven op 1. Deze instellingen worden onmiddellijk van kracht zonder dat de server opnieuw wordt opgestart. Zie sp_configurevoor meer informatie.

In het volgende voorbeeld wordt de max server memory (MB) optie ingesteld op 12.288 MB of 12 GB. Hoewel sp_configure hiermee de naam van de optie max server memory (MB)wordt opgegeven, kunt u de (MB)optie weglaten.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 12288;
GO

RECONFIGURE;
GO

De volgende query retourneert informatie over de momenteel geconfigureerde waarden en de waarde die momenteel wordt gebruikt. Deze query retourneert resultaten, ongeacht of de sp_configure optie Geavanceerde opties weergeven is ingeschakeld.

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

SQL Server Management Studio gebruiken

Gebruik min server memory (MB) en max server memory (MB) configureer opnieuw de hoeveelheid geheugen (in megabytes) die wordt beheerd door SQL Server Memory Manager voor een exemplaar van SQL Server.

1. Klik in Objectverkenner met de rechtermuisknop op een server en selecteer Eigenschappen.

2. Selecteer de pagina Geheugen van het venster Servereigenschappen . De huidige waarden van minimaal servergeheugen en maximumservergeheugen worden weergegeven.

3. Voer in servergeheugenopties de gewenste getallen in voor Minimaal servergeheugen en Maximum servergeheugen. Zie minimaal servergeheugen (MB) en maximaal servergeheugen (MB) in dit artikel voor aanbevelingen.

In de volgende schermopname ziet u alle drie de stappen:

Pagina's in het geheugen vergrendelen (LPIM)

Windows-toepassingen kunnen gebruikmaken van AWE-API's (Address Windowing Extensions) van Windows voor het toewijzen van fysiek geheugen en dit te koppelen aan de adresruimte van het proces. Het LPIM Windows-beleid bepaalt welke accounts toegang hebben tot de API om gegevens in fysiek geheugen te bewaren, waardoor het systeem de gegevens niet kan pagineren naar het virtuele geheugen op schijf. Het toegewezen geheugen met behulp van AWE is vergrendeld totdat de toepassing deze expliciet vrijgeeft of afsluit. Het gebruik van de AWE-API's voor geheugenbeheer in 64-bits SQL Server wordt ook vaak vergrendelde pagina's genoemd. Het vergrendelen van pagina's in het geheugen kan ervoor zorgen dat de server responsief blijft wanneer het geheugen naar de schijf wordt gepagineerd. De optie Pagina's vergrendelen in het geheugen is ingeschakeld in exemplaren van SQL Server Standard-editie en hoger wanneer het account met rechten om te draaien sqlservr.exe de Windows Lock pages in memory (LPIM) gebruikersrechten toegewezen heeft gekregen.

Als u de optie Pagina's vergrendelen in het geheugen voor SQL Server wilt uitschakelen, verwijdert u de gebruikersrechten om pagina's in het geheugen te vergrendelen voor het account met de bevoegdheden voor het opstarten van sqlservr.exe (het opstartaccount van SQL Server).

Het gebruik van LPIM heeft geen invloed op dynamisch geheugenbeheer van SQL Server, zodat het kan worden uitgebreid of verkleind op verzoek van andere geheugenbediende medewerkers. Wanneer u de pagina's vergrendelt in het geheugen , wordt het ten zeerste aanbevolen om een bovengrens in te stellen voor max server memory (MB). Zie het maximale servergeheugen (MB) voor meer informatie.

LPIM moet worden gebruikt wanneer er tekenen zijn dat het sqlservr proces wordt uitgepaginad. In dit geval wordt fout 17890 gerapporteerd in het foutenlogboek, vergelijkbaar met het onderstaande voorbeeld:

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

Het gebruik van LPIM met een onjuist geconfigureerde max server memory (MB) instelling die geen rekening houdt met andere geheugengebruikers in het systeem, kan instabiliteit veroorzaken, afhankelijk van de hoeveelheid geheugen die is vereist voor andere processen, of sql Server-geheugenvereisten buiten het bereik van max server memory (MB). Zie het maximum aantal servergeheugens voor meer informatie. Als de LPIM-bevoegdheid ( Lock pages in memory ) wordt verleend (op 32-bits of 64-bits systemen), raden we u ten zeerste aan om in te stellen max server memory (MB) op een specifieke waarde in plaats van de standaardwaarde van 2.147.483.647 MB (megabytes).

Pagina's vergrendelen in het geheugen inschakelen

Nadat u de vorige informatie hebt overwogen, raadpleegt u Pagina's vergrendelen in het geheugen inschakelen (Windows) om de optie Pagina's vergrendelen in het geheugen in te schakelen door de bevoegdheid aan het serviceaccount voor het exemplaar van SQL Server toe te kennen.

Als u het serviceaccount voor het exemplaar van SQL Server wilt bepalen, raadpleegt u SQL Server Configuration Manager of voert u een query uit op de service_account uit sys.dm_server_services. Zie sys.dm_server_servicesvoor meer informatie.

Status van Vergrendelde pagina's in het geheugen bekijken

Gebruik de volgende query om te bepalen of aan het serviceaccount voor het SQL Server-exemplaar de bevoegdheid Lock pages in memory is verleend. Deze query wordt ondersteund in SQL Server 2016 (13.x) SP1 en hoger.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

De volgende waarden geven sql_memory_model_desc de status van LPIM aan:

• CONVENTIONAL. Het recht om pagina’s in het geheugen te vergrendelen wordt niet verleend.
• LOCK_PAGES. De bevoegdheid om pagina's in het geheugen te vergrendelen is verleend.
• LARGE_PAGES. Het voorrecht "Pagina's in het geheugen vergrendelen" wordt verleend in de Enterprise-modus met ingeschakelde traceervlag 834. Dit is een geavanceerde configuratie en wordt niet aanbevolen voor de meeste omgevingen. Zie traceringsvlag 834 voor meer informatie en belangrijke opmerkingen.

Gebruik de volgende methoden om te bepalen of het SQL Server-exemplaar vergrendelde pagina's gebruikt:

• De uitvoer van de volgende Transact-SQL query geeft niet-nulwaarden aan voor 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';
  

• Het huidige SQL Server-foutenlogboek rapporteert het bericht Using locked pages in the memory manager tijdens het opstarten van de server.

• In de sectie Memory Manager van de DBCC MEMORYSTATUS-uitvoer wordt een niet-nulwaarde voor het AWE Allocated item weergegeven.

Meerdere exemplaren van SQL Server

Wanneer u meerdere exemplaren van de database-engine uitvoert, zijn er verschillende benaderingen die u kunt gebruiken om geheugen te beheren:

• Gebruik max server memory (MB) in elk exemplaar om het geheugengebruik te beheren, zoals eerder beschreven. Stel de maximuminstellingen voor elk exemplaar vast, waarbij u voorzichtig bent met het feit dat de totale toelage niet meer is dan het totale fysieke geheugen op uw computer. Mogelijk wilt u elk exemplaar geheugen proportioneel geven aan de verwachte werkbelasting of databasegrootte. Deze aanpak heeft het voordeel dat wanneer nieuwe processen of exemplaren worden opgestart, er onmiddellijk gratis geheugen beschikbaar is voor deze processen. Het nadeel is dat als u niet alle exemplaren uitvoert, geen van de uitgevoerde exemplaren het resterende vrije geheugen kan gebruiken.

• Gebruik min server memory (MB) in elk exemplaar om het geheugengebruik te beheren, zoals eerder beschreven. Minimale instellingen voor elk exemplaar instellen, zodat de som van deze minimumlimieten 1 - 2 GB kleiner is dan het totale fysieke geheugen op uw computer. Nogmaals, u kunt deze minimale waarden evenredig vaststellen aan de verwachte belasting van dat exemplaar. Deze aanpak heeft het voordeel dat als niet alle exemplaren tegelijkertijd worden uitgevoerd, de exemplaren die worden uitgevoerd, het resterende vrije geheugen kunnen gebruiken. Deze methode is ook handig wanneer er een ander geheugenintensief proces op de computer is, omdat sql Server er ten minste een redelijke hoeveelheid geheugen op zou krijgen. Het nadeel is dat wanneer een nieuw exemplaar (of een ander proces) wordt gestart, het enige tijd kan duren voordat de actieve exemplaren geheugen vrijgeven, met name als ze gewijzigde pagina's terug naar hun databases moeten schrijven.

• Gebruik zowel als max server memory (MB)min server memory (MB) in elk exemplaar om het geheugengebruik te beheren, het maximale gebruik en de minimale geheugenbeveiliging van elk exemplaar te observeren en af te stemmen binnen een breed scala aan mogelijke geheugengebruiksniveaus.

• Doe niets (niet aanbevolen). De eerste instanties die met een workload worden geconfronteerd, wijzen meestal al het geheugen toe. Niet-actieve exemplaren of exemplaren die later zijn gestart, kunnen uiteindelijk alleen worden uitgevoerd met slechts een minimale hoeveelheid geheugen die beschikbaar is. SQL Server probeert geen geheugengebruik te verdelen over exemplaren. Alle exemplaren reageren echter op signalen van Windows-geheugenmeldingen om de grootte van hun geheugenvoetafdruk aan te passen. Windows balanceert het geheugen niet tussen toepassingen met de Memory Notification API. Het biedt alleen globale feedback over de beschikbaarheid van geheugen op het systeem.

U kunt deze instellingen wijzigen zonder de exemplaren opnieuw op te starten, zodat u eenvoudig kunt experimenteren om de beste instellingen voor uw gebruikspatroon te vinden.

Voorbeelden

Eén. De maximale servergeheugenoptie instellen op 4 GB

In het volgende voorbeeld wordt de max server memory (MB) optie ingesteld op 4096 MB of 4 GB. Hoewel sp_configure hiermee de naam van de optie max server memory (MB)wordt opgegeven, kunt u de (MB)optie weglaten.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'max server memory', 4096;
GO

RECONFIGURE;
GO

Dit zal een verklaring opleveren die vergelijkbaar is met Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. de nieuwe geheugenlimiet onmiddellijk van kracht wordt na het uitvoeren van RECONFIGURE. Zie sp_configurevoor meer informatie.

B. Huidige geheugentoewijzing bepalen

De volgende query vraagt informatie op over momenteel toegewezen geheugen.

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. Bekijk de waarde van max server memory (MB)

De volgende query retourneert informatie over de momenteel geconfigureerde waarde en de waarde die in gebruik is. Deze query retourneert resultaten, ongeacht of de sp_configure optie Geavanceerde opties weergeven is ingeschakeld.

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

Verwante inhoud

• Geheugenbeheer Architectuurhandleiding
• Prestaties bewaken en afstemmen voor betere resultaten
• opnieuw configureren (Transact-SQL)
• Serverconfiguratieopties
• sp_configure (Transact-SQL)
• Opstartopties voor Database Engine Service
• Geheugenlimieten voor Windows- en Windows Server-releases