MSSQLSERVER_701

Artigo
06/14/2024

Se aplica a: SQL Server

Detalles

Attribute	Valor
Nombre del producto	SQL Server
Id. de evento	701
Origen de eventos	MSSQLSERVER
Componente	SQLEngine
Nombre simbólico	NOSYSMEM
Texto del mensaje	El sistema no tiene memoria suficiente para ejecutar esta consulta.

Nota:

Este artículo se centra en SQL Server. Para obtener información sobre cómo solucionar problemas de memoria en Azure SQL Database, consulte Solución de problemas de memoria con Azure SQL Database.

Explicación

El error 701 se produce cuando SQL Server no ha podido asignar suficiente memoria para ejecutar una consulta. La memoria insuficiente puede deberse a una serie de factores que incluyen la configuración del sistema operativo, la disponibilidad de memoria física, otros componentes que usan memoria dentro de SQL Server o a límites de memoria en la carga de trabajo actual. En la mayoría de los casos, la transacción que no se ha realizado no es la causa de este error. En general, las causas se pueden agrupar en tres:

Presión de memoria externa o del sistema operativo

La presión externa hace referencia a un uso elevado de la memoria procedente de un componente externo al proceso que provoca una insuficiencia de memoria para SQL Server. Debe averiguar si otras aplicaciones del sistema consumen memoria y contribuyen a la baja disponibilidad de la memoria. SQL Server es una de las pocas aplicaciones diseñadas para responder a la presión de memoria del sistema operativo al reducir su uso de memoria. Esto significa que, si alguna aplicación o controlador solicita memoria, el sistema operativo envía una señal a todas las aplicaciones para que liberen memoria y, como respuesta, SQL Server reduce su propio uso de memoria. Muy pocas aplicaciones responden porque no están diseñadas para escuchar esa notificación. Por lo tanto, si SQL comienza a reducir su uso de memoria, se reduce su grupo de memoria y es posible que los componentes que necesiten memoria no lo obtengan. Se empiezan a producir errores 701 y de otro tipo relacionados con la memoria. Para más información, vea Arquitectura de memoria de SQL Server.

Presión de memoria interna, no procedente de SQL Server

La presión de memoria interna hace referencia a la baja disponibilidad de memoria causada por factores internos del proceso de SQL Server. Hay componentes que se pueden ejecutar dentro del proceso de SQL Server que son "externos" al motor de SQL Server. Algunos ejemplos son las DLL como servidores vinculados, componentes SQLCLR, procedimientos extendidos (XP) y automatización OLE (sp_OA*). Otros incluyen antivirus u otros programas de seguridad que insertan DLL dentro de un proceso con fines de supervisión. Un problema o un diseño deficiente en cualquiera de estos componentes podría provocar un consumo de memoria elevado. Por ejemplo, considere un servidor vinculado que almacena en caché 20 millones de filas de datos que proceden de un origen externo a la memoria de SQL Server. En lo que a SQL Server se refiere, ningún distribuidor de memoria notificará un uso elevado de memoria, pero la memoria consumida dentro del proceso de SQL Server será alta. Este crecimiento de memoria desde una DLL de servidor vinculado, por ejemplo, haría que SQL Server empezara a reducir su uso de memoria (vea más arriba) y crearía condiciones de memoria baja de para los componentes dentro de SQL Server, lo que provocaría errores como el error 701.

Presión de memoria interna, procedente de componentes de SQL Server

La presión de memoria interna procedente de componentes internos del motor de SQL Server también puede provocar el error 701. Hay cientos de componentes, de los que se realiza un seguimiento a través de sys.dm_os_memory_clerks, que asignan memoria en SQL Server. Debe identificar qué distribuidor de memoria es responsable de las asignaciones de memoria más grandes para poder resolverlo este problema. Por ejemplo, si ve que el distribuidor de memoria OBJECTSTORE_LOCK_MANAGER muestra la asignación de memoria grande, debe comprender aún más por qué el Administrador de bloqueos consume tanta memoria. Es posible que encuentre que hay consultas que adquieren un gran número de bloqueos y las optimizan mediante índices, o acortan las transacciones que contienen bloqueos durante largos períodos o comprueban si la extensión de bloqueo está deshabilitada. Cada componente o distribuidor de memoria tiene una manera única de acceder a la memoria y de usarla. Para obtener más información, consulte sys.dm_os_memory_clerks y sus descripciones.

Acción del usuario

Si el error 701 aparece ocasionalmente o durante un breve período, podría haber un problema de memoria de corta duración que se resolvió a sí mismo. Es posible que no tenga que tomar medidas en esos casos. Pero si el error se produce varias veces, en varias conexiones y se conserva durante períodos de segundos o más, siga los pasos para solucionar más problemas.

En la siguiente lista se describen los pasos generales que ayudarán a resolver los errores de memoria.

Herramientas de diagnóstico y captura

Las herramientas de diagnóstico que le permitirán recopilar datos de solución de problemas son Monitor de rendimiento, sys.dm_os_memory_clerks y DBCC MEMORYSTATUS.

Configure y recopile los siguientes contadores con Monitor de rendimiento:

Memoria: MB disponibles
Proceso: Espacio de trabajo
Proceso: Bytes privados
SQL Server: Administrador de memoria: (todos los contadores)
SQL Server: Administrador de búfer: (todos los contadores)

Recopilación de salidas periódicas de esta consulta en el servidor SQL Server afectado

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

Una manera alternativa y automatizada de capturar estos puntos de datos consiste en usar herramientas como PSSDIAGo SQL LogScout.

Si usa Pssdiag, debe configurarlo para capturar el recopilador Perfmon y el recopilador Diagnóstico personalizado\Error de memoria de SQL
Si usa SQL LogScout, debe configurarlo para capturar el escenario de memoria.

En las secciones siguientes se describen pasos más detallados para cada escenario: presión de memoria externa o interna.

Presión externa: diagnósticos y soluciones

Para diagnosticar condiciones de memoria baja en el sistema fuera del proceso de SQL Server, recopile contadores del monitor de rendimiento. Investigue si hay aplicaciones o servicios que no sean SQL Server que consumen memoria en este servidor mediante el examen de estos contadores:

Memoria: MB disponibles
Proceso: Espacio de trabajo
Proceso: Bytes privados

Esta es una recopilación de registros de Perfmon de ejemplo mediante 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)) }
}
}

Revise el registro de eventos del sistema y busque errores relacionados con la memoria (por ejemplo, memoria virtual baja).
Revise el registro de eventos de la aplicación para ver si hay problemas de memoria relacionados con la aplicación.

Este es un script de PowerShell de ejemplo para consultar los registros de eventos de aplicación y sistema para la palabra clave "memory". No dude en usar otras cadenas como "recurso" para la búsqueda:
```
Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
```
Solucione cualquier problema de código o configuración de aplicaciones o servicios menos críticos para reducir su uso de memoria.
Si las aplicaciones además de SQL Server consumen recursos, intente detener o reprogramar estas aplicaciones, o considere la posibilidad de ejecutarlas en un servidor independiente. Estos pasos quitarán la presión de memoria externa.

Presión de memoria interna, no procedente de SQL Server: diagnósticos y soluciones

Para diagnosticar la presión de memoria interna causada por módulos (DLL) dentro de SQL Server, use el siguiente enfoque:

Si SQL Server no usa la opción Bloquear páginas en memoria (API de AWE), la mayoría de su memoria se refleja en el contador Process:Private Bytes (SQLServrinstancia) en Monitor de rendimiento. El uso general de memoria procedente del motor de SQL Server se refleja en el contador SQL Server: Administrador de memoria: Memoria total del servidor (KB). Si encuentra una diferencia significativa entre el valor Proceso: Bytes privados y SQL Server: Administrador de memoria: Memoria total del servidor (KB), es probable que esa diferencia se deba a una DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si el valor de Bytes privados es 300 GB y el de Memoria total del servidor es de 250 GB, aproximadamente 50 GB de la memoria total del proceso viene de fuera del motor de SQL Server.
Si SQL Server usa páginas de bloqueo en memoria (API de AWE), es más difícil identificar el problema porque el monitor de rendimiento no ofrece contadores de AWE que realizan un seguimiento del uso de memoria para procesos individuales. El uso general de memoria procedente del motor de SQL Server se refleja en el contador SQL Server: Administrador de memoria: Memoria total del servidor (KB). Los valores típicos process:Private Bytes pueden variar entre 300 MB y 1-2 GB en general. Si encuentra un uso significativo de Proceso: Bytes privados más allá de este uso típico, es probable que la diferencia se deba a una DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si el contador bytes privados es de 5 a 4 GB y SQL Server usa páginas de bloqueo en memoria (AWE), una gran parte de los bytes privados puede provener del motor de SQL Server. Se trata de una técnica de aproximación.
Use la utilidad Tasklist para identificar las DLL que se cargan dentro del espacio de SQL Server:
```
tasklist /M /FI "IMAGENAME eq sqlservr.exe"
```
También puede usar esta consulta para examinar los módulos cargados (DLL) y ver si hay algo inesperado.
```
SELECT * FROM sys.dm_os_loaded_modules
```
Si sospecha que un módulo de servidor vinculado provoca un consumo significativo de memoria, puede configurarlo para que se ejecute fuera del proceso si deshabilita la opción Permitir en proceso. Consulte Creación de servidores vinculados (SQL Server Motor de base de datos) para obtener más información. No todos los proveedores OLEDB del servidor vinculado se ejecutan sin proceso; póngase en contacto con el fabricante del producto para obtener más información.
En el caso poco frecuente de que se usen objetos de automatización OLE (sp_OA*), puede configurar el objeto para que se ejecute en un proceso fuera de SQL Server estableciendo el contexto = 4 (Solo servidor OLE local (.exe). Para más información, vea sp_OACreate.

Uso interno de memoria por el motor de SQL Server: diagnósticos y soluciones

Comience a recopilar contadores de monitor de rendimiento para SQL Server:SQL Server:Buffer Manager, SQL Server: Administrador de memoria.
Consulte la DMV de distribuidores de memoria de SQL Server varias veces para ver dónde se produce el mayor consumo de memoria dentro del motor:
```
SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC
```
Como alternativa, puede observar la salida DBCC MEMORYSTATUS más detallada y la forma en que cambia cuando se ven estos mensajes de error.
```
DBCC MEMORYSTATUS
```
Si identifica un claro responsable entre los distribuidores de memoria, céntrese en solucionar los detalles del consumo de memoria de ese componente. Estos son algunos ejemplos:
- Si el distribuidor de memoria MEMORYCLERK_SQLQERESERVATIONS consume memoria, identifique las consultas que usan grandes concesiones de memoria y optimícelas mediante índices, vuelva a escribirlas (quite ORDER por ejemplo), o bien aplique sugerencias de consulta.
- Si se almacena en caché un gran número de planes de consulta ad hoc, el CACHESTORE_SQLCP distribuidor de memoria usaría grandes cantidades de memoria. Identifique consultas no parametrizadas cuyos planes de consulta no se pueden reutilizar y parametrizarlas mediante la conversión a procedimientos almacenados, o mediante sp_executesql, o mediante la parametrización FORZADA.
- Si el almacén de caché del plan de objetos CACHESTORE_OBJCP consume mucha memoria, siga estos pasos: identifique qué procedimientos almacenados, funciones o desencadenadores usan mucha memoria y, posiblemente, vuelva a diseñar la aplicación. Normalmente esto puede ocurrir debido a grandes cantidades de base de datos o esquemas con cientos de procedimientos en cada uno.
- Si el distribuidor de memoria OBJECTSTORE_LOCK_MANAGER es el que muestra las asignaciones de memoria grandes, identifique las consultas que aplican muchos bloqueos y optimícelas mediante índices. Acorte las transacciones que hacen que los bloqueos no se liberen durante largos períodos de tiempo en determinados niveles de aislamiento, o bien compruebe si la extensión de bloqueo está deshabilitada.

Alivio rápido que puede hacer que la memoria esté disponible

Las siguientes acciones pueden liberar memoria y ponerla a disposición de SQL Server:

Compruebe los siguientes parámetros de configuración de memoria de SQL Server y considere la posibilidad de aumentar la memoria máxima del servidor si es posible:
- memoria de servidor máxima
- memoria de servidor mínima
  
  Observe si hay algún valor fuera de lo normal. Corríjalos según sea necesario. Investigue el porqué de los mayores requisitos de memoria. La configuración predeterminada figura en las Opciones de configuración de la memoria del servidor.
Si no ha configurado la memoria máxima del servidor, especialmente con bloqueo de páginas en memoria, considere la posibilidad de establecer en un valor determinado para permitir cierta memoria para el sistema operativo. Consulte Bloquear páginas en la opción de configuración del servidor de memoria .
Compruebe la carga de trabajo de consulta: número de sesiones simultáneas, que actualmente ejecutan consultas y compruebe si hay aplicaciones menos críticas que se pueden detener temporalmente o mover a otra instancia de SQL Server.
Si ejecuta SQL Server en una máquina virtual (VM), asegúrese de que la memoria de la máquina virtual no tiene demasiadas confirmaciones. Para obtener ideas sobre cómo configurar la memoria de las máquinas virtuales, vea este blog Virtualization – Overcommitting memory and how to detect it within the VM (Virtualización: confirmación excesiva de memoria y cómo detectarla en la VM), y Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment) (Solución de problemas de rendimiento de máquinas virtuales ESX/ESXi [confirmación excesiva de memoria]).
Puede ejecutar los siguientes comandos DBCC para liberar varias memorias caché de memoria de SQL Server.
- DBCC FREESYSTEMCACHE
- DBCC FREESESSIONCACHE
- DBCC FREEPROCCACHE
Si usa Resource Governor, se recomienda comprobar la configuración del grupo de recursos o del grupo de cargas de trabajo, y ver si no limitan demasiado la memoria.
Si el problema continúa, tendrá que investigar más y posiblemente aumentar los recursos del servidor (RAM).

Compartir por