lunes, 17 de febrero de 2014

SQL Server - Opciones de configuración de memoria del servidor

SQL Server

http://technet.microsoft.com/es-es/library/ms178067.aspx




Opciones de configuración de memoria del servidor

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 un proceso de SQL Server usado por una instancia de SQL Server.
El valor de configuración predeterminado para Memoria de servidor mínima es 0 y para Memoria de servidor máxima es 2147483647 MB. De manera predeterminada, SQL Server puede cambiar sus requisitos de memoria de manera dinámica basándose en los recursos del sistema disponibles.
Nota Nota
Si establece el valor Memoria de servidor máxima en el valor mínimo, puede reducir significativamente el rendimiento de SQL Server e incluso impedir que se inicie. Si no puede iniciar SQL Server tras cambiar esta opción, inicie esta herramienta mediante la opción de inicio –f y restablezca la opción max server memory a su valor anterior. Para obtener más información, vea Opciones de inicio del servicio de motor de base de datos.
Cuando SQL Server utiliza la memoria de manera dinámica, realiza una consulta periódica en el sistema para determinar la cantidad de memoria libre. El mantenimiento de esta memoria libre evita la paginación en el sistema operativo (SO). Si hay menos memoria libre, SQL Server libera memoria para el sistema operativo. Si hay más memoria libre, SQL Server puede asignar más memoria. SQL Server agrega memoria solo cuando su carga de trabajo requiere más memoria; un servidor inactivo no aumenta el tamaño de su espacio de direcciones virtuales.
SQL Server usa la API de notificación de memoria QueryMemoryResourceNotification para determinar el momento en que el Administrador de memoria de SQL Server puede asignar y liberar memoria.
Se recomienda permitir a SQL Server utilizar memoria dinámicamente; sin embargo, puede establecer las opciones de memoria manualmente y restringir la cantidad de memoria a la que SQL Server puede obtener acceso. 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 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 SQL Server.
Establecer manualmente las opciones de memoria

Establezca Memoria de servidor mínima y Memoria de servidor máxima de manera que abarquen 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 que se ejecutan en el mismo equipo.
Use Memoria de servidor mínima 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.
Nota Nota
No se garantiza que SQL Server asigne la cantidad de memoria especificada en Memoria de servidor mínima. Si la carga en el servidor no precisa nunca que se asigne la cantidad de memoria especificada en Memoria de servidor mínima, SQL Server se ejecutará con menos memoria.
Tipo de SO
Cantidades mínimas de memoria disponibles para Memoria de servidor máxima
32 bits
64 MB
64 bits
128 MB
Cómo configurar las opciones de memoria utilizando SQL Server Management Studio

Utilice las dos opciones de memoria de servidor Memoria del servidor mínima y Memoria del 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

Para establecer una cantidad fija de memoria
  1. En el Explorador de objetos, haga clic con el botón secundario 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. La configuración predeterminada para min server memory es 0 y para max server memory es 2147483647 megabytes (MB).
Maximizar el rendimiento para aplicaciones de red

Para optimizar el uso de memoria del sistema para SQL Server, debe limitar la cantidad de memoria utilizada por el sistema para el almacenamiento en memoria caché de archivos. Para limitar la memoria caché del sistema de archivos, hay que asegurarse de que no esté activada la opción Maximizar el rendimiento para compartir archivos. Puede especificar la cantidad mínima de memoria caché del sistema de archivos seleccionando Minimizar la memoria usada o Balance.

Para comprobar la configuración actual de su sistema operativo

  1. Haga clic en Inicio y en Panel de control, haga doble clic en Conexiones de red y, a continuación, haga doble clic en Conexión de área local.
  2. En la pestaña General, haga clic en Propiedades, seleccione Compartir impresoras y archivos para redes Microsoft y, a continuación, haga clic en Propiedades.
  3. Si está seleccionada la opción Maximizar el rendimiento para aplicaciones de red, elija cualquier otra opción, haga clic en Aceptar y, a continuación, cierre el resto de cuadros de diálogo.
Bloquear páginas en la memoria

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 la memoria de SQL Server está establecida en ON en las instancias de 32 y 64 bits de la edición SQL Server 2012 Standard y posterior cuando a la cuenta con privilegios para ejecutar sqlservr.exe se le ha concedido el derecho de usuario "Bloquear páginas en la memoria” (LPIM). En versiones anteriores de SQL Server, establecer la opción de bloqueo de páginas para una instancia de 32 bits de SQL Server requiere que la cuenta con privilegios para ejecutar sqlservr.exe tenga el derecho del usuario LPIM y que la opción de configuración “awe_enabled” esté establecida en ON.
Para deshabilitar la opción de Bloquear páginas en la memoria para SQL Server, quite el derecho de usuario “Bloquear páginas en la memoria” para la cuenta de inicio de SQL Server.

Para deshabilitar Bloquear páginas en la memoria

Para deshabilitar 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 la opción de Bloquear páginas en la memoria.
  6. En el cuadro de diálogo Configuración de la directiva de seguridad local, seleccione la cuenta con privilegios para ejecutar sqlservr.exe y haga clic en Quitar.
Administrador de memoria virtual

Los sistemas operativos de 32 bits proporcionan acceso a un espacio de direcciones virtuales de 4 GB. Los 2 GB de memoria virtual son privados para cada proceso y están disponibles para el uso de las aplicaciones. Esta cantidad de 2 GB está reservada para uso del sistema operativo. Todas las ediciones de sistemas operativos incluyen un modificador que puede proporcionar a las aplicaciones acceso a 3 GB de espacio de direcciones virtuales, quedando limitado el sistema operativo a 1 GB. Para obtener más información acerca del uso de la configuración de memoria del modificador, vea la documentación de Windows acerca de la optimización de 4 gigabytes (4 GB). Cuando SQL Server de 32 bits se ejecuta en un sistema operativo de 64 bits, el espacio de direcciones virtuales disponible para el usuario es el total de 4 GB.
El Administrador de memoria virtual (VMM) de Windows asigna las regiones confirmadas de espacio de direcciones a la memoria física disponible.
Para obtener más información sobre la cantidad de memoria física admitida por los distintos sistemas operativos, vea la documentación de Windows titulada "Límites de memoria para versiones de Windows".
Los sistemas de memoria virtual permiten una mayor asignación de memoria física, de forma que la proporción de memoria virtual a memoria física puede ser superior a 1:1. Como resultado, los programas más grandes se pueden ejecutar en equipos con una diversidad de configuraciones de memoria física. No obstante, el uso de una cantidad de memoria virtual significativamente superior al promedio combinado de los espacios de trabajo de todos los procesos puede provocar un rendimiento bajo.
Memoria de servidor mínima y Memoria de servidor máxima son opciones avanzadas. Si utiliza el procedimiento almacenado del sistemasp_configure para cambiar estos valores, podrá cambiarlos solo si Mostrar opciones avanzadas tiene establecido el valor 1. Estos valores surten efecto inmediatamente, sin necesidad de reiniciar el servidor.
Ejecutar 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:
  • Utilizar max server memory para controlar el uso de memoria. 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.
  • Utilizar min server memory para controlar el uso de memoria. 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.
Proporcionar la cantidad máxima de memoria a SQL Server

32 bits
64 bits
Memoria convencional
Hasta el límite de espacio de direcciones virtuales del proceso en todas las ediciones de SQL Server:
  • 2 GB
  • 3 GB con el parámetro de arranque /3gb1
  • 4 GB en WOW642
Hasta el límite de espacio de direcciones virtuales del proceso en todas las ediciones de SQL Server:
  • 8 TB en la arquitectura x64
1 /3gb es un parámetro de arranque del sistema operativo. Para obtener más información, visite MSDN Library.
2 WOW64 (Windows on Windows 64) es un modo en el que SQL Server de 32 bits se ejecuta en un sistema operativo de 64 bits. Para obtener más información, visite MSDN Library.
Ejemplos

En el ejemplo siguiente se establece la opción max server memory en 4 GB:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Conceptos

Supervisión y optimización del rendimiento
Opciones de configuración del servidor





SQL Server

http://technet.microsoft.com/es-es/library/ms189081.aspx



Supervisión y optimización del rendimiento

 El objetivo de supervisar bases de datos es evaluar el rendimiento de un servidor. Una supervisión eficaz implica tomar instantáneas periódicas del rendimiento actual para aislar procesos que causan problemas y recopilar datos de forma continua a lo largo del tiempo para realizar el seguimiento de las tendencias de rendimiento.
La evaluación continua del rendimiento de la base de datos ayuda a minimizar los tiempos de respuesta y a maximizar el rendimiento, obteniendo como resultado un rendimiento óptimo. El tráfico de red, la E/S de disco y el uso de la CPU eficientes son factores clave para obtener un buen rendimiento. Es necesario analizar a fondo los requisitos de las aplicaciones, comprender la estructura lógica y física de los datos, evaluar el uso de la base de datos y negociar contrapartidas, como el procesamiento de transacciones en línea (OLTP) frente a los sistemas de ayuda para la toma de decisiones.
Ventajas de las bases de datos de supervisión y optimización del rendimiento

Microsoft SQL Server y el sistema operativo Microsoft Windows proporcionan herramientas que le permiten ver las condiciones actuales de la base de datos y realizar un seguimiento del rendimiento a medida que estas cambian. Existen diversas herramientas y técnicas que pueden usarse para supervisar Microsoft SQL Server. Conocer el modo de supervisar SQL Server puede ayudarle a:
  • Determinar si el rendimiento se puede mejorar. Por ejemplo, al supervisar los tiempos de respuesta a las consultas usadas con frecuencia, puede determinar si es necesario cambiar la consulta o los índices de las tablas.
  • Evaluar la actividad de los usuarios. Por ejemplo, al supervisar usuarios que intentan conectarse a una instancia de SQL Server, puede determinar si la seguridad está configurada correctamente y probar las aplicaciones o sistemas de desarrollo. Por ejemplo, al supervisar las consultas SQL mientras se ejecutan, puede determinar si están escritas correctamente y si producen los resultados esperados.
  • Solucionar problemas o depurar componentes de aplicaciones, como procedimientos almacenados.

Supervisión en un entorno dinámico

La supervisión es importante, puesto que SQL Server ofrece un servicio en un entorno dinámico. Las condiciones cambiantes se traducen en cambios en el rendimiento. En sus evaluaciones, los cambios de rendimiento se aprecian a medida que el número de usuarios aumenta, los métodos de acceso y conexión de los usuarios cambian, el contenido de la base de datos crece, las aplicaciones cliente cambian, los datos de las aplicaciones cambian, las consultas son más complejas y el tráfico de red crece. Con la ayuda de las herramientas de SQL Server para supervisar el rendimiento, puede asociar algunos cambios del rendimiento con las condiciones cambiantes y las consultas complejas. A continuación se muestran algunos escenarios a modo de ejemplo:
  • Mediante la supervisión de los tiempos de respuesta para las consultas utilizadas con frecuencia, puede determinar si es necesario modificar la consulta o los índices de las tablas donde es necesario ejecutar las consultas.
  • Mediante la supervisión de las consultas Transact-SQL cuando se ejecutan, puede determinar si están escritas correctamente y si producen los resultados esperados.
  • Mediante la supervisión de los usuarios que intentan conectarse a una instancia de SQL Server, puede determinar si la seguridad está configurada de forma correcta y probar las aplicaciones o sistemas de desarrollo.
El tiempo de respuesta se mide como el tiempo necesario para devolver la primera fila del conjunto de resultados al usuario, en forma de confirmación visual de que se está procesando una consulta. El rendimiento es el número total de consultas controladas por el servidor durante un periodo determinado.
A medida que aumenta el número de usuarios, aumenta la competencia para obtener recursos de un servidor, y esto hace que el tiempo de respuesta aumente y el rendimiento global disminuya.
Tareas de supervisión y optimización del rendimiento

Descripción de la tarea
Tema
Proporciona los pasos necesarios para supervisar eficazmente cualquier componente de SQL Server.
Enumera las herramientas de supervisión y optimización de SQL Server.
Proporciona información acerca de cómo establecer una línea base de rendimiento.
Describe cómo aislar problemas de rendimiento de base de datos.
Describe cómo supervisar y seguir el rendimiento del servidor para identificar cuellos de botella.
Describe cómo usar SQL Server y las herramientas de supervisión de rendimiento y actividad de Windows.
Describe cómo mostrar y guardar planes de ejecución en un archivo de formato XML.