Mejorar el rendimiento del disco y la paralelización en SQL Server

Fuente: http://miblogtecnico.wordpress.com/2012/12/23/mejorar-el-rendimiento-del-disco-y-la-paralelizacion-en-sql-server/

En el funcionamiento diario de nuestra base de datos, existen diversos factores que pueden mejorar el rendimiento general de SQL Server. Un factor primordial es el tamaño del sector del disco y otro es el grado de paralelismo.

Tamaño del disco

Sabemos que SQL Server utiliza un tamaño fijo en las páginas de datos de 8Kb y es un valor que no podemos cambiar. Esas páginas son leídas del disco duro y cargadas en memoria. Lo ideal es que ese tamaño coincida con el del disco duro para que así a la hora de leer páginas, pueda leer sectores del disco completos y se correlacionen con los tamaños fijos.

En la página web de la wikipedia, podemos consulta y verificar que el tamaño por defecto del sector del disco duro cuando se formatean en NTFS es 4Kb. Osea que cuando SQL Server tiene que leer una única página de datos del disco duro para ponerla en memoria, necesita leer como mínimo 2 sectores de disco… una pérdida de tiempo y rendimiento.

Para mejorar el acceso a disco, debemos encontrar un valor ideal, o por lo menos compatible con los 8Kb de las páginas de datos que mejore el rendimiento. Como podemos ver en esta url de Microsoft, donde se habla de esta misma problemática, un valor óptimo de sector en disco para SQL Server es 64 Kb.

http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

64 Kb principalmente porque una extensión de SQL Server son 8 página de 8 Kb, osea 64Kb, con lo que mejoramos por un factor de 16 el sector de disco del formateo por defecto de NTFS si tuviese que leer esa misma extensión.

Para formatear un disco duro de NTFS a 64 Kb, podemos hacerlo con este comando:

format E: /A:64k /V:Data

Grado de Paralelismo

Otro factor que puede afectar al rendimiento de SQL Server es cómo se usa el paralelismo en nuestro servidor. Por defecto, si una máquina física tiene 4 procesadores, SQL Server, intentará utilizar esos 4 procesadores para intentar procesar consultas muy complejas más rápidamente. Para hacerlo, necesita paralelizar (“trocear”) esa consulta entre los 4 procesadores, ejecutar en cada uno de esos procesadores, y luego unir los resultados de todos los procesadores para enviarlo al cliente o la aplicación.

Este proceso de trocear, ejecutar y volver a unir en las consultas paralelas tiene un coste. Si la consulta es muy compleja, como por ejemplo en las consultas típicas de un OLAP, puede ser interesante tener activo el paralelismo. En cambio en servidores del tipo OLTP donde las consultas son muchas pero sencillas, el paralelismo no tiene sentido, ya que un único procesador puede hacer ese trabajo más eficientemente que paralelizándolo entre varios. En esta web de Microsoft se detalla este mismo problema:

http://support.microsoft.com/kb/247500/es

Una manera lógica es tener desactivado el paralelismo en bases de datos del tipo OLTP y tener activo en OLAP, aunque eso depende del entorno. Una forma de comprobar dicho entorno, es capturando los paquetes del tipo CXPACKET con los extend events. Si vemos que existen muchos paquetes CXPACKET y que tardan mucho en salir, eso indica que SQL Server está tardando mucho tiempo en procesar el paralelizado. Osea que le cuesta más paralelizar la consulta, que ejecutarla en un único procesador.

Para que una consulta sólo utilice un único procesador, como es el caso de las consultas OLTP, podemos utilizar los query_hits en la cláusula OPTION de nuestras select y variar el MAXDOP.

http://msdn.microsoft.com/es-es/library/ms190322.aspx

Por ejemplo en esta consulta, le estamos indicando a SQL Server que use sólo 1 procesador con MAXDOP:

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)

Por defecto el comportamiento de MAXDOP, puede ser variado en la configuración de la instancia, en el apartado de Advanced y Parallelism. Una valor por defecto de 0 enMax Degree of Parallelism, indica a la instancia que por defecto use todos los procesadores disponibles para todas las consultas.  También es cierto que podemos ajustar dicho valor por defecto con el valor de Cost Threshold for Parallelism, que indica que si el coste de la consulta supera el valor de 5, entonces por defecto utilizará paralelismo. Una valor óptimo para un servidor de bases de datos del tipo OLTP es un max degree of parallelism a 1 para que así siempre use 1 procesador para consultas cortas.

http://msdn.microsoft.com/es-es/library/ms188603(v=sql.90).aspx