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

Sommario

Questo articolo descrive come risolvere i problemi di memoria SQL Server, inclusi errori di memoria insufficiente e errori di allocazione della memoria, condizioni di memoria insufficiente e problemi di prestazioni correlati. Illustra i sintomi, le tre categorie principali di pressione di memoria (esterna, interna da moduli non motore e interni dai componenti del motore di SQL Server), gli strumenti di diagnostica che è possibile usare per raccogliere dati e i passaggi che è possibile eseguire per correggere o alleviare la pressione della memoria in un'istanza di SQL Server.

Sintomi degli errori dovuti a memoria insufficiente

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, molte fonti possono causare consumo di memoria e pressione sulla memoria, che possono portare a condizioni di memoria esaurita.

Gli errori comuni indicano memoria insufficiente in SQL Server. Esempi di questi errori includono:

  • 701: Errore di allocazione di memoria sufficiente per eseguire una query.
  • 802: Impossibile ottenere memoria per allocare pagine nel pool di buffer (pagine di dati o di indice).
  • 1204: Errore di allocazione della memoria per i blocchi.
  • 6322: Errore di allocazione della memoria per il parser XML.
  • 6513: errore durante l'inizializzazione di CLR a causa di una pressione di memoria.
  • 6533: AppDomain rimosso dalla memoria a causa di memoria esaurita.
  • 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: impossibile eseguire una query per mancanza di memoria per le allocazioni di memoria (ordinamento e hashing).
  • 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. Il tracciamento, ETW e le notifiche vengono ignorati.
  • 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 di problemi di memoria insufficiente

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 dell'errore. È possibile raggruppare le cause in tre categorie.

Pressione di memoria esterna o del sistema operativo

La pressione esterna si riferisce all'utilizzo elevato della memoria da un componente esterno al processo di SQL Server che porta a memoria insufficiente per SQL Server. Controllare 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 riducendo l'uso della memoria. 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. Quando SQL Server riduce l'utilizzo della memoria, il pool di memoria si riduce e i componenti che richiedono memoria potrebbero non ottenerla. Di conseguenza, si inizia a ricevere 701 o altri errori correlati alla memoria. Per altre informazioni su come SQL Server alloca e libera in modo dinamico la memoria, vedere architettura di memoria SQL Server. Per informazioni dettagliate sulla diagnostica e sulle soluzioni, consulta Pressione della memoria esterna in questo articolo.

Tre ampie categorie di problemi 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 risponde alle nuove richieste di applicazioni per le risorse provando a liberare memoria. Individuare le applicazioni che esauriscono la memoria e adottare misure per bilanciare la memoria tra di esse senza esaurimento della RAM.
  • Problemi del driver di dispositivo: i driver di dispositivo possono causare il paging dei set di lavoro di tutti i processi se un driver chiama erroneamente una funzione di allocazione della memoria.
  • Problemi del prodotto del sistema operativo.

Per una spiegazione dettagliata e la procedura di risoluzione dei problemi, vedere MSSQLSERVER_17890.

Pressione della memoria interna da moduli non-engine

La pressione interna della memoria si riferisce alla disponibilità di memoria insufficiente causata da fattori all'interno del processo di SQL Server. Alcuni componenti eseguiti all'interno del processo di SQL Server sono external 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 programmi antivirus o di sicurezza che inseriscono DLL nel processo a scopo di monitoraggio. Un problema o una progettazione scadente in uno di questi componenti può 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. Dal punto di vista del motore, nessun gestore della memoria segnala un utilizzo elevato della memoria, ma la memoria utilizzata all'interno del processo SQL Server è elevata. Questo aumento dell’utilizzo della memoria causato da una DLL di un server collegato fa sì che SQL Server inizi a ridurre il proprio utilizzo della memoria e crea condizioni di memoria ridotta per i componenti del motore, con conseguenti errori di memoria esaurita. Per informazioni dettagliate sulla diagnosi e sulle soluzioni, vedere Pressione della memoria interna causata da moduli esterni al motore.

Nota

Alcune DLL Microsoft usate nello spazio di processo SQL Server (ad esempio, MSOLEDBSQL e SQL Server Native Client possono interfacciarsi con l'infrastruttura di memoria SQL Server per la creazione di report e l'allocazione. Eseguire SELECT * FROM sys.dm_os_memory_clerks WHERE type='MEMORYCLERK_HOST' per ottenere un elenco di essi e tenere traccia del consumo di memoria per alcune delle allocazioni. SQL Server Native Client (SNAC) è deprecato. Il nuovo sviluppo deve usare MSOLEDBSQL o il driver ODBC Microsoft per SQL Server.

Pressione interna sulla memoria dei componenti del motore di SQL Server

La pressione sulla memoria interna causata dai componenti interni del motore SQL Server può anche causare errori di memoria esaurita. Centinaia di componenti monitorati tramite memory clerks allocano memoria in SQL Server. Identificare quali gestori della memoria sono responsabili delle allocazioni di memoria maggiori per risolvere il problema. Ad esempio, se il memory clerk OBJECTSTORE_LOCK_MANAGER mostra un'allocazione elevata, individua il motivo per cui il Lock Manager sta consumando così tanta memoria. È possibile trovare query che acquisiscono molti blocchi. Ottimizza queste query utilizzando gli indici, riducendo la durata delle transazioni che mantengono i blocchi per molto tempo o verificando se l'escalation dei blocchi è disabilitata. Ogni gestore della memoria o componente ha un modo unico di usare la memoria. Per altre informazioni, vedere sys.dm_os_memory_clerks e le descrizioni dei tipi di clerk di memoria. Per informazioni dettagliate sulla diagnostica e sulle soluzioni, vedere Utilizzo della memoria interna da parte del motore di SQL Server.

Tipi di pressione di memoria

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

Diagramma che mostra i tre tipi di pressione della memoria in SQL Server: esterna/OS, interna da moduli non del motore e interna da componenti del motore.

Strumenti di diagnostica per risolvere i problemi di memoria

Usare gli strumenti di diagnostica seguenti per raccogliere i dati di risoluzione dei problemi.

Monitoraggio prestazioni

Configurare e raccogliere i contatori seguenti usando Monitor prestazioni:

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

DMV e DBCC MEMORYSTATUS

Usare sys.dm_os_memory_clerks o DBCC MEMORYSTATUS per osservare l'utilizzo complessivo della memoria all'interno di SQL Server. sys.dm_os_memory_clerks restituisce una riga per ogni clerk di memoria ed è il punto di partenza migliore per trovare quali componenti utilizzano la maggior parte della memoria. DBCC MEMORYSTATUS restituisce un'istantanea più dettagliata che raggruppa le informazioni per gestore della memoria, pool di buffer e clerk.

Report sul consumo di memoria in SSMS

Per visualizzare l'utilizzo della memoria in SQL Server Management Studio (SSMS):

  1. Aprire SSMS e connettersi a un server.
  2. In Esplora oggetti selezionare e tenere premuto (o fare clic con il pulsante destro del mouse) sul nome dell'istanza SQL Server.
  3. Nel menu contestuale, seleziona Report>Report standard>Consumo di memoria.

PSSDiag o SQL LogScout

Per acquisire automaticamente questi punti dati, usare uno strumento come PSSDiag o SQL LogScout.

  • Se si utilizza PSSDiag, configurarlo in modo da acquisire il collettore Perfmon e il collettore Diagnostica personalizzata\Errore di memoria SQL.
  • Se utilizzi SQL LogScout, configuralo per acquisire lo scenario Memory.

Soluzione rapida per liberare memoria

Le azioni seguenti potrebbero liberare memoria e renderla disponibile per SQL Server. Usarli come sollievo a breve termine mentre si esamina la causa radice.

Modificare la 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:

  • memoria massima del server
  • Memoria minima del server

Nota

Se si notano impostazioni insolite, correggerle in base alle esigenze e tenere conto di requisiti di memoria maggiori. Le impostazioni predefinite sono elencate nelle opzioni di configurazione della memoria del server.

Se non è stata impostata la memoria massima del server, in particolare con Lock Pages in Memory abilitata, impostarla su un valore specifico per lasciare memoria per il sistema operativo. Per altre informazioni, vedere l'opzione di configurazione del server Lock pages in memory (LPIM).

Modificare o spostare il carico di lavoro

Esamina il carico di lavoro delle query, compreso il numero di sessioni simultanee e le query attualmente in esecuzione. Controllare se è possibile arrestare le applicazioni meno critiche temporaneamente o spostarle in un'altra istanza di 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 Scaricare il carico di lavoro di sola lettura su una 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.

Controllare la configurazione della memoria della macchina virtuale

Se si esegue SQL Server in una macchina virtuale, assicurarsi che l'host non sovrascriva la memoria della macchina virtuale. Per indicazioni sul dimensionamento della memoria per SQL Server nelle macchine virtuali Azure, vedere Procedure consigliate per SQL Server in Macchine virtuali di Azure. Per le VM ospitate su VMware, consultare la documentazione del fornitore dell'hypervisor per rilevare ed evitare la sovraallocazione della memoria.

Rilasciare memoria all'interno di SQL Server

Eseguire uno o più dei comandi DBCC seguenti per liberare SQL Server cache di memoria. Usare questi comandi con cautela nei sistemi di produzione perché cancellano le cache che devono essere ripopolate:

  • DBCC FREESYSTEMCACHE
  • DBCC FREESESSIONCACHE
  • DBCC FREEPROCCACHE

Riavviare il servizio SQL Server

Se l'esaurimento della memoria è critico e SQL Server non è in grado di elaborare le query, è possibile riavviare il servizio come ultima risorsa. Questa azione elimina tutte le connessioni attive e cancella le cache, quindi usarla solo quando altre opzioni hanno esito negativo.

Esaminare le impostazioni di Resource Governor

Se si usa Resource Governor, controllare le impostazioni del pool di risorse e del gruppo di carico di lavoro per assicurarsi che non limitino la memoria troppo drasticamente. Per altre informazioni, vedere Resource Governor.

Aggiungere altre RAM

Se il problema persiste dopo i passaggi precedenti, esaminare ulteriormente e prendere in considerazione l'aumento delle risorse del server (RAM) nel server fisico o virtuale.

Diagnosticare e correggere la pressione della memoria

Se un errore di memoria insufficiente si verifica solo occasionalmente o per un breve periodo, il problema potrebbe essere temporaneo e risolversi da solo, e potrebbe non essere necessario alcun intervento. Se l'errore si verifica più volte tra più connessioni e persiste per secondi o più, seguire la diagnostica e le soluzioni nelle sezioni seguenti per identificare e risolvere la causa radice.

Pressione della memoria esterna

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

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

    • Memoria:MByte 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 i problemi di memoria correlati all'applicazione.

    Di seguito è riportato un esempio di script di PowerShell che esegue 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 SQL Server utilizzano risorse, provare ad arrestarle o riprogrammarle o eseguirle in un server separato. Questi passaggi rimuovono l'utilizzo di memoria esterna.

Pressione della memoria interna da moduli non-engine

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 Blocca pagine in memoria (API AWE), la maggior parte dell'utilizzo della memoria compare nel contatore Process:Private Bytes (istanza sqlservr) in Monitoraggio prestazioni. Il contatore SQL Server:Memory Manager: Total Server Memory (KB) mostra l'utilizzo complessivo della memoria dall'interno del motore di SQL Server. Se si rileva una differenza significativa tra Process:Private Bytes e SQL Server:Memory Manager: Total Server Memory (KB), tale differenza probabilmente deriva da una DLL (server collegato, XP, SQLCLR e così via). Ad esempio, se Private Bytes è 300 GB e Total Server Memory è 250 GB, circa 50 GB della memoria complessiva nel processo proviene dall'esterno del motore SQL Server.

  • Se SQL Server usa l'API AWE (Lock Pages in Memory), è più difficile identificare il problema perché Monitor prestazioni non offre contatori AWE che tengono traccia dell'utilizzo della memoria per singoli processi. Il contatore SQL Server:Memory Manager: Total Server Memory (KB) mostra l'utilizzo complessivo della memoria all'interno del motore di SQL Server. I valori tipici di Process:Private Bytes possono variare tra 300 MB e 1-2 GB complessivi. Se Process:Private Bytes è significativamente superiore a questo intervallo tipico, è probabile che la differenza provenga da una DLL (server collegato, XP, SQLCLR e così via). Ad esempio, se Private Bytes è di 4-5 GB e SQL Server usa Lock Pages in Memory (AWE), una parte importante di Private Bytes potrebbe provenire dall'esterno del motore di SQL Server. Questo valore è un'approssimazione.

  • Usare l'utilità tasklist per identificare le DLL caricate all'interno del processo 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 consumo significativo di memoria, configurarlo per l'esecuzione fuori processo deselezionando l'opzione Allow inprocess. Per altre informazioni, vedere Creare server collegati. Non tutti i provider OLE DB per server collegati possono essere eseguiti fuori processo. Per ulteriori informazioni, contattare il produttore del dispositivo.

  • Nel raro caso in cui vengono usati gli 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 di 4 (solo server OLE (local (.exe). Per altre informazioni, vedere sp_OACreate.

Utilizzo interno della memoria dal motore di SQL Server

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

  • Iniziare a raccogliere contatori Monitor prestazioni per SQL Server: SQL Server:Buffer Manager e SQL Server:Memory Manager.

  • Interrogare più volte la DMV dei memory clerk di SQL Server per vedere dove si registra 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, osserva l'output più dettagliato DBCC MEMORYSTATUS e come cambia quando visualizzi questi messaggi di errore.

    DBCC MEMORYSTATUS

  • Se individui un responsabile evidente tra i componenti della memoria, concentrati sui dettagli del consumo di memoria di quel componente. Ecco vari esempi:

    • Se il MEMORYCLERK_SQLQERESERVATIONS memory clerk sta consumando memoria, identificare le query che usano grant di memoria elevati e ottimizzarle utilizzando indici, riscrivendole (ad esempio, rimuovendo ORDER BY) oppure applicando hint di query per i grant di memoria (MIN_GRANT_PERCENT e MAX_GRANT_PERCENT). Per ulteriori informazioni, vedere i suggerimenti di query . È anche possibile creare un pool di risorse per controllare l'utilizzo delle concessioni di memoria. Per ulteriori informazioni sulle assegnazioni di memoria, vedi Troubleshoot slow performance or low memory issues caused by memory grants in SQL Server.
    • Se nella cache vengono memorizzati molti piani di query ad hoc, il gestore di memoria CACHESTORE_SQLCP utilizza molta memoria. Identificare le query non parametrizzate i cui piani non possono essere riutilizzati e parametrizzarle convertendole in stored procedure o usando la parametrizzazione sp_executesql o FORCED. Se il flag di traccia 174 è abilitato, è possibile disabilitarlo per verificare se il problema è stato risolto.
    • Se l'archivio CACHESTORE_OBJCP cache del piano oggetti utilizza una quantità eccessiva di memoria, identificare le stored procedure, le funzioni o i trigger che usano grandi quantità di memoria e prendere in considerazione la riprogettazione dell'applicazione. Questo accade in genere con molti database o schemi che contengono centinaia di procedure.
    • Se il memory clerk OBJECTSTORE_LOCK_MANAGER mostra allocazioni di grandi dimensioni, identificare le query che acquisiscono molti blocchi e ottimizzarle usando gli indici. Ridurre la durata delle transazioni che mantengono blocchi per lunghi periodi a determinati livelli di isolamento oppure verificare se l'escalation dei blocchi è disabilitata.
    • Se si osserva un valore molto grande 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 riscontrano problemi di memoria con In-Memory OLTP dal memory clerk MEMORYCLERK_XTP, vedere Monitorare e risolvere i problemi di utilizzo della memoria per In-Memory OLTP e Errori di memoria insufficiente dei metadati tempdb ottimizzati per la memoria (HkTempDB).

Domande frequenti

Perché SQL Server usa quasi tutte le RAM nel server?

Per impostazione predefinita, il pool di buffer SQL Server aumenta fino a memorizzare nella cache le pagine di dati e ridurre le I/O fisiche, quindi l'uso della memoria a stato stabile si avvicina comunemente all'impostazione max server memory. Questo comportamento è previsto e non è una fuga. Per limitare il consumo di memoria e lasciare un margine per il sistema operativo e gli altri processi, configurare max server memory. Per informazioni dettagliate, vedere Opzioni di configurazione della memoria del server.

Qual è la differenza tra la memoria massima del server e la memoria impegnata mostrata in Gestione attività?

L'impostazione max server memory pone un limite alla memoria che il buffer pool di SQL Server e la maggior parte dei memory clerk all'interno del motore possono allocare. Gestione attività mostra la memoria impegnata dell'intero processo sqlservr.exe. Questa vista include le allocazioni che i componenti effettuano all'esterno del pool di buffer, ad esempio CLR, provider di server collegati, stored procedure estese e buffer di backup. Di conseguenza, la memoria totale del processo può superare il numero massimo di memoria del server. Per altre informazioni, vedere Guida all'architettura di gestione della memoria.

Quando è necessario abilitare Blocco pagine in memoria (LPIM)?

Attivare Blocca pagine in memoria quando il sistema operativo riduce il working set di SQL Server. Questo problema viene visualizzato come errore 17890 o calo improvviso nella memoria totale del server. Associare LPIM con un valore esplicito di memoria massima del server per lasciare RAM per il sistema operativo e altri processi. Non abilitare LPIM per impostazione predefinita in ogni istanza. Usarlo per risolvere un problema di paging confermato.

Cosa mi dice sys.dm_os_memory_clerks?

sys.dm_os_memory_clerks restituisce una riga per ogni memory clerk attivo all'interno del motore SQL Server, con la quantità di memoria impegnata dal clerk. Usarlo per individuare quale componente, ad esempio pool di buffer, cache dei piani, gestore dei blocchi o allocazioni di memoria per le query, consuma più memoria e per orientare gli interventi di ottimizzazione.

  • Last updated on