Condividi tramite

MSSQLSERVER_701

  • Articolo

Si applica a: SQL Server

Dettagli

Attributo valore
Nome prodotto SQL Server
ID evento 701
Origine evento MSSQLSERVER
Componente SQLEngine
Nome simbolico NOSYSMEM
Testo del messaggio Memoria di sistema insufficiente per eseguire questa query.

Nota

Questo articolo è incentrato su SQL Server. Per informazioni sulla risoluzione dei problemi di memoria insufficiente in database SQL di Azure, vedere Risolvere gli errori di memoria insufficiente con database SQL di Azure.

Spiegazione

L'errore 701 si verifica quando SQL Server non è riuscito ad allocare memoria sufficiente per eseguire una query. La memoria insufficiente può essere causata da diversi fattori che includono impostazioni del sistema operativo, disponibilità della memoria fisica, altri componenti usano memoria all'interno di SQL Server o limiti di memoria per il carico di lavoro corrente. Nella maggior parte dei casi, la transazione non riuscita non è la causa dell'errore. Nel complesso, le cause possono essere raggruppate in tre:

Pressione 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. Pochissime altre applicazioni rispondono perché non sono progettate per l'ascolto di tale notifica. Pertanto, se SQL inizia a ridurre l'utilizzo della memoria, il pool di memoria viene ridotto e a qualsiasi componente che necessita di memoria potrebbe non ottenerlo. Si inizia a ricevere 701 e altri errori correlati alla memoria. Per altre informazioni, vedere Architettura della memoria di SQL Server

Utilizzo della memoria interna, 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. Esistono componenti che possono essere eseguiti all'interno del processo di SQL Server che sono "esterni" al motore di SQL Server. Alcuni esempi includono DLL come server collegati, componenti 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 provenienti 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'avvio del taglio dell'utilizzo della memoria (vedere sopra) e creerà condizioni di memoria insufficiente per i componenti all'interno di SQL Server, causando errori come 701.

Pressione di memoria interna, proveniente da componenti di SQL Server

La pressione interna della memoria proveniente dai componenti all'interno del motore di SQL Server può anche causare l'errore 701. Sono disponibili centinaia di componenti, rilevati tramite sys.dm_os_memory_clerks, che allocano memoria in SQL Server. È necessario identificare quali clerk di memoria sono responsabili delle allocazioni di memoria più grandi per poter risolvere ulteriormente questo problema. Ad esempio, se si scopre che il OBJECTSTORE_LOCK_MANAGER clerk di memoria mostra l'allocazione di memoria di grandi dimensioni, è necessario comprendere ulteriormente il motivo per cui Gestione blocchi sta consumando tanta memoria. È possibile che siano presenti query che acquisiscono un numero elevato di blocchi e li ottimizzano usando indici o abbreviare le transazioni che contengono blocchi per lunghi periodi o verificare 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 sys.dm_os_memory_clerks e le relative descrizioni.

Azione utente

Se l'errore 701 viene visualizzato occasionalmente o per un breve periodo, potrebbe verificarsi un problema di memoria di breve durata che si è risolto. In questi casi potrebbe non essere necessario intervenire. Tuttavia, se l'errore si verifica più volte, in più connessioni e viene mantenuto per periodi di secondi o più lunghi, seguire la procedura per risolvere ulteriori problemi.

L'elenco seguente illustra i passaggi generali che consentiranno di risolvere gli errori di memoria.

Strumenti di diagnostica e acquisizione

Gli strumenti di diagnostica che consentono di raccogliere i dati di risoluzione dei problemi sono Monitor prestazioni, sys.dm_os_memory_clerks e DBCC MEMORYSTATUS.

Configurare e raccogliere i contatori seguenti con Monitor prestazioni:

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

Raccogliere output periodici di questa query nell'istanza di SQL Server interessata

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

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, configurare 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, configurare per acquisire lo scenario di memoria

Le sezioni seguenti descrivono i passaggi più dettagliati per ogni scenario, ovvero la pressione di memoria interna o esterna.

Pressione esterna: diagnostica e soluzioni

  • Per diagnosticare condizioni di memoria insufficiente nel sistema all'esterno del processo di SQL Server, raccogliere i contatori di Performance Monitor. Verificare se le applicazioni o i servizi diversi da SQL Server utilizzano memoria in questo server esaminando questi contatori:

    • Memoria:MB disponibile
    • 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 uno script di PowerShell di esempio 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: diagnostica e soluzioni

Per diagnosticare la pressione della memoria interna causata da moduli (DLL) all'interno di SQL Server, usare l'approccio seguente:

  • Se SQL Server non usa l'opzione Blocca pagine 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 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 di blocco in memoria (API AWE), è più difficile identificare il problema perché Monitoraggio prestazioni non offre contatori AWE che tengono traccia dell'utilizzo della memoria per singoli processi. L'utilizzo complessivo della memoria proveniente dal 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 complessivi. Se si trova 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 5-4 GB e SQL Server usa le pagine di blocco in memoria (AWE), una parte importante dei byte privati può 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 questa query per esaminare i moduli caricati (DLL) e verificare se non è previsto che ci sia qualcosa

    SELECT * FROM sys.dm_os_loaded_modules

  • Se si sospetta che un modulo di 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 (SQL Server motore di database). Non tutti i provider OLEDB del server collegato esauriscono il processo; per ulteriori 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 impostando il contesto = 4 (solo server OLE (.exe). Per altre informazioni, vedere sp_OACreate.

Utilizzo interno della memoria da parte del motore di SQL Server: diagnostica e soluzioni

  • Iniziare a raccogliere i contatori di Monitoraggio prestazioni per SQL Server:SQL Server:Buffer Manager, SQL Server: Memory Manager.

  • 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 DBCC MEMORYSTATUS più dettagliato 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 MEMORYCLERK_SQLQERESERVATIONS clerk di memoria sta consumando memoria, identificare le query che usano concessioni di memoria enormi e ottimizzarle tramite indici, riscriverle (rimuovere ORDER per esempio) o applicare hint per la query.
    • Se un numero elevato di piani di query ad hoc viene memorizzato nella cache, il clerk di memoria CACHESTORE_SQLCP utilizzerebbe grandi quantità di memoria. Identificare query non con parametri i cui piani di query non possono essere riutilizzati e parametrizzarli convertendoli in stored procedure oppure usando sp_executesqlo la parametrizzazione FORCED.
    • Se l'archivio cache del piano oggetti CACHESTORE_OBJCP consuma molta memoria, eseguire le operazioni seguenti: identificare le stored procedure, le funzioni o i trigger che usano molta 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 memoria OBJECTSTORE_LOCK_MANAGER mostra le 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 lunghi periodi in determinati livelli di isolamento o controllare se l'escalation dei blocchi è disabilitata.

Sollievo rapido che può rendere disponibile la memoria

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

  • 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

      Valutare eventuali impostazioni non comuni e, se necessario, correggerle. Considerare i requisiti di memoria aggiuntivi. Le impostazioni predefinite sono elencate nelle opzioni di configurazione della memoria del server.

  • Se non è stata configurata la memoria massima del server in particolare con Blocco pagine in memoria, prendere in considerazione l'impostazione di un valore specifico per consentire una quantità di memoria per il sistema operativo. Vedere l'opzione di configurazione Blocco pagine nel server di memoria .

  • Controllare il carico di lavoro della 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.

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

  • È possibile eseguire i comandi DBCC seguenti per liberare diverse cache di memoria di SQL Server.

    • DBCC FREESYSTEMCACHE

    • DBCC FREESESSIONCACHE

    • DBCC FREEPROCCACHE

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

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