Solución de los problemas de memoria insuficiente o poca memoria en SQL Server

Artículo
12/23/2024

Síntomas

SQL Server usa una arquitectura de memoria compleja que corresponde al conjunto de características complejo y enriquecido. Debido a la variedad de necesidades de memoria, podría haber muchas fuentes de consumo de memoria y presión de memoria, lo que en última instancia provocaría condiciones de memoria insuficiente.

Hay errores comunes que indican una memoria baja en SQL Server. Algunos ejemplos de errores son:

701: Error al asignar suficiente memoria para ejecutar una consulta.
802: Error al obtener memoria para asignar páginas en el grupo de búferes (páginas de datos o de índice).
1204: Error al asignar memoria para bloqueos.
6322: Error al asignar memoria para el analizador XML.
6513: Error al inicializar CLR debido a la presión de memoria.
6533: AppDomain descargado debido a la memoria insuficiente.
8318: Error al cargar contadores de rendimiento de SQL debido a una memoria insuficiente.
8356 o 8359: el seguimiento ETW o SQL no se puede ejecutar debido a una memoria baja.
8556: Error al cargar MSDTC debido a una memoria insuficiente.
8645: Error al ejecutar una consulta debido a que no hay memoria para concesiones de memoria (ordenación y hash) Para obtener más información, vea Cómo solucionar el error 8645 de SQL Server.
8902: Error al asignar memoria durante la ejecución de DBCC.
9695 o 9696: error al asignar memoria para las operaciones de Service Broker.
17131 o 17132: Error de inicio del servidor debido a una memoria insuficiente.
17890: Error al asignar memoria debido a que el sistema operativo pagina la memoria SQL.
18053: El error se imprime en modo terse porque se produjo un error durante el formato. Se omite el seguimiento, ETW, notificaciones, etc.
22986 o 22987: errores de captura de datos modificados debido a una memoria insuficiente.
25601: el motor de Xevent no tiene memoria.
26053: las interfaces de red sql no se inicializan debido a una memoria insuficiente.
30085, 30086, 30094: se produce un error en las operaciones de texto completo de SQL debido a una memoria insuficiente.

Causa

Muchos factores pueden causar memoria insuficiente. Estos factores incluyen la configuración del sistema operativo, la disponibilidad de memoria física, los componentes que usan memoria dentro de SQL Server y los límites de memoria en la carga de trabajo actual. En la mayoría de los casos, la consulta que produce un error de memoria insuficiente no es la causa de este error. En general, las causas se pueden agrupar en tres categorías:

Causa 1: 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 una disponibilidad de memoria baja. 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 una aplicación o controlador solicita memoria, el sistema operativo envía una señal a todas las aplicaciones para liberar memoria y SQL Server responderá reduciendo su propio uso de memoria. Algunas otras aplicaciones responden porque no están diseñadas para escuchar esa notificación. Por lo tanto, si SQL Server 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. Como resultado, empezará a obtener 701 u otros errores relacionados con la memoria. Para obtener más información sobre cómo SQL asigna y libera memoria dinámicamente, consulte Arquitectura de memoria de SQL Server. Para obtener diagnósticos y soluciones más detallados para el problema, consulte Presión de memoria externa en este artículo.

Hay tres categorías amplias de problemas que pueden causar presión de memoria del sistema operativo:

Problemas relacionados con la aplicación: una o varias aplicaciones agotan la memoria física disponible. El sistema operativo responderá a las nuevas solicitudes de aplicación para los recursos intentando liberar memoria. El enfoque común es encontrar qué aplicaciones agotan la memoria y tomar los pasos necesarios para equilibrar la memoria entre ellos sin llevar a agotamiento de RAM.
Problemas del controlador de dispositivo: los controladores de dispositivo pueden provocar la paginación del conjunto de trabajo de todos los procesos si el controlador llama incorrectamente a una función de asignación de memoria.
Problemas del producto del sistema operativo.

Para obtener una explicación detallada de estos y pasos de solución de problemas, consulte MSSQLSERVER_17890.

Causa 2: Presión interna de memoria, 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. Algunos componentes que se pueden ejecutar dentro del proceso de SQL Server son "externos" al motor de SQL Server. Entre los ejemplos se incluyen proveedores OLE DB (DLL), como servidores vinculados, procedimientos o funciones 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 la posibilidad de almacenar en caché un servidor vinculado de 20 millones de filas de datos de un origen externo en 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 de un archivo DLL de servidor vinculado, por ejemplo, provocaría que SQL Server empezara a cortar su uso de memoria (consulte más arriba) y creará condiciones de memoria bajas para los componentes dentro de SQL Server, lo que provocaría errores de memoria insuficiente. Para obtener diagnósticos y soluciones más detallados sobre el problema, consulte Presión de memoria interna, no procedente de SQL Server.

Nota:

Algunos archivos DLL de Microsoft usados en el espacio de procesos de SQL Server (por ejemplo, MSOLEDBSQL, SQL Native Client) pueden interactuar con la infraestructura de memoria de SQL Server para la generación de informes y la asignación. Puede ejecutar select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' para obtener una lista de ellos y realizar un seguimiento del consumo de memoria de algunas de sus asignaciones.

Causa 3: Presión interna de memoria procedente de componentes de SQL Server

La presión interna de memoria procedente de componentes dentro del motor de SQL Server también puede provocar errores de memoria insuficiente. Hay cientos de componentes de los que se realiza un seguimiento a través de distribuidores de memoria que asignan memoria en SQL Server. Debe identificar qué distribuidores de memoria son responsables de las asignaciones de memoria más grandes para resolver este problema. Por ejemplo, si encuentra que el OBJECTSTORE_LOCK_MANAGER distribuidor de memoria muestra una asignación de memoria grande, debe comprender por qué el Administrador de bloqueos consume tanta memoria. Es posible que encuentre que hay consultas que adquieren muchos bloqueos. Puede optimizar estas consultas mediante índices, acortar las transacciones que contengan bloqueos durante mucho tiempo o comprobar 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 más información, vea Tipos de distribuidores de memoria y sus descripciones. Para obtener diagnósticos y soluciones más detallados sobre el problema, consulte Uso de memoria interna por el motor de SQL Server.

Representación visual de los tipos de presión de memoria

En el gráfico siguiente se muestran los tipos de presión que pueden dar lugar a condiciones de memoria insuficientes en SQL Server:

Captura de pantalla de los tipos de presión de memoria.

Herramientas de diagnóstico para recopilar datos de solución de problemas

Puede usar las siguientes herramientas de diagnóstico para recopilar datos de solución de problemas:

Supervisión del rendimiento

Configure y recopile los siguientes contadores con Monitor de rendimiento:

Memoria:MBytes 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)

DMV o DBCC MEMORYSTATUS

Puede usar sys.dm_os_memory_clerks o DBCC MEMORYSTATUS para observar el uso general de memoria dentro de SQL Server.

Informe estándar de consumo de memoria en SSMS

Visualización del uso de memoria en SQL Server Management Studio:

Inicie SQL Server Management Studio y conéctese a un servidor.
En Explorador de objetos, haga clic con el botón derecho en el nombre de la instancia de SQL Server.
En el menú contextual, seleccione Informes>estándar de consumo de memoria de informes.>

PSSDiag o SQL LogScout

Una manera alternativa y automatizada de capturar estos puntos de datos es usar herramientas como PSSDiag o SQL LogScout.

Si usa PSSDiag, configúrelo para capturar el recopilador perfmon y el recopilador de errores de memoria de SQL personalizados.
Si usa SQL LogScout, configúrelo 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).

Metodología de solución de problemas

Si aparece ocasionalmente un error de memoria insuficiente o durante un breve período, puede haber un problema de memoria de corta duración que se resuelva a sí mismo. Es posible que no tenga que tomar medidas en esos casos. Sin embargo, si el error se produce varias veces en varias conexiones y persiste durante períodos de segundos o más, siga los diagnósticos y soluciones de las secciones siguientes para solucionar los errores de memoria.

Presión de memoria externa

Para diagnosticar condiciones de memoria baja en el sistema fuera del proceso de SQL Server, use los métodos siguientes:

Recopile contadores de 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:MBytes disponibles
Proceso: Espacio de trabajo
Proceso: Bytes privados

Este es un ejemplo de la recopilación de registros de Perfmon 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 ejemplo de un script de PowerShell para consultar los registros de eventos del sistema y de la aplicación 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

Para diagnosticar la presión de memoria interna causada por módulos (DLL) dentro de SQL Server, use los métodos siguientes:

Si SQL Server no usa páginas bloqueadas 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:Memory Manager: Memoria total del servidor (KB). Si encuentra una diferencia significativa entre el valor Process:Private Bytes y SQL Server:Memory Manager: Memoria total del servidor (KB), es probable que esa diferencia provena de un archivo DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si los bytes privados son de 300 GB y la memoria total del servidor es de 250 GB, aproximadamente 50 GB de la memoria general del proceso procede de fuera del motor de SQL Server.
Si SQL Server usa páginas bloqueadas 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 dentro del motor de SQL Server se refleja en el contador SQL Server:Memory Manager: Memoria total del servidor (KB). Los valores de Proceso: Bytes privados típicos pueden variar entre 300 MB y 1-2 GB en general. Si encuentra un uso significativo de Process:Private Bytes más allá de este uso típico, es probable que la diferencia provena de un archivo DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si el contador Bytes privados es de 4 a 5 GB y SQL Server usa páginas bloqueadas en memoria (AWE), es posible que una gran parte de los bytes privados provena de fuera 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 la siguiente consulta para examinar los módulos cargados (DLL) y ver si algo inesperado está allí.
```
SELECT * FROM sys.dm_os_loaded_modules
```
Si sospecha que un módulo del servidor vinculado está causando un consumo significativo de memoria, puede configurarlo para que se agote el proceso deshabilitando la opción Permitir inprocesamiento . Vea Creación de servidores vinculados para más información. No todos los proveedores OLE DB del servidor vinculado pueden agotarse del proceso. Para obtener más información, póngase en contacto con el fabricante del producto.
En el caso poco frecuente en el que se usan objetos de automatización OLE (sp_OA*), puede configurar el objeto para que se ejecute en un proceso fuera de SQL Server especificando un valor de contexto de 4 (solo servidor OLE local (.exe). Para más información, vea sp_OACreate.

Uso de memoria interna por el motor de SQL Server

Para diagnosticar la presión interna de memoria procedente de componentes dentro del motor de SQL Server, use los métodos siguientes:

Empiece a recopilar contadores de Monitor de rendimiento para SQL Server: Administrador de búferes de SQL Server y 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 más detallada DBCC MEMORYSTATUS y la forma en que cambia al ver 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 MEMORYCLERK_SQLQERESERVATIONS de memoria consume memoria, identifique las consultas que usan concesiones de memoria enormes y optimice a través de índices, reescriba (quite ORDER by, por ejemplo) o aplique sugerencias de consulta de concesión de memoria (vea min_grant_percent y sugerencias de max_grant_percent ). También puede crear un grupo de reguladores de recursos para controlar el uso de la memoria de concesión de memoria. Para obtener información detallada sobre las concesiones de memoria, consulte Solución de problemas de rendimiento lento o de poca memoria causados por concesiones de memoria en SQL Server.
- 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, mediante sp_executesql, o mediante FORCED la parametrización. Si ha habilitado la marca de seguimiento 174, puede deshabilitarla para ver si se resuelve el problema.
- Si el almacén CACHESTORE_OBJCP de caché del plan de objetos consume demasiada memoria, identifique qué procedimientos almacenados, funciones o desencadenadores usan grandes cantidades de memoria y posiblemente rediseñe la aplicación. Normalmente, esto puede ocurrir debido a grandes cantidades de bases de datos o esquemas con cientos de procedimientos en cada uno.
- Si el OBJECTSTORE_LOCK_MANAGER distribuidor de memoria muestra asignaciones de memoria grandes, identifique las consultas que aplican muchos bloqueos y optimice mediante índices. Acorte las transacciones que provocan que los bloqueos no se liberen durante largos períodos en determinados niveles de aislamiento o comprueben si la extensión de bloqueo está deshabilitada.
- Si observa un tamaño muy grande TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore'), puede usar la marca de seguimiento 4618 para limitar el tamaño de la memoria caché.
- Si observa problemas de memoria con OLTP en memoria procedente del MEMORYCLERK_XTP distribuidor de memoria, puede consultar Supervisión y solución de problemas de uso de memoria para metadatos de tempdb optimizados para memoria (HkTempDB) sin memoria.

Solución rápida que puede hacer que la memoria esté disponible

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

Cambio de las opciones de configuración de memoria

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

Nota:

Si observa una configuración inusual, corrijalas según sea necesario y tenga en cuenta el aumento de los requisitos de memoria. La configuración predeterminada figura en las Opciones de configuración de la memoria del servidor.

Si no ha configurado memoria máxima del servidor, especialmente con Páginas bloqueadas en memoria, considere la posibilidad de establecerla en un valor determinado para permitir cierta memoria para el sistema operativo. Consulte la opción de configuración Páginas bloqueadas en el servidor de memoria .

Cambiar o mover la carga de trabajo fuera del sistema

Investigue la carga de trabajo de consulta: número de sesiones simultáneas, actualmente en ejecución de consultas y vea si hay aplicaciones menos críticas que se pueden detener temporalmente o mover a otra instancia de SQL Server.

En el caso de las cargas de trabajo de solo lectura, considere la posibilidad de moverlas a una réplica secundaria de solo lectura en un entorno AlwaysOn. Para obtener más información, consulte Descarga de la carga de trabajo de solo lectura en la réplica secundaria de un grupo de disponibilidad AlwaysOn y Configuración del acceso de solo lectura a una réplica secundaria de un grupo de disponibilidad AlwaysOn.

Asegúrese de que la configuración de memoria adecuada para las máquinas virtuales

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 para las máquinas virtuales, consulte Virtualización: sobreasignación de memoria y cómo detectarla en la máquina virtual y Solución de problemas de rendimiento de la máquina virtual ESX/ESXi (sobrecommitimiento de memoria).

Liberar memoria dentro de SQL Server

Puede ejecutar uno o varios de los siguientes comandos DBCC para liberar varias cachés de memoria de SQL Server:

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Reinicie el servicio de SQL Server.

En algunos casos, si necesita tratar el agotamiento crítico de la memoria y SQL Server no puede procesar consultas, puede considerar la posibilidad de reiniciar el servicio.

Considere la posibilidad de usar Resource Governor para escenarios específicos

Si usa Resource Governor, se recomienda comprobar la configuración del grupo de recursos y del grupo de cargas de trabajo para ver si no están limitando la memoria demasiado drásticamente.

Adición de más RAM en el servidor físico o virtual

Si el problema continúa, debe investigar más y posiblemente aumentar los recursos del servidor (RAM).

Compartir a través de