Comparación de planes de ejecución

Una de las cosas que leí últimamente fue el comparador de planes que ahora esta incluido en SQL Server 2016 Managment Studio (el cual por cierto ya no esta incluido en el SQL Server y puede ser bajado de manera independiente).

Si desean saber esto más específicamente por favor lean el articulo de Tiger Team. Si alguna vez han tenido la necesidad de mejorar un query, normalmente es necesario tener algo que se llama una linea base, esto pude ser un query que cambia en el tiempo, que recibe distintos parámetros y su comportamiento cambia radicalmente o que cambio porque se migro de versión o se esta cambiando a un nuevo servidor.

Una de las formas de ver esto es por medio de tomar un traza de profiler o de extended events, otra era la de obtener los planes de ejecución pero el comparar queries grandes era algo tedioso o rápido. Para esto ahora Managment studio cuenta con un comparador de planes.

SELECT * FROM 
stackoverflow.stackoverflow.Users u
LEFT JOIN StackOverflow.stackoverflow.Badges b
ON u.id = b.UserId
LEFT JOIN StackOverflow.stackoverflow.Votes v
ON u.id = v.UserId
WHERE u.Location LIKE 'Me%' AND YEAR(b.date) = 2015 

Tomando como ejemplo este codigo y basandome en la base de stackoverflow y usando las estadisticas de IO y TIME obtenemos

(56002 row(s) affected)
Table 'Users'. Scan count 3, logical reads 53292, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Badges'. Scan count 3, logical reads 80421, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Votes'. Scan count 3, logical reads 310221, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4578 ms,  elapsed time = 9478 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Esto nos da un tiempo de casi 10 segundos total con un tiempo de cpu de 5 segundos, bastante bueno (normalmente el tiempo de CPU debe de estar aproximado al doble del tiempo del CPU sino tenemos waits altos).

Ahora haremos un pequeño cambio

SELECT * FROM 
stackoverflow.stackoverflow.Users u
LEFT JOIN StackOverflow.stackoverflow.Badges b
ON u.id = b.UserId
LEFT JOIN StackOverflow.stackoverflow.Votes v
ON u.id = v.UserId
WHERE u.Location LIKE 'Me%' AND YEAR(b.date) = 2015
OPTION (MERGE JOIN)

El único cambio aparente fue que estoy forzando a que elija hacer un merge, podríamos obtener un plan similar si tuvieras malas estadísticas, mientras escribo esto ya llega 5 minutos y no termina... 26 minutos después, creo que lo pude haber hecho serializable y hubiera bastado

(56002 row(s) affected)
Table 'Badges'. Scan count 3, logical reads 79425, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Votes'. Scan count 3, logical reads 310461, physical reads 429, read-ahead reads 3193, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 3, logical reads 52748, physical reads 404, read-ahead reads 51717, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 492, logical reads 39341, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 122955 ms,  elapsed time = 1615231 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Ahora ya tenemos ambos planes de ejecución, para poder hacer uso de la herramienta tenemos que salvarlos como .sqlplan, luego abrirlo con el SSMS y una vez abierto dar clic derecho en cualquier parte del plan y elegir "compare plan" y seleccionar el otro, cualquier punto en verde serán cuestiones idénticas, mientras que aquellas que estén mal se encontraran en color rojizo, esto se puede usar hasta con el plan estimado vs el plan actual para descubrir que puede estar mal.

Mas información:

Comparacion Tool released with the latest SSMS