Introducción 

En la instalación de la instancia de SQL Server es conveniente adoptar ciertas recomendaciones de configuración para mejorar el rendimiento. Estas sugerencias se pueden establecer a nivel de base de datos y a nivel de instancia. También afectan al equipo donde se instala dicha instancia.

Objetivo

El objetivo del presente documento, es enumerar y explicar las diferentes recomendaciones para la mejora del rendimiento de SQL Server.

Se parte de la base de que ya existen en la instancia de SQL Server los pertinentes trabajos de reconstrucción de índices y actualización de estadísticas. La reconstrucción de índices evita la fragmentación del disco y la actualización de estadísticas posibilita que el optimizador de consultas de SQL Server pueda escoger el mejor plan de ejecución para la resolución de las consultas.

En caso de tratarse de servidores virtualizado es posible que se de condiciones que impidan una correcta asignación de memoria a las diferentes instancias de SQL Server, disminuyendo el rendimiento general del sistema. En estos sistemas es recomendable que se siga una serie de recomendaciones incluidas en el siguiente documento:

Best Practices for SQL Server on VMware

1 Recomendaciones a nivel del Servidor donde se ejecuta la Instancia de SQL Server

Para servidores donde se ejecutan servicios como el de SQL Server, el plan de ahorro de energía se debe establecer a ‘Alto rendimiento’, no se puede poner ‘Equilibrado’. 

Hay que verificar también que no este activo el ahorro de energía a nivel de la Bios de la máquina. Estas dos opciones hacen que no se esté utilizando la velocidad total de los procesadores. Y esto influye en la resolución de las consultas que se lanzan a SQL Server.

 Es recomendable también activar para la instancia la directiva ‘lock pages in memory’. 

La opción Páginas bloqueadas en memoria es una directiva de Windows que determinan que cuentas de usuario pueden mantener datos en la memoria física mediante un proceso. Habilitando la directiva a la cuenta con que se ejecuta SQL Server, se evita que Windows pueda sacar de la memoria física a SQL Server. Desde el menú de inicio, ejecutar gpedit.msc, Configuración de equipo, Configuración de Windows, Asignación de derechos de usuario, Bloquear páginas en memoria (http://technet.microsoft.com/en-us/library/ms190730.aspx):

Para asegurarse de si se ha activado correctamente se puede lanzar la siguiente consulta:

SELECT sql_Memory_model_desc FROM sys.dm_os_sys_info

Otro punto a tener en cuenta para mejorar el rendimiento de las operaciones de Entrada/Salida es el formateo de los discos a un tamaño del sector de 64K. También hay que comprobar el alineamiento de las particiones de los discos del servidor, para que no se realicen más lecturas de las necesarias.

2 Recomendaciones a nivel de Instancia de SQL Server

A continuación, se detallan diferentes recomendaciones de configuración de la instancia de SQL Server.

Opciones de paralelismo

Es conveniente ajustar el número de procesadores que intervienen en la ejecución de planes paralelos para las consultas lanzadas a SQL Server. 

Por defecto la propiedad ‘Grado de paralelismo máximo’ viene definido a 0, esto significa que se pueden utilizar todas las cpus para resolver una consulta, y esto en entornos con varios nodos NUMA no es lo más optimo, ya que se estarán ejecutando hilos en diferentes cpus de diferentes nodos NUMAS, con todo el coste que ello conlleva. Esta propiedad se puede establecer a un número no superior al nº de cpus del nodo NUMA.

Ejemplo: Si se dispone de 2 nodos Numa con 16 CPUs, esta propiedad se puede establecer a una cuarta parte de las CPUs NUMA, sin superar el nº de CPUs por nodo

En relación con la propiedad anterior, también es recomendable establecer la propiedad ‘Umbral de costo para paralelismo’ a un valor mayor que 5 que es el valor por defecto, por ejemplo 50. Esto evita que SQL sobre utilice el paralelismo, dado que nuestra base de datos es de tipología OLTP (transaccional) y no lo requiere en tanta medida como una base de datos  OLAP o reporting.

A estas dos propiedades se accede a través de la sección ‘Opciones avanzadas’ de las propiedades de la instancia de SQL Server:

También se puede modificar estas dos propiedades desde el analizador de consultas con las siguientes instrucciones:

EXEC sp_configure 'show advanced option', '1';

GO

EXEC sp_configure 'max degree of parallelism', 8;  

GO

EXEC sp_configure 'cost threshold for parallelism', '50'

GO

RECONFIGURE WITH OVERRIDE;  

Se pueden comprobar los valores configurados con las siguientes instrucciones:

EXEC sp_configure 'max degree of parallelism'

GO

EXEC sp_configure 'cost threshold for parallelism'

Utilización de Memoria

Es conveniente ajustar la memoria máxima que puede adquirir el motor de SQL a un valor menor que el total de memoria física del servidor. De esta manera se evita que se sufra paginación porque el SO no tenga la suficiente memoria. 

Por defecto la propiedad ‘Cantidad máxima de memoria del servidor (en MB)’ esta defina para que se adquiera toda la memoria disponible, ajustarla a un 80% mas o menos es una buena práctica.

A esta propiedad se accede a través de la sección ‘Memoria’ de las propiedades de la instancia de SQL Server:

También se puede modificar esta propiedad desde el analizador de consultas con las siguientes instrucciones:

EXEC sys.sp_configure N'max server memory (MB)', N'¿¿¿¿?'

GO

RECONFIGURE WITH OVERRIDE

Consultas Ad-hoc

Es conveniente activar la propiedad ‘Optimizar para cargas de trabajo ad-hoc’, ya que en la base de datos se hace un uso intensivo de consultas ad-hoc y de este modo se reduce el espacio en cache utilizado para estas consultas.

A esta propiedad se accede a través de la sección ‘Opciones Avanzadas’ de las propiedades de la instancia de SQL Server:

También se puede modificar esta propiedad desde el analizador de consultas con las siguientes instrucciones:

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO

Compatibilidad de la base de datos master

Hay que revisar el nivel de compatibilidad de la base de datos de sistema master. En las sucesivas actualizaciones de versión de SQL Server, puede darse el caso en que la base de datos master mantenga el nivel de compatibilidad de la versión anterior y el nivel de compatibilidad debe coincidir con la versión de instancia.

Para acceder a la base de datos master, hay que desplegar la carpeta de Bases de datos del sistema:

Sobre la base de datos master, pulsar botón derecho propiedades e ir a la sección ‘opciones’:

Configuración del número de ficheros de Tempdb 

Este es un punto a destacar en las sugerencias de optimización de SQL Server.

La base de datos del sistema tempdb es un recurso global disponible para todos los usuarios conectados a la instancia. Esta base de datos se usa para objetos temporales creados explícitamente como tablas temporales, variables de tabla, tablas devueltas por funciones o cursores, resultados intermedios de ordenación, y mucho más.

Es recomendable que SQL Server alinee el número de ficheros de datos de tempdb, con el número de procesadores para mejorar la eficiencia en operaciones de reserva y liberación de extensiones de objetos temporales. 

Por norma general se recomienda que el número de ficheros de tempdb no sobrepasen el valor de 8.

Para acceder a la base de datos tempdb, hay que desplegar la carpeta de Bases de datos del sistema (igual que para acceder a la base de datos master).  Sobre la base de datos tempdb, pulsar botón derecho propiedades e ir a la sección de ‘Archivos’:

Solo hay que pulsar el botón Agregar para ir añadiendo los ficheros necesarios, especificando el nombre y las opciones de tamaño y crecimiento automático.

Cabe destacar que la base de datos tempdb se vuelve a crear cada vez que se inicia la instancia de SQL Server. Así que el uso de los diferentes ficheros agregados no se hará efectivo hasta el reinicio de la instancia de SQL Server. 

Para mayor claridad se pueden crear directorios, donde ubicar los distintos ficheros de Tempdb (.mdf), con la siguiente nomenclatura:

C:\MSSQLSERVER_TEMPDB

E:\MSSQLSERVER_TEMPDB

Unidades de disco diferentes para cada tipo de fichero

En relación con el apartado anterior, lo óptimo seria poder poner cada tipo de fichero en una unidad de disco. Una unidad para los ficheros de tempdb, otra para los ficheros de datos (.mdf) y otra para el fichero de log (.ldf)

Separar las cargas en diferentes discos puede mejorar sustancialmente las latencias de escritura y de lectura.

3 Recomendaciones a nivel de Base de Datos

Autocrecimiento de la base de datos

Una vez se tiene un conocimiento aproximado del tamaño de la base de datos, es conveniente ajustar la propiedad de autocrecimiento de los ficheros a un valor constante, en vez de utilizar un porcentaje, que es lo que tenemos por defecto. 

De este modo la base de datos crece siempre igual y no depende del tamaño actual. Se trata de tener siempre espacio reservado para un largo periodo y evitar trabajos de autocrecimiento demasiado frecuentes con el coste que ello conlleva. A esta propiedad se accede a través de la sección ‘Archivos’ de las propiedades de la base de datos:

Verificación de página 

Hay que modificar el valor de la propiedad ‘Comprobación de páginas’ a CHECKSUM. Hasta ahora se ha conservado el valor ‘TORN PAGE DETECTION’ de versiones anteriores. A partir de la versión 4.4.2200 ya se actualiza dicho valor. Esta propiedad de base de datos, nos permite seleccionar el nivel de chequeo que garantiza la integridad de las páginas de datos y así detectar posibles errores de disco que dañen la integridad de los datos. 

A esta propiedad se accede a través de la sección ‘Opciones’ de las propiedades de la base de datos:

También se puede modificar esta propiedad desde el analizador de consultas con las siguientes instrucciones:

USE [master]

GO

ALTER DATABASE [nombre_BD] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT

GO

Correcciones del optimizador de consultas.

Se puede activar el parámetro ‘Correcciones del optimizador de consultas’ para que se apliquen los parches de optimización que salieron con los respectivos service packs y hotfixes de Microsoft. A parir de SQL 2016 (nivel de compatibilidad 130) ya no es necesario, vienen activados por defecto.

A esta propiedad se accede a través de la sección ‘Opciones’ de las propiedades de la base de datos:

También se puede modificar esta propiedad desde el analizador de consultas con las siguientes instrucciones:

USE [nombre_BD]

GO

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = On;

GO