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

Resumen

En este artículo se describe cómo solucionar los problemas de memoria de SQL Server, incluidos los errores de memoria insuficiente y de asignación de memoria, las condiciones de memoria insuficiente y los problemas de rendimiento relacionados. Explica los síntomas, las tres categorías principales de presión de memoria (externa, interna de módulos ajenos al motor e interna de componentes del motor de SQL Server), las herramientas de diagnóstico que puede utilizar para recopilar datos y los pasos que puede seguir para corregir o aliviar la presión de memoria en una instancia de SQL Server.

Síntomas de errores de memoria baja

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, muchos factores pueden provocar consumo de memoria y presión sobre la memoria, lo que puede dar lugar a situaciones de falta de memoria.

Los errores comunes indican una memoria baja en SQL Server. Entre los ejemplos de estos errores se incluyen:

• 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 se descargó por falta de memoria.
• 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).
• 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 omiten el seguimiento, ETW y las notificaciones.
• 22986 o 22987: errores en la captura de datos de cambio por 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 de problemas de memoria baja

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 falla con un error de falta de memoria no es la causa del error. Puede agrupar las causas en tres categorías.

Presión de memoria externa o del sistema operativo

La presión externa hace referencia a un uso elevado de memoria de un componente fuera del proceso de SQL Server que conduce a una memoria insuficiente para SQL Server. Compruebe 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 reduciendo su uso de memoria. 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 responde reduciendo su propio uso de memoria. Pocas otras aplicaciones responden porque no están diseñadas para detectar esa notificación. Cuando SQL Server reduce su uso de memoria, su bloque de memoria se reduce, y es posible que los componentes que necesitan memoria no puedan obtenerla. Como resultado, empezará a recibir errores 701 u otros errores relacionados con la memoria. Para obtener más información sobre cómo SQL Server asigna y libera memoria dinámicamente, consulte SQL Server arquitectura de memoria. Para obtener diagnósticos y soluciones detallados, consulte Presión de memoria externa en este artículo.

Tres amplias categorías de problemas 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 responde a las nuevas solicitudes de aplicación para los recursos intentando liberar memoria. Busque las aplicaciones que agotan la memoria y tome medidas para equilibrar la memoria entre ellas sin agotar la RAM.
• Problemas con los controladores de dispositivos: los controladores de dispositivos pueden provocar la paginación del conjunto de trabajo en todos los procesos si un controlador invoca incorrectamente una función de asignación de memoria.
• Problemas del producto del sistema operativo.

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

Presión de memoria interna de módulos que no son de motor

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 ejecutan 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 programas antivirus o de seguridad que insertan archivos DLL en el proceso con fines de supervisión. Un problema o un diseño deficiente en cualquiera de estos componentes puede provocar un gran consumo de memoria. Por ejemplo, imagine un servidor vinculado que almacena en caché 20 millones de filas de datos de un origen externo en la memoria de SQL Server. Desde la perspectiva del motor, ningún memory clerk informa de un uso alto de memoria, pero la memoria consumida en el proceso de SQL Server es alta. Este crecimiento de memoria en una DLL de servidor vinculado hace que SQL Server empiece a reducir su uso de memoria y crea situaciones de memoria insuficiente para los componentes del motor, lo que provoca errores de falta de memoria. Para obtener diagnósticos y soluciones detallados, consulte Presión de memoria interna de módulos que no son de motor.

Presión de memoria interna de los componentes del motor de SQL Server

La presión interna de memoria de los componentes dentro del motor de SQL Server también puede provocar errores de memoria insuficiente. Cientos de componentes, de los que se realiza un seguimiento mediante encargados de memoria, asignan memoria en SQL Server. Identifique qué gestores de memoria son responsables de las mayores asignaciones para solucionar el problema. Por ejemplo, si el OBJECTSTORE_LOCK_MANAGER memory clerk muestra una asignación elevada, averigüe por qué el administrador de bloqueos consume tanta memoria. Es posible que encuentre consultas que adquieran muchos bloqueos. Optimice estas consultas usando índices, acortando las transacciones que mantienen bloqueos durante mucho tiempo o comprobando si está deshabilitado el escalado de bloqueos. Cada gestor de memoria o componente tiene una manera única de utilizar la memoria. Para obtener más información, consulte sys.dm_os_memory_clerks y las descripciones de los tipos de memory clerk. Para obtener diagnósticos y soluciones detallados, consulte Uso de memoria interna por el motor de SQL Server.

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:

Herramientas de diagnóstico para solucionar problemas de memoria

Use las siguientes herramientas de diagnóstico para recopilar datos de solución de problemas.

Supervisión del rendimiento

Configure y recopile los contadores siguientes mediante 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)

DMVs y DBCC MEMORYSTATUS

Use sys.dm_os_memory_clerks o DBCC MEMORYSTATUS para observar el uso general de memoria dentro de SQL Server. sys.dm_os_memory_clerks devuelve una fila por cada memory clerk y es el mejor punto de partida para identificar qué componentes consumen más memoria. DBCC MEMORYSTATUS devuelve una instantánea más detallada que agrupa la información por administrador de memoria, grupo de búferes y clerks.

Informe de consumo de memoria en SSMS

Para ver el uso de memoria en SQL Server Management Studio (SSMS):

1. Abra SSMS y conéctese a un servidor.
2. En Explorador de objetos, seleccione y mantenga presionado (o haga clic con el botón derecho) en el nombre de la instancia de SQL Server.
3. En el menú contextual, seleccione Informes>Informes estándar>Consumo de memoria.

PSSDiag o SQL LogScout

Para capturar automáticamente estos puntos de datos, use una herramienta como PSSDiag o SQL LogScout.

• Si usa PSSDiag, configúrelo para que capture el recopilador Perfmon y el recopilador Custom Diagnostics\SQL Memory Error.
• Si usa SQL LogScout, configúrelo para capturar el escenario Memoria.

Alivio rápido para liberar memoria

Las siguientes acciones pueden liberar memoria y ponerla a disposición de SQL Server. Úselos como alivio a corto plazo mientras investiga la causa principal.

Cambio de la 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

Si no estableció la memoria máxima del servidor, especialmente con Bloquear páginas en memoria habilitada, establézcala en un valor específico para dejar memoria para el sistema operativo. Para obtener más información, consulte la opción de configuración del servidor Bloquear páginas en memoria (LPIM).

Cambiar o mover la carga de trabajo

Revise la carga de trabajo de consulta, incluido el número de sesiones simultáneas y las consultas que se están ejecutando actualmente. Compruebe si puede detener aplicaciones menos críticas temporalmente o moverlas 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 Descargar la carga de trabajo de solo lectura en la réplica secundaria de un grupo de disponibilidad Always On y Configurar el acceso de solo lectura a una réplica secundaria de un grupo de disponibilidad Always On.

Comprobación de la configuración de memoria de la máquina virtual

Si ejecuta SQL Server en una máquina virtual (VM), asegúrese de que el host no sobrecommita la memoria de la máquina virtual. Para obtener instrucciones sobre el ajuste de tamaño de memoria para SQL Server en máquinas virtuales de Azure, consulte Procedimientos recomendados de memoria para SQL Server en Azure Virtual Machines. Para las máquinas virtuales hospedadas en VMware, consulte la documentación del proveedor de hipervisor sobre cómo detectar y evitar la sobrecarga de memoria.

Liberar memoria dentro de SQL Server

Ejecute uno o varios de los siguientes comandos DBCC para liberar SQL Server memoria caché. Use estos comandos con precaución en los sistemas de producción porque borran las memorias caché que deben volver a rellenarse:

• DBCC FREESYSTEMCACHE
• DBCC FREESESSIONCACHE
• DBCC FREEPROCCACHE

Reinicie el servicio de SQL Server.

Si el agotamiento de memoria es crítico y SQL Server no puede procesar consultas, puede reiniciar el servicio como último recurso. Esta acción quita todas las conexiones activas y borra las memorias caché, por lo que úsela solo cuando se produzca un error en otras opciones.

Revisión de la configuración de Resource Governor

Si usa Resource Governor, compruebe la configuración del grupo de recursos y del grupo de cargas de trabajo para asegurarse de que no limiten la memoria demasiado drásticamente. Para obtener más información, vea Resource Governor.

Agregar más RAM

Si el problema continúa después de los pasos anteriores, investigue más y considere la posibilidad de aumentar los recursos del servidor (RAM) en el servidor físico o virtual.

Diagnóstico y corrección de la presión de memoria

Si aparece un error de memoria insuficiente solo ocasionalmente o durante un breve período, el problema podría ser pasajero y resolverse por sí solo, y puede que no sea necesario tomar ninguna medida. Si el error se produce varias veces en varias conexiones y persiste durante segundos o más, siga los diagnósticos y soluciones de las secciones siguientes para identificar y solucionar la causa principal.

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:

• Recopilar contadores del Monitor de rendimiento. Compruebe si las aplicaciones o servicios distintos de SQL Server consumen memoria en este servidor examinando 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 los problemas de memoria relacionados con la aplicación.

  Este es un script de PowerShell de ejemplo que consulta los registros de eventos del sistema y la aplicación para la palabra clave "memory". Puede 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*"
  

• Corrija cualquier problema de configuración o código para aplicaciones o servicios menos críticos para reducir su uso de memoria.

• Si las aplicaciones además de SQL Server consumen recursos, intente detenerlos o volver a programarlos o ejecútelos en un servidor independiente. Estos pasos eliminan la presión de memoria externa.

Presión de memoria interna de módulos que no son de motor

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 Lock Pages in Memory (API AWE), la mayor parte de su uso de memoria se muestra en el contador Process:Private Bytes (instancia sqlservr) del Monitor de rendimiento. El contador SQL Server:Memory Manager: Memoria total del servidor (KB) muestra el uso general de memoria desde el motor de SQL Server. Si encuentra una diferencia significativa entre Process:Private Bytes y SQL Server:Memory Manager: Memoria total del servidor (KB), es probable que esa diferencia provenga de un archivo DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si Bytes privados es de 300 GB y memoria total del servidor es de 250 GB, aproximadamente 50 GB de la memoria general del proceso proviene 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 Monitor de rendimiento no ofrece contadores de AWE que realizan un seguimiento del uso de memoria para procesos individuales. El contador SQL Server:Memory Manager: Memoria total del servidor (KB) muestra el uso general de memoria dentro del motor de SQL Server. Los valores típicos process:Private Bytes pueden variar entre 300 MB y 1-2 GB en general. Si Process:Private Bytes es significativamente mayor que este intervalo típico, es probable que la diferencia provenga de un archivo DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si Los bytes privados son de 4 a 5 GB y SQL Server usa páginas de bloqueo en memoria (AWE), una gran parte de bytes privados puede provenir de fuera del motor de SQL Server. Este valor es una aproximación.

• Use la tasklist utilidad para identificar archivos DLL cargados dentro del proceso de SQL Server.

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"
  

• También puede usar la siguiente consulta para examinar módulos cargados (DLL) y comprobar si hay algo inesperado:

  SELECT * FROM sys.dm_os_loaded_modules
  

• Si sospecha que un módulo de servidor enlazado está provocando un consumo significativo de memoria, configúrelo para que se ejecute fuera del proceso desactivando la opción Permitir procesamiento en proceso. Para obtener más información, consulte Creación de servidores vinculados. No todos los proveedores OLE DB del servidor vinculado pueden salir del proceso. Para obtener más información, póngase en contacto con el fabricante del proveedor.

• 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 (local (.exe) solo servidor OLE). Para obtener más información, consulte sp_OACreate.

Uso de memoria interna por el motor de SQL Server

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

• Empiece a recopilar contadores de Monitor de rendimiento para SQL Server: SQL Server:Buffer Manager y SQL Server:Memory Manager.

• Consulte la DMV de memory clerks 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, observe la salida más detallada DBCC MEMORYSTATUS y cómo cambia cuando aparezcan estos mensajes de error.

  DBCC MEMORYSTATUS
  

• Si identifica un claro responsable entre los memory clerks, céntrese en los detalles del consumo de memoria de ese componente. Estos son algunos ejemplos:

  • Si el MEMORYCLERK_SQLQERESERVATIONS memory clerk está consumiendo memoria, identifique las consultas que usan grandes concesiones de memoria y optimícelas usando índices, reescribiéndolas (por ejemplo, quitando ORDER BY) o aplicando sugerencias de consulta para concesión de memoria (MIN_GRANT_PERCENT y MAX_GRANT_PERCENT). Para obtener más información, consulte Sugerencias de consulta. También puede crear un grupo de recursos para controlar el uso de concesión de memoria. Para obtener más información 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 almacenan en caché muchos planes de consulta ad hoc, el CACHESTORE_SQLCP memory clerk utiliza grandes cantidades de memoria. Identifique las consultas no parametrizadas cuyos planes no se puedan reutilizar y parametrícelas convirtiéndolas en procedimientos almacenados, mediante sp_executesql o mediante la parametrización FORCED. Si la marca de seguimiento 174 está habilitada, puede deshabilitarla para ver si corrige 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 considere la posibilidad de rediseñar la aplicación. Esto suele ocurrir con muchas bases de datos o esquemas que contienen cientos de procedimientos.
  • Si el OBJECTSTORE_LOCK_MANAGER memory clerk muestra asignaciones de memoria elevadas, identifique las consultas que adquieren muchos bloqueos y optimícelas mediante el uso de índices. Reduzca la duración de las transacciones que mantienen bloqueos durante periodos prolongados en determinados niveles de aislamiento, o compruebe si la escalada de bloqueos 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 In-Memory OLTP en el memory clerk MEMORYCLERK_XTP, consulte Supervisar y solucionar problemas de uso de memoria para In-Memory OLTP y Errores de memoria insuficiente en metadatos de tempdb optimizados para memoria (HkTempDB).

Preguntas más frecuentes

¿Por qué SQL Server usa casi toda la RAM en el servidor?

Por diseño, el grupo de búferes de SQL Server crece para almacenar en caché las páginas de datos y reducir la E/S física, por lo que la memoria de estado estable se aproxima normalmente a la configuración máxima de memoria del servidor. Este comportamiento se espera y no es una fuga. Para limitar el consumo y dejar espacio para el sistema operativo y otros procesos, configure el máximo de memoria del servidor. Para obtener más información, consulte Opciones de configuración de memoria del servidor.

¿Cuál es la diferencia entre el número máximo de memoria del servidor y la memoria confirmada que se muestra en el Administrador de tareas?

La configuración memoria máxima del servidor limita la memoria que el grupo de búfer de SQL Server y la mayoría de los memory clerks dentro del motor pueden comprometer. El Administrador de tareas muestra la memoria comprometida de todo el proceso sqlservr.exe. Esta vista incluye asignaciones que los componentes realizan fuera del grupo de búferes, como CLR, proveedores de servidores vinculados, procedimientos almacenados extendidos y búferes de copia de seguridad. Como resultado, la memoria total del proceso puede superar el máximo de memoria del servidor. Para obtener más información, consulte La guía de arquitectura de administración de memoria.

¿Cuándo debo habilitar Bloquear páginas en memoria (LPIM)?

Active Bloquear páginas en memoria cuando el sistema operativo recorte el conjunto de trabajo de SQL Server. Este problema aparece como error 17890 o caídas repentinas en memoria total del servidor. Empareja LPIM con un valor explícito máximo de memoria del servidor para dejar RAM para el sistema operativo y otros procesos. No habilite LPIM de forma predeterminada en cada instancia. Úselo para solucionar un problema de paginación confirmado.

¿Qué me dice sys.dm_os_memory_clerks?

sys.dm_os_memory_clerks devuelve una fila por cada administrador de memoria activo en el motor de SQL Server, con la cantidad de memoria que tiene comprometida. Úselo para buscar qué componente (por ejemplo, grupo de búferes, caché de planes, administrador de bloqueos o concesiones de memoria de consulta) consume la mayor parte de la memoria y para dirigir el esfuerzo de optimización.