Vamos a tratar de explicar cómo interpretar planes de ejecución gráficos básicos, es decir, planes de ejecución para sentencias del tipo SELECT, UPDATE, INSERT Y DELETE, con sólo unos pocos JOINS y si no hay funciones avanzadas o indirectas.
Los planes gráficos están basados en iconos, y el número de iconos a aprender es mínimo. Cada icono representa un operador específico dentro de la ejecución del plan. Se va a utilizar el término "icono" y "operador" de forma intercambiable.
Hay 78 operadores disponibles. Afortunadamente, no es necesario memorizar todos antes de que podamos leer un plan de ejecución gráfico. La mayoría de las consultas utilizan sólo un pequeño subconjunto de operadores, y nos centraremos en los principales. Si vemos un icono desconocido, se puede encontrar más información al respecto en los libros en pantalla. .
Un plan de ejecución gráfico muestra cuatro tipos distintos de operadores:
• Los operadores lógicos y físicos, también llamados iteradores, aparecen como iconos azules y representan la ejecución de consultas u operaciones DML.
• Operadores de paralelismo físico son también iconos azules y representan operaciones de paralelismo. Son un subconjunto de los operadores lógicos y físicos, pero conllevan un nivel completamente diferente de análisis en el plan de ejecución.
• Operadores de cursor, tienen iconos de color amarillo y representan operaciones de cursor de SQL.
• Elementos del lenguaje son iconos verdes y representan elementos del lenguaje SQL, como ASSIGN, DECLARE, IF, SELECT (RESULT), WHILE.
Nos centraremos principalmente en los operadores lógicos y físicos, incluyendo algunos operadores de paralelismo físico. Algunos textos enumeran en orden alfabético todos los operadores, pero esta no es la forma más fácil de aprenderlos, aquí nos centraremos en los iconos más comunes.
Podemos aprender mucho de cómo trabajan los operadores observando la forma en que operan dentro de los planes de ejecución. La clave, es aprender a utilizar las propiedades de cada uno de los operadores y profundizar en ellas. Cada operador, tiene un conjunto diferente de características. Algunos operadores, principalmente Sort, Hash Match (Aggregate) y Hash Join requieren una cantidad variable de memoria para ejecutarse. Una consulta con uno de estos operadores puede tener un tiempo de retardo antes de su ejecución, pudiendo afectar negativamente al rendimiento. A continuación, se muestra una lista de los operadores.
La mayoría de los operadores se comportan de dos formas: de bloqueo o de no bloqueo. Un operador no-bloqueo crea datos de salida al mismo tiempo que recibe la entrada. Un operador de bloqueo tiene que obtener todos los datos antes de generar su salida. Los operadores de bloqueo pueden producir problemas de concurrencia, perjudicando el rendimiento.
Un ejemplo de un operador de no-bloqueo sería Merge Join que produce datos de salida mientras va leyendo los datos de entrada. Podemos saberlo porque un operador Merge Join necesita datos para funcionar correctamente, por lo que no puede producir su salida si no dispone de datos de entrada.
Un ejemplo de un operador de bloqueo sería el Hash Match. Pues debe poseer todos los datos antes de poder unirlos y producir una salida.
La clave para entender los planes de ejecución, es empezar a aprender a entender lo que hacen y cómo afecta esto a la consulta.
Algunas consultas de tabla sencilla
Vamos a comenzar con algunos planes muy sencillos con base en consultas de tabla individuales.
Clustered Index Scan (exploración de Índice Agrupado)
Uno de los operadores más comunes es el Clustered Index Scan (exploración del índice agrupado). Esta operación se produce cuando se recorre un índice agrupado o algún otro índice, no puede satisfacer las necesidades de la consulta. En ese caso, SQL Server debe escanear, todo el conjunto de datos.
Consideramos la siguiente consulta sencilla (pero ineficaz) sobre una tabla:
SELECT * FROM dbo.tbConfiguracion
La figura muestra el plan de ejecución real:
Podemos ver que el motor utiliza una operación de exploración de índice agrupado para recuperar la Información requerida. Mirando sólo el plan gráfico no nos dice lo suficiente sobre el plan de ejecución o de los operadores. Si se coloca el puntero del ratón sobre el icono Examen de índice clúster se abre una ventana con la información que se disponga de forma inmediata.
En la parte inferior de la información sobre la herramienta, se encuentra la etiqueta, Objeto. Esta indica qué objeto, fue el índice agrupado utilizado. En este caso fue PK_tbConfiguracion. Las propiedades enumeradas anteriormente pueden ser útiles para la comprensión de cómo trabaja el operador y lo que está haciendo. Algunas de las propiedades son fáciles de entender, pero otras pueden soportar alguna información adicional más compleja. El costo estimado de E/S y Costo estimado de la CPU son medidas asignadas por el optimizador, y el costo de cada operador contribuye al coste total del plan.
No todos los operadores serán útiles para determinar en cada plan de ejecución. Por ejemplo, Reelances y Rewinds sólo son importantes cuando se trata de uniones de bucles anidados, pero no hay ninguno en este plan, por lo que ahora no los necesitamos.
Los índices en SQL Server se almacenan en un árbol balanceado o árbol B (una serie de nodos que apuntan a un padre). Un índice agrupado no sólo almacena la clave estructurada como un índice normal, sino que también clasifica y almacena los datos en el nivel más bajo del índice, conocidos como hojas, que es la razón por la que solo puede haber un índice agrupado por tabla. Esto significa que un recorrido del índice agrupado es muy similar en concepto a un recorrido completo de la tabla. El índice completo o un gran porcentaje de él que se recorre, fila por fila, con el fin de recuperar los datos necesarios para la consulta.
El recorrido del índice se produce a menudo, como en este caso, cuando existe un índice, pero el optimizador determina que hay tantas filas de retorno que es más rápido simplemente explorar todos los valores en el índice en lugar de utilizar las claves proporcionadas por dicho índice. En otras ocasiones se necesita recorrer el índice porque el índice no es suficientemente selectivo para que el optimizador esté seguro de encontrar los valores que necesita sin la exploración de un gran porcentaje del índice. Esto también puede ocurrir cuando las estadísticas de ese índice no están actualizadas y muestran información incorrecta. También puede haber situaciones en las que una consulta se aplica a los valores de las columnas, lo que significa que el optimizador no puede determinar el valor de una columna por lo que tiene que escanear todo índice para encontrarlo.
Una pregunta obvia que hay que hacerse, si vemos un recorrido completo del índice en su plan de ejecución, es si está devolviendo más filas de las necesarias. Si el número de filas devuelto es mayor de lo esperado, es un fuerte indicador de que se necesita añadir una cláusula WHERE de forma que sólo se devuelvan las filas que realmente se necesitan. Devolver filas innecesarias malgasta recursos de SQL Server y perjudica el rendimiento general.
Clustered Index Seek (Búsqueda de Índice Agrupado)
Una búsqueda de índice agrupado se produce cuando una consulta utiliza el índice para acceder a una única fila, o unas pocas filas contiguas. Es una de las formas más rápidas para recuperar los datos del sistema.
Podemos hacer fácilmente la consulta anterior más eficiente añadiendo la cláusula WHERE. Esto limita la cantidad de datos que deben ser devueltos, lo que hace que sea más probable que se utilicen los índices para devolver los datos apropiados.
select * from dbo.tbInsConfiguracion where bteAplicacion = 0 and strParametro = 'strDescripcion'
El plan ahora se ve así.
Las búsquedas de índice son completamente diferentes de las exploraciones (scan), donde el motor recorre todas las filas para encontrar lo que necesita. Las búsquedas (seek) de índices agrupados y no agrupados, sucede cuando el optimizador es capaz de localizar un índice que se puede utilizar para recuperar los registros requeridos. Por tanto, se indica al motor que busque en el índice en vez de explorar toda la tabla.
Cuando se utiliza un índice en una búsqueda, los valores de las claves se utilizan para identificar rápidamente la fila o filas de datos que se necesitan. Es similar a buscar una palabra en un índice de un libro para obtener el número de página correcto. El beneficio de la búsqueda de índice agrupado es que, No sólo es una operación de bajo costo en comparación con una exploración completa sino que además no son necesarios pasos adicionales para obtener los datos, ya que están almacenados en el índice, a nivel de hoja.
En el ejemplo anterior, tenemos un índice agrupado La operación de búsqueda llevada a cabo se muestra en la figura
El índice utilizado es el mismo que el ejemplo anterior, que pasa a ser a la vez la clave principal y el índice agrupado para esta tabla. Esto se identifica de nuevo en la parte inferior de la información sobre herramientas en Objeto. Esta vez, sin embargo, la operación es una búsqueda. Una búsqueda tiene un predicado, o predicados, que son los filtros mediante los cuales se recuperan los valores. En este caso, el predicado son los valores que pasamos en la consulta después del WHERE, es decir el ‘0’ y ‘strDescripcion’ para buscar en las columnas. Sin embargo, en la sección de Buscar predicados se está mostrando @1 en lugar del valor ‘0’ o ‘strDescripcion’. Esto se debe a que se trata de una consulta simple y se ha clasificado para parametrización simple. La parametrización simple de SQL Server crea una consulta parametrizada, que es muy similar a un procedimiento almacenado, de modo que se pueda volver a utilizar el plan generado la próxima vez que esta consulta se llame con un valor diferente. De lo contrario, si esta consulta se llama de nuevo con valores diferentes, se obtendría un plan completamente nuevo. Al ver el cambio de valor en el predicado, se puede comenzar a entender cómo usar la información disponible a través de un plan de ejecución para entender lo que está ocurriendo dentro del Servidor SQL.
NonClustered Index Seek (Búsqueda de Índice no Agrupado)
Esta operación no es diferente de la búsqueda con el índice agrupado solo que en este caso los únicos datos disponibles son los que están almacenados en el propio índice.
Vamos a ejecutar una consulta ligeramente diferente, una que utiliza un índice no agrupado:
SELECT bteAplicacion
FROM sorolla.tbInsconfiguracion
WHERE strParametro LIKE 'strDescri%'
Obtenemos una búsqueda de índice (no agrupado). Un índice no agrupado sólo almacena los valores clave; es decir no almacena datos. El optimizador podría tener que buscar los datos en el índice agrupado, lo que puede penalizar ligeramente el rendimiento debido a operaciones de E/S adicionales.
En el ejemplo, el índice satisface todas las necesidades de la consulta, es decir, todos los datos necesarios se almacenan en el índice, en la clave debido a que sólo nos estamos refiriendo a dos columnas, la columna clave para el propio índice y el nombre.
Key Lookup (Búsqueda por clave)
Es necesario un operador Key Lookup (hay dos: RID y Claves) para obtener datos de la pila o del índice agrupado, respectivamente, cuando se utiliza un índice no agrupado, pero no está cubierto por un índice. Un ejemplo es una consulta que devuelve varias columnas de una tabla.
SELECT p.strNumDoc,
p.strEjercicio,
p.strExpediente,
p.strfase
FROM dbo.tbExpediente AS p
WHERE p.strExpediente LIKE '010092%';
Debería ver un plan como el que se muestra
Este plan involucra más de una operación, La lectura del plan se realiza de derecha a izquierda y de arriba abajo, la primera operación que vemos es un Index Scan que contiene todas las columnas que hace referencia la consulta, incluidas en la SELECT.
En este caso, el índice de la columna strExpediente proporciona una forma rápida de recuperar la información en base a los criterios del filtrado LIKE '010092%'. Sin embargo, ya que sólo la columna strExpediente y la clave del índice agrupado se almacenan con el índice no agrupado, como se hace referencia a otras columnas, es necesario que busque estas en otra parte.
El optimizador de consultas se ve obligado a no sólo leer el índice no agrupado, sino también a leer el índice agrupado para reunir todos los datos necesarios para procesar la consulta. Esta es una búsqueda de clave y, esencialmente, significa que el optimizador no puede recuperar las filas en una sola operación, y tiene que usar una clave agrupada (o un identificador de fila a partir de la tabla) para devolver las filas correspondientes de un índice agrupado (o de la propia tabla).
Podemos entender el funcionamiento de Key Lookup utilizando la información contenida dentro del plan de ejecución. Las piezas de información que ahora nos interesan son: la lista de salida y los predicados Seek.
El optimizador ahora rastreará la información referente a las necesidades del índice agrupado. SQL Server utiliza la clave de un índice agrupado como método para buscar datos que se almacenan en el índice agrupado a partir de un índice no agrupado, que sólo contiene información clave, la clave agrupada, y cualquier columna de búsqueda.
Para buscar las filas apropiadas de datos, SQL Server utiliza los valores en una Clave de búsqueda de Primary Key (PK_tbVidaContableExpediente el índice agrupado. En la ventana de información sobre herramientas para el operador de búsqueda de claves, se puede ver la lista de salida que contiene la última columna que necesita
La presencia de una clave de búsqueda, es una indicación de que el rendimiento de consulta podría beneficiarse de la presencia de un índice de cobertura. Un índice de cobertura se crea por cualquiera que tenga todas las columnas necesarias como parte de la clave del índice, o mediante el uso de la operación INCLUDE en último nivel (el nivel hoja) de modo que estén disponibles para su uso con el índice.
Una operación de combinación, que combine los resultados de las dos operaciones, siempre acompaña una búsqueda de claves. En este caso, se trataba de la operación bucles anidados.
 |
Si esta tabla no hubiese tenido un índice agrupado, el operador tendría un operador de búsqueda RID RID lookup. De esta forma las filas de una tabla llevan una marca única que se almacena. Los fundamentos de la operación de RID Lookup son los mismos que los de un Key Lookup.
Table Scan (Escaneado de Tabla)
El escaneado de tabla se realiza tablas que carecen de índices agrupados.
Por ejemplo al realizar una consulta sobre una tabla sin índices.
select * from dbo.base_Documental
Cuando se necesitan todas (o la mayoría) de las filas de una tabla entonces suele ser más rápido para el optimizador devolverlas todas antes de buscar cada fila en un índice. Esto sucede comúnmente en las tablas con pocas filas.
Suponiendo que el número de filas de una tabla es relativamente pequeño, el recorrido completo de la tabla no suele ser un problema. Pero si la tabla es grande, entonces es posible que se desee investigar la forma de volver a escribir la consulta para que devuelva menos filas, o agregar un índice para acelerar el rendimiento.
RID lookup (Operaciones de búsqueda RID)
Búsqueda de RID es el equivalente a Table Scan (Escanedado de Tabla) para Key Lookup (Búsqueda por clave). Pues los índices no agrupados, no siempre tienen todos los datos necesarios para realizar una consulta de forma satisfactoria, en ese caso, se requiere una operación adicional para obtener esos datos. Cuando hay una índice agrupado en la tabla, se utiliza un operador Key Lookup (Clave de búsqueda), como se ha descrito anteriormente. Cuando no existe un índice agrupado, debe buscar en los datos utilizando un identificador interno conocido como identificador de fila o RID.
Si en la consulta anterior forzamos a filtrar los resultados utilizando la columna de clave principal, vemos un plan diferente que utiliza una combinación de una búsqueda de índice y una búsqueda de RID.
select * from dbo.base_Documental where id = 1
Para devolver los resultados de esta consulta, el optimizador de consultas primero realiza una búsqueda sobre el índice de la clave principal. Si bien este índice es útil para identificar las filas que cumplen el criterio WHERE, todas las columnas de datos requeridos no están presentes en el índice. ¿Cómo podemos saber esto?
Si nos fijamos en la información sobre herramientas en la figura,
para la búsqueda de índice, vemos el valor Bmk1000 en la lista de salida. Este Bmk1000 es una columna adicional, no se hace referencia en la consulta. Es el valor de clave del índice no agrupado que se va a utilizar en el operador de los bucles anidados para unirlos con los datos de la operación de búsqueda de RID.
A continuación, el optimizador de consultas realiza una búsqueda RID, que es un tipo de marca que se pone en la tabla (que no tiene un índice agrupado), y se utiliza como identificador de fila para buscar las filas que se devolverán. En otras palabras, ya que la tabla no dispone de un índice agrupado (que incluya todas las filas), debe utilizar un identificador de fila que una el índice con el resto de datos. Esto añade tiempo adicional a la búsqueda, ya que se tienen que realizar dos operaciones diferentes, que luego se combinan a través de un bucle anidado.
En la información sobre herramientas para la búsqueda de RID, aparece de nuevo Bmk1000, pero esta vez en la sección Predicados de Búsqueda (Seek Predicates). Este es el valor de la clave, que es un identificador de fila o RID, a partir el índice no agrupado.
Si una búsqueda RID devuelve el número de filas grande (en este caso devuelve 1 [Ver Seek Keys]), sin embargo, es posible que sea necesario considerar una reestructuración de la consulta para ver cómo se puede conseguir que realice menos una operación de E/S, mediante la adición de un índice agrupado.
No hay comentarios:
Publicar un comentario