Planes de ejecución con SQL Server III, planes de ejecución gráficos

Los planes de ejecución nos ayudarán a escribir código SQL eficiente, solucionar problemas de SQL o monitorear e informar sobre nuestros sistemas.
Aquí se pueden consultar las dos partes previas:

Planes de ejecución con SQL Server I

Planes de ejecución con SQL Server II

Permisos requeridos para ver los planes de ejecución

Para generar planes de ejecución de consultas es necesario tener los permisos necesarios dentro de la base de datos. El administrador de base de datos tendrá generalmente todos los permisos pero para dar permisos a cualquier usuario se puede ejecutar.

GRANT SHOWPLAN TO [nombre de usuario]

Esta acción permitirá a un usuario ver los planes de ejecución para la base de datos.

Trabajar con los planes de ejecución gráficos

Comenzaremos con una de las consultas más simples posibles,

SELECT * FROM dbo.tbNombretabla

Ver el plan estimado

Vamos a ver el plan de ejecución estimado en forma gráfica que ha generado el optimizador de consultas.
Es posible averiguar la estimación del plan más óptimo calculada por el optimizador de una delas siguientes maneras, primero hay que seleccionar la consulta a analizar y después:

• Hacer clic en el icono plan de ejecución estimado en la barra de herramientas.

• Hacer clic en la ventana de consulta y seleccionar la misma opción de arriba en el menú.

• Hacer clic en la opción de consulta en la barra de menú (pulsando botón derecho del ratón) y seleccionar la misma opción.

• Pulsar CTRL + L en el teclado.

Estas opciones nos muestran algo así.

 Visualmente, no es fácil distinguir entre un plan y un estimado y un plan real. Las diferencias están en los datos subyacentes, vamos a explicar este plan, pero antes vamos a obtener el plan real de ejecución.

Obtener el real de ejecución

Los dos planes suelen ser idénticos, pero a veces pueden ser diferentes como se comentó en una entrada anterior.
Hay varias formas de obtener nuestro primer plan de ejecución real:

• Hacer clic en el icono en la barra de herramienta llamada Incluir plan de ejecución actual

• Hacer clic en el menú de consulta y elegir el plan de ejecución actual

• Elegir la misma opción en el menú contextual que sale al pulsar el botón derecho del ratón.

• Pulsar CTRL + M.

Con cada uno de estos ejemplos mostrados SQL Server crea un plan de ejecución. Se puede activar el plan de ejecución que se prefiera y ejecutar la consulta. El plan se muestra de este modo:

En este caso sencillo, el plan actual es visualmente idéntico al plan estimado.

Interpretación de los planes de ejecución gráficos

Los iconos que se ven en la imagen de arriba representan los dos primeros de aproximadamente 79 operadores que representan diversas acciones y decisiones que conforman un plan de ejecución. El icono de la izquierda del plan es el operador SELECT un operador que se verá muy a menudo.
Cada operador tiene un componente lógico y un componente físico. Con frecuencia son los mismos, pero cuando se mira un plan estimado, sólo se están viendo los operadores lógicos. Al buscar en un plan real, sólo se verán los operadores físicos que establecen la lógica de orden de procesamiento orden en  que SQL Server recuperará la información de la consulta. Esto significa que, leemos los planes de izquierda a derecha. En el ejemplo anterior el orden lógico es la definición del SELECT seguido por el operador índice clúster.
Sin embargo, encontraremos que por lo general la ejecución del plan irá en la otra dirección, de derecha a izquierda. Esto no es debido a que los planes de ejecución estén distribuidos incorrectamente. Es porque el orden físico de las operaciones es con frecuencia más fácil entender que el orden lógico de las operaciones.
Hay una flecha que apunta entre los dos iconos. Esta flecha representa los datos transmitidos entre los operadores, como el representado por los iconos. En este caso, si leemos el plan de ejecución en la dirección del flujo de datos, la dirección física, es de derecha a izquierda, tenemos un operador de índice clúster que pasa al operador SELECT. La dirección de la flecha indica la dirección del flujo de datos.
El espesor de la flecha refleja la cantidad de datos que se pasan, una flecha más gruesa representa más filas. Situados sobre la flecha con el puntero del ratón se muestra el número de filas, el tamaño que ocupa y otros datos. 

Por ejemplo, si una consulta devolviera dos filas, pero el plan de ejecución muestra un gran espesor flecha entre algunos de los operadores iniciales del plan y sólo se reduce a una flecha delgada al final, justo antes del operador SELECT, entonces hay que investigar la consulta para mejorar su rendimiento..
Debajo de cada icono de operador se visualiza un número como un porcentaje. Este número representa el costo relativo estimado de la consulta para ese operador. Ese costo es el tiempo estimado en porcentaje de ejecución de cada operación. Sin embargo, el tiempo de ejecución no es una representación de lo que hace realmente el sistema. El desarrollador de planes de ejecución de SQL Server utiliza como base para estos números, información desactualizada. Hay que interpretarlos como unidades de coste de cara al optimizador, en lugar de como una medida real. Estos estos números les llamaremos “costos”, porque la mayoría de la documentación hace referencia a ellos de esta forma. En este caso de ejemplo la totalidad del costo estimado se asocia con el recorrido de la tabla a través del índice.
Mientras que un coste puede ser representado como 0% o 100%, recordar que, son porcentajes y no números no reales, incluso un operador mostrando un 0% tendrá un pequeño coste asociado.
Por encima de los iconos se muestra gran parte de la cadena de consulta que se ajusta a la ventana, y una leyenda indicando  "costo de la consulta (relativo al lote)" del 100%. Del mismo modo que cada consulta puede tener múltiples operadores, y cada uno de los operadores tendrán un coste relativo a la consulta.

Herramientas relativas a los planes de ejecución

También es posible ejecutar múltiples consultas dentro de un lote y obtener planes de ejecución para todos. A continuación, se mostrarán los diferentes costos como parte de un todo. Estos costos se basan en estimaciones y, por tanto, deben interpretarse con un ojo puesto en la realidad que subyace a los planes representados, no simplemente suponiendo que los números mostrados son válidos.

Cada uno de los iconos y las flechas del plan tiene una ventana emergente llamada Información sobre herramientas, que se puede acceder con solo pasar el puntero del ratón sobre el icono.

Aquí vemos los siguientes números generados por el optimizador:

-Tamaño de la caché del plan. ¿Cuánta memoria ocupa la caché del plan para esta consulta?. Este es un número útil en la investigación de problemas de rendimiento de la memoria caché porque seremos capaces de ver la memoria que ocupa el plan.

-Grado de paralelismo. Aquí no mostrado. Si este plan utilizara múltiples procesadores mostraría el número de procesadores en uso, al haber sólo uno no se muestra y si lo hace aparecería el número 1.

-Costo de operador estimado. Es el costo porcentual que se ha explicado más arriba.

-Costo de subárbol estimado. Nos dice el coste acumulado del optimizador asignado a este paso y todos los pasos anteriores, pero recuerda que se debe leer de derecha a izquierda. Este número no tiene sentido en realidad pues es una evaluación matemática utilizada por el optimizador de consultas para determinar el coste del operador en cuestión; Representa una estimación del costo en segundos que el optimizador cree que le llevará al operador.

Número de filas estimado. Calculado sobre la base de las estadísticas disponibles para el optimizador de la tabla o índice en cuestión.

Vemos también en la parte inferior la instrucción que representa toda la consulta que se está procesando en SQL Server.

Si nos fijamos en la información sobre para el siguiente operador vemos que cada uno de los diferentes operadores tendrán un conjunto distinto de datos. El operador índice se está realizando un trabajo de naturaleza diferente a la Selección del operador anterior. Así obtenemos un conjunto diferente de datos.

En este caso primero, figuran la Operación física  y la Operación lógica. Los operadores lógicos son los resultados de los cálculos del optimizador para lo que debería suceder cuando se ejecuta la consulta. Los operadores físicos representan lo que realmente ocurre. Los  operadores lógicos y físicos suelen ser los mismos, pero no siempre. Después de eso, vemos el Costo de E/S estimado, El Costo del operador estimado y el Costo de subárbol estimado. El coste subárbol es simplemente la sección del árbol de ejecución desde el inicio hasta el operador actual trabajando de nuevo de derecha a izquierda y de arriba a abajo. Esto es importante porque si estamos analizando una consulta con problemas podemos ver en que operador concreto se disparan los costes de tiempo de la consulta.

Debajo aparece el Costo de CPU estimado. SQL Server basa todas estas  estimaciones en las estadísticas disponibles sobre cualquier tabla o cualquier columna de la base de datos. Por tanto el costo de E/S y el costo de la CPU no son valores reales, sino el coste estimado asignado por el optimizador de consultas durante sus cálculos.

Estos números pueden ser útiles para ver si la mayor parte del costo estimado es de E/S o si es carga de CPU. Un número más grande significa que SQL Server utiliza más recursos para esta operación. Como son valores estimados pueden diferir del costo real.
En este caso, el costo del operador y el costo subárbol son los mismos, ya que  la exploración de la tabla es el único operador significativo, en términos del trabajo realizado para ejecutar la consulta. Para árboles más complejos, con más operadores, se verá que el costo del subárbol se acumula como el coste individual de cada operador anterior que se añade al total.

También vemos el número estimado de filas. El plan muestra este número para cada operación porque cada operación está tratando con diferentes conjuntos de datos. Cuando tengamos planes de ejecución más complicados, el número de filas varía a medida que se muestre para cada operador diferente. Ver cómo cada operador añade o filtra filas, nos ayudará a comprender el proceso de ejecución de la consulta.

Ordenado. Otro dato importante, cuando se trata de solucionar problemas de rendimiento  es el valor booleano que indica si la consulta vendrá ordenada o no.  Si es falso las filas vendrán desordenadas. Ciertas operaciones, por ejemplo, la cláusula ORDER BY pueden requerir información para aparecer en un orden determinado, ordenado por un determinado valor o conjunto de valores.

Saber si los datos están ordenados ayuda a ver que el costo adicional puede ser de debido a esto.

Por último, Id de nodo es el ordinal, lo que significa simplemente el número de orden, del propio nodo. Cuando el optimizador genera un plan, los números de las operaciones llevan un orden lógico de las operaciones.
Todos estos detalles están disponibles para ayudarnos a entender lo que está sucediendo dentro de la consulta en cuestión. Podemos recorrer los distintos operadores, observando cómo el costo del subárbol se  acumula, cómo el número de filas cambia, y así sucesivamente. Con estos datos, podemos identificar las consultas que utilizan cantidades excesivas de CPU o tablas que necesitan más índices o identificar otros problemas de rendimiento.

Propiedades de los operadores

Se dispone de más información que la presentada hasta ahora.  Haciendo clic con el botón derecho del ratón sobre en cualquier icono dentro de un plan de ejecución gráfico y seleccionando el elemento de menú Propiedades obtenemos una lista detallada de información sobre esa operación.

Hay propiedades del objeto que tienen un triángulo a la izquierda. Esto significa que podemos ampliarlos para mostrar más información.
En muchas, pero no todas las propiedades vendrá una breve descripción en la parte inferior de la pantalla, como se puede ver en la imagen superior  Otras propiedades, no mostradas en este ejemplo, tienen una elipsis que nos permite abrir una nueva ventana con más detalles sobre dicha propiedad.