post icon

Tutorial básico para interpretar el plan de ejecución de SQL Server

Para realizar SQL Tunning a nuestras bases de datos, en la mayoría de los casos necesitaremos paciencia y tiempo para analizar con detenimiento como está funcionando todo, así en base a eso, determinar medidas de acción para la optimización.

Para ver el plan de ejecución de un query es muy sencillo en SSMS, podemos ver el Estimado y el Real:

Plan de Ejecución Estimado: Desde el Menú Consultas -> Mostrar Plan de Ejecución Estimado o con la combinación de teclas Ctrl + L. Esto no ejecutará la query sino más bien lo analizará y mostrará una aproximación del costo de su ejecución.

Plan de Ejecución Real: Desde el Menú Consultas -> Incluir Plan de Ejecución Real o con la combinación de teclas Ctrl + M. Con esta opción podremos ver el costo real de una query, pero a diferencia del anterior, se muestra al terminar la ejecución de la misma.

También otra opción es ejecutar esto, que nos mostrará en modo texto el plan de ejecución.

SET SHOWPLAN_ALL { ON | OFF }

Veamos las distintas opciones que pueden aparecer y en que situaciones deberían hacerlo.

Table Scan

Esto indica que el motor necesita leer completamente la tabla sin utilizar un índice. En la mayoría de los casos su aparición quiere decir que estamos haciendo mal las cosas, y necesitamos urgente crear un índice o reestructurarlo si ya existe alguno. Aunque no siempre es así, el motor siempre intenta predecir los costos de ejecución basados en las estadísticas que va almacenando, si el estima que va ser más rápido leer toda la tabla en vez de leer un índice, usará ese método. Esto suele suceder con tablas poco pobladas y la query en cuestión no conlleva ningún filtro, ya que reduce considerablemente el overhead. Ejemplo:

SELECT * FROM tabla;

Clustered Index Scan

Es semejante al Table Scan, ya que recorre completamente la tabla pero utilizando ésta vez alguno de los índices clustered del que dispone. Aparece normalmente en tablas que están pobladas considerablemente. Ejemplo:

SELECT * FROM tabla;

Clustered Index Seek

Ver esto es síntoma de un correcto uso de los índices clustered en la base de datos, para verlo en acción simplemente tenemos que trabajar con los campos que tiene índices, Ejemplo:

SELECT * FROM tabla WHERE columna = 44;

Index Scan

Este es muy parecido al Table Scan y Clustered Index Scan, la principal diferencia radica en que éste utiliza un índice Non-Clustered para recorrer la tabla. Justamente para esta sección y las que vienen más abajo hace unos días escribí un artículo que les ayudará a diferenciar entre un Indice Agrupado y uno No-Agrupado. Aunque muchas veces suele ser síntoma de un mal uso de los índices, también aparece cuando usamos las cláusulas ORDER BY, JOIN o GROUP BY. Ejemplo

SELECT columnaNonClustered FROM tabla ORDER BY columnaNonClustered desc

Index Seek

Ya va siendo obvio que hace el index seek, trabaja igual que el Clustered Index Seek, pero trabajará con un Indice Non-Clustered. Si Ejemplo:

SELECT columnaNonClustered FROM tabla WHERE columnaNonClustered = 44;

Bookmark Lookup

Con lo ya visto en el articulo anterior comprenderemos mejor como funciona el Bookmark Lookup. Debemos comprender que no podremos evitar tener este tipo de operación en la BD, lo que si se debe buscar es que sea mínima su aparición y cuando lo hace, lo haga con un costo bajo.

Este aparece cuando se requiere hacer un salto del apuntador del índice non-clustered a la páginas de datos real. Una de las maneras para evitar su aparición excesiva es limitar los campos requeridos en la query, sólo solicitar los que están incluidos en el índice, ésta es la principal razón por la que habrán escuchado más de una vez, “no escribas querys SELECT * FROM..“.

En algunos casos extremos (repito, muy extremos) se podría considerar la inclusión de todas las columnas de la tabla dentro del índice.

Una de las mejoras que se introdujo a partir de SQL Server 2005, es que en los índices Non-Clustered se pueden agregar o copiar el contenido de las columnas, sin que estas sean parte del índice en sí, es decir, que no servirán para realizar búsquedas ni filtros pero si cuando los necesite mostrar tendrá “una copia a mano” de los datos, sin tener la necesidad de usar el puntero para buscar en la tabla el dato. Esta característica  es un arma de doble filo, si bien es cierto que si se lo usa bien podría reducir considerablemente el costo de las consultas, pero su lado contraproducente, es que al tener más datos los índices, estos crecen rápidamente porque ocupan más espacio y por ende se tienen menos claves por cada página de índice lo que podría llegar a aumentar el nivel de I/O. Así que, deben considerar el contexto para utilizarlo.

RID Lookup

Este operador no es muy frecuente de ver, y normalmente aparece cuando el motor intenta optimizar por su cuenta la query y no tenemos un índice agrupado (Clustered) entonces buscará a través del índice único ROW ID (de ahí el nombre RID). Si requiere solución este tipo de operaciones es muy dependiente de caso. Ejemplo

SELECT columna1, columna2 FROM tabla WHERE columna 1 < 100000;

Sort

Esta operación veremos cuando el motor requiere ordenar algún campo que no está indizado, por ejemplo cuando aplicamos la cláusula ORDER BY, GROUP BY, TOP, etc. Normalmente cuando aparece debemos echar ojo, para saber si falta algún índice en alguna tabla, pero no siempre es así, llenar de índices la BD no es sano tampoco, y existen ocasiones que debemos ejecutar querys poco frecuentes que no ameritan la creación de otro índice más.

SELECT * FROM tabla ORDER BY columna;

JOIN

El operador JOIN se divide en tres tipos de JOIN nuevamente. Entran en acción cuando justamente se hacen uso de las cláusulas JOIN para unir dos o más tablas en la query y está determinado normalmente por el volumen de datos con el que se trabajará para que SQL Server elija usar uno u otro.

   Nested Loop Join

Normalmente vemos este operador cuando el volumen de datos con el que se trabajará es relativamente pequeño. Ejemplo.

SELECT * FROM tabla1 t1 INNER JOIN tabla2 t2 ON  t1.columna = t2.columna WHERE t2.columna = 77; 

   Merge Join

Este operador al igual que el anterior se hace su aparición en la unión de tablas, pero cuando el volumen de datos es considerablemente más grande, hablamos de cientos de miles comunmente

SELECT * FROM tabla1 t1 INNER JOIN tabla2 t2 ON  t1.columna = t2.columna;

   Hash Join

Este tipo de JOIN es muy especifico para grandes volúmenes de datos, especialmente si no están indizados. Si nos aparece en nuestra BD OLTP, deberíamos preocuparnos ya que está pensado en funcionar mejor en sobre los diseños OLAP, así que si no están trabajando con algún DATA WAREHOUSING, es muy poco probable que les sea útil. El Hash Join es todo un mundo a parte, y merece un artículo dedicado netamente a él, así que extenderemos el tema más adelante.

Hash Match

Cuando ven este operador es porque el motor está compando contenido, puede aparecer en un JOIN, WHERE y son lugares donde no deberían estar, lo hacen por falta de indices principalmente. Donde si son muy útiles es cuando incluimos la cláusula DISTINCT, UNION, UNION ALL, en donde no solo se compara el valor de un campo, sino de todo un conjunto de columnas o incluso filas y columnas.

Stream Aggregate

Aparece principalmente cuando agrupamos los datos, y mezclamos con funciones agregadas como MIN, SUM, AVG, también con la cláusula HAVING. También es frecuente ver que este operador lleva acompañado a SORT, a quien utiliza para ordenar primeramente los datos antes de agrupar.

Para complementar y profundizar este sencillo articulo, existen muchos libros que pueden leer. La mayor parte de la buena información se encuentra en Ingles pero vale la pena leerlo. Les dejo uno de los libros gratuitos que toca el tema, escrito por Red Gate.

Comentarios desde Facebook:

  1. avatar
    Sebastian Mozilla Firefox Windows
    25 septiembre 2013 at 09:06 #

    Muy buena info..Gracias!!

  2. avatar
    Maritus PARAGUAY Mozilla Firefox Windows
    23 julio 2012 at 09:31 #

    Muchas gracias por el tutorial,me viene de maravilla,ya que estoy comenzando con esto de SQL SERVER..

Trackbacks/Pingbacks

  1. Rendimiento en SQL SERVER | devblog dooflow SPAIN WordPress - 27 marzo 2015

    […] http://devtroce.com/2011/08/04/tutorial-basico-para-interpretar-el-plan-de-ejecucion-de-sql-server/ […]

  2. Clausula IN vs BETWEEN AND [SQL Tunning] | DevTroce.com UNITED STATES WordPress - 26 julio 2012

    […] Para comprender éste articulo recomiendo que haga una lectura introductoria sobre la interpretación de los planes de ejecución en SQL Server. Lo que demostraremos será la ventaja en rendimiento del BETWEEN AND sobre la […]

  3. Como optimizar las querys con cláusula LIKE en SQL [SQL-Tunning] | DevTroce.com UNITED STATES WordPress - 5 agosto 2011

    […] Si bien existen varios argumentos que podemos agregar al LIKE, el más común y pesado es el comodín (%). Este “problema“, si se lo puede llamar así ocurren en SQL Server, Oracle, Postgre y MySQL (probablemente en otros motores también, pero no me constan) así como la solución que veremos aplica a todos ellos. Si requieren comprender el plan de ejecución pueden leerlo aquí. […]

Responder