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

  • Artículo

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 provoca condiciones de memoria insuficiente.

Hay errores comunes que indican poca memoria 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 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 la poca memoria.
  • 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 SQL Server error 8645.
  • 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 memoria insuficiente.
  • 17890: Error al asignar memoria debido a que el sistema operativo pagina la memoria SQL.
  • 22986 o 22987: cambie los errores de captura de datos debido a una memoria insuficiente.
  • 25601: El motor de Xevent no tiene memoria.
  • 26053: Las interfaces de red de SQL no se pueden inicializar 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 memoria procedente de un componente fuera del proceso que conduce a memoria insuficiente para SQL Server. Tiene que averiguar si otras aplicaciones del sistema consumen memoria y contribuyen a una baja disponibilidad de 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 una aplicación o un 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, su grupo de memoria se reduce y es posible que los componentes que necesiten memoria no lo obtengan. Como resultado, empieza 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, vea SQL Server Arquitectura de memoria. 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 generales de problemas que pueden causar presión de memoria del sistema operativo:

  • Problemas relacionados con la aplicación: una o varias aplicaciones agotan juntos 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 están agotando la memoria y tomar los pasos necesarios para equilibrar la memoria entre ellas sin provocar agotamiento de RAM.
  • Problemas de controladores 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 de producto del sistema operativo.

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

Causa 2: Presión de memoria interna, no procedente de SQL Server

La presión interna de memoria hace referencia a la baja disponibilidad de memoria causada por factores dentro 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 de SQLCLR, procedimientos extendidos (XPs) y OLE Automation (sp_OA*). Otros incluyen antivirus u otros programas de seguridad que insertan archivos 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 gran consumo de memoria. Por ejemplo, considere la posibilidad de almacenar en caché 20 millones de filas de datos de un origen externo en SQL Server memoria. En lo que respecta a SQL Server, ningún empleado 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 comenzara a reducir su uso de memoria (consulte más arriba) y crearía condiciones de memoria baja 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 SQL Server infraestructura de memoria para informes y 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 para algunas de sus asignaciones.

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

La presión de memoria interna 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 los empleados de memoria que asignan memoria en SQL Server. Debe identificar qué empleados de memoria son responsables de las asignaciones de memoria más grandes para resolver este problema. Por ejemplo, si detecta que el OBJECTSTORE_LOCK_MANAGER empleado de memoria muestra una asignación de memoria grande, debe comprender por qué el Administrador de bloqueos consume tanta memoria. Es posible que haya consultas que adquieran muchos bloqueos. Puede optimizar estas consultas mediante índices, acortando las transacciones que contienen bloqueos durante mucho tiempo o comprobando si la escalación de bloqueos está deshabilitada. Cada componente o empleado de memoria tiene una manera única de acceder y usar la memoria. Para obtener más información, vea Tipos de empleados de memoria y sus descripciones. Para obtener diagnósticos y soluciones más detallados sobre el problema, consulte Uso interno de memoria por SQL Server motor.

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 provocar condiciones de memoria insuficiente 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:

Monitor de rendimiento

Configure y recopile los siguientes contadores con Monitor de rendimiento:

  • Memory:Available MBytes
  • Proceso:Conjunto de trabajo
  • Process:Private Bytes
  • SQL Server:Administrador de memoria: (todos los contadores)
  • SQL Server:Administrador de búferes: (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

Vea el uso de memoria en SQL Server Management Studio:

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

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 diagnósticos personalizados\Error de memoria sql .

  • Si usa SQL LogScout, configúrelo para capturar el escenario 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 Monitor de rendimiento contadores. Investigue si las aplicaciones o servicios distintos de SQL Server consumen memoria en este servidor examinando estos contadores:

    • Memory:Available MBytes
    • Proceso:Conjunto de trabajo
    • Process:Private Bytes

    Este es un ejemplo de 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 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 "resource" 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 volver a programar 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 parte de su memoria se refleja en el contador Process:Private Bytes (SQLServrinstancia) de 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 Process:Private Bytes y SQL Server:Memory Manager: Total Server Memory (KB), esa diferencia probablemente procede de un archivo DLL (servidor vinculado, XP, SQLCLR, etc.). Por ejemplo, si los bytes privados son 300 GB y la memoria total del servidor es de 250 GB, aproximadamente 50 GB de la memoria general del proceso proceden 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 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:Administrador de memoria: Memoria total del servidor (KB). Proceso típico: los valores de bytes privados 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 de bytes privados es de 4 a 5 GB y SQL Server usa páginas bloqueadas en memoria (AWE), una gran parte de los bytes privados puede proveniendo de fuera del motor de SQL Server. Se trata de una técnica de aproximación.

  • Use la utilidad Tasklist para identificar los archivos DLL que se cargan dentro de SQL Server espacio:

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

  • También puede usar la siguiente 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 está causando un consumo significativo de memoria, puede configurarlo para que se quede sin proceso deshabilitando la opción Permitir inprocesamiento . Consulte Creación de servidores vinculados para obtener más información. No todos los proveedores OLE DB de servidor vinculado pueden que se quede sin 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 obtener más información, consulte sp_OACreate.

Uso interno de memoria por SQL Server motor

Para diagnosticar la presión de memoria interna 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: SQL Server:Administrador de búferes y SQL Server: Administrador de memoria.

  • Consulte la DMV de los 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 cuando ve estos mensajes de error.

    DBCC MEMORYSTATUS

  • Si identifica un delincuente claro entre los empleados de memoria, céntrese en abordar los detalles del consumo de memoria para ese componente. Estos son algunos ejemplos:

    • Si el empleado MEMORYCLERK_SQLQERESERVATIONS de memoria consume memoria, identifique las consultas que usan concesiones de memoria enormes y optimice a través de índices, reescribalas (quite ORDER by, por ejemplo) o aplique sugerencias de consulta de concesión de memoria (vea sugerencias de min_grant_percent y 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 memoria baja 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 empleado de memoria usaría grandes cantidades de memoria. Identifique consultas no parametrizadas cuyos planes de consulta no se puedan reutilizar y parametrizarlos mediante la conversión a procedimientos almacenados, mediante sp_executesqlo mediante FORCED la parametrización. Si ha habilitado la marca de seguimiento 174, puede deshabilitarla para ver si esto 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 empleado de memoria muestra asignaciones de memoria grandes, identifique las consultas que aplican muchos bloqueos y optimice mediante índices. Acortar las transacciones que hacen que los bloqueos no se liberen durante largos períodos en determinados niveles de aislamiento o compruebe si la escalación 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 procedentes del MEMORYCLERK_XTP empleado de memoria, puede consultar Supervisión y solución de problemas de uso de memoria para In-Memory oltp y metadatos tempdb optimizados para memoria (HkTempDB) errores de memoria insuficiente.

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

Las siguientes acciones pueden liberar memoria y hacer que esté disponible para SQL Server:

Cambiar la configuración de la memoria

Compruebe los siguientes parámetros de configuración de memoria SQL Server y considere la posibilidad de aumentar la memoria máxima del servidor si es posible:

  • memoria máxima del servidor
  • memoria mínima del servidor

Nota:

Si observa una configuración inusual, corríjalas según sea necesario y tenga en cuenta el aumento de los requisitos de memoria. La configuración predeterminada se muestra en Opciones de configuración de memoria del servidor.

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

Cambio o traslado de la carga de trabajo del sistema

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

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

Garantizar una 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 se ha confirmado en exceso. Para obtener ideas sobre cómo configurar la memoria para máquinas virtuales, consulte Virtualization - Overcommitting memory and how to detect it within the VM and Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment).

Liberar memoria dentro de SQL Server

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

  • DBCC FREESYSTEMCACHE

  • DBCC FREESESSIONCACHE

  • DBCC FREEPROCCACHE

Reinicio del servicio SQL Server

En algunos casos, si necesita lidiar con 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 limitan demasiado la memoria.

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).