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

Sintomi

SQL Server usa un'architettura di memoria complessa che corrisponde al set di funzionalità complesso e completo. A causa dell'ampia gamma di esigenze di memoria, potrebbero esserci molte origini di consumo di memoria e di pressione della memoria, causando in definitiva condizioni di memoria insufficiente.

Esistono errori comuni che indicano una memoria insufficiente in SQL Server. Esempi di errori includono:

• 701: non è possibile allocare memoria sufficiente per eseguire una query.
• 802: errore di recupero della memoria per l'allocazione di 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:Errore di inizializzazione di CLR a causa della pressione della memoria.
• 6533: AppDomain scaricato a causa di memoria insufficiente.
• 8318: errore di 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 della memoria insufficiente.
• 8556: errore di caricamento di MSDTC a causa di memoria insufficiente.
• 8645: Errore di esecuzione di una query a causa della mancanza di memoria per le concessioni di memoria (ordinamento e hash) Per altre informazioni, vedere Come risolvere SQL Server errore 8645.
• 8902: errore di allocazione della memoria durante l'esecuzione di 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.
• 22986 o 22987: errori di acquisizione dei dati delle modifiche dovuti a memoria insufficiente.
• 25601: Il motore Xevent non è in memoria.
• 26053: l'inizializzazione delle interfacce di rete SQL non riesce 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. Nel complesso, le cause possono essere raggruppate in tre categorie:

Causa 1: Pressione della 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 scoprire se altre applicazioni nel sistema consumano memoria e contribuiscono a una bassa disponibilità di memoria. SQL Server è una delle pochissime 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 risponderà riducendo il proprio utilizzo di memoria. Poche altre applicazioni rispondono perché non sono progettate per restare in ascolto di tale notifica. Pertanto, se SQL Server inizia a ridurre l'utilizzo della memoria, il pool di memoria viene ridotto e, a seconda dei componenti che richiedono memoria, potrebbe non ottenerlo. Di conseguenza, si inizia a ottenere 701 o altri errori correlati alla memoria. Per altre informazioni su come SQL alloca e libera dinamicamente la memoria, vedere SQL Server Memory Architecture. Per una diagnostica e soluzioni più dettagliate per il problema, vedere Pressione della memoria esterna in questo articolo.

Esistono tre categorie generali di problemi che possono causare una pressione della memoria del sistema operativo:

• Problemi correlati alle applicazioni: una o più applicazioni insieme esauriscono la memoria fisica disponibile. Il sistema operativo risponderà alle nuove richieste dell'applicazione per le risorse provando a liberare memoria. L'approccio comune consiste nell'individuare le applicazioni che esauriscono la memoria e adottare i passaggi necessari per bilanciare la memoria tra di esse senza portare all'esaurimento della RAM.
• Problemi del driver di dispositivo: i driver di dispositivo possono causare il paging 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 della risoluzione dei problemi, vedere MSSQLSERVER_17890.

Causa 2: Pressione interna della memoria, non proveniente da SQL Server

La pressione interna della memoria si riferisce alla bassa disponibilità di memoria 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. Gli 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 scadente 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 in memoria SQL Server. Per quanto riguarda SQL Server, nessun impiegato di memoria segnalerà un utilizzo elevato della memoria, ma la memoria utilizzata all'interno del processo di SQL Server sarà elevata. Questa crescita della memoria da una DLL del server collegato, ad esempio, causerebbe SQL Server di iniziare a ridurre l'utilizzo della memoria (vedere sopra) e creerà condizioni di memoria ridotte per i componenti all'interno di SQL Server, causando errori di memoria insufficiente. Per una diagnostica e soluzioni più dettagliate sul problema, vedere Pressione della memoria interna, non proveniente da SQL Server.

Causa 3: Pressione interna della memoria proveniente da SQL Server componenti

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 monitorati tramite archivi di memoria che allocano memoria in SQL Server. È necessario identificare quali archivi di memoria sono responsabili delle allocazioni di memoria più grandi per risolvere questo problema. Ad esempio, se si rileva che l'impiegato OBJECTSTORE_LOCK_MANAGER di memoria mostra un'allocazione di memoria di grandi dimensioni, è necessario comprendere il motivo per cui Gestione blocchi sta consumando così tanta memoria. È possibile che siano presenti query che acquisiscono molti blocchi. È possibile ottimizzare queste query usando gli indici, abbreviando le transazioni che contengono blocchi per un lungo periodo di tempo o controllando se l'escalation dei blocchi è disabilitata. Ogni componente o impiegato di memoria ha un modo univoco per accedere e usare la memoria. Per altre informazioni, vedere Tipi di clerk di memoria e relative descrizioni. Per una diagnostica e soluzioni più dettagliate sul problema, vedere Utilizzo della memoria interna da parte del motore di SQL Server.

Rappresentazione visiva dei tipi di pressione della memoria

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

Strumenti di diagnostica per la raccolta dei dati di risoluzione dei problemi

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

Performance Monitor

Configurare e raccogliere i contatori seguenti con Monitor prestazioni:

• Memoria:MByte disponibili
• Processo: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 sull'utilizzo della 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 ReportStandard Reports MemoryConsumption (Consumo> di memoria report > standard).

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 errori di diagnostica personalizzata\SQL Memory Error .

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

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

Metodologia di risoluzione dei problemi

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 da solo. Potrebbe non essere necessario intervenire in questi casi. Tuttavia, se l'errore si verifica più volte su più connessioni e persiste per periodi di secondi o più, seguire la diagnostica e le soluzioni nelle sezioni seguenti per risolvere ulteriormente gli errori di memoria.

Pressione della 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. Esaminare se applicazioni o servizi diversi da SQL Server utilizzano memoria nel server esaminando questi contatori:

  • Memoria:MByte disponibili
  • Processo:Working Set
  • Process:Private Bytes

  Ecco 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 la memoria virtuale insufficiente.

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

  Di seguito è riportato un esempio di script di PowerShell per eseguire query sui log eventi di sistema e dell'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 a arrestare o riprogrammare queste applicazioni o a eseguirle in un server separato. Questi passaggi rimuoveranno la pressione della memoria esterna.

Pressione interna della memoria, non proveniente da SQL Server

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

• Se SQL Server non usa le 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 dall'interno del motore 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 proviene probabilmente da una DLL (server collegato, XP, SQLCLR e così via). Ad esempio, se i byte privati sono 300 GB e la memoria totale del server è di 250 GB, circa 50 GB di memoria complessiva nel processo provengono dall'esterno del motore di SQL Server.

• Se SQL Server usa l'API AWE (Locked Pages in Memory), è più difficile identificare il problema perché il Monitor prestazioni non offre contatori AWE che tengono traccia dell'utilizzo della memoria per i singoli processi. L'utilizzo complessivo della memoria all'interno del motore SQL Server si riflette nel contatore SQL Server:Memory Manager: Total Server Memory (KB). Processo tipico: i valori dei byte privati possono variare tra 300 MB e 1-2 GB complessivi. Se si rileva un utilizzo significativo di Process:Private Bytes oltre questo uso tipico, la differenza deriva probabilmente 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 Le pagine bloccate in memoria (AWE), gran parte 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 eventuali DLL caricate all'interno dello spazio 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 stia causando un consumo significativo di memoria, è possibile configurarlo per l'esecuzione del processo disabilitando l'opzione Consenti inprocesso . Per altre informazioni, vedere Creare server collegati . Non tutti i provider OLE DB del server collegato potrebbero esaurire il processo. 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 SQL Server specificando un valore di contesto 4 (solo server OLE locale (.exe). Per altre informazioni, vedere sp_OACreate.

Utilizzo interno della memoria da parte del motore di SQL Server

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

• Iniziare a raccogliere i contatori Monitor prestazioni per SQL Server: SQL Server:Buffer Manager e SQL Server: Gestione memoria.

• Eseguire più query sulla DMV degli archivi di memoria SQL Server per verificare dove si verifica il consumo più elevato di memoria 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 chiaro trasgressore tra gli impiegati di memoria, concentrarsi sull'affrontare le specifiche del consumo di memoria per quel componente. Eccone alcuni esempi:

  • Se l'impiegato MEMORYCLERK_SQLQERESERVATIONS di memoria sta consumando memoria, identificare le query che usano enormi concessioni di memoria e ottimizzarle tramite indici, riscriverle (ad esempio rimuovere ORDER by) o applicare hint di query di concessione della memoria (vedere min_grant_percent e max_grant_percent hint ). È anche possibile creare un pool di Resource Governor per controllare l'utilizzo della memoria con concessione di memoria. Per informazioni dettagliate sulle concessioni di memoria, vedere Risolvere i problemi di prestazioni lente o di memoria insufficiente causati dalle concessioni di memoria in SQL Server.
  • Se viene memorizzato nella cache un numero elevato di piani di query ad hoc, l'impiegato CACHESTORE_SQLCP di memoria userà grandi quantità di memoria. Identificare le query non con parametri i cui piani di query non possono essere riutilizzati e parametrizzarle convertendole in stored procedure, usando sp_executesqlo usando 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 oggetti sta consumando troppa memoria, identificare quali stored procedure, funzioni o trigger 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.
  • Se il clerk di OBJECTSTORE_LOCK_MANAGER memoria mostra allocazioni di memoria di grandi dimensioni, identificare le query che applicano molti blocchi e ottimizzarli usando gli indici. Abbreviare le transazioni che causano il rilascio di blocchi per lunghi periodi 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 In-Memory OLTP provenienti dall'impiegato MEMORYCLERK_XTP di memoria, è possibile fare riferimento a Monitoraggio e risoluzione dei problemi di utilizzo della memoria per In-Memoryerrori di memoria insufficiente dei metadati tempdb ottimizzati per la memoria e OLTP (HkTempDB).

Rilievo 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 SQL Server seguenti e valutare la possibilità di aumentare la memoria massima del server, se possibile:

• max server memory
• memoria minima del server

Se non è stata configurata la memoria massima del server, in particolare con Le pagine bloccate in memoria, è consigliabile impostarla su un valore specifico per consentire una certa memoria per il sistema operativo. Vedere l'opzione di configurazione del server Locked Pages in Memory.See the Locked Pages in Memory server configuration .See the Locked Pages in Memory server configuration.See

Modificare o spostare il carico di lavoro dal sistema

Esaminare il carico di lavoro della query: numero di sessioni simultanee, attualmente in esecuzione, 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 corretta configurazione della memoria 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 - Overcommitting memory e come rilevarla all'interno della macchina virtuale e Risoluzione dei problemi di prestazioni della macchina virtuale ESX/ESXi (overcommitment di memoria).

Rilasciare memoria all'interno di SQL Server

È possibile eseguire uno o più dei comandi DBCC seguenti per liberare diverse cache di memoria 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 la memoria non viene limitata in modo troppo drastico.

Aggiungere più RAM nel server fisico o virtuale

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