Buenas a tod@s , hoy empiezo una nueva serie de Posts denominada “SQL Server y los enemigos del paralelismo” . Esta serie sale de la charla de paralelismo que tuve el placer de impartir en este ultimo SolidQ Summit 2012 :).

Paralelismo, planes paralelos y operadores

Cuando ejecutamos una consulta contra SQL Server el optimizador de consultas empieza a generar planes de ejecución para escoger de todos ellos el “mas barato”. Estos planes pueden ser planes en serie o planes paralelos. Dependiendo del coste de los planes de ejecución y del umbral cost threshold for parallelism (que por definición viene con el valor 5) el optimizador optará por un tipo de plan u otro. El algoritmo que se utiliza para decidir que plan utilizar es el siguiente:

El optimizador optará por un plan en serie siempre y cuando el coste del mismo no supere el valor del umbral cost threshold for parallelism. Si lo supera el optimizador escogerá el plan con mínimo coste entre el mejor de los planes paralelos y el mejor de los planes en serie.

Cuando SQL Server escoja un plan paralelo veremos en los planes de ejecución una imagen representativa indicando que los operadores trabajan en paralelo y además veremos operadores propios del paralelismo denominados operadores exchange. Existen tres operadores exchange:

• Distribute streams: Distribuye las filas en diversos hilos para pasar a trabajar en modo paralelo.
• Gather Streams: Agrupa las filas de distintos hilos que vienen de operaciones paralelas.
• Repartition Streams: Redistribuye las filas en diversos hilos acorde a una lógica.

Además de estos operadores exchange, hemos dicho que el resto de operadores también pueden trabajar en paralelo o no. Para distinguir si un operador trabaja en paralelo o en serie podemos fijarnos en su icono. Si posee el icono con las flechas es que trabaja en paralelo y si no es que trabaja en serie.

Por ejemplo el operador Sort que realiza ordenaciones, puede ejecutarse en serie o en paralelo. Aquí tenemos un ejemplo de los iconos del operador:

Una vez dicho esto vamos a clasificar los planes en relación con el paralelismo como:

• Planes óptimos: 100% del plan se ejecuta en paralelo
• Planes subóptimos: hay partes del plan que se ejecutan en serie.
• Planes en serie: No se ejecutan en paralelo.

El operador TOP enemigo del paralelismo

En este apartado vamos a ver una pequeña demostración de como el operador TOP hace que un plan que se puede ejecutar completamente en paralelo pase a tener partes que se ejecutan en serie. Para ello vamos a trabajar con un par de tablas:

• Customer: 200 filas
• Sales_Small: 1.000.000 de filas

Vamos a lanzar la siguiente consulta:

declare @cmp varchar(100)
select @cmp=c.Companyname
from Customer as c
inner join Sales_small as s
    on c.CustomerID=s.CustomerID
where s.Amount<10
go

El plan de ejecución que genera esta consulta es el siguiente:

Como podemos observar el plan de ejecución que se genera es un plan paralelo a excepción del primer operador que realiza un Clustered Index Scan de la tabla Customers que solamente tenia 200 filas. El Optimizador decide no hacer el scan en paralelo por el volumen de datos tan bajo que tiene la tabla customers. De modo que consideramos que se trata de un plan óptimo en cuanto a paralelismo.

En cambio si modificamos la consulta anterior por la siguiente:

declare @cmp varchar(100)
select @cmp=c.Companyname
from Customer as c
inner join
(
    select top 1000000 * from Sales_small 
) as s
    on c.CustomerID=s.CustomerID
where Amount<10
go

Tenemos una consulta que está realizando exactamente el mismo cruce entre tablas con el mismo número de filas. Esta vez si vemos el plan de ejecución tenemos:

Ahora nos ha generado un plan de ejecución completamente en serie deshabilitando el paralelismo que teníamos con la anterior consulta, y lo único que hemos hecho ha sido añadir una subquery con un TOP de 1.000.000 de filas (todas las que tiene la tabla customer). Como podemos comprobar en este ejemplo, el operador TOP está haciendo que SQL Server no utilice el plan paralelo anterior.

Otra alternativa podría ser utilizar esta consulta donde añadimos el filtro dentro de la subquery:

declare @cmp varchar(100)
select @cmp=c.Companyname
from Customer as c
inner join
(
    select top 1000000 * from Sales_small where Amount<10
) as s
    on c.CustomerID=s.CustomerID
go

Al ejecutar esta consulta el plan que se obtiene es el siguiente:

Como podemos observar en esta imagen vemos que tenemos un plan subóptimo en términos de paralelismo puesto que el uso del operador TOP  está forzando a que el plan se serialice mediante el operador gather streams, se ejecute el TOP y posteriormente se paralelice de nuevo mediante el operador Distribute Streams. Hemos mejorado la consulta anterior que nos dejaba un plan completamente en serie pero todavía no hemos conseguido el plan óptimo en paralelo, todo esto ha sido debido a la introducción del operador TOP en la consulta.

Por último, para demostrar que efectivamente el responsable de estos planes es el operador TOP y no el hecho de añadir una subconsulta, vamos a ejecutar la siguiente consulta:

declare @cmp varchar(100)
select @cmp=c.Companyname
from Customer as c
inner join
(
    select * from Sales_small 
) as s
    on c.CustomerID=s.CustomerID
where Amount<10
go

En esta consulta hemos eliminado el TOP que teníamos antes, y si ahora vemos el plan de ejecución tenemos:

Ahora ya volvemos a tener el mismo plan de ejecución que teníamos al principio y lo único que hemos hecho ha sido eliminar el operador TOP de la consulta.

Conclusiones

Cuando tenemos consultas que sabemos que pueden ser pesadas y que pueden beneficiarse del uso del paralelismo, como pueden ser operaciones de Ordenación de grandes volúmenes de datos, filtrados de grandes volúmenes o agrupaciones, tenemos que tener en cuenta que operadores como el TOP que acabamos de ver pueden hacer que nuestros planes de ejecución no sean los más óptimos. Así que recordad que el operador TOP es uno de los operadores que no se lleva bien con el paralelismo de SQL Server y que es…. un enemigo del paralelismo!  🙂