Problemen met onvoldoende geheugen of weinig geheugen in SQL Server oplossen

Artikel
01/30/2024

Symptomen

SQL Server maakt gebruik van een complexe geheugenarchitectuur die overeenkomt met de complexe en uitgebreide functieset. Vanwege de verschillende geheugenbehoeften kunnen er veel bronnen van geheugenverbruik en geheugendruk zijn, waardoor uiteindelijk geheugenproblemen worden veroorzaakt.

Er zijn veelvoorkomende fouten die duiden op weinig geheugen in SQL Server. Voorbeelden van fouten zijn:

701: Onvoldoende geheugen toewijzen om een query uit te voeren.
802: Kan geen geheugen ophalen om pagina's toe te wijzen in de buffergroep (gegevens- of indexpagina's).
1204: Fout bij het toewijzen van geheugen voor vergrendelingen.
6322: Fout bij het toewijzen van geheugen voor XML-parser.
6513:Kan CLR niet initialiseren vanwege geheugendruk.
6533: AppDomain is uitgeladen vanwege onvoldoende geheugen.
8318: Kan SQL-prestatiemeteritems niet laden vanwege onvoldoende geheugen.
8356 of 8359: ETW of SQL-tracering kan niet worden uitgevoerd vanwege onvoldoende geheugen.
8556: Kan MSDTC niet laden vanwege onvoldoende geheugen.
8645: Kan een query niet uitvoeren omdat er geen geheugen beschikbaar is voor geheugentoekenningen (sorteren en hashen) Zie How to troubleshoot SQL Server error 8645 (Problemen met SQL Server fout 8645 oplossen) voor meer informatie.
8902: Fout bij het toewijzen van geheugen tijdens dbcc-uitvoering.
9695 of 9696: Fout bij het toewijzen van geheugen voor Service Broker-bewerkingen.
17131 of 17132: opstartfout van de server vanwege onvoldoende geheugen.
17890: Fout bij het toewijzen van geheugen omdat SQL-geheugen wordt weggezet door het besturingssysteem.
22986 of 22987: Fouten bij het vastleggen van gegevens wijzigen vanwege onvoldoende geheugen.
25601: Xevent-engine heeft onvoldoende geheugen.
26053: SQL-netwerkinterfaces kunnen niet worden geïnitialiseerd vanwege onvoldoende geheugen.
30085, 30086, 30094: SQL-bewerkingen in volledige tekst mislukken vanwege onvoldoende geheugen.

Oorzaak

Veel factoren kunnen leiden tot onvoldoende geheugen. Dergelijke factoren zijn onder andere de instellingen van het besturingssysteem, de beschikbaarheid van fysiek geheugen, onderdelen die gebruikmaken van geheugen in SQL Server en geheugenlimieten voor de huidige workload. In de meeste gevallen is de query die mislukt met een fout met onvoldoende geheugen niet de oorzaak van deze fout. Over het algemeen kunnen de oorzaken worden gegroepeerd in drie categorieën:

Oorzaak 1: Geheugendruk voor extern of besturingssysteem

Externe druk verwijst naar een hoog geheugengebruik dat afkomstig is van een onderdeel buiten het proces dat leidt tot onvoldoende geheugen voor SQL Server. U moet nagaan of andere toepassingen op het systeem geheugen verbruiken en bijdragen aan een lage beschikbaarheid van het geheugen. SQL Server is een van de weinige toepassingen die zijn ontworpen om te reageren op geheugendruk van het besturingssysteem door het geheugengebruik te verlagen. Dit betekent dat als een toepassing of stuurprogramma geheugen aanvraagt, het besturingssysteem een signaal verzendt naar alle toepassingen om geheugen vrij te maken en SQL Server reageert door het eigen geheugengebruik te verminderen. Weinig andere toepassingen reageren omdat ze niet zijn ontworpen om naar die melding te luisteren. Dus als SQL Server begint met het verminderen van het geheugengebruik, wordt de geheugengroep verminderd en krijgen de onderdelen die geheugen nodig hebben het mogelijk niet. Als gevolg hiervan krijgt u 701- of andere geheugengerelateerde fouten. Zie SQL Server Geheugenarchitectuur voor meer informatie over hoe SQL dynamisch geheugen toewijst en vrij maakt. Zie Externe geheugendruk in dit artikel voor meer gedetailleerde diagnostische gegevens en oplossingen voor het probleem.

Er zijn drie brede categorieën problemen die geheugenbelasting van het besturingssysteem kunnen veroorzaken:

Toepassingsgerelateerde problemen: een of meer toepassingen samen gebruiken het beschikbare fysieke geheugen. Het besturingssysteem reageert op nieuwe toepassingsaanvragen voor resources door geheugen vrij te maken. De algemene aanpak is om te bepalen welke toepassingen het geheugen uitputten en de benodigde stappen te ondernemen om het geheugen in balans te brengen zonder tot RAM-uitputting te leiden.
Problemen met apparaatstuurprogramma's: apparaatstuurprogramma's kunnen ervoor zorgen dat alle processen in de werkset worden geplaatst als het stuurprogramma ten onrechte een geheugentoewijzingsfunctie aanroept.
Productproblemen met het besturingssysteem.

Raadpleeg MSSQLSERVER_17890 voor een gedetailleerde uitleg van deze en probleemoplossingsstappen.

Oorzaak 2: Interne geheugendruk, niet afkomstig van SQL Server

Interne geheugendruk verwijst naar een lage geheugen beschikbaarheid veroorzaakt door factoren in het SQL Server proces. Sommige onderdelen die in het SQL Server-proces kunnen worden uitgevoerd, zijn 'extern' van de SQL Server-engine. Voorbeelden hiervan zijn OLE DB-providers (DLL's), zoals gekoppelde servers, SQLCLR-procedures of -functies, uitgebreide procedures (XPs) en OLE Automation (sp_OA*). Andere omvatten antivirusprogramma's of andere beveiligingsprogramma's die DLL's in een proces injecteren voor bewakingsdoeleinden. Een probleem of een slecht ontwerp in een van deze onderdelen kan leiden tot een groot geheugenverbruik. Overweeg bijvoorbeeld een gekoppelde server om 20 miljoen rijen met gegevens uit een externe bron in SQL Server geheugen op te nemen. Wat SQL Server betreft, rapporteert geen enkele geheugenmedewerker een hoog geheugengebruik, maar het geheugen dat binnen het SQL Server proces wordt verbruikt, is hoog. Deze geheugengroei van een gekoppelde server-DLL zou er bijvoorbeeld voor zorgen dat SQL Server het geheugengebruik begint te verminderen (zie hierboven) en zorgt voor weinig geheugen voor onderdelen in SQL Server, waardoor er geheugenfouten optreden. Zie Interne geheugendruk, niet afkomstig van SQL Server voor meer gedetailleerde diagnostische gegevens en oplossingen voor het probleem.

Opmerking

Enkele Microsoft-DLL's die worden gebruikt in de SQL Server procesruimte (bijvoorbeeld MSOLEDBSQL , SQL Native Client) kunnen interfacen met SQL Server geheugeninfrastructuur voor rapportage en toewijzing. U kunt uitvoeren select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' om een lijst met deze items op te halen en dat geheugenverbruik voor sommige van hun toewijzingen bij te houden.

Oorzaak 3: Interne geheugenbelasting afkomstig van SQL Server component(s)

Interne geheugendruk die afkomstig is van onderdelen in de SQL Server-engine kan ook leiden tot fouten met onvoldoende geheugen. Er worden honderden onderdelen bijgehouden via geheugenmedewerkers die geheugen toewijzen in SQL Server. U moet bepalen welke geheugenmedewerker(s) verantwoordelijk zijn voor de grootste geheugentoewijzingen om dit probleem op te lossen. Als u bijvoorbeeld merkt dat de OBJECTSTORE_LOCK_MANAGER geheugenmedewerker een grote geheugentoewijzing weergeeft, moet u begrijpen waarom Lock Manager zoveel geheugen verbruikt. Mogelijk zijn er query's die veel vergrendelingen verkrijgen. U kunt deze query's optimaliseren met behulp van indexen, het verkorten van transacties die vergrendelingen lange tijd bewaren of controleren of escalatie van vergrendelingen is uitgeschakeld. Elke geheugenmedewerker of elk onderdeel heeft een unieke manier om het geheugen te openen en te gebruiken. Zie typen geheugenmedewerkers en hun beschrijvingen voor meer informatie. Zie Intern geheugengebruik door SQL Server engine voor meer gedetailleerde diagnostische gegevens en oplossingen voor het probleem.

Visuele weergave van de typen geheugendruk

In de volgende grafiek ziet u de typen druk die kunnen leiden tot onvoldoende geheugen in SQL Server:

Schermopname van typen geheugenbelasting.

Diagnostische hulpprogramma's voor het verzamelen van gegevens voor probleemoplossing

U kunt de volgende diagnostische hulpprogramma's gebruiken om gegevens voor probleemoplossing te verzamelen:

Prestatiemeter

Configureer en verzamel de volgende tellers met Prestatiemeter:

Geheugen:Beschikbare MBytes
Proces:Werkset
Proces:privébytes
SQL Server:Memory Manager: (alle tellers)
SQL Server:Bufferbeheer: (alle tellers)

DMV's of DBCC MEMORYSTATUS

U kunt sys.dm_os_memory_clerks of DBCC MEMORYSTATUS gebruiken om het totale geheugengebruik in SQL Server te bekijken.

Standaardrapport geheugenverbruik in SSMS

Geheugengebruik weergeven in SQL Server Management Studio:

Start SQL Server Management Studio en maak verbinding met een server.
Klik in Objectverkenner met de rechtermuisknop op de naam van het SQL Server exemplaar.
Selecteer in het contextmenu Rapporten>Standaardrapporten>Geheugenverbruik.

PSSDiag of SQL LogScout

Een alternatieve, geautomatiseerde manier om deze gegevenspunten vast te leggen, is door hulpprogramma's zoals PSSDiag of SQL LogScout te gebruiken.

Als u PSSDiag gebruikt, configureert u deze om de Perfmon-collector en de verzamelaar Aangepaste diagnostische gegevens\SQL-geheugenfout vast te leggen.
Als u SQL LogScout gebruikt, configureert u dit om het scenario Geheugen vast te leggen.

In de volgende secties worden gedetailleerdere stappen beschreven voor elk scenario (externe of interne geheugendruk).

Methodologie voor probleemoplossing

Als er af en toe of voor een korte periode een fout met onvoldoende geheugen wordt weergegeven, kan er een tijdelijk geheugenprobleem zijn dat vanzelf wordt opgelost. In dergelijke gevallen hoeft u mogelijk geen actie te ondernemen. Als de fout echter meerdere keren optreedt bij meerdere verbindingen en gedurende een periode van seconden of langer aanhoudt, volgt u de diagnostische gegevens en oplossingen in de volgende secties om geheugenfouten verder op te lossen.

Druk op extern geheugen

Gebruik de volgende methoden om problemen met weinig geheugen op het systeem te diagnosticeren buiten het SQL Server proces:

Prestatiemeteritems verzamelen. Onderzoek of andere toepassingen of services dan SQL Server geheugen op deze server verbruiken door deze tellers te bekijken:

Geheugen:Beschikbare MBytes
Proces:Werkset
Proces:privébytes

Hier volgt een voorbeeld van perfmon-logboekverzameling met behulp van PowerShell:

clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\Memory\Available MBytes"),
   ("\\$serverName" +"\Process(*)\Working Set"),
   ("\\$serverName" +"\Process(*)\Private Bytes")
)

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
$_.CounterSamples | ForEach-Object   {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
}
}

Bekijk het systeemgebeurtenislogboek en zoek naar geheugengerelateerde fouten (bijvoorbeeld weinig virtueel geheugen).
Bekijk het logboek van de toepassingsgebeurtenis voor problemen met toepassingsgerelateerd geheugen.

Hier volgt een voorbeeld van een PowerShell-script om een query uit te voeren op de systeem- en toepassingslogboeken voor het trefwoord 'geheugen'. Gebruik gerust andere tekenreeksen, zoals 'resource' voor uw zoekopdracht:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Los eventuele code- of configuratieproblemen op voor minder kritieke toepassingen of services om hun geheugengebruik te verminderen.
Als toepassingen naast SQL Server resources verbruiken, kunt u deze toepassingen stoppen of opnieuw plannen of overwegen om ze op een afzonderlijke server uit te voeren. Met deze stappen wordt de externe geheugendruk verwijderd.

Interne geheugendruk, niet afkomstig van SQL Server

Gebruik de volgende methoden om interne geheugenbelasting te diagnosticeren die wordt veroorzaakt door modules (DLL's) in SQL Server:

Als SQL Server geen vergrendelde pagina's in het geheugen (AWE-API) gebruikt, wordt het grootste deel van het geheugen weergegeven in de teller Process:Private Bytes (SQLServrexemplaar) in Performance Monitor. Het totale geheugengebruik dat afkomstig is van de SQL Server-engine, wordt weergegeven in de teller SQL Server:Memory Manager: Total Server Memory (KB). Als u een aanzienlijk verschil vindt tussen de waarde Process:Private Bytes en SQL Server:Memory Manager: Total Server Memory (KB), is dat verschil waarschijnlijk afkomstig van een DLL (gekoppelde server, XP, SQLCLR, enzovoort). Als privé-bytes bijvoorbeeld 300 GB zijn en totaal servergeheugen 250 GB is, komt ongeveer 50 GB van het totale geheugen in het proces van buiten de SQL Server-engine.
Als SQL Server gebruikmaakt van vergrendelde pagina's in het geheugen (AWE-API), is het lastiger om het probleem te identificeren omdat de prestatiemeter geen AWE-tellers biedt die het geheugengebruik voor afzonderlijke processen bijhouden. Het totale geheugengebruik binnen de SQL Server engine wordt weergegeven in de teller SQL Server:Memory Manager: Total Server Memory (KB). Typisch proces: waarden voor privébytes kunnen variëren tussen 300 MB en 1-2 GB in totaal. Als u een aanzienlijk gebruik van Process:Private Bytes vindt buiten dit normale gebruik, is het verschil waarschijnlijk afkomstig van een DLL (gekoppelde server, XP, SQLCLR, enzovoort). Als de teller privébytes bijvoorbeeld 4-5 GB is en SQL Server vergrendelde pagina's in geheugen (AWE) gebruikt, kan een groot deel van de privébytes afkomstig zijn van buiten de SQL Server-engine. Dit is een benaderingstechniek.
Gebruik het hulpprogramma Taaklijst om dll's te identificeren die in SQL Server ruimte zijn geladen:
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
U kunt ook de volgende query gebruiken om geladen modules (DLL's) te onderzoeken en te zien of er iets onverwachts is.
```
SELECT * FROM sys.dm_os_loaded_modules
```
Als u vermoedt dat een gekoppelde servermodule een aanzienlijk geheugenverbruik veroorzaakt, kunt u deze zo configureren dat deze niet meer wordt verwerkt door de optie Inverwerking toestaan uit te schakelen. Zie Gekoppelde servers maken voor meer informatie. Niet alle OLE DB-providers van de gekoppelde server kunnen niet meer worden verwerkt. Neem voor meer informatie contact op met de fabrikant van het product.
In het zeldzame geval waarin OLE-automatiseringsobjecten (sp_OA*) worden gebruikt, kunt u het object configureren om te worden uitgevoerd in een proces buiten SQL Server door een contextwaarde van 4 op te geven (alleen lokale (.exe) OLE-server). Zie sp_OACreate voor meer informatie.

Intern geheugengebruik door SQL Server-engine

Gebruik de volgende methoden om de interne geheugendruk te diagnosticeren die afkomstig is van onderdelen in de SQL Server-engine:

Begin met het verzamelen van prestatiemeteritems voor SQL Server: SQL Server:BufferBeheer en SQL Server: Memory Manager.
Voer meerdere keren een query uit op de DMV van de SQL Server-geheugenmedewerkers om te zien waar het hoogste geheugenverbruik plaatsvindt in de engine:
```
SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC
```
U kunt ook de gedetailleerdere DBCC MEMORYSTATUS uitvoer en de manier waarop deze verandert bekijken wanneer u deze foutberichten ziet.
```
DBCC MEMORYSTATUS
```
Als u een duidelijke overtreder onder de geheugenmedewerkers identificeert, richt u zich op het aanpakken van de specifieke gegevens van het geheugenverbruik voor dat onderdeel. Hier volgen enkele voorbeelden:
- Als de geheugenmedewerker MEMORYCLERK_SQLQERESERVATIONS geheugen verbruikt, identificeert u query's die gebruikmaken van enorme geheugentoekenningen en optimaliseert u deze via indexen, herschrijft u ze (bijvoorbeeld verwijderen ORDER by) of past u hints voor geheugentoekenningen toe (zie min_grant_percent en max_grant_percent hints ). U kunt ook een resource governor-pool maken om het gebruik van geheugentoestemmingsgeheugen te beheren. Zie Problemen met trage prestaties of weinig geheugen oplossen die worden veroorzaakt door geheugentoelagen in SQL Server voor gedetailleerde informatie over geheugentoelagen.
- Als een groot aantal ad-hocqueryplannen in de cache wordt opgeslagen, gebruikt de CACHESTORE_SQLCP geheugenmedewerker grote hoeveelheden geheugen. Identificeer niet-geparameteriseerde query's waarvan queryplannen niet opnieuw kunnen worden gebruikt en parameteriseer ze door te converteren naar opgeslagen procedures, met behulp van sp_executesql, of door parameterisatie te gebruiken FORCED . Als u traceringsvlag 174 hebt ingeschakeld, kunt u deze uitschakelen om te zien of het probleem hiermee is opgelost.
- Als de cacheopslag CACHESTORE_OBJCP van het objectplan te veel geheugen verbruikt, identificeert u welke opgeslagen procedures, functies of triggers grote hoeveelheden geheugen gebruiken en ontwerpt u de toepassing mogelijk opnieuw. Meestal kan dit gebeuren als gevolg van grote hoeveelheden databases of schema's met honderden procedures in elke database.
- Als de OBJECTSTORE_LOCK_MANAGER geheugenmedewerker grote geheugentoewijzingen toont, identificeert u query's die veel vergrendelingen toepassen en optimaliseert u deze met behulp van indexen. Verkort transacties die ertoe leiden dat vergrendelingen gedurende lange perioden niet worden vrijgegeven in bepaalde isolatieniveaus of controleer of escalatie van vergrendelingen is uitgeschakeld.
- Als u zeer groot TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore') ziet, kunt u traceringsvlag 4618 gebruiken om de grootte van de cache te beperken.
- Als u geheugenproblemen ziet met In-Memory OLTP afkomstig van de MEMORYCLERK_XTP geheugenmedewerker, kunt u geheugengebruik bewaken en oplossen voor In-Memory OLTP en geheugengeoptimale tempdb-metagegevens (HkTempDB) van geheugenfouten raadplegen.

Snelle verlichting die geheugen beschikbaar kan maken

De volgende acties kunnen geheugen vrijmaken en beschikbaar maken voor SQL Server:

Instellingen voor geheugenconfiguratie wijzigen

Controleer de volgende SQL Server parameters voor geheugenconfiguratie en overweeg indien mogelijk het maximum aantal servergeheugens te verhogen:

maximaal servergeheugen
minimale servergeheugen

Opmerking

Als u ongebruikelijke instellingen ziet, corrigeer deze indien nodig en houd rekening met verhoogde geheugenvereisten. Standaardinstellingen worden vermeld in Configuratieopties voor servergeheugen.

Als u het maximale servergeheugen niet hebt geconfigureerd, met name met vergrendelde pagina's in het geheugen, kunt u overwegen om dit in te stellen op een bepaalde waarde om wat geheugen voor het besturingssysteem toe te staan. Zie de configuratieoptie Vergrendelde pagina's in geheugenserver .

Werkbelasting van het systeem wijzigen of verplaatsen

Onderzoek de queryworkload: het aantal gelijktijdige sessies, dat momenteel query's uitvoert, en kijk of er minder kritieke toepassingen zijn die tijdelijk kunnen worden gestopt of naar een andere SQL Server kunnen worden verplaatst.

Voor alleen-lezenwerkbelastingen kunt u deze verplaatsen naar een secundaire replica met het kenmerk Alleen-lezen in een AlwaysOn-omgeving. Zie Alleen-lezen workload offloaden naar secundaire replica van een AlwaysOn-beschikbaarheidsgroep en Alleen-lezentoegang configureren voor een secundaire replica van een AlwaysOn-beschikbaarheidsgroep voor meer informatie.

Zorg voor de juiste geheugenconfiguratie voor virtuele machines

Als u SQL Server uitvoert op een virtuele machine (VM), controleert u of het geheugen voor de VM niet te veel wordt overgecommitteerd. Voor ideeën over het configureren van geheugen voor VM's raadpleegt u Virtualization - Overcommitting memory and how to detect it in the VM and Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment).

Geheugen vrijgeven in SQL Server

U kunt een of meer van de volgende DBCC-opdrachten uitvoeren om meerdere SQL Server geheugencaches vrij te maken:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

SQL Server-service opnieuw starten

In sommige gevallen kunt u overwegen de service opnieuw op te starten als u te maken hebt met kritieke geheugenuitputting en SQL Server geen query's kan verwerken.

Overweeg het gebruik van Resource Governor voor specifieke scenario's

Als u Resource Governor gebruikt, raden we u aan de instellingen van de resourcegroep en workloadgroep te controleren om te zien of ze het geheugen niet te drastisch beperken.

Meer RAM-geheugen toevoegen op de fysieke of virtuele server

Als het probleem zich blijft voordoen, moet u verder onderzoeken en mogelijk serverresources (RAM) verhogen.

Delen via