MSSQLSERVER_701

Se aplica a:SQL Server

Detalles

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 tanto, si SQL empieza a reducir su uso de memoria, su grupo de memoria se reduce y es posible que los componentes que necesiten memoria no la 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 los distribuidores de memoria realizan el seguimiento, 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 haya consultas que adquieran un gran número de bloqueos y los optimicen mediante índices, o bien que acorten transacciones que mantienen bloqueos durante largos períodos, o que comprueben 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.

Acción del usuario

Si el error 701 aparece ocasionalmente o durante un breve período de tiempo, es posible que haya un problema de memoria de corta duración que se haya resuelto de forma automática. 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 las salidas periódicas de esta consulta en la instancia de SQL Server afectada

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 system y applicaiton 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 Páginas bloqueadas en memoria (AWE API), la mayor parte de su memoria se refleja en el contador Proceso: Bytes privados (instancia de SQLServr) del 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 bloqueadas en memoria (AWE API), es más difícil identificar el problema porque Monitor de rendimiento no ofrece contadores de AWE que realicen el 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 de Proceso: Bytes privados típicos 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 valor del contador Bytes privados es de 5-4 GB y SQL Server usa Páginas bloqueadas en memoria (AWE), es posible que una gran parte de los bytes privados provengan 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 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. Vea Creación de servidores vinculados para más información. No todos los proveedores OLEDB de servidor vinculado se pueden ejecutar fuera de 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 si establece context = 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

• Empiece a recopilar contadores del 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 las 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 el uso de 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 bases de datos o esquemas con cientos de procedimientos.
  • 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.

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:

• 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 Páginas bloqueadas en memoria, considere la posibilidad de establecerla en un valor determinado a fin de permitir algo de memoria para el sistema operativo. Vea la opción de configuración del servidor Páginas bloqueadas en memoria.

• Compruebe la carga de trabajo de consulta: número de sesiones simultáneas, consultas actualmente en ejecución y compruebe si hay aplicaciones menos críticas que se puedan detener temporalmente o moverse 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 para las máquinas virtuales, consulte este blog Virtualización: sobreasignación de memoria y cómo detectarla dentro de la máquina virtual y Solución de problemas de rendimiento de la máquina virtual ESX/ESXi (sobrecommitimiento de memoria).

• Puede ejecutar los siguientes comandos DBCC para liberar varias memorias caché de memoria 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).