Planes de ejecución con SQL Server II

Planes estimado y real de ejecución

Como se mencionó en la primera parte de Planes de Ejecución con SQL Server, hay dos tipos distintos de plan de ejecución. En primer lugar, existe el plan que representa la salida del optimizador. Es el plan de ejecución estimado. Corresponde con una primera estimación del optimizador y no representan la ejecución real cuando se ejecute la consulta física.
El siguiente es el plan que representa la salida real de la ejecución de la consulta. Este tipo de plan es el plan de ejecución real y representa lo que realmente ocurrió cuando se ejecutó la consulta.

Muchas veces coincidirán ambos planes con los mismos costes. Pero hay ocasiones en las que SQL Server ejecutará un plan nuevo completamente diferente debido por ejemplo a un cambio en las estadísticas, u otros cambios que ocurren cuando el motor de almacenamiento procesa las consultas.
Los planes estimados son los planes almacenados en la caché, esto significa que es posible acceder a los datos disponibles en los planes de ejecución reales solamente mediante la ejecución de una consulta. Dado que los planes nunca  acceden directamente a los datos, son muy útiles para grandes y complejas consultas que podrían llevar mucho tiempo en ejecutarse. Los planes de ejecución reales son preferibles porque muestran estadísticas de ejecución como el número de filas que accederán por cada operador dado.

Ejecución del Plan de Reutilización

Es costoso en tiempo de servidor generar planes de ejecución. Mientras que SQL Server puede hacer consultas en menos de un milisegundo, puede tardar segundos o incluso minutos en crear una plan de ejecución, por lo que SQL Server guarda y reutiliza los planes siempre que sea posible con el fin de reducir el tiempo de utilización del servidor. A medida que se crean, los planes se almacenan en una sección de memoria llamada la caché del plan.  Cuando se envía una consulta al servidor, el analizados crea un hash, como un código de la consulta. El hash es un identificador único para cada consulta.
El optimizador busca las consultas en la caché. Si existe una consulta que coincide con la consulta, el proceso de optimización omite el cálculo y se reutiliza el plan de ejecución almacenado en la caché.
Es importante y una buena práctica escribir consultas que SQL Server pueda volver a utilizar en su planes. Para asegurar esta reutilización, lo mejor es utilizar procedimientos almacenados o consultas parametrizadas. Está últimas son  consultas, donde las variables dentro de la consulta son identificadas con parámetros, similares a un procedimiento almacenado, donde se alimentan estos parámetros con valores.
SQL Server no mantiene los planes de ejecución en la memoria para siempre. Estos envejecen lentamente utilizando una fórmula de "edad" que multiplica el costo estimado del plan por el número de veces que se ha utilizado (por ejemplo, un plan con un costo estimado de 10 que ha sido referenciados 5 veces tiene un valor "edad" de 50). Un proceso interno trabaja para liberar a todos los tipos de caché (incluyendo la caché del plan), analiza periódicamente los objetos en la memoria caché.
Si se cumplen los siguientes criterios, el plan se elimina de la memoria:

• Se requiere más memoria para el sistema

• La "edad" del plan ha superado cierto límite

• El plan no está siendo referenciado actualmente por una conexión existente.
Los planes de ejecución no son fijos. Ciertos eventos y acciones pueden hacer que se recompile. Hay que tener en cuenta que volver a compilar un plan de ejecución puede ser muy costoso. Las siguientes acciones pueden conducir a la recopilación de un plan de ejecución:

• Cambiar la estructura de una tabla a la que hace referencia la consulta.

• Cambiar un índice utilizado por la consulta.

• Eliminar un índice utilizado por la consulta.

• La actualización de las estadísticas utilizadas por la consulta.

• Llamar a la función, sp_recompile.

• Someter las claves de las tablas referenciadas por la consulta a un gran número de inserciones o eliminaciones (lo que conduce a cambios en las estadísticas).

• Tablas con Triggers que se activan cuando se ha producido un crecimiento significativo en las filas insertadas o eliminadas.

• Mezclar código (sólo de consulta) DDL y (de modificación)  DML dentro de una única consulta, a menudo llamado compilación diferida.

• Cambiar las opciones SET dentro de la ejecución de la consulta.

• Cambiar la estructura o esquema de las tablas temporales utilizadas por la consulta.
• Cambios en las vistas dinámicas utilizadas por la consulta.

• Cambios en las opciones de cursor dentro de la consulta.

• Cambios en un conjunto de filas en una vista dividida distribuida.

• Utilizar cursores de cliente, si se cambian las opciones para navegar.

Eliminar planes de ejecución de la caché

Como la caché juega un papel tan importante en el funcionamiento de los planes de ejecución, se hacen necesarias algunas herramientas para consultar y trabajar con la caché del plan. En primer lugar, es posible querer ver cuánto tiempo tarda un plan en compilar, o investigar cómo ajustes de menor importancia podrían crear ligeramente planes diferentes planes. Para borrar la memoria caché, ejecutar lo siguiente:
DBCC FREEPROCCACHE

ADVERTENCIA: El borrado de la caché en un entorno de producción borrará la caché para todas las bases de datos del servidor. Ese puede provocar una pérdida significativa de rendimiento porque SQL Server debe volver a crear cada plan de almacenado en la caché del plan.

Para trabajar con una consulta individual, es mejor centrarse sólo en esa consulta a la hora de eliminarla de la caché del plan, para ellos se puede  utilizar sql_handle o bien plan_handle. 

Formatos del plan de ejecución

Aunque SQL Server produce un único plan de ejecución para una consulta determinada, podemos verlo de tres maneras distintas.

• Planes gráficos
• Planes de texto
• Planes XML.

El que elijamos dependerá del nivel de detalle que deseemos ver, y en los métodos utilizados para generar o recuperar ese plan.

Planes gráficos

Los planes gráficos son el tipo más utilizado. Son rápidos y fáciles de leer. Podemos ver los dos planes de ejecución, el  estimado y el real de forma gráfica.
El formato y la estructura gráfica permiten la comprensión de la mayoría de los planes de forma muy fácil. Sin embargo, la información detallada queda oculta en hojas de propiedades y  es más difícil de conseguir.

Planes de texto

Pueden resultar muy difíciles de leer, pero proporcionan información detallada  disponible inmediatamente. El formato de texto es exportable al bloc de notas o a Word, desde donde es posible ejecutar búsquedas de palabras clave. Aunque  la información que proporcionan está disponible inmediatamente, hay menos detalle sobre el plan de ejecución de salida en este tipo de plan, por lo que puede ser menos útil que otros tipos de planes.
Hay tres formatos de planes de texto:
• SHOWPLAN_ALL - Un conjunto razonablemente completo de los datos que muestra el plan de ejecución estimado para la consulta.
• SHOWPLAN_TEXT - Proporciona un conjunto muy limitado de datos para su uso con herramientas como osql.exe. Sólo muestra el plan de ejecución estimado.
• STATISTICS PROLFILE - Similar a SHOWPLAN_ALL excepto que representa los datos correspondientes al plan de ejecución real.

Planes XML

Los planes XML presentan un conjunto completo de datos disponibles de un plan, en formato XML. El formato XML es ideal para la transmisión de datos por lo que es posible compartirlo rápidamente.
Utilizando XQuery podemos consultar los datos XML directamente. XML es muy difícil de leer, por lo que será necesario ayudarse de recursos gráficos o textuales para su correcta interpretación.
Existen dos variedades de plan de XML:

• SHOWPLAN_XML - El plan generado por el optimizador antes de la ejecución.

• STATISTICS_XML - El formato XML del plan de ejecución real.