Risolvere i problemi di memoria esaurita o di memoria insufficiente in SQL Server

Sintomi

SQL Server usa un'architettura di memoria complessa che corrisponde al set di funzionalità complesso e avanzato. A causa della varietà di esigenze di memoria, potrebbero esserci molte fonti di utilizzo della memoria e utilizzo della memoria, causando in ultima analisi condizioni di memoria insufficiente.

Ci sono errori comuni che indicano memoria insufficiente in SQL Server. Esempi di errori includono:

• 701: Errore di allocazione di memoria sufficiente per eseguire una query.
• 802: Errore di recupero della memoria per allocare pagine nel pool di buffer (dati o pagine di indice).
• 1204: Errore di allocazione della memoria per i blocchi.
• 6322: Errore di allocazione della memoria per il parser XML.
• 6513: Impossibile inizializzare CLR a causa di un utilizzo elevato della memoria.
• 6533: AppDomain scaricato a causa di memoria insufficiente.
• 8318: errore durante il caricamento dei contatori delle prestazioni SQL a causa di memoria insufficiente.
• 8356 o 8359: L'esecuzione della traccia ETW o SQL non riesce a causa di memoria insufficiente.
• 8556: errore durante il caricamento di MSDTC a causa di memoria insufficiente.
• 8645: Errore durante l'esecuzione di una query a causa di concessioni di memoria (ordinamento e hashing) Per altre informazioni, vedere Come risolvere l'errore di SQL Server 8645.
• 8902: Errore di allocazione della memoria durante l'esecuzione DBCC.
• 9695 o 9696: errore di allocazione della memoria per le operazioni di Service Broker.
• 17131 o 17132: errore di avvio del server a causa di memoria insufficiente.
• 17890: errore di allocazione della memoria a causa del paging della memoria SQL da parte del sistema operativo.
• 18053: L'errore viene stampato in modalità terse perché si è verificato un errore durante la formattazione. Verranno ignorate le tracce, Anali eventi per Windows (ETW), le notifiche e così via.
• 22986 o 22987: errori di Change Data Capture dovuti a memoria insufficiente.
• 25601: il motore Xevent non è in memoria.
• 26053: le interfacce di rete SQL non vengono inizializzate a causa di memoria insufficiente.
• 30085, 30086, 30094: le operazioni full-text SQL hanno esito negativo a causa di memoria insufficiente.

Causa

Molti fattori possono causare memoria insufficiente. Tali fattori includono le impostazioni del sistema operativo, la disponibilità della memoria fisica, i componenti che usano la memoria all'interno di SQL Server e i limiti di memoria per il carico di lavoro corrente. Nella maggior parte dei casi, la query che ha esito negativo con un errore di memoria insufficiente non è la causa di questo errore. In generale, le cause possono essere raggruppate in tre categorie:

Causa 1: Utilizzo elevato di memoria esterna o del sistema operativo

La pressione esterna si riferisce all'utilizzo elevato della memoria proveniente da un componente esterno al processo che porta a memoria insufficiente per SQL Server. È necessario verificare se altre applicazioni nel sistema utilizzano memoria e contribuiscono alla disponibilità di memoria insufficiente. SQL Server è una delle poche applicazioni progettate per rispondere alla pressione della memoria del sistema operativo riducendone l'uso. Ciò significa che se un'applicazione o un driver richiede memoria, il sistema operativo invia un segnale a tutte le applicazioni per liberare memoria e SQL Server risponde riducendo il proprio utilizzo della memoria. Poche altre applicazioni rispondono perché non sono progettate per l'ascolto di tale notifica. Pertanto, se SQL Server inizia a ridurre l'utilizzo della memoria, il pool di memoria viene ridotto e, a qualsiasi componente necessario, la memoria non venga recuperata. Di conseguenza, si inizia a ricevere 701 o altri errori correlati alla memoria. Per altre informazioni su come SQL alloca e libera in modo dinamico la memoria, vedere Architettura della memoria di SQL Server. Per informazioni più dettagliate sulla diagnostica e sulle soluzioni per il problema, vedere Utilizzo della memoria esterna in questo articolo.

Esistono tre ampie categorie di problemi che possono causare un utilizzo elevato della memoria del sistema operativo:

• Problemi relativi all'applicazione: una o più applicazioni insieme esauriscono la memoria fisica disponibile. Il sistema operativo risponderà alle nuove richieste di applicazioni per le risorse provando a liberare memoria. L'approccio comune consiste nel trovare le applicazioni che esauriscono la memoria ed eseguire i passaggi necessari per bilanciare la memoria tra di loro senza portare all'esaurimento della RAM.
• Problemi del driver di dispositivo: i driver di dispositivo possono causare il paging del working set di tutti i processi se il driver chiama erroneamente una funzione di allocazione della memoria.
• Problemi del prodotto del sistema operativo.

Per una spiegazione dettagliata di questi passaggi e risoluzione dei problemi, vedere MSSQLSERVER_17890.

Causa 2: Utilizzo interno della memoria, non proveniente da SQL Server

La pressione interna della memoria si riferisce alla disponibilità di memoria insufficiente causata da fattori all'interno del processo di SQL Server. Alcuni componenti che possono essere eseguiti all'interno del processo di SQL Server sono "esterni" al motore di SQL Server. Alcuni esempi includono provider OLE DB (DLL) come server collegati, procedure o funzioni SQLCLR, procedure estese (XP) e automazione OLE (sp_OA*). Altri includono antivirus o altri programmi di sicurezza che inseriscono DLL all'interno di un processo a scopo di monitoraggio. Un problema o una progettazione insufficiente in uno di questi componenti potrebbe causare un consumo elevato di memoria. Si consideri, ad esempio, un server collegato che memorizza nella cache 20 milioni di righe di dati da un'origine esterna nella memoria di SQL Server. Per quanto riguarda SQL Server, nessun clerk di memoria segnala un utilizzo elevato della memoria, ma la memoria utilizzata all'interno del processo di SQL Server sarà elevata. Questa crescita di memoria da una DLL del server collegato, ad esempio, provocherebbe l'avvio di SQL Server all'interruzione dell'utilizzo della memoria (vedere sopra) e creerà condizioni di memoria insufficiente per i componenti all'interno di SQL Server, causando errori di memoria insufficiente. Per informazioni più dettagliate sulla diagnostica e sulle soluzioni relative al problema, vedere Utilizzo interno della memoria, non proveniente da SQL Server.

Causa 3: Utilizzo interno della memoria proveniente dai componenti di SQL Server

Anche la pressione interna della memoria proveniente dai componenti all'interno del motore di SQL Server può causare errori di memoria insufficiente. Sono disponibili centinaia di componenti rilevati tramite clerk di memoria che allocano memoria in SQL Server. È necessario identificare quali clerk di memoria sono responsabili delle allocazioni di memoria più grandi per risolvere questo problema. Ad esempio, se si rileva che il OBJECTSTORE_LOCK_MANAGER clerk di memoria mostra un'allocazione di memoria di grandi dimensioni, è necessario comprendere perché Gestione blocchi sta consumando tanta memoria. È possibile che siano presenti query che acquisiscono molti blocchi. È possibile ottimizzare queste query usando indici, abbreviando le transazioni che contengono blocchi per molto tempo o verificando se l'escalation dei blocchi è disabilitata. Ogni clerk o componente di memoria ha un modo univoco di accedere e usare la memoria. Per altre informazioni, vedere Tipi di clerk di memoria e le relative descrizioni. Per informazioni più dettagliate sulla diagnostica e sulle soluzioni relative al problema, vedere Utilizzo interno della memoria dal motore di SQL Server.

Rappresentazione visiva dei tipi di pressione di memoria

Il grafico seguente illustra i tipi di pressione che possono causare condizioni di memoria insufficiente in SQL Server:

Strumenti di diagnostica per raccogliere i dati di risoluzione dei problemi

È possibile usare gli strumenti di diagnostica seguenti per raccogliere i dati di risoluzione dei problemi:

Monitoraggio prestazioni

Configurare e raccogliere i contatori seguenti con Monitor prestazioni:

• Memoria:Byte disponibili
• Process:Working Set
• Process:Private Bytes
• SQL Server:Memory Manager: (tutti i contatori)
• SQL Server:Buffer Manager: (tutti i contatori)

DMV o DBCC MEMORYSTATUS

È possibile usare sys.dm_os_memory_clerks o DBCC MEMORYSTATUS per osservare l'utilizzo complessivo della memoria all'interno di SQL Server.

Report Standard utilizzo memoria in SSMS

Visualizzare l'utilizzo della memoria in SQL Server Management Studio:

1. Avviare SQL Server Management Studio e connettersi a un server.
2. In Esplora oggetti fare clic con il pulsante destro del mouse sul nome dell'istanza di SQL Server.
3. Nel menu di scelta rapida selezionare Report standard Consumo>memoria report.>

PSSDiag o SQL LogScout

Un modo alternativo e automatizzato per acquisire questi punti dati consiste nell'usare strumenti come PSSDiag o SQL LogScout.

• Se si usa PSSDiag, configurarlo per acquisire l'agente di raccolta Perfmon e l'agente di raccolta di errori di diagnostica personalizzata\Errore di memoria SQL.

• Se si usa SQL LogScout, configurarlo per acquisire lo scenario di memoria .

Le sezioni seguenti descrivono i passaggi più dettagliati per ogni scenario (pressione di memoria esterna o interna).

Metodologia di risoluzione degli errori

Se occasionalmente viene visualizzato un errore di memoria insufficiente o per un breve periodo, potrebbe verificarsi un problema di memoria di breve durata che si risolve. In questi casi potrebbe non essere necessario intervenire. Tuttavia, se l'errore si verifica più volte in più connessioni e persiste per periodi di secondi o più lunghi, seguire la diagnostica e le soluzioni nelle sezioni seguenti per risolvere ulteriormente gli errori di memoria.

Utilizzo elevato di memoria esterna

Per diagnosticare condizioni di memoria insufficiente nel sistema all'esterno del processo di SQL Server, usare i metodi seguenti:

• Raccogliere Monitor prestazioni contatori. Verificare se le applicazioni o i servizi diversi da SQL Server utilizzano memoria in questo server esaminando questi contatori:

  • Memoria:Byte disponibili
  • Process:Working Set
  • Process:Private Bytes

  Di seguito è riportato un esempio di raccolta di log perfmon con 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)) }
  }
  }
  

• Esaminare il registro eventi di sistema e cercare gli errori correlati alla memoria, ad esempio memoria virtuale insufficiente.

• Esaminare il registro eventi dell'applicazione per individuare problemi di memoria correlati all'applicazione.

  Di seguito è riportato un esempio di script di PowerShell per eseguire una query sui registri eventi di sistema e applicazione per la parola chiave "memory". È possibile usare altre stringhe come "risorsa" per la ricerca:

  Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  

• Risolvere eventuali problemi di codice o configurazione per applicazioni o servizi meno critici per ridurre l'utilizzo della memoria.

• Se le applicazioni oltre a SQL Server utilizzano risorse, provare ad arrestare o riprogrammare queste applicazioni oppure provare a eseguirle in un server separato. Questi passaggi rimuoveranno la pressione di memoria esterna.

Utilizzo della memoria interna, non proveniente da SQL Server

Per diagnosticare la pressione della memoria interna causata da moduli (DLL) all'interno di SQL Server, usare i metodi seguenti:

• Se SQL Server non usa pagine bloccate in memoria (API AWE), la maggior parte della memoria viene riflessa nel contatore Process:Private Bytes (SQLServristanza) in Monitor prestazioni. L'utilizzo complessivo della memoria proveniente dal motore di SQL Server si riflette nel contatore SQL Server:Memory Manager: Total Server Memory (KB). Se si rileva una differenza significativa tra il valore Process:Private Bytes e SQL Server:Memory Manager: Total Server Memory (KB), tale differenza è probabilmente proveniente da una DLL (server collegato, XP, SQLCLR e così via). Ad esempio, se i byte privati sono di 300 GB e Total Server Memory è 250 GB, circa 50 GB della memoria complessiva nel processo provengono dall'esterno del motore di SQL Server.

• Se SQL Server usa pagine bloccate in memoria (API AWE), è più difficile identificare il problema perché il Monitor prestazioni non offre contatori AWE che tengono traccia dell'utilizzo della memoria per singoli processi. L'utilizzo complessivo della memoria all'interno del motore di SQL Server si riflette nel contatore SQL Server:Memory Manager: Total Server Memory (KB). I valori tipici di Process:Private Bytes possono variare tra 300 MB e 1-2 GB nel complesso. Se si rileva un utilizzo significativo di Process:Private Bytes oltre questo uso tipico, è probabile che la differenza provenisse da una DLL (server collegato, XP, SQLCLR e così via). Ad esempio, se il contatore Byte privati è di 4-5 GB e SQL Server usa pagine bloccate in memoria (AWE), una parte importante dei byte privati potrebbe provenire dall'esterno del motore di SQL Server. Si tratta di una tecnica di approssimazione.

• Usare l'utilità Tasklist per identificare le DLL caricate all'interno dello spazio di SQL Server:

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"
  

• È anche possibile usare la query seguente per esaminare i moduli caricati (DLL) e verificare se è presente qualcosa di imprevisto.

  SELECT * FROM sys.dm_os_loaded_modules
  

• Se si sospetta che un modulo del server collegato causi un utilizzo significativo della memoria, è possibile configurarlo per esaurire il processo disabilitando l'opzione Consenti inprocess . Per altre informazioni, vedere Creare server collegati. Non tutti i provider OLE DB del server collegato potrebbero esaurirsi. Per altre informazioni, contattare il produttore del prodotto.

• Nel raro caso in cui vengono usati oggetti di automazione OLE (sp_OA*), è possibile configurare l'oggetto per l'esecuzione in un processo esterno a SQL Server specificando un valore di contesto pari a 4 (solo server OLE (.exe). Per altre informazioni, vedere sp_OACreate.

Utilizzo interno della memoria da parte del motore di SQL Server

Per diagnosticare l'utilizzo di memoria interna proveniente dai componenti all'interno del motore di SQL Server, usare i metodi seguenti:

• Iniziare a raccogliere Monitor prestazioni contatori per SQL Server: Gestione buffer di SQL Server e SQL Server: Gestione memoria.

• Eseguire più volte una query sulla DMV di memoria di SQL Server per verificare dove si verifica il consumo di memoria più elevato all'interno del motore:

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

• In alternativa, è possibile osservare l'output più dettagliato DBCC MEMORYSTATUS e il modo in cui cambia quando vengono visualizzati questi messaggi di errore.

  DBCC MEMORYSTATUS
  

• Se si identifica un trasgressore chiaro tra i clerk di memoria, concentrarsi su come affrontare le specifiche del consumo di memoria per tale componente. Ecco vari esempi:

  • Se il clerk MEMORYCLERK_SQLQERESERVATIONS di memoria usa memoria, identificare le query che usano grandi concessioni di memoria e ottimizzarle tramite indici, riscriverle (rimuovere ORDER by, ad esempio) o applicare hint per la query di concessione di memoria (vedere hint per min_grant_percent e max_grant_percent hint ). È anche possibile creare un pool di Resource Governor per controllare l'utilizzo della memoria concessa in memoria. Per informazioni dettagliate sulle concessioni di memoria, vedere Risolvere i problemi di rallentamento delle prestazioni o memoria insufficiente causati dalle concessioni di memoria in SQL Server.
  • Se un numero elevato di piani di query ad hoc viene memorizzato nella cache, il CACHESTORE_SQLCP clerk di memoria utilizzerebbe grandi quantità di memoria. Identificare query non con parametri i cui piani di query non possono essere riutilizzati e parametrizzarli convertendo in stored procedure, usando sp_executesqlo tramite FORCED la parametrizzazione. Se è stato abilitato il flag di traccia 174, è possibile disabilitarlo per verificare se il problema viene risolto.
  • Se l'archivio CACHESTORE_OBJCP cache del piano di oggetti utilizza una quantità eccessiva di memoria, identificare le stored procedure, le funzioni o i trigger che usano grandi quantità di memoria ed eventualmente riprogettare l'applicazione. In genere, ciò può verificarsi a causa di grandi quantità di database o schemi con centinaia di procedure in ognuna di esse.
  • Se il clerk di OBJECTSTORE_LOCK_MANAGER memoria mostra allocazioni di memoria di grandi dimensioni, identificare le query che applicano molti blocchi e ottimizzarle usando gli indici. Abbreviare le transazioni che causano il rilascio dei blocchi per periodi lunghi in determinati livelli di isolamento o verificare se l'escalation dei blocchi è disabilitata.
  • Se si osservano dimensioni molto grandi TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore'), è possibile usare il flag di traccia 4618 per limitare le dimensioni della cache.
  • Se si osservano problemi di memoria con OLTP in memoria proveniente dal clerk di memoria, è possibile fare riferimento a Monitoraggio e risoluzione dei problemi relativi all'utilizzo della memoria per i metadati OLTP in memoria e i metadati tempdb ottimizzati per la MEMORYCLERK_XTP memoria (HkTempDB) in caso di errori di memoria insufficiente.

Sollievo rapido che può rendere disponibile la memoria

Le azioni seguenti possono liberare memoria e renderla disponibile per SQL Server:

Modificare le impostazioni di configurazione della memoria

Controllare i parametri di configurazione della memoria di SQL Server seguenti e prendere in considerazione l'aumento della memoria massima del server, se possibile:

• max server memory
• min server memory

Se non è stata configurata la memoria massima del server, in particolare con Pagine bloccate in memoria, è consigliabile impostarla su un valore specifico per consentire una certa memoria per il sistema operativo. Vedere l'opzione di configurazione Pagine bloccate nel server di memoria .

Modificare o spostare il carico di lavoro dal sistema

Esaminare il carico di lavoro di query: numero di sessioni simultanee, attualmente in esecuzione di query e verificare se sono presenti applicazioni meno critiche che possono essere arrestate temporaneamente o spostate in un altro SQL Server.

Per i carichi di lavoro di sola lettura, è consigliabile spostarli in una replica secondaria di sola lettura in un ambiente Always On. Per altre informazioni, vedere Offload del carico di lavoro di sola lettura nella replica secondaria di un gruppo di disponibilità Always On e Configurare l'accesso in sola lettura a una replica secondaria di un gruppo di disponibilità Always On.

Verificare la configurazione di memoria appropriata per le macchine virtuali

Se si esegue SQL Server in una macchina virtuale, assicurarsi che la memoria per la macchina virtuale non venga sovracommessa. Per informazioni su come configurare la memoria per le macchine virtuali, vedere Virtualizzazione - Overcommit della memoria e come rilevarla all'interno della macchina virtuale e risoluzione dei problemi di prestazioni delle macchine virtuali ESX/ESXi (overcommitment della memoria).

Rilasciare memoria all'interno di SQL Server

È possibile eseguire uno o più dei seguenti comandi DBCC per liberare diverse cache di memoria di SQL Server:

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

Riavviare il servizio SQL Server

In alcuni casi, se è necessario gestire l'esaurimento critico della memoria e SQL Server non è in grado di elaborare le query, è possibile prendere in considerazione il riavvio del servizio.

Prendere in considerazione l'uso di Resource Governor per scenari specifici

Se si usa Resource Governor, è consigliabile controllare le impostazioni del pool di risorse e del gruppo di carico di lavoro per verificare se non limitano la memoria troppo drasticamente.

Aggiungere più RAM nel server fisico o virtuale

Se il problema persiste, è necessario analizzare ulteriormente ed eventualmente aumentare le risorse del server (RAM).