martes, 12 de febrero de 2013

SQL Server: Hint MAXDOP

Antes de entrar en detalles sobre el hint MAXDOP explicaremos en qué consisten los hints (traducido literalmente "sugerencia", "insinuación"), son agregados a consultas SQL de tipo SELECT, INSERT, DELETE, UPDATE y MERGE que sustituyen cualquier plan de ejecución que pueda seleccionar el optimizador de consultas para la consulta que lo incluye. Partiendo de la base que el optimizador de consultas normalmente debería seleccionar el mejor plan de ejecución se debe ir con cuidado con el uso de los hints.

Podemos encontrar tres tipos de hints en SQL Server:

  • Join Hints, fuerzan el tipo de Join (LOOP, HASH, MERGE, REMOTE) que se va a usar en la consulta, por tanto, sólo aplica en consultas de tipo SELECT, DELETE y UPDATE. Podemos encontrar más información al respecto en el siguiente enlace del MSDN: Join Hints.

  • Query Hints, se aplican en consultas de tipo SELECT, INSERT (sólo cuando contiene una cláusula SELECT), DELETE, UPDATE y MERGE y pueden afectar a todas las partes de la consulta dependiendo del hint, por ejemplo:

    • HASH y ORDER afectan a las cláusulas GROUP BY o DISTINCT.
    • MERGE, HASH y CONCAT afectan a la cláusula UNION.
    • ...
    Para añadirlos utilizamos la cláusula OPTION, el hint MAXDOP es de este tipo (luego entraremos en más detalles sobre su uso). Podemos encontrar más información al respecto en el siguiente enlace del MSDN: Query Hints.

  • Table Hints (también conocidos como Lock Hints), se aplican en consultas de tipo SELECT, INSERT (sólo cuando contiene una cláusula SELECT), DELETE, UPDATE y MERGE, invalidan el comportamiento predeterminado del optimizador de consultas mientras dura la instrucción DML (Data Manipulation Language) especificando un tipo de bloqueo, uno o más índices,... Estos hints se especifican en la cláusula FROM de la instrucción mediante la cláusula WITH y sólo afectan a la tabla o vista a la que se hace referencia en esa cláusula. Podemos encontrar más información al respecto en el siguiente enlace del MSDN: Table Hints.

Bien, tras la breve introducción anterior sobre qué son los hints vamos a entrar en detalles del hint MAXDOP, este hint invalida el valor del parámetro de configuración max degree of parallelism (máximo grado de paralelismo); si MAXDOP se establece a cero el servidor elige el grado máximo de paralelismo. Es decir, con el hint MAXDOP podemos configurar la consulta para que se ejecute en un nº concreto de CPUs (pero no podemos indicar cuáles). Veamos un ejemplo de su funcionamiento, supongamos la siguiente consulta en la base de datos AdventureWorks:
-- Obtenemos los contactos ordenados por apellido, nombre (sin MAXDOP)
SELECT *
FROM Person.Contact
ORDER BY LastName, FirstName
Obtenemos el siguiente plan de ejecución con el correspondiente coste de paralelismo (lo que cuesta hacer que la consulta se ejecute en varias CPUs, un 3% del total de la consulta).


Ahora ejecutamos la siguiente consulta con el hint MAXDOP = 1 para la consulta se ejecute en 1 única CPU.
-- Obtenemos los contactos ordenados por apellido, nombre (MAXDOP = 1)
SELECT *
FROM Person.Contact
ORDER BY LastName, FirstName
OPTION (MAXDOP 1)
Tras añadir el hint MAXDOP = 1 si observamos el plan de ejecución vemos que el coste de paralelismo ha desaparecido (al ejecutarse la consulta en una única CPU).


Entonces, si desaparece el coste de paralelismo ¿significa esto que la consulta es menos costosa si incluimos la cláusula correspondiente al hint MAXDOP? Ejecutemos las 2 consultas a la vez:
-- Obtenemos los contactos ordenados por apellido, nombre (sin MAXDOP)
SELECT *
FROM Person.Contact
ORDER BY LastName, FirstName

-- Obtenemos los contactos ordenados por apellido, nombre (MAXDOP = 1)
SELECT *
FROM Person.Contact
ORDER BY LastName, FirstName
OPTION (MAXDOP 1)
Y analicemos el plan de ejecución de nuevo, en este caso podemos ver que la consulta sin el hint MAXDOP, a pesar de tener el coste de paralelismo es más rápida que la consulta con el hint MAXDOP (34% del total del tiempo de ejecutar ambas frente a 66%), es decir, aunque ejecutar la consulta en N CPUs tenga un coste de paralelismo esta opción es más rápida que ejecutar la consulta en 1 CPU evitando el coste de paralelismo; cuidado, esto es para el ejemplo anterior, con otras consultas y/o servidor la cosa podría cambiar, no siempre es mejor o peor ejecutar una consulta con paralelismo, dependerá de cada caso.


Vayamos a un caso práctico, ¿para qué nos puede valer el hint MAXDOP? Imaginemos un servidor con una instancia de SQL Server que contiene 2 bases de datos A y B, sobre la base de datos A hay múltiples usuarios trabajando y sobre la B se están haciendo únicamente labores de mantenimiento, por ejemplo, regenerando los índices; pues bien, podemos hacer que las consultas de regeneración de índices de la base de datos B incluyan el hint MAXDOP = 1 (uso de una única CPU) para que éstas no utilicen todas las CPUs disponibles y así se vea afectado lo menos posible el rendimiento de los usuarios conectados a la base de datos A. ¿Se os ocurren otros casos? Seguro que sí, podéis añadir los comentarios que creáis oportunos al respecto.

Y hasta aquí el artículo de hoy, espero que os haya sido interesante. Recordad también que dentro de areaTIC podéis encontrar otros artículos, no dudéis en consultar nuestro archivo, también podéis seguirnos por RSS o las principales redes sociales (twitter, facebook, linkedin...) 


LECTURAS RELACIONADAS RECOMENDADAS POR AREATIC.NET

No hay comentarios:

Publicar un comentario