Modificar la opción de configuración MAXDOP

Introducción

En muchas ocasiones se presentan problemas con el desempeño de los servicios de Microsoft SQL Server, principalmente cuando se tienen procesadores que contienen mas de un núcleo o Core. Esto se debe a que no se esta usando la capacidad de llevar a cabo el paralelismo de las consultas de forma adecuada.

Es por ello que debe optarse por modificar el valor de MAXDOP en las opciones de configuración de servidor.

Consideraciones

Max Degree of Parallelism (MAXDOP) controla el número de procesadores que se utilizan para ejecutar una consulta en un plan paralelo. El valor establecido en esta opción determina los recursos de proceso y subproceso que utilizarán los operadores del plan de consulta que realizan el trabajo en paralelo.

En general, cuando una instancia de Microsoft SQL Server se ejecuta en una computadora que tiene más de un microprocesador o CPU, el motor de la base de datos detecta el grado de paralelismo, dependiendo del número de procesadores utilizados para ejecutar una sola declaración, para llevar a cabo ejecución del plan paralelo.

De forma predeterminada, cuando se instala una instancia de Microsoft SQL Server, el valor de Maxdop es 0, lo que significa que el grado máximo de paralelismo lo determinará el motor de SQL Server. Cuando no se requiere paralelismo, el valor debe establecerse en 1.

Dependiendo de la versión de Microsoft SQL Server, Microsoft ha establecido algunas recomendaciones para llevar a cabo la configuración de esta opción.

Server NUMA Nodes	Logical Processors	MAXDOP Value	MS SQL Server version
Single	Less than or equal to 8	At or below # of logical processors	2008-2019
Single	Greater than 8	At 8	2008-2019
Multiple	Less than or equal to 8 per NUMA node	At or below # of logical processors per NUMA node	2008-2014
Multiple	Greater than 8 per NUMA node	At 8	2008-2014
Multiple	Less than or equal to 16 per NUMA node	At or below # of logical processors per NUMA node	2016-2019
Multiple	Greater than 16 per NUMA node	At half the number of logical processors per NUMA node with a MAX value of 16	2016-2019

Para Microsoft SQL Server 2016 y versiones superiores, el término nodo NUMA en la tabla se refiere a nodos soft-NUMA creados automáticamente por Microsoft SQL Server, o nodos NUMA basados ​​en hardware, si la opción soft-NUMA ha sido desactivada.

La versión de Microsoft SQL Server 2019 hace recomendaciones automáticas para llevar a cabo la configuración del servidor MAXDOP durante el proceso de instalación.

Ejemplo:

Se tiene instalado Microsoft SQL Server 2016 en un servidor que cuenta con 2 procesadores de 16 núcleos, por lo que se recomienda que se modifique el valor de maxdop, según las recomendaciones de Microsoft, podemos cambiar el valor hasta 16, por lo que de acuerdo con la recomendación procederemos a ello.

  

Para llevar a cabo la modificación se puede utilizar la siguiente secuencia de comandos, recuerde que se trata de una opción de configuración avanzada.

 

-- Asegurar que se cuente con la visibilidad de la opción de configuración

sp_configure 'Show Advanced Options', 1;

reconfigure;

go

sp_configure 'max degree of parallelism', 16;

reconfigure with override;

go

-- Restablecer al valor de visibilidad de las opciones avanzadas

sp_configure 'show advanced options', 0;

reconfigure;

 

Esta opción avanzada no requiere que se reinicien los servicios de Microsoft SQL Server, por lo que se puede hacer uso de este nuevo valor de forma inmediata.

Conclusión

Es importante mencionar que la modificación del valor de esta opción de configuración debe ser llevada a cabo después de llevar a cabo un análisis de desempeño de las consultas, lo mas recomendable es utilizar un servidor de desarrollo y llevar a cabo pruebas de desempeño antes de hacerlo en un ambiente de producción.