MSSQLSERVER_701

Si applica a:SQL Server

Dettagli

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 clerk di memoria, 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 oppure 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 Tipi di clerk di memoria 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 Performance Monitor, sys.dm_os_memory_clerks e DBCC MEMORYSTATUS.

Configurare e raccogliere i contatori seguenti con Performance Monitor:

• 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 sull'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 i 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 pagine bloccate in memoria (API AWE), la maggior parte della memoria viene riflessa nel contatore Process:Private Bytes (SQLServr istanza) in Performance Monitor. 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 bloccate in memoria (API AWE), è più difficile identificare il problema perché Performance Monitor 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 nel complesso. 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 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 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. Non tutti i provider OLEDB del server collegato potrebbero esaurirsi; per ulteriori informazioni, contattare il produttore del prodotto.

• Nel raro caso in cui vengano 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 locale (.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 le query non con parametri i cui piani di query non possono essere riutilizzati e parametrizzarli convertendoli in stored procedure oppure usando sp_executesql o tramite 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 Pagine bloccate in memoria, è consigliabile impostare un valore specifico per consentire una quantità di memoria per il sistema operativo. Vedere l'opzione di configurazione del server Pagine bloccate in 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).