Felsöka problem med minnesbrist eller minnesbrist i SQL Server

Artikel
03/21/2024

Symptom

SQL Server använder en komplex minnesarkitektur som motsvarar den komplexa och omfattande funktionsuppsättningen. På grund av de olika minnesbehoven kan det finnas många källor till minnesförbrukning och minnestryck, vilket i slutändan orsakar minnesbrist.

Det finns vanliga fel som indikerar att det finns ont om minne i SQL Server. Exempel på fel är:

701: Det gick inte att allokera tillräckligt med minne för att köra en fråga.
802: Det gick inte att hämta minne för att allokera sidor i buffertpoolen (data eller indexsidor).
1204: Det gick inte att allokera minne för lås.
6322: Det gick inte att allokera minne för XML-parser.
6513:Det gick inte att initiera CLR på grund av minnesbelastning.
6533: AppDomain har inaktiverats på grund av slut på minne.
8318: Det gick inte att läsa in SQL-prestandaräknare på grund av otillräckligt minne.
8356 eller 8359: ETW- eller SQL-spårningen kan inte köras på grund av lite minne.
8556: Det gick inte att läsa in MSDTC på grund av otillräckligt minne.
8645: Det gick inte att köra en fråga på grund av att det inte finns minne för minnestilldelningar (sortering och hashning) Mer information finns i Felsöka SQL Server fel 8645.
8902: Det gick inte att allokera minne under DBCC-körningen.
9695 eller 9696: Det gick inte att allokera minne för Service Broker-åtgärder.
17131 eller 17132: Serverstartfel på grund av otillräckligt minne.
17890: Det gick inte att allokera minne på grund av att SQL-minnet har bläddrats ut av operativsystemet.
22986 eller 22987: Ändra datainsamlingsfel på grund av otillräckligt minne.
25601: Xevent-motorn har slut på minne.
26053: SQL-nätverksgränssnitten initieras inte på grund av otillräckligt minne.
30085, 30086, 30094: SQL-fulltextåtgärder misslyckas på grund av otillräckligt minne.

Orsak

Många faktorer kan orsaka otillräckligt minne. Sådana faktorer omfattar operativsysteminställningar, tillgänglighet för fysiskt minne, komponenter som använder minne inuti SQL Server och minnesgränser för den aktuella arbetsbelastningen. I de flesta fall är frågan som misslyckas med ett minnesfel inte orsaken till det här felet. Sammantaget kan orsakerna grupperas i tre kategorier:

Orsak 1: Externt minnestryck eller os-minnestryck

Externt tryck syftar på hög minnesanvändning som kommer från en komponent utanför processen som leder till otillräckligt minne för SQL Server. Du måste ta reda på om andra program i systemet förbrukar minne och bidrar till låg minnestillgänglighet. SQL Server är ett av de få program som har utformats för att svara på minnesbelastningen i operativsystemet genom att minska minnesanvändningen. Det innebär att om ett program eller en drivrutin begär minne skickar operativsystemet en signal till alla program för att frigöra minne, och SQL Server svarar genom att minska sin egen minnesanvändning. Få andra program svarar eftersom de inte är utformade för att lyssna efter det meddelandet. Om SQL Server börjar minska minnesanvändningen minskas därför minnespoolen och de komponenter som behöver minne kanske inte får den. Därför börjar du få 701- eller andra minnesrelaterade fel. Mer information om hur SQL dynamiskt allokerar och frigör minne finns i SQL Server Minnesarkitektur. Mer detaljerad diagnostik och lösningar för problemet finns i Externt minnestryck i den här artikeln.

Det finns tre breda kategorier av problem som kan orsaka minnesbelastning i operativsystemet:

Programrelaterade problem: Ett eller flera program uttömmer tillsammans det tillgängliga fysiska minnet. Operativsystemet svarar på nya programbegäranden för resurser genom att försöka frigöra lite minne. Den vanliga metoden är att ta reda på vilka program som uttömmer minnet och vidta nödvändiga åtgärder för att balansera minnet mellan dem utan att leda till RAM-överbelastning.
Problem med enhetsdrivrutiner: Enhetsdrivrutiner kan orsaka arbetsuppsättningsväxling för alla processer om drivrutinen felaktigt anropar en minnesallokeringsfunktion.
Problem med operativsystemprodukt.

En detaljerad förklaring av dessa steg och felsökningssteg finns i MSSQLSERVER_17890.

Orsak 2: Internt minnestryck som inte kommer från SQL Server

Internt minnestryck avser låg minnestillgänglighet som orsakas av faktorer i SQL Server processen. Vissa komponenter som kan köras i SQL Server processen är "externa" för SQL Server motorn. Exempel är OLE DB-providers (DLL:er) som länkade servrar, SQLCLR-procedurer eller funktioner, utökade procedurer (XPs) och OLE Automation (sp_OA*). Andra inkluderar antivirusprogram eller andra säkerhetsprogram som matar in DLL:er i en process i övervakningssyfte. Ett problem eller dålig design i någon av dessa komponenter kan leda till stor minnesförbrukning. Anta till exempel att en länkad server cachelagra 20 miljoner rader data från en extern källa till SQL Server minne. När det gäller SQL Server kommer ingen minnestjänsteman att rapportera hög minnesanvändning, men minnet som förbrukas i SQL Server processen kommer att vara högt. Den här minnestillväxten från en länkad server-DLL skulle till exempel leda till att SQL Server börjar minska minnesanvändningen (se ovan) och skapar låga minnesförhållanden för komponenter inuti SQL Server, vilket orsakar minnesfel. Mer detaljerad diagnostik och lösningar på problemet finns i Internt minnestryck, som inte kommer från SQL Server.

Obs!

Några Microsoft DLL:er som används i SQL Server processutrymme (till exempel MSOLEDBSQL , SQL Native Client) kan samverka med SQL Server minnesinfrastruktur för rapportering och allokering. Du kan köra select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' för att hämta en lista över dem och spåra minnesförbrukningen för vissa av deras allokeringar.

Orsak 3: Internt minnestryck från SQL Server komponenter

Internt minnestryck från komponenter i SQL Server-motorn kan också leda till minnesfel. Det finns hundratals komponenter som spåras via minnesbiträden som allokerar minne i SQL Server. Du måste identifiera vilka minnesbiträden som ansvarar för de största minnesallokeringarna för att lösa problemet. Om du till exempel upptäcker att OBJECTSTORE_LOCK_MANAGER minnestjänstemannen visar en stor minnesallokering måste du förstå varför Låshanteraren förbrukar så mycket minne. Det kan finnas frågor som hämtar många lås. Du kan optimera dessa frågor med hjälp av index, förkorta alla transaktioner som låser sig under en längre tid eller kontrollera om låseskalering är inaktiverat. Varje minnestjänsteman eller komponent har ett unikt sätt att komma åt och använda minne. Mer information finns i typer av minnesbiträden och deras beskrivningar. Mer detaljerad diagnostik och lösningar på problemet finns i Intern minnesanvändning efter SQL Server motor.

Visuell representation av minnesbelastningstyperna

Följande diagram illustrerar de typer av tryck som kan leda till minnesbrist i SQL Server:

Skärmbild av minnestryckstyper.

Diagnostikverktyg för att samla in felsökningsdata

Du kan använda följande diagnostikverktyg för att samla in felsökningsdata:

Prestandaövervakare

Konfigurera och samla in följande räknare med Prestandaövervakaren:

Minne:Tillgängliga MByte
Process:Arbetsuppsättning
Process:Privata byte
SQL Server:Memory Manager: (alla räknare)
SQL Server:Buffer Manager: (alla räknare)

DMV:er eller DBCC MEMORYSTATUS

Du kan använda sys.dm_os_memory_clerks eller DBCC MEMORYSTATUS för att observera den totala minnesanvändningen i SQL Server.

Standardrapport för minnesförbrukning i SSMS

Visa minnesanvändning i SQL Server Management Studio:

Starta SQL Server Management Studio och anslut till en server.
Högerklicka på SQL Server instansnamn i Object Explorer.
I snabbmenyn väljer du Rapporter>StandardRapporter>Minnesförbrukning.

PSSDiag eller SQL LogScout

Ett alternativt automatiserat sätt att samla in dessa datapunkter är att använda verktyg som PSSDiag eller SQL LogScout.

Om du använder PSSDiag konfigurerar du det för att avbilda Perfmon-insamlaren och insamlaren Custom Diagnostics\SQL Memory Error .
Om du använder SQL LogScout konfigurerar du det för att avbilda minnesscenariot .

I följande avsnitt beskrivs mer detaljerade steg för varje scenario (externt eller internt minnestryck).

Felsökningsmetodik

Om ett minnesfel ibland uppstår eller under en kort period kan det finnas ett kortlivade minnesproblem som löser sig självt. Du kanske inte behöver vidta åtgärder i dessa fall. Men om felet inträffar flera gånger på flera anslutningar och kvarstår i sekunder eller längre följer du diagnostiken och lösningarna i följande avsnitt för att felsöka minnesfel ytterligare.

Externt minnestryck

Använd följande metoder för att diagnostisera minnesbrist i systemet utanför SQL Server processen:

Samla in prestandaövervakningsräknare. Undersök om andra program eller tjänster än SQL Server förbrukar minne på den här servern genom att titta på dessa räknare:

Minne:Tillgängliga MByte
Process:Arbetsuppsättning
Process:Privata byte

Här är ett exempel på Perfmon-loggsamling med 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)) }
}
}

Granska systemhändelseloggen och leta efter minnesrelaterade fel (till exempel lite virtuellt minne).
Granska programhändelseloggen för programrelaterade minnesproblem.

Här är ett exempel på ett PowerShell-skript för att fråga system- och programhändelseloggarna efter nyckelordet "minne". Använd gärna andra strängar som "resurs" för sökningen:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Åtgärda eventuella kod- eller konfigurationsproblem för mindre kritiska program eller tjänster för att minska minnesanvändningen.
Om program förutom SQL Server förbrukar resurser kan du prova att stoppa eller schemalägga om dessa program eller överväga att köra dem på en separat server. De här stegen tar bort externt minnestryck.

Internt minnestryck som inte kommer från SQL Server

Använd följande metoder för att diagnostisera internt minnestryck som orsakas av moduler (DLL: er) i SQL Server:

Om SQL Server inte använder AWE API (Locked Pages in Memory) återspeglas det mesta av minnet i räknaren Process:Private Bytes (SQLServrinstans) i Prestandaövervakaren. Den totala minnesanvändningen som kommer inifrån SQL Server-motorn återspeglas i räknaren SQL Server:Memory Manager: Total Server Memory (KB). Om du hittar en betydande skillnad mellan värdet Process:Private Bytes och SQL Server:Memory Manager: Total Server Memory (KB), kommer den skillnaden troligen från en DLL (länkad server, XP, SQLCLR och så vidare). Om privata byte till exempel är 300 GB och det totala serverminnet är 250 GB kommer cirka 50 GB av det totala minnet i processen utanför SQL Server motorn.
Om SQL Server använder AWE API (Locked Pages in Memory) är det svårare att identifiera problemet eftersom Prestandaövervakaren inte erbjuder AWE-räknare som spårar minnesanvändning för enskilda processer. Den totala minnesanvändningen i SQL Server-motorn återspeglas i räknaren SQL Server:Memory Manager: Total Server Memory (KB). Typiska värden för process:Privata byte kan variera mellan 300 MB och 1–2 GB totalt. Om du hittar en betydande användning av Process:Privata byte utöver den här vanliga användningen kommer troligen skillnaden från en DLL (länkad server, XP, SQLCLR och så vidare). Om räknaren för privata byte till exempel är 4–5 GB och SQL Server använder låsta sidor i minnet (AWE), kan en stor del av de privata byteen komma utanför SQL Server motorn. Det här är en uppskattningsteknik.
Använd verktyget Tasklist för att identifiera DLL:er som läses in i SQL Server utrymme:
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
Du kan också använda följande fråga för att undersöka inlästa moduler (DLL: er) och se om något oväntat finns där.
```
SELECT * FROM sys.dm_os_loaded_modules
```
Om du misstänker att en länkad servermodul orsakar betydande minnesförbrukning kan du konfigurera den så att den tar slut genom att inaktivera alternativet Tillåt inprocess . Mer information finns i Skapa länkade servrar . Alla OLE DB-leverantörer för länkade servrar kan inte få slut på processen. Kontakta produkttillverkaren om du vill ha mer information.
I sällsynta fall där OLE-automatiseringsobjekt (sp_OA*) används kan du konfigurera objektet så att det körs i en process utanför SQL Server genom att ange kontextvärdet 4 (endast lokal (.exe) OLE-server). Mer information finns i sp_OACreate.

Intern minnesanvändning av SQL Server motor

Använd följande metoder för att diagnostisera internt minnestryck som kommer från komponenter i SQL Server-motorn:

Börja samla in prestandaövervakningsräknare för SQL Server: SQL Server:Buffer Manager och SQL Server: Memory Manager.

Fråga SQL Server minnesansvariga DMV flera gånger för att se var den högsta minnesförbrukningen inträffar i motorn:

SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

Du kan också se mer detaljerade DBCC MEMORYSTATUS utdata och hur de ändras när du ser dessa felmeddelanden.
```
DBCC MEMORYSTATUS
```
Om du identifierar en tydlig gärningsman bland minnesbiträdena fokuserar du på att ta itu med detaljerna om minnesförbrukning för den komponenten. Här är flera exempel:
- Om minnestjänstemannen MEMORYCLERK_SQLQERESERVATIONS förbrukar minne kan du identifiera frågor som använder stora minnestillslag och optimera dem via index, skriva om dem (ta bort ORDER bytill exempel) eller använda frågetips för minnesbeviljande (se min_grant_percent och max_grant_percent tips ). Du kan också skapa en resursguvernörspool för att styra användningen av minne som beviljar minne. Detaljerad information om minnestillskott finns i Felsöka problem med långsamma prestanda eller minnesbrist som orsakas av minnestillskott i SQL Server.
- Om ett stort antal ad hoc-frågeplaner cachelagras skulle minnestjänstemannen CACHESTORE_SQLCP använda stora mängder minne. Identifiera icke-parametriserade frågor vars frågeplaner inte kan återanvändas och parametrisera dem genom att konvertera till lagrade procedurer, med hjälp sp_executesqlav eller med hjälp FORCED av parameterisering. Om du har aktiverat spårningsflagga 174 kan du inaktivera den för att se om det löser problemet.
- Om cachelagret CACHESTORE_OBJCP för objektplanen förbrukar för mycket minne identifierar du vilka lagrade procedurer, funktioner eller utlösare som använder stora mängder minne och eventuellt gör om programmet. Detta kan vanligtvis inträffa på grund av stora mängder databaser eller scheman med hundratals procedurer i varje.
- Om minnestjänstemannen OBJECTSTORE_LOCK_MANAGER visar stora minnesallokeringar identifierar du frågor som tillämpar många lås och optimerar dem med hjälp av index. Korta ned transaktioner som gör att lås inte släpps under långa perioder på vissa isoleringsnivåer eller kontrollera om låseskalering är inaktiverat.
- Om du observerar mycket stor TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore') kan du använda spårningsflagga 4618 för att begränsa cachens storlek.
- Om du ser minnesproblem med In-Memory OLTP som kommer från MEMORYCLERK_XTP minnestjänstemannen kan du läsa Övervaka och felsöka minnesanvändning för In-Memory OLTP - och minnesoptimerade tempdb-metadata (HkTempDB) för minnesfel.

Snabb lättnad som kan göra minnet tillgängligt

Följande åtgärder kan frigöra minne och göra det tillgängligt för SQL Server:

Ändra inställningar för minneskonfiguration

Kontrollera följande SQL Server minneskonfigurationsparametrar och överväg att öka det maximala serverminnet om det är möjligt:

maximalt serverminne
minsta serverminne

Obs!

Om du märker ovanliga inställningar korrigerar du dem efter behov och tar hänsyn till ökade minnesbehov. Standardinställningar visas i Konfigurationsalternativ för serverminne.

Om du inte har konfigurerat maximalt serverminne, särskilt med låsta sidor i minnet, bör du överväga att ange ett visst värde för att tillåta lite minne för operativsystemet. Se konfigurationsalternativet Låsta sidor i minnesservern .

Ändra eller flytta arbetsbelastningen från systemet

Undersök frågearbetsbelastningen: antalet samtidiga sessioner, som för närvarande kör frågor och se om det finns mindre kritiska program som kan stoppas tillfälligt eller flyttas till en annan SQL Server.

Överväg att flytta dem till en skrivskyddad sekundär replik i en AlwaysOn-miljö för skrivskyddade arbetsbelastningar. Mer information finns i Avlasta skrivskyddad arbetsbelastning till en sekundär replik av en AlwaysOn-tillgänglighetsgrupp och Konfigurera skrivskyddad åtkomst till en sekundär replik av en AlwaysOn-tillgänglighetsgrupp.

Se till att det finns rätt minneskonfiguration för virtuella datorer

Om du kör SQL Server på en virtuell dator (VM) kontrollerar du att minnet för den virtuella datorn inte överskrids. Information om hur du konfigurerar minne för virtuella datorer finns i Virtualisering – Överansträngt minne och hur du identifierar det i den virtuella datorn och Felsöka prestandaproblem för virtuella ESX/ESXi-datorer (överallokering av minne).

Frigör minne i SQL Server

Du kan köra ett eller flera av följande DBCC-kommandon för att frigöra flera SQL Server minnescacheminnen:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Starta om SQL Server-tjänsten

I vissa fall, om du behöver hantera kritisk överbelastning av minne och SQL Server inte kan bearbeta frågor, kan du överväga att starta om tjänsten.

Överväg att använda Resource Governor för specifika scenarier

Om du använder Resource Governor rekommenderar vi att du kontrollerar inställningarna för resurspoolen och arbetsbelastningsgruppen för att se om de inte begränsar minnet alltför drastiskt.

Lägga till mer RAM-minne på den fysiska eller virtuella servern

Om problemet kvarstår måste du undersöka ytterligare och eventuellt öka serverresurserna (RAM).

Dela via