Compartir a través de

Opciones de configuración de la memoria del servidor

Se aplica a:SQL Server

El uso de memoria para el motor de base de datos de SQL Server está limitado por un par de opciones de configuración y min server memory (MB)max server memory (MB). Con el tiempo y en circunstancias normales, SQL Server intentará reclamar memoria hasta el límite establecido por max server memory (MB).

Nota:

Índices de almacén de columnas: información general y In-Memory información general de OLTP y escenarios de uso son objetos que tienen sus propios administradores de memoria, lo que facilita la supervisión de su uso del grupo de búferes. Para obtener más información, consulte sys.dm_os_memory_clerks.

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. Se recomienda en todas las versiones de SQL Server configurar un límite superior para el uso de memoria de SQL Server mediante la configuración de max server memory (MB).

  • Desde SQL Server 2019 (15.x), el programa de instalación de SQL en servidores Windows proporciona una recomendación para max server memory (MB) una instancia de SQL Server independiente basada en un porcentaje de memoria del sistema disponible en el momento de la instalación.
  • En cualquier momento, puede volver a configurar los límites de memoria (en megabytes) para un proceso de SQL Server usado por una instancia de SQL Server a través de las min server memory (MB) opciones de configuración y max server memory (MB) .

Nota:

Esta guía se centra en una instancia de SQL Server en Windows. Si desea obtener información sobre la configuración de memoria en Linux, consulte Procedimientos recomendados de rendimiento y directrices de configuración para SQL Server en Linux y la sección sobre la opción configuración memory.memorylimitmb.

Recomendaciones

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

Opción Valor predeterminado Mínimo permitido Recomendado
min server memory (MB) 0 0 0
max server memory (MB) 2 147 483 647 megabytes (MB) 128 MB 75 % de la memoria del sistema disponible no consumida por otros procesos, incluidas otras instancias. Para obtener recomendaciones más detalladas, consulte Memoria de servidor máxima.

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.

  • Establecer max server memory (MB) el valor demasiado alto puede hacer que una sola instancia de SQL Server compita por la memoria con otras instancias de SQL Server hospedadas en el mismo host.
  • Sin embargo, establecer max server memory (MB) demasiado bajo es una oportunidad de rendimiento perdida y podría causar problemas de presión de memoria y rendimiento en la instancia de SQL Server.
  • Establecer max server memory (MB) en el valor mínimo incluso puede impedir que SQL Server se inicie. Si no puede iniciar SQL Server después de cambiar esta opción, iníciela con la -f opción de inicio y restablezca max server memory (MB) a su valor anterior. Para más información, consulte Opciones de inicio del servicio de motor de base de datos.
  • No se recomienda establecer max server memory (MB) y min server memory (MB) ser el mismo valor o cerca de los mismos valores.

Nota:

La opción de memoria de servidor máxima solo limita el tamaño del grupo de búferes de SQL Server. La opción de memoria de servidor máxima no limita un área de memoria restante no reservada que SQL Server deja para las asignaciones de otros componentes, como procedimientos almacenados extendidos, objetos COM, archivos DLL y EXE no compartidos.

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 adecuada restando, de la memoria física total, la memoria necesaria para el sistema operativo (SO), las asignaciones de memoria no controladas por la max server memory (MB) configuración y cualquier otra instancia de SQL Server (y otros usos del sistema, si el servidor está hospedado en otras aplicaciones que consumen memoria, incluyendo 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 min server memory (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 min server memory (MB) el inicio. Sin embargo, después de que el uso de memoria haya alcanzado este valor debido a la carga del cliente, SQL Server no puede liberar memoria a menos que se reduzca el valor de min server memory (MB) . Por ejemplo, cuando varias instancias de SQL Server se instalan simultáneamente en el mismo servidor, considere la posibilidad de establecer el min server memory (MB) parámetro para reservar memoria para una instancia.

  • Establecer un min server memory (MB) valor es esencial en un entorno virtualizado para garantizar que la presión de memoria del host subyacente no intente desasignar la memoria del grupo de búferes en una máquina virtual invitada más allá de lo necesario para 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 se garantiza que SQL Server asigne la cantidad de memoria especificada en min server memory (MB). Si la carga en el servidor nunca requiere asignar la cantidad de memoria especificada en min server memory (MB), SQL Server usará menos memoria.

Memoria de servidor máxima

Use max server memory (MB) para garantizar que el sistema operativo y otras aplicaciones no experimenten presión perjudicial en la memoria procedente de SQL Server.

  • Antes de establecer la configuración, supervise el max server memory (MB) consumo general de memoria del servidor que hospeda la instancia de SQL Server, durante el funcionamiento normal, para determinar la disponibilidad y los requisitos de memoria. Para una configuración inicial o cuando no se pudo recopilar el uso de memoria del proceso de SQL Server con el tiempo, use el siguiente enfoque de procedimientos recomendados generalizados para configurar max server memory (MB) para una sola instancia:
    • A partir de la memoria total del sistema operativo, resta el equivalente de posibles asignaciones de memoria de subprocesos de SQL Server fuera max server memory (MB) de control, que es el tamañode pila 1 multiplicado por subprocesos de trabajo máximo calculados2.
    • A continuación, resta 25% para otras asignaciones de memoria fuera max server memory (MB) del control, como búferes de copia de seguridad, archivos DLL de procedimientos almacenados extendidos, objetos creados mediante procedimientos de Automatización (sp_OA llamadas) y asignaciones de proveedores de servidores vinculados. Se trata de una aproximación genérica; su consumo puede variar.
    • Lo que permanece debe ser la max server memory (MB) configuración de una sola instancia.

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

2 Para obtener más información sobre los subprocesos de trabajo predeterminados calculados para un número determinado de CPU afinidadizadas en el host actual, vea Configuración del servidor : número máximo de subprocesos de trabajo.

Establecimiento de opciones manualmente

Las opciones min server memory (MB) de servidor y max server memory (MB) se pueden establecer para abarcar 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

Las min server memory (MB) opciones y max server memory (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 ejemplo siguiente se establece la max server memory (MB) opción 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.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE 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)';

Uso de SQL Server Management Studio

Use min server memory (MB) y max server memory (MB) para volver a configurar 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:

Captura de pantalla de las opciones de configuración de memoria en SSMS.

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. Al usar el derecho de usuario Bloquear páginas en memoria , se recomienda encarecidamente establecer un límite superior para max server memory (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 una configuración configurada incorrectamente max server memory (MB) que no tenga en cuenta otros consumidores de memoria en el sistema podría provocar inestabilidad, dependiendo de la cantidad de memoria necesaria para otros procesos o requisitos de memoria de SQL Server fuera del ámbito de max server memory (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), se recomienda encarecidamente establecer max server memory (MB) en un valor específico, en lugar de dejar el valor predeterminado de 2.147.483.647 megabytes (MB).

Nota:

A partir de SQL Server 2012 (11.x), la marca de seguimiento 845 no es necesaria para que Standard Edition use páginas bloqueadas.

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.

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. Se concede el privilegio de fijar páginas en memoria en el modo empresarial con la marca de traza 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 advertencias importantes, consulte la 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 AS omn
     INNER JOIN sys.dm_os_nodes AS 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:

  • Use max server memory (MB) en cada instancia para controlar el uso de memoria, como se ha detallado 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.

  • Use min server memory (MB) en cada instancia para controlar el uso de memoria, como se ha detallado 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.

  • Use y max server memory (MB)min server memory (MB) en cada instancia para controlar el uso de memoria, observar y ajustar el uso máximo de cada instancia y la protección de memoria mínima dentro de una amplia gama de posibles niveles de uso de memoria.

  • 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

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

En el ejemplo siguiente se establece la max server memory (MB) opción 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.

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE 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)';

Contenido relacionado

  • Last updated on