Opciones de configuración de la memoria del servidor

Se aplica a: SQL Server (todas las versiones admitidas)

Vuelva a configurar la cantidad de memoria (en megabytes) para un proceso de SQL Server que usa una instancia de SQL Server. Existen dos opciones de memoria del servidor: min server memory y max server memory. Estas opciones cambian la cantidad de memoria que el administrador de memoria de SQL Server puede asignar a los procesos de SQL Server.

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

De forma predeterminada, SQL Server puede cambiar sus requisitos de memoria de manera dinámica basándose en los recursos del sistema disponibles. Para obtener más información, consulte Administración dinámica de memoria.

SQL Server puede usar memoria dinámicamente; sin embargo, es posible 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, las asignaciones de memorias no controladas por la configuración max_server_memory y todas las demás instancias de SQL Server (y otros usos del sistema, si el equipo no está dedicado totalmente a SQL Server). Esta diferencia es la cantidad de memoria máxima que puede asignar a la instancia de SQL Server actual.

Establecimiento de opciones manualmente

Las opciones de servidor memoria de servidor mínima y memoria de servidor máxima pueden establecerse 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 junto con los requisitos de memoria de otras aplicaciones u otras instancias de SQL Server que se ejecutan en el mismo host.

Use min_server_memory para garantizar una cantidad mínima de memoria disponible para el Administrador de memoria de SQL Server en una instancia de SQL Server. SQL Server no asignará inmediatamente la cantidad de memoria especificada en Memoria de servidor mínima 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 . Por ejemplo, cuando puede haber varias instancias de SQL Server en el mismo host simultáneamente, establezca el parámetro min_server_memory en vez de max_server_memory para reservar memoria para una instancia. Además, el establecimiento del valor min_server_memory 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 (VM) de SQL Server invitada más allá de lo necesario para disfrutar de un rendimiento aceptable.

Use max_server_memory para garantizar que el sistema operativo no experimenta presión de memoria perjudicial. Para establecer la configuración de memoria de servidor máxima, supervise el consumo total del proceso SQL Server para determinar los requisitos de memoria. Si es una configuración inicial o en el caso de que no haya oportunidad de recopilar el uso de memoria de proceso de SQL Server con el tiempo, utilice el siguiente enfoque recomendado generalizado para configurar max_server_memory para una sola instancia:

• De la memoria del sistema operativo total, reste el equivalente de las asignaciones potenciales de memoria de SQL Server al control de max server memory, que está formado por el tamaño de la pila ¹ * subprocesos de trabajo máximos calculados ² .
• A continuación, reste un 25 % de las otras asignaciones de memoria fuera del control de max server memory, como los búferes de copia de seguridad y otros componentes. Se trata de una aproximación genérica; el consumo puede variar.
• El resto debería ser la configuración de max_server_memory 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.

Use SQL Server Management Studio

Use las dos opciones de memoria de servidor memoria de servidor mínima y memoria de servidor máxima para reconfigurar la cantidad de memoria (en megabytes) administrada por el Administrador de memoria de SQL Server para una instancia de SQL Server. De forma predeterminada, SQL Server puede cambiar sus requisitos de memoria de manera dinámica basándose en los recursos del sistema disponibles.

Procedimiento para configurar una cantidad fija de memoria (no recomendado)

Para establecer una cantidad fija de memoria:

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

2. Haga clic en el nodo Memoria .

3. En Opciones de memoria del servidor, escriba la cantidad que desea para Cantidad mínima de memoria del servidor y Cantidad máxima de memoria del servidor.

   Use la configuración predeterminada si desea que SQL Server pueda cambiar dinámicamente sus requisitos de memoria según los recursos del sistema disponibles. Se recomienda establecer una memoria de servidor máxima, tal y como se describe anteriormente.

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

Bloquear páginas en la memoria (LPIM)

Esta directiva de Windows determina qué cuentas pueden usar un proceso para mantener los datos en la memoria física, impidiendo que el sistema realice la paginación de los datos en la memoria virtual del disco. 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á establecida en ON en las instancias de la edición SQL Server Standard y posterior cuando a la cuenta con privilegios para ejecutar sqlservr.exe se le ha 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).

La configuración de esta opción 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. Cuando se usa el derecho de usuario Bloquear páginas en la memoria se recomienda establecer un límite superior para la memoria de servidor máxima, tal y como se describe anteriormente.

Para habilitar Bloquear páginas en la memoria

Para habilitar la opción de bloqueo de páginas en memoria:

1. En el menú Inicio , haga clic en Ejecutar. En el cuadro Abrir , escriba gpedit.msc.

   Se abrirá el cuadro de diálogo Directiva de grupo .

2. En la consola Directiva de grupo , expanda Configuración del equipo y, a continuación, expanda Configuración de Windows.

3. Expanda Configuración de seguridad y, a continuación, expanda Directivas locales.

4. Seleccione la carpeta Asignación de derechos de usuario .

   Las directivas se mostrarán en el panel de detalles.

5. En el panel, haga doble clic en Bloquear páginas en la memoria.

6. En el cuadro de diálogo Configuración de la directiva de seguridad local, agregue la cuenta con privilegios para ejecutar sqlservr.exe (la cuenta de inicio de SQL Server).

Varias instancias de SQL Server

Cuando esté ejecutando varias instancias de Motor de base de datos, existen tres maneras con las que puede administrar la memoria:

• Usar memoria de servidor máxima 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 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 de su 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.

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

Especificación de la cantidad máxima de memoria

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

Ejemplos

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

En el ejemplo siguiente se establece la opción max server memory en 4 GB. Tenga en cuenta que, aunque sp_configure especifica el nombre de la opción como max server memory (MB), el ejemplo muestra que se omite el elemento (MB).

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 la siguiente:

La opción de configuración "Memoria de servidor máxima (MB)" ha cambiado de 2147483647 a 4096. Ejecute la instrucción RECONFIGURE para instalar.

Ejemplo B: Determinación de 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;  

Ejemplo C. Determinación del valor para "Memoria de servidor máxima (MB)"

La siguiente consulta devuelve información sobre el valor configurado actualmente y el que utiliza SQL Server. Esta consulta devolverá resultados independientemente de si el valor "Mostrar opciones avanzadas" está establecido en "true".

SELECT c.value, c.value_in_use
FROM sys.configurations c WHERE c.[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
Ediciones y características admitidas de SQL Server 2016
Ediciones y características admitidas de SQL Server 2017
Ediciones y características admitidas de SQL Server 2017 en Linux
Memory Limits for Windows and Windows Server Releases (Límites de memoria para versiones de Windows y Windows Server)