Grimpi IT Blog

octubre 28, 2008

Entender el Plan de Ejecución en SQL Server 2005/2008

Filed under: Engine, Plan de ejecución, SQL Server — Etiquetas: , , — grimpi @ 11:51 pm

Cada vez que se ejecuta una consulta en un motor de bases de datos, internamente se ejecutan una serie de operaciones, que varían según la consulta, los datos y obviamente, el motor de base de datos. El conjunto de pasos que tiene que realizar el motor para ejecutar la consulta, se llama Plan de Ejecución. Hoy vamos a explicar cómo entender el plan de Ejecución de SQL Server 2005/2008. Que operaciones podemos encontrar en el plan de ejecución?

Table Scan:
Significa que el motor tiene que leer toda la tabla. Esto solo puede suceder cuando la tabla es Heap (o sea, no tiene un índice clustered). En algunos casos, cuando es una tabla chica, un Table Scan es la mejor opción, ya que produce poco overhead. De hecho la tabla puede tener índices y sin embargo el SQL elige usar un table scan porque sería más rápido. Pero cuando la tabla es más grande, no debería haber Table Scan, ya que es muy costoso. Para solucionar este problema, hay ver si la tabla tiene índices y si se están usando correctamente. Lo importante es prestarle atención cuando vemos un table Scan. Muchas veces, nuestro problemas de performance pasan por ahí.

Ejemplo:
(Creamos una tabla sin ningún tipo de índice y se le hace una consulta)

CREATE TABLE [TablaPrueba1]
(
Campo1 int IDENTITY (1, 1) NOT NULL ,
Campo2 int,
Campo3 int,
Campo4 int,
Campo5 char (30)
)

SELECT * FROM TablaPrueba1

Clustered Index Scan:
Esta operación es muy similar a un table scan. El motor recorre toda la tabla. La diferencia entre uno y otro, es que el Clustered Index Scan se realiza en una tabla que tiene un índice Clustered y el Table Scan en una tabla que no tiene este tipo de indice.

Otra vez tenemos que evaluar si esta opción es la que realmente queremos. Muchas veces, por un mal uso de los índices, se ejecuta esta operación, cuando en realidad queríamos otra más eficiente.
Ejemplo de un Clustered Index Scan:

CREATE TABLE [TablaPrueba3]
(
Campo1 int IDENTITY (1, 1) NOT NULL,
Campo2 int,
Campo3 int,
Campo4 int,
Campo5 char (30)
CONSTRAINT [PK_Campo3] PRIMARY KEY CLUSTERED
(
[Campo1]
))

SELECT * FROM TablaPrueba3

Clustered Index Seek:
Si vemos esta operación, en general, podemos estar contentos. Significa que el motor está usando efectivamente el índice Clustered de la tabla.
Ejemplo de esta operación:

(Usamos la tabla creada en el ejemplo anterior con un índice Clustered, le insertamos 10000 registros para que el motor prefiriera usar el índice antes que un scan y filtramos por el índice).

SET NOCOUNT ON
DECLARE @Top int
SET @Top = 0
WHILE @Top <> 10000
BEGIN
INSERT INTO
TablaPrueba3 VALUES (convert(int,rand()*20000),convert(int,rand()*20000),convert(int,rand()*20000), ‘P’)
SET @Top = @Top+1
END

SELECT * FROM TablaPrueba3 WHERE Campo1 = 2

Index Seek:
Aquí también si vemos esta operación, podemos estar contentos. Es similar que el Clustered Index Seek, pero con la diferencia de que se usa un indice Non Clustered.

(Creamos un índice Non Clustered sobre la tabla del ejemplo anterior)

CREATE INDEX [IDX_Campo3] ON [dbo].[TablaPrueba3](Campo2,Campo3) ON [PRIMARY]

SELECT Campo2 FROM TablaPrueba3 WHERE Campo2 = 2 and Campo3 = 2

Index Scan:
Esta operación se ejecuta cuando se lee el índice completo de una tabla. Es preferible a un Table Scan, ya que obviamente leer un indice es mas chico que una tabla. Esta operación puede ser síntoma de un mal uso del índice, aunque también puede ser que el motor haya seleccionado que esta es la mejor operación. Es muy común un Index Scan en un join o en un ORDER BY o GROUP BY.

Usemos la tabla TablaPrueba3, creada en el ejemplo anterior:

(Como no hay ningún filtro, el motor debe leer toda la tabla. Sin embargo, al traer solo el Campo2, que pertenece a un índice Non Clustered, en vez de hacer un Table Scan, es mas optimo hacer un Index Scan).


SELECT Campo2 FROM TablaPrueba3

Bookmark Lookup:
Esta es una operación muy importante, donde hay algunas diferencias entre 2000 y 2005 que vale la pena saber. El Bookmark Lookup indica que SQL Server necesita ejecutar un salto del puntero desde la página de índice a la página de datos de la tabla para recuperar los datos. Esto sucede siempre que tenemos un índice Non Clustered. Para evitar esta operación, hay que limitar los campos que queremos traer en la consulta.
Si el campo que vamos a extraer, esta fuera del índice, entonces se va a ejecutar esta operación y no queda otra opción (para SQL Server 2000). Acá reside la importancia de evitar los SELECT * FROM …

Veamos el siguiente ejemplo usando nuevamente la tabla TablaPrueba3, pero con la siguiente consulta, tanto para SQL Server 2000 y SQL Server 2005:

Ejemplo: (Se trae todos los campos de la tabla, filtrando por un campo perteneciente a un índice non clustered).

SELECT Campo2, Campo3, Campo4 FROM TablaPrueba3 WHERE Campo2 = 2

SQL Server 2000:

SQL Server 2005:

Si vemos el plan de ejecución (de SQL Server 2000), se realizó la operación Index Seek, pero además, aparece la operación Bookmark Lookup. Esto pasa porque en este ejemplo además de traer el campo2 y campo3 que son parte del índice, debe leer el campo5, que solo está en la página de datos y no en el índice. Ademas, como podemos ver, la misma consulta, para exactamente la misma tabla con los mismos datos e índices, pareciera generar un plan de ejecución diferente en SQL Server 2000 y SQL Server 2005.
Pero sin embargo, no es así. Dado que dentro de la estructura interna de un índice non clustered, se almacena un puntero al índice clustered, el boorkmark lookup internamente se traduce como un salto a la lectura del índice clustered de la tabla. Para entender mejor este punto, recomiendo leer este post que escribí hace un tiempo. En SQL Server 2000 toda esta operación es encapsulada en un solo icono al mostrar graficamente el plan, mientras que en SQL Server 2005, el plan de ejecución está más detallado.
Pero la real diferencia entre ambas versiones (2000 y 2005) no es una simple cuestión estética. Una de las más interesantes nuevas características de SQL Server 2005, es la posibilidad de incorporar en la pagina final del índice (donde residen los valores), campos de la tabla que son externos al índice.
Que significa que “externo al índice”?
Significa que el campo no es parte de la estructura del índice, que no va a ser utilizado por el motor a la hora de filtrar y buscar, pero sin embargo, su contenido está copiado a la estructura de la última página del índice. La ventaja de esto, es que le ahorra al motor del SQL, hacer el boorkmark lookup, operación bastante costosa. La desventaja, es que al hacer más grande el índice, entran menos registros por página, lo cual podría llevar a que se tengan que hacer mas operaciones de I/O. Por lo tanto, es necesario hacer una evaluación de costo/beneficio, antes de incluir campos adicionales al índice.


Joins:
Un join es la relación entre 2 tablas. SQL tiene tres tipos de joins. Neested Loop Join, Merge Join y Hash Join. Dependiendo de las características de la consulta y de la cantidad de registros, el motor puede decidir uno u otro.
Ninguno es peor o mejor “per se”. Todo depende de las características de la consulta y del volumen de datos.

  • Neested Loop Join: Suele ser generalmente el más frecuente. Es también el algoritmo más simple de todo. Este operador fisico es usado por el motor cuando tenemos un join entre 2 tablas y la cantidad de registros es relativamente baja. Tambien aplica con cierto tipo de joins (cross joins por ejemplo).
  • Merge Join: Otro de los tipos de join que existen. Generalmente se usa cuando las cantidades de registros a comparar son relativamente grandes y están ordenadas. Aun si no están ordenadas, el motor puede predecir que es más rápido ordenar la tabla y hacer el merge join que hacer un Neested Loop Join. En muchas situaciones es frecuente ver que una consulta anteriormente usaba Neested Loop Join y en algún momento paso a usar un Merge Join. La razón de esto, es porque el volumen de datos aumento y por lo tanto, es mas optimo usar un Merge join.
  • Hash Join: Otro tipo más de join que existe. Mientras que los Loop Joins trabajan bien para conjuntos chicos de datos y los merge join para conjuntos moderados de datos, el hash join es especialmente útil en grandes conjuntos de datos, generalmente en datawarehouses. Este operador es mucho mas paralelizable y escalable. También se usa generalmente cuando las tablas relacionadas no tienen índice en ninguna de los campos a comparar. Hay que prestar atención si vemos este tipo de operaciones, ya que puede significar un mal uso de los índices. Sin embargo, los hash joins consumen mucha memoria y SQL Server tiene un límite en la cantidad de operaciones de este tipo que puede efectuar simultáneamente. Existen varios subtipos de hash joins. El que quiere ver en detalle esto, en este link hay una excelente explicación (http://blogs.msdn.com/craigfr/archive/2006/08/10/687630.aspx).

Ejemplo:
(Se va a ejecutar exactamente la misma consulta con una tabla con 50 registros y con 2000 registros, para ver cómo cambia en función del volumen de datos, el tipo de operación)

SELECT T1.* FROM tablaprueba3 T1 INNER JOIN TablaPrueba3 T2 ON T2.Campo4 = T1.Campo1

Consulta con 50 registros en la tabla

Consulta con 20000 registros en la tabla

Agregaciones:
Las agregaciones refieren a agrupar un conjunto grande de datos en un conjunto de datos más chico.

  • Stream Aggregate: Este tipo de operaciones ocurre cuando hay se llama a un función de agregación, como MIN, COUNT, MAX, SUM, etc. El operador Stream Aggregate requiere que la información esté ordenada por las columnas dentro de sus grupos. Primero, el optimizador ordenará si los datos no están ordenados por un operador Sort anterior. En cierta manera, el Stream Aggregate es similar al Merge Join, en cuanto a en que situaciones se produce.
  • Hash Match (Aggregate): Hay que tener cuidado cuando vemos este operador. Esta operación también ocurre cuando se llama a funciones de agregación del tipo MIN, COUNT, AVG, etc. Así como el Stream Aggregate es comparable al Merge Join, el Hash Match Aggregate es similar al Hash Join. Lo que hace internamente es armar una tabla de hash. En situaciones donde la cantidad de registros es elevada o no se están indexadas las columnas por las cuales agrupa la consulta, el motor del SQL va a elegir esta operación.

Ejemplo:

SELECT MAX(Campo2) FROM TablaPrueba3 GROUP BY Campo2

SELECT MAX(Campo4) FROM TablaPrueba3 GROUP BY Campo4

Como podemos observar en este ejemplo, las 2 consultas son prácticamente similares en estructura, solo que el primer caso agrupa el campo2 que esta indexado y en el segundo caso, agrupa el campo4, que no está indexado y por eso usa el operador Hash Match.

Sort:
Otro punto para observar, es cuando vemos un sort. Como el nombre lo indica, esta operación ordena. Ahora, el Sort solo se hace cuando el campo o los campos que se desean ordenar, no están indexados. A veces esta operación se ejecuta sola, sin que nosotros hayamos puesto en la consulta el ORDER BY, porque el motor necesita ordenar los datos por alguna razón, por ejemplo, para ejecutar un Merge Join. Pero recordemos que si ordenamos por un campo indexado y este indice esta siendo utilizado, no se ejecuta esta operación.

Ejemplo de esta operación:

SELECT * FROM TablaPrueba3 ORDER BY Campo3

About these ads

36 comentarios »

  1. Que buen artículo, me ha servido.

    Comentario por Cristian. — noviembre 10, 2008 @ 4:31 pm

  2. Muy interesante me ha servido para tener un mejor panorama ya que no conozco casi nada al respecto.
    Gracias

    Comentario por luis — noviembre 11, 2008 @ 8:54 pm

  3. Muy bueno! Para un conocimiento más profundo del análisis del execution plan, existe un e-book gratuito q se puede descargar en el siguiente link:

    http://siteadvisor.hk/sites/red-gate.com/downloads/15013151/

    Saludos!

    Comentario por Javier — noviembre 12, 2008 @ 5:50 pm

  4. Gracias por el dato.
    Corrijo el link que estaba incorrecto:

    http://downloads.red-gate.com/ebooks/HighPerformanceSQL_ebook.zip

    Saludos!

    Comentario por Esteban — noviembre 12, 2008 @ 6:54 pm

    • Excelente aporte, muchas gracias

      Comentario por Yerko Zarricueta — diciembre 13, 2010 @ 12:09 pm

  5. Excelente explicacion la verdad has sido muy clro y sobre todo usando ejemplos que a mi parecer hace las cosas mas faciles de comprender

    Gracias :-D

    Comentario por Luis Segura — diciembre 11, 2008 @ 8:24 pm

  6. GRACIAS POR EL ARTICULO,,,,,,,,,,,,,,,EXCELENTE

    Comentario por omar toribio — enero 10, 2009 @ 3:43 pm

  7. muy bueno, pero necesito tambien si pueden enviar como crear indices para que sirven y todo lo relacionado con el mismo.

    Comentario por jose — junio 3, 2009 @ 6:18 pm

  8. Muy claro y facil de entender, muchas gracias, tmb quisiera saber en base al plan de execucion como puedo comparar un query con otro para saber cual se tarda menos,

    Comentario por Ana — noviembre 4, 2009 @ 4:04 pm

  9. Exceltente el post .. se entendio clarisimo … muy buena…

    Comentario por ricardo Garcia — diciembre 29, 2009 @ 2:08 pm

  10. Excelente post, muy claro y conciso.

    Una consulta que tardaba 8 minutos en procesarse ahora al entender el plan de ejecución y aplicar un par de ajustes en un indice se resuelve en 1 segundo.

    La verdad que aportaciones como estas hacen de Internet una verdadera gozada.

    Muchas gracias

    Comentario por Frank González — diciembre 30, 2009 @ 12:14 pm

  11. Me parece de gran aporte la explicacion.
    Entre el link y efectivamente hay un manual al respecto.
    Alguien conoce donde se puede bajar el emanual en español…

    Comentario por Rodrigo Olivares — enero 13, 2010 @ 6:57 pm

  12. Excelente, me ayudó mucho a optimizar mis consultas

    Comentario por manuel — enero 21, 2010 @ 12:18 pm

  13. Muy bueno el articulo… muy util

    Comentario por Pedro Colorado — junio 22, 2010 @ 12:05 am

  14. Perfecto, muy bien explicado, me vino barbaro. Gracias

    Comentario por Waldo — julio 8, 2010 @ 1:43 pm

  15. la verdad espectacular, muy claro!

    Comentario por andy — agosto 2, 2010 @ 2:24 pm

  16. Muy buen post, muchas grácias, no había encontrado nada tan concreto.

    Comentario por Carlos Valencia — septiembre 8, 2010 @ 2:04 pm

  17. !!bárbaro, excelente articulo!!

    Comentario por Ricardo — octubre 22, 2010 @ 4:51 pm

  18. Gracias Grimpi!

    Muy buen artículo, me ha servido mucho.

    Comentario por ada — octubre 28, 2010 @ 7:38 am

  19. Muy buen artículo.
    Una consulta ¿Soy el único que notó y sufre la desaparición de “Manage Indexes” del menú contextual del plan de ejecución?
    Es decir, cuando veo un index seek o index scan, etc. en lugar de poder dar clic derecho para ver qué campos tiene el índice, hay que buscar la tabla en el object explorer, y luego buscar el índice. Me parece una gran pérdida de tiempo. Sobre todo cuando se trabaja en entornos donde el nombre del índice no enumera los campos que indexa.
    Pablo

    Comentario por Pablo — marzo 18, 2011 @ 2:09 pm

  20. Excelente, es una ayuda para los que queremos optimizar nuestras consultas.

    Comentario por malkavian_net — julio 10, 2011 @ 1:45 am

  21. ¡Excelente artículo!

    Comentario por Anónimo — agosto 9, 2011 @ 11:51 am

  22. Muy buen artículo , bastante explícito y claro , felicitaciones y que sigas adelante

    Comentario por kleber G — agosto 19, 2011 @ 9:47 pm

  23. Fantástico articulo, simple, claro y conciso.

    Mucho mejor explicado que en algunos libros que he leido sobre performance.

    Comentario por Santimacnet — agosto 26, 2011 @ 11:16 am

  24. [...] Tutorial: Entender Plan de Ejecución SQL Server [...]

    Pingback por Guia Preparación Examen 70-433 « Santimacnet's Blog — agosto 26, 2011 @ 11:20 am

  25. Muy buen aporte, te felicito. Gracias por compartir el articulo, estaba buscando justo una explicación sencilla y concreta.

    Comentario por Anónimo — septiembre 8, 2011 @ 10:55 pm

  26. Ecselente jeje! Muy buen aporte, gracias. Aunque no estaría mal mejorarlo agregando detallando un poco más en otra sección.

    Comentario por Anónimo — septiembre 14, 2011 @ 1:42 pm

  27. Muy buen articulo. Gracias, era justo lo que necesita entender.

    Comentario por Carmenza — noviembre 1, 2011 @ 3:51 pm

  28. Esta muy bien la informacion que has descrito anteriormente y fue de mucha ayuda para mi.

    Gracias por compartir tu conocimiento con los demas, pues eso es lo que hace que nuestra comunidad sea tan importante y grande.

    Comentario por ringer de la cruz — enero 7, 2012 @ 3:56 pm

  29. eN Lo personal es muy buen blog, gracias por el apoyo que dejas escrito.

    Comentario por Anónimo — septiembre 12, 2012 @ 7:06 pm

  30. Muchas Gracias, articulo super util

    Comentario por Waldo — octubre 11, 2012 @ 2:55 pm

  31. Excelente, seguir creciendo en relacion a sql!!…gracias!

    Comentario por Os — junio 20, 2013 @ 9:08 pm

  32. Me ha servido de mucho!

    Comentario por Crisdlr — febrero 27, 2014 @ 4:51 pm

  33. Excelente Articulo

    Comentario por Anónimo — marzo 26, 2014 @ 4:59 pm


RSS feed para los comentarios de esta entrada. TrackBack URI

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

El tema Shocking Blue Green. Blog de WordPress.com.

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 31 seguidores

%d personas les gusta esto: