martes, 2 de enero de 2018

SQL Server Opciones de configuración de memoria

Opciones de configuración de memoria

Introducción

Iniciare la discusión de las opciones de configuración mencionando la denominada show advanced options, debe usarse la opción para mostrar las opciones avanzadas cuando se usa el procedimiento almacenado del sistema sp_configure. Cuando se establece en 1, puede enumerar las opciones avanzadas. Es importante mencionar en este punto, que las opciones de configuración avanzadas requieren que sean modificadas por el administrador de SQL Server. El valor predeterminado es 0, indicando que no se muestren las opciones avanzadas utilizando el procedimiento almacenado. La configuración se aplica inmediatamente sin un reinicio del servidor. Sin embargo, para que el valor pueda ser establecido, se requiere que se utilice el comando RECONFIGURE, para que el valor sea establecido. A continuación se muestra el uso de estos elementos:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE

Una vez que se hayan realizado los ajustes a los valores de las opciones de configuración, se recomienda que se vuelva a establecer el valor de show advanced options en 0.

sp_configure 'show advanced options', 0;
GO
RECONFIGURE

Opciones de Memoria

Hablaremos de las opciones de configuración de memoria, aquellas que establecen como debe utilizarse la memoria para el trabajo de SQL Server. Las opciones de configuración de memoria en cuestión son las siguientes:

Nombre de opción
Descripción
index create memory (KB)
Memoria para tipos de creación de índice (kBytes)
max server memory (MB)
tamaño máximo de la memoria del servidor (MB)
min memory per query (KB)
memoria mínima por consulta (kBytes)
min server memory (MB)
Tamaño mínimo de la memoria del servidor (MB)

Estas opciones se han mantenido presentes desde hace tiempo en Microsoft SQL Server. Veamos cómo afecta cada una de estas opciones.

Opción index create memory

La opción memoria de creación de índice controla la cantidad máxima de memoria inicialmente asignada para crear índices. Si posteriormente se necesita más memoria para la creación del índice, y la memoria está disponible, el servidor la usará, excediendo así la configuración de esta opción. Si la memoria adicional requerida no está disponible, la creación del índice continuará usando la memoria ya asignada.
El valor por defecto de esta opción es 0 (cero), el valor mínimo aceptable es 704 (KB) y el máximo es 2147483647 (KB),  sin embargo, el valor establecido por defecto significa que la cantidad de memoria que se asigna es de 704 KB y como se ha establecido, si es requerida más memoria para la creación, ésta se usara.

Esta opción es auto-configurable y generalmente funciona sin requerir ajuste. Sin embargo, si tiene dificultades para crear índices, considere aumentar el valor de esta opción desde su valor de ejecución. Está es una opción de configuración avanzada,  requiere que se haya indicado que se quiere que se muestren las  mismas. La configuración tiene efecto inmediatamente, es una opción dinámica (sin un reinicio del servidor).
Para cambiar el valor de esta opción de configuración usando el procedimiento almacenado se ejecuta el siguiente comando:

sp_configure 'index create memory (KB)', 1024
GO
RECONFIGURE

Opciones de memoria del servidor

Como se puede observar existen dos opciones de configuración de memoria del servidor, la memoria mínima del servidor (min server memory) y la memoria máxima del servidor (max server memory), para reconfigurar la cantidad de memoria (en megabytes) administrada por el SQL Server Memory Manager para un proceso de Microsoft SQL Server.
El valor de la configuración predeterminada para la memoria mínima del servidor es 0, y el valor de la configuración predeterminada para la memoria máxima del servidor es 2,147,483,647 megabytes (MB). Es importante indicar que, de forma predeterminada, Microsoft SQL Server puede cambiar sus requisitos de memoria de forma dinámica en función de los recursos disponibles del sistema. La cantidad de memoria mínima permitida para establecer la memoria máxima del servidor es de 128 MB.

Si bien, Microsoft SQL Server puede usarla  memoria de forma dinámica; es recomendable establecer las opciones de memoria de forma manual y restringir la cantidad de memoria a la que Microsoft SQL Server puede acceder. Es imprescindible que antes de establecer la cantidad de memoria que se asignará a Microsoft SQL Server, se determine la cantidad de memoria adecuada para la configuración llevando a cabo la resta, de la memoria física total, de la memoria requerida para el sistema operativo, es recomendado reservar de 1GB-4GB para el sistema operativo, las asignaciones de memoria no controladas por la configuración max_server_memory y cualquier otra instancia de Microsoft SQL Server (y otros usos del sistema, si la computadora no está totalmente dedicada a Microsoft SQL Server). Esta diferencia es la cantidad máxima de memoria que puede asignar a la instancia actual de Microsoft SQL Server. Se debe establecer el valor de max_server_memory para garantizar que el sistema operativo no experimente una presión de memoria perjudicial.
Por ejemplo, si un servidor cuenta con 16 GB de memoria física y se ha instalado Windows Server 2016 Standard Edition, se requiere un mínimo de 512 MB de memoria para el sistema operativo, suponiendo que solo estará el servidor dedicado al manejo de las bases de datos de Microsoft SQL Server, en una sola instancia y que es posible que se haya identificado la asignación de memoria de 512 MB, entonces el total de memoria que puede utilizar es 15 GB, sin embargo, es recomendable en estos casos, dedicar un 80% de la memoria a Microsoft SQL Server, de esta forma el máximo de memoria de servidor que se debe asignar es de 12.5 GB.

Se debe establecer un valor de min_server_memory para garantizar una cantidad mínima de memoria disponible para el SQL Server Memory Manager para una instancia de Microsoft SQL Server. Microsoft SQL Server no asignará inmediatamente la cantidad de memoria especificada en la memoria mínima del servidor al inicio de las operaciones. Sin embargo, una vez que el uso de la memoria ha alcanzado este valor debido a la carga del cliente, Microsoft SQL Server no puede liberar memoria a menos que se reduzca el valor mínimo de la memoria del servidor. Establecer el valor de la opción min_server_memory es esencial en un entorno virtualizado para garantizar la memoria que se necesita para un rendimiento aceptable.
Estás son opciones de configuración avanzada, se requiere que se haya indicado que se quiere que se muestren las  mismas. La configuración establecida en ambos valores tiene efecto inmediatamente, se trata de opciones dinámicas (sin un reinicio del servidor).

Para cambiar los valores de éstas opciones de configuración, se ejecutan los siguientes comandos:

sp_configure 'max server memory (MB)', 12800
sp_configure 'min server memory (MB)', 4096
RECONFIGURE

Opción min memory per query

La opción de memoria mínima por consulta se usa para especificar la cantidad mínima de memoria (en kilobytes (KB)) que se asignan para la ejecución de una consulta. Es posible establecer en cualquier valor entre 512 y 2.147.483.647 bytes (2 gigabytes (GB)). El valor predeterminado de esta opción, cuando se instala el motor de Microsoft SQL Server, es de 1,024 KB.
El procesador de consultas de Microsoft SQL Server intenta determinar la cantidad óptima de memoria para asignar a una consulta. Esta opción permite al administrador especificar la cantidad mínima de memoria que recibe cada consulta. Las consultas generalmente reciben más memoria que esta si tienen operaciones de clasificación y hash en un gran volumen de datos. Debe tenerse cuidado al aumentar el valor ya que puede mejorar el rendimiento para algunas consultas pequeñas o medianas, pero hacerlo podría generar una mayor competencia por los recursos de la memoria.

Para cambiar el valor de ésta opción de configuración, se ejecuta el siguiente comando:

sp_configure 'min memory per query (KB)', 2048
RECONFIGURE

Comentarios

Se han visto cada una de las opciones de configuración de memoria, estas mismas opciones aparecen en la pestaña de memoria de la ventana de propiedades del servidor, como se puede observar en la siguiente imagen:


Si bien, es posible realizar el cambio en esta ventana, ya que los valores se muestran y están habilitados, es preferible llevar a cabo la modificación de estos valores con el procedimiento almacenado de sistema, de esta forma se puede mantener un control apropiado sobre los valores indicados en la opción de configuración.

Particularmente recomiendo que se modifiquen los valores de máximo y mínimo de memoria de servidor únicamente y se lleve a cabo un análisis del comportamiento del servidor para modificar las otras dos opciones de memoria, lo más recomendable es dejar que Microsoft SQL Server lleve a cabo la asignación dinámica de la opción index creation memory.


Es importante indicar que el ajuste de la opción de configuración de memoria mínima por consulta tiene prioridad sobre la opción de memoria de creación de índice. Si alteran ambas opciones y la memoria de creación de índice es menor que la memoria mínima por consulta, se recibirá un mensaje de advertencia, pero el valor se establece al indicado. Durante la ejecución de la consulta, recibirá una advertencia similar.

No hay comentarios.:

Publicar un comentario