Rendimiento SQL Server: grado de paralelismo

El tema de los grados de paralelismo en SQL Server es bastante extenso y tiene una bibliografía bastante amplia sobre todo en inglés (como casi todo en informática), en este post intentaré dar una visión global de qué es y para qué sirven los grados de paralelismo en SQL Server y sobre todo cuando y cómo utilizarlos.

En primer lugar y lo más importante es disipar cualquier idea que puedas tener acerca de esta opción (Max Degree of Parallelism) y su vinculación a la cantidad de procesadores que SQL Server puede utilizar cuando trata de procesar más de una conexión (o usuario) - no es así. Si SQL Server tiene acceso a cuatro procesadores ociosos y tiene cuatro procesos para ejecutar, utilizará los cuatro procesadores - independientemente del grado máximo de paralelismo.

Entonces, ¿qué esta opción no? Max Degree of Parallelism establece el número máximo de procesadores que SQL Server puede utilizar para una consulta. Si SQL Server tiene que devolver una gran cantidad de datos para una consulta a veces tiene sentido romper la misma en un número de consultas más pequeñas, cada consulta devuelve entonces un subconjunto del total de resultados. De esta manera el servidor SQL puede hacer uso de más de un procesador y, por lo tanto, devolver un montón de registros con mayor rapidez para una determinada consulta de lo que podría hacerlo con uno solo.

Hay una serie de criterios que deben cumplirse antes que SQL Server decida romper la consulta en más de una (Intra Query Parallelism) pero a nosotros nos basta con saber que la decisión se basa en la memoria disponible y, lo más importante, la disponibilidad de los procesadores (carece de sentido hablar de paralelismo si nuestro servidor solo tiene 1 procesador).

Si esto es así ¿por qué tenemos que pensar en esta opción? Porque dejarlo en el valor predeterminado 0 (dejando de SQL Server decidir) a veces se pueden dar efectos no deseados como:

  1. Las consultas pueden ir más lento con el paralelismo.
  2. El tiempo de respuesta para una consulta en particular puede llegar a ser no-determinista y esto puede molestar a los usuarios. El tiempo puede variar debido a que:
    1. La consulta a veces puede ir en paralelo y, a veces puede que no.
    2. La consulta puede ser bloqueada por una consulta ejecutada en paralelo a ella y que termina por acaparar todos los procesadores.

Una vez visto esto hay dos cosas importantes a saber sobre el paralelismo en SQL Server:

  • Las consultas en paralelo pueden generar muchos hilos ejecución, más de lo especificado en la propia opción del grado máximo de paralelismo. Por ejemplo para un grado de paralelismo de 4 se podrían permitir más de 12 hilos de ejecución, cuatro para la consulta y el resto para operaciones adicionales que se utilizan para las clases, discusiones, agregados y la recolección, etc.
  • Las consultas en paralelo pueden causar que los procesos del servidor (SPID) tengan que esperar un wait type 0x0200 o CXPACKET.

Con estos datos podemos concluir que generalmente no se debe utilizar el paralelismo en entornos en los cuales vemos que las consultas debido a esto son más lentas, esta lentitud se puede deber a:

  1. Si el sistema tiene un rendimiento de datos muy pobres en el subsistema de disco (s), entonces la descomposición de consultas es poco probable que vaya más rápido.
  2. Posible sesgo de los datos, o posible bloqueo en un rango de datos por una CPU, es decir, un proceso paralelo que está detrás, etc.
  3. Si hay una falta de un índice para un predicado que da lugar a una exploración de tabla. . Una operación de consulta en paralelo puede ocultar el hecho de que la consulta se ejecutaría mucho más rápido con un solo procesador pero con los índices adecuados.

En consecuencia se recomienda probar el sistema sin paralelismo (Max Degree of Parallelism (DOP) = 1) para que estos problemas puedan ser identificados.

Los otros efectos de paralelismo mencionadas anteriormente, deberían explicarse por sí mismos y deben conducir a la idea de que el paralelismo en sub consultas no es realmente apropiado para las aplicaciones de Procesamiento de Transacciones En Línea (OLTP). Estas aplicaciones dónde varía el tiempo de consulta tanto que puede molestar a los usuarios y donde el servidor de servicio a un número de usuarios simultáneos es poco probable que se deba elegir un plan paralelo debido al perfil de carga de trabajo de la CPU.

Otro caso dónde no es recomendable utilizar paralelismo es en aplicaciones como Microsoft Dynamics NAV porque estas se comunican con el servidor SQL a través de ODBC y las peticiones al servidor se convierten en simples declaraciones T-SQL. Estas declaraciones son muy simples Select, Update, Insert y Delete de SQL y no requieren de la ejecución en paralelo en SQL Server. Aún así, como hemos visto anteriormente, esto no impide que el compilador a veces trate de utilizar la ejecución en paralelo en la planificación de las consultas y esto, a veces, puede afectar negativamente al rendimiento. Es por eso que se recomienda establecer el grado máximo de paralelismo en 1 para evitarlo.

¿Cuándo entonces debemos habilitar el paralelismo en SQL Server?

En aplicaciones para el almacenamiento de datos (data warehouse), para el soporte de toma de decisiones, o para escenarios de presentación de informes, todas ellas aplicaciones donde hay un bajo número de consultas de alta carga de trabajo en una caja grande con un montón de memoria.

Así que han decidido emplear el paralelismo ¿qué valor se debe establecer para la opción grado de paralelismo máximo? Bueno, una pauta que se suele tomar como referencia es que si tienes 8 procesadores entonces max degree of parallelism = 4, esta es probable que sea la configuración óptima, sin embargo no hay nada que lo diga, los procesos de prueba y error son la única manera de estar seguros.

Mi consejo sería no establecer este valor para más de la mitad el número de procesadores que se tienen. Si tiene menos de 6 procesadores que establecería DOP a 1, que no permite ningún paralelismo. También se podría hacer una excepción si se tuviera una base de datos que sólo va a apoyar un proceso de usuario (algún tipo de datos de las tareas de almacenamiento o presentación de informes) - aquí la excepción sería establecer max degree of parallelism a 0 (valor por defecto), que permite decidir al propio SQL Server.

Establacer el valor de Max Degree of Parallelism

Existen 3 métodos para modificar esta opción avanzada, 2 de ellos por consola y una desde el SQL Server Managment Studio

Opción 1:

USE MASTER
GO

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'max degree of parallelism', 1
RECONFIGURE
GO

Esta opción es la más larga pero a la vez la más segura porque evitas errores del tipo The configuration option 'max degree of parallelism' does not exist, or it may be an advanced option. y Ad hoc update to system catalogs is not supported.

Opción 2:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

sp_configure 'max degree of parallelism', 8;
RECONFIGURE WITH OVERRIDE;
GO

Opción 3:

Accediendo a las propiedades avanzadas de la base de datos desde el SQL Server Managment Studio y editar la opción de Grado de paralelismo máximo (ver imagen)

Sin-título-1

Resumen: Cuando SQL Server se ejecuta en un equipo con más de un microprocesador o CPU, detecta el mejor grado de paralelismo, es decir, el número de procesadores que se emplea para ejecutar una única instrucción en cada ejecución de planes en paralelo. Puede utilizar la opción max degree of parallelism para limitar el número de procesadores utilizados en la ejecución de planes paralelos. Para que el servidor pueda determinar el Grado máximo de paralelismo, establezca esta opción en 0, que es el valor predeterminado. Si se establece el grado máximo de paralelismo en 0, SQL Server puede utilizar todos los procesadores disponibles hasta un número de 64. Para suprimir la generación del plan paralelo, establezca max degree of parallelism en 1.

Comentarios