Opciones de configuración de la memoria del servidor

Se aplica a:SQL Server

El uso de memoria en el motor de base de datos de SQL Server está limitado por dos opciones de configuración, Memoria de servidor mínima (MB) y Memoria de servidor máxima (MB). Con el tiempo y en circunstancias normales, SQL Server intentará reclamar memoria hasta el límite establecido por la memoria de servidor máxima (MB).

En versiones anteriores de SQL Server, el uso de memoria carecía de límites prácticamente, lo que indicaba a SQL Server que toda la memoria del sistema estaba disponible usarla. En todas las versiones de SQL Server, se recomienda configurar un límite superior para el uso de memoria de SQL Server mediante la configuración de la memoria de servidor máxima (MB).

• Desde SQL Server 2019 (15.x), el programa de instalación de SQL en servidores Windows muestra una recomendación de memoria de servidor máxima (MB) para una instancia independiente de SQL Server, según el porcentaje de memoria del sistema que haya disponible en el momento de la instalación.
• En cualquier momento, puede volver a configurar los límites de memoria (en megabytes) de un proceso de SQL Server utilizado por una instancia de SQL Server, usando para ello las opciones de configuración Memoria de servidor mínima (MB) y Memoria de servidor máxima (MB).

Recomendaciones

La configuración predeterminada y los valores mínimos permitidos para estas opciones son los siguientes:

Con estos límites, SQL Server puede cambiar sus requisitos de memoria de manera dinámica según los recursos del sistema disponibles. Para obtener más información, consulte Administración dinámica de memoria.

• Si el valor de Memoria de servidor máxima (MB) se establece en una cifra demasiado alta, puede hacer que una única de instancia de SQL Server compita por la memoria con otras instancias de SQL Server hospedadas en el mismo host.
• Sin embargo, establecer Memoria de servidor máxima (MB) demasiado baja es una oportunidad de rendimiento perdida y podría causar problemas de presión de memoria y de rendimiento en la instancia de SQL Server.
• Si Memoria de servidor máxima (MB) se establece en el valor mínimo, puede hasta impedir que SQL Server se inicie. Si no puede iniciar SQL Server tras cambiar esta opción, inícielo mediante la opción de inicio -f y restablezca la opción Memoria de servidor máxima (MB) a su valor anterior. Para más información, consulte Opciones de inicio del servicio de motor de base de datos.
• Se desaconseja establecer Memoria de servidor máxima (MB) y Memoria de servidor mínima (MB) en el mismo valor o aproximadamente en los mismos valores.

SQL Server puede usar memoria dinámicamente, aunque también se pueden establecer las opciones de memoria manualmente y restringir la cantidad de memoria a la que SQL Server puede acceder. Antes de establecer la cantidad de memoria para SQL Server, determine la configuración de memoria apropiada restando de la memoria física total la memoria necesaria para el sistema operativo (SO), las asignaciones de memorias no controladas por la opción Memoria de servidor máxima (MB) y todas las demás instancias de SQL Server (y otros usos del sistema, si el servidor hospeda otras aplicaciones que consumen memoria, incluidas otras instancias de SQL Server). Esta diferencia es la cantidad de memoria máxima que puede asignar a la instancia de SQL Server actual.

Se puede configurar memoria hasta el límite del espacio de direcciones virtuales de proceso en todas las ediciones de SQL Server. Para obtener más información, consulte Memory Limits for Windows and Windows Server Releases (Límites de memoria para versiones de Windows y Windows Server).

Memoria de servidor mínima

Use Memoria de servidor mínima (MB) para garantizar una cantidad mínima de memoria disponible para el Administrador de memoria de SQL Server.

• SQL Server no asignará inmediatamente la cantidad de memoria especificada en Memoria de servidor mínima (MB) durante el inicio. No obstante, cuando el uso de memoria ha alcanzado este valor debido a una carga del cliente, SQL Server no puede liberar memoria a menos que se reduzca el valor de Memoria de servidor mínima (MB). Por ejemplo, si hay varias instancias de SQL Server instaladas simultáneamente en el mismo servidor, considere la posibilidad de establecer la opción Memoria de servidor mínima (MB) para reservar memoria para una instancia.

• Establecer un valor en Memoria de servidor mínima (MB) es esencial en un entorno virtualizado para asegurarse de que la presión de memoria del host subyacente no intenta desasignar memoria del grupo de búferes en una máquina virtual invitada más allá de lo necesario para disfrutar de un rendimiento aceptable. Lo ideal es que las instancias de SQL Server en una máquina virtual no tengan que competir con los procesos proactivos de desasignación de memoria del host virtual.

• No es seguro que SQL Server asigne la cantidad de memoria especificada en Memoria de servidor mínima (MB). Si la carga en el servidor no precisa nunca que se asigne la cantidad de memoria especificada en Memoria de servidor mínima (MB), SQL Server usará menos memoria.

Memoria de servidor máxima

Use Memoria de servidor máxima (MB) para evitar que el sistema operativo y otras aplicaciones experimenten una presión de memoria perjudicial procedente de SQL Server.

• Antes de establecer la opción Memoria de servidor máxima (MB), supervise el consumo total de memoria del servidor que hospeda la instancia de SQL Server durante un funcionamiento normal para determinar la disponibilidad y los requisitos de memoria. Si se trata de una configuración inicial, o en caso de que no haya oportunidad de recopilar el uso de memoria de proceso de SQL Server a lo largo del tiempo, utilice el siguiente método recomendado generalizado para configurar Memoria de servidor máxima (MB) para una sola instancia:
  • De la memoria total del sistema operativo, reste el equivalente de las asignaciones potenciales de memoria de subproceso de SQL Server al control de Memoria de servidor máxima (MB), que es el tamaño de pila¹ multiplicado por los subprocesos de trabajo máximos calculados².
  • A continuación, reste un 25 % de otras asignaciones de memoria del control max server memory (MB), como los búferes de copia de seguridad, los archivos DLL de procedimiento almacenado extendido, los objetos creados mediante procedimientos de Automation (llamadas sp_OA) y las asignaciones de proveedores de servidores vinculados. Se trata de una aproximación genérica; su consumo puede variar.
  • El resto debería ser la opción Memoria de servidor máxima (MB) para la instalación de una única instancia.

¹ Consulte la guía de arquitectura de administración de memoria para obtener información sobre los tamaños de pila de subprocesos por arquitectura.

² Consulte la página de documentación sobre cómo Establecer la opción de configuración del servidor Máximo de subprocesos de trabajo para obtener información sobre los subprocesos de trabajo predeterminados calculados para un determinado número de CPU con afinidad en el host actual.

Establecimiento de opciones manualmente

Las opciones de servidor Memoria de servidor mínima (MB) y Memoria de servidor máxima (MB) se pueden establecer en un intervalo de valores de memoria. Este método es útil para que los administradores de bases de datos o de sistemas configuren una instancia de SQL Server con los requisitos de memoria de otras aplicaciones u otras instancias de SQL Server que se ejecutan en el mismo host.

Uso de Transact-SQL

Memoria de servidor mínima (MB) y Memoria de servidor máxima (MB) son opciones avanzadas. Cuando se usa el procedimiento almacenado del sistema sp_configure para cambiar estos valores, podrá cambiarlos solo si Mostrar opciones avanzadas está establecido en 1. Estos valores surten efecto inmediatamente, sin necesidad de reiniciar el servidor. Para obtener más información, consulte sp_configure.

En el siguiente ejemplo, la opción Memoria de servidor máxima (MB) se establece en 12 288 MB (o 12 GB). Aunque sp_configure especifica el nombre de la opción como max server memory (MB), (MB) se puede omitir.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

La siguiente consulta devuelve información sobre los valores configurados actualmente y el valor que se utiliza en este momento. Esta consulta devolverá resultados independientemente de si la opción de sp_configure "Mostrar opciones avanzadas" está habilitada.

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

Use SQL Server Management Studio

Use Memoria del servidor mínima (MB) y Memoria del servidor máxima (MB) para reconfigurar la cantidad de memoria (en megabytes) administrada por el Administrador de memoria de SQL Server para una instancia de SQL Server.

1. En el Explorador de objetos, haga clic con el botón derecho en un servidor y seleccione Propiedades.

2. Seleccione la página Memoria de la ventana Propiedades del servidor. Se muestran los valores actuales de Memoria de servidor mínima y Memoria de servidor máxima.

3. En Opciones de memoria del servidor, escriba las cantidades que quiera en Memoria de servidor mínima y Memoria de servidor máxima. Para obtener recomendaciones, consulte Memoria de servidor mínima (MB) y Memoria de servidor máxima (MB) en este artículo.

En la captura de pantalla siguiente se muestran los tres pasos:

Bloquear páginas en la memoria (LPIM)

Las aplicaciones basadas en Windows pueden usar las API de Extensiones de ventanas de direcciones de Windows (AWE) para asignar memoria física al espacio de direcciones del proceso. Esta directiva de Windows LPIM determina qué cuentas pueden acceder a la API para mantener los datos en memoria física, lo que impide que el sistema pagine los datos en la memoria virtual del disco. La memoria asignada mediante AWE se bloquea hasta que la aplicación la libera o sale explícitamente. El uso de las API de AWE para la administración de memoria en SQL Server de 64 bits también se conoce frecuentemente como páginas bloqueadas. El bloqueo de páginas en memoria puede mantener el servidor activo cuando se produce la paginación en la memoria del disco. La opción Bloquear páginas en memoria está habilitada en las instancias de la edición SQL Server Standard y posterior si la cuenta con privilegios para ejecutar sqlservr.exe tiene concedido el derecho de usuario de Windows Bloquear páginas en memoria (LPIM).

Para deshabilitar la opción Bloquear páginas en memoria para SQL Server, quite el derecho de usuario Bloquear páginas en memoria a la cuenta con privilegios que ejecuta la cuenta de inicio de sqlservr.exe (la cuenta de inicio de SQL Server).

El uso de LPIM no afecta a la administración dinámica de memoria de SQL Server, lo que permite expandir o contraer a petición de otros distribuidores de memoria. Si se usa el derecho de usuario Bloquear páginas en la memoria, se recomienda encarecidamente establecer un límite superior para Memoria de servidor máxima (MB). Para obtener más información, consulte Memoria de servidor máxima (MB).

LPIM se debe usar cuando haya indicios de que el proceso sqlservr se está paginando. En este caso, se notificará el error 17890 en el registro de errores, similar al siguiente ejemplo: .

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

El uso de LPIM con un valor Memoria de servidor máxima (MB) incorrecto que no tenga en cuenta otros consumidores de memoria del sistema puede provocar inestabilidad, según la cantidad de memoria que otros procesos requieran o los requisitos de memoria de SQL Server fuera del ámbito de Memoria de servidor máxima (MB). Para más información, consulte max server memory. Si se concede el privilegio Bloquear páginas en memoria (LPIM) (en sistemas de 32 o 64 bits), recomendamos encarecidamente establecer Memoria de servidor máxima (MB) en un valor específico, en lugar de dejar el valor predeterminado de 2 147 483 647 megabytes (MB).

Habilitar Bloqueo de páginas en memoria

Tras sopesar la información anterior, para habilitar la opción Bloquear páginas en memoria concediendo el privilegio a la cuenta de servicio de la instancia de SQL Server, consulte Habilitar la opción Bloquear páginas en memoria (Windows).

Para determinar la cuenta de servicio de la instancia de SQL Server, consulte el Administrador de configuración de SQL Server o haga una consulta a service_account desde sys.dm_server_services. Para obtener más información, consulte sys.dm_server_services (Transact-SQL).

Ver el estado de Bloquear páginas en memoria

Use la siguiente consulta para determinar si el privilegio Bloquear páginas en memoria se ha concedido a la cuenta de servicio de la instancia de SQL Server. Esta consulta se puede usar en SQL Server 2016 (13.x) SP1 y versiones posteriores.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Los siguientes valores de sql_memory_model_desc indican el estado de LPIM:

• CONVENTIONAL. El privilegio Bloquear páginas en memoria no se ha concedido.
• LOCK_PAGES. El privilegio Bloquear páginas en memoria se ha concedido.
• LARGE_PAGES. El privilegio Bloquear páginas en memoria se ha concedido en el modo de empresa con la marca de seguimiento 834 habilitada. Se trata de una configuración avanzada y no se recomienda para la mayoría de los entornos. Para obtener más información y salvedades importantes a este respecto, consulte Marca de seguimiento 834.

Use los métodos siguientes para determinar si la instancia de SQL Server usa páginas bloqueadas:

• La salida de la siguiente consulta de Transact-SQL indicará valores distintos de cero para locked_page_allocations_kb:

  SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb 
  FROM sys.dm_os_memory_nodes omn 
  INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id) 
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• El registro de errores actual de SQL Server notifica el mensaje durante el inicio del servidor Using locked pages in the memory manager.

• La sección Administrador de memoria de la salida DBCC MEMORYSTATUS mostrará un valor distinto de cero para el elemento AWE Allocated.

Varias instancias de SQL Server

Cuando se ejecutan varias instancias del motor de base de datos, existen diferentes formas de administrar la memoria:

• Usar Memoria de servidor máxima (MB) en cada instancia para controlar el uso de memoria, tal y como se detalla anteriormente. Establezca los valores máximos de cada instancia, teniendo cuidado de que la asignación total no sea mayor que la memoria física total de su equipo. Es buena idea proporcionar a cada instancia memoria proporcional a la carga de trabajo o al tamaño de la base de datos esperados. Este método tiene la ventaja de que cuando se inician nuevos procesos o instancias, habrá memoria libre para ellos de forma inmediata. El inconveniente es que si no está ejecutando todas las instancias, ninguna de las instancias que se están ejecutando podrá utilizar el resto de la memoria libre.

• Usar Memoria de servidor mínima (MB) en cada instancia para controlar el uso de memoria, tal y como se detalla anteriormente. Establezca la configuración mínima de cada instancia, de manera que la suma de estos mínimos sea 1-2 GB menos que la memoria física total del equipo. De nuevo, puede establecer estos mínimos proporcionalmente a la carga de trabajo que se espera por cada instancia. Este método tiene la ventaja de que si no se ejecutan todas las instancias a la vez, las que se estén ejecutando pueden utilizar el resto de la memoria libre. Este método también resulta útil cuando en el equipo se está ejecutando otro proceso que consuma mucha memoria, puesto que asegura que SQL Server recibirá, al menos, una cantidad de memoria razonable. El inconveniente es que cuando se inicia una nueva instancia (o cualquier otro proceso), es posible que pase algún tiempo hasta que las instancias que se están ejecutando liberen memoria, especialmente si para ello deben escribir páginas modificadas en sus bases de datos.

• Usar tanto Memoria de servidor máxima (MB) como Memoria de servidor mínima (MB) en cada instancia para controlar el uso de memoria, vigilando y optimizando el uso máximo de cada instancia y la protección de memoria mínima dentro de una amplia gama de niveles de uso de memoria posibles.

• No hacer nada (no se recomienda). Las primeras instancias que se presenten con una carga de trabajo intentarán asignar toda la memoria. Puede que las instancias inactivas o las instancias que se inician más tarde terminen ejecutándose con una cantidad mínima de memoria disponible. SQL Server no intenta equilibrar el uso de memoria en todas las instancias. Sin embargo, todas las instancias responderán a las señales de notificación de memoria de Windows para ajustar el tamaño de su superficie de memoria. Windows no equilibra la memoria entre las aplicaciones con la API de notificación de memoria. Simplemente proporciona informes globales acerca de la disponibilidad de memoria del sistema.

Esta configuración se puede cambiar sin tener que reiniciar las instancias; por tanto, se puede experimentar fácilmente para encontrar la mejor configuración para el patrón de uso.

Ejemplos

A. Establecer la opción de memoria de servidor máxima en 4 GB

En el siguiente ejemplo, la opción Memoria de servidor máxima (MB) se establece en 4096 MB (o 4 GB). Aunque sp_configure especifica el nombre de la opción como max server memory (MB), (MB) se puede omitir.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Esto dará como resultado una instrucción similar a Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. El nuevo límite de memoria surtirá efecto inmediatamente después de ejecutar RECONFIGURE. Para obtener más información, consulte sp_configure.

B. Determinar la asignación de memoria actual

La consulta siguiente devuelve información acerca de la memoria asignada actual.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

C. Ver el valor de max server memory (MB)

La siguiente consulta devuelve información sobre el valor configurado actualmente y el valor en uso. Esta consulta devolverá resultados independientemente de si la opción de sp_configure "Mostrar opciones avanzadas" está habilitada.

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

Pasos siguientes

• Guía de arquitectura de administración de memoria
• Supervisión y optimización del rendimiento
• RECONFIGURE (Transact-SQL)
• Opciones de configuración de servidor (SQL Server)
• sp_configure (Transact-SQL)
• Opciones de inicio del servicio de motor de base de datos
• Memory Limits for Windows and Windows Server Releases (Límites de memoria para versiones de Windows y Windows Server)