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
Grimpi IT Blog 