Milena Petrovic

Planes de ejecución de consultas SQL Server – Viendo los planes

December 18, 2015 by
En Planes de ejecución de consultas SQL Server – Básicos, describimos los planes de ejecución de consultas en SQL Server y por qué son importantes para el análisis de desempeño. En este artículo, nos enfocaremos en los métodos para abrir los planes, tanto los verdaderos como los estimados.

Si usted mira al elemento Query en el menú de SQL Server Management Studio, usted verá dos opciones relacionadas a los planes de consultas –Display Estimated Execution plan e Include Actual Execution plan.

Un plan de ejecución estimado es un plan de consultas SQL Server que es generado sin realmente correr la consulta (o procedimiento almacenado) para el cual el plan es creado. Está basado en una estimación de comportamiento esperado. Es útil para analizar cómo se comportaría una consulta sin realmente correrla. Esto es muy útil para propósitos de pruebas en ambientes donde el desempeño no debería ser afectado al correr el código real (por ejemplo, correr una sentencia SELECT con uniones complejas con tablas enormes), o cuando correr el código no es posible debido a los cambios en los datos que hace (por ejemplo, ejecutar un UPDATE). Su desventaja es que puede ser poco preciso en algunos escenarios.

Un plan de ejecución real es el plan de consultas SQL Server que es generado después de que una consulta fuera ejecutada. Es más confiable, y está basado en la ejecución real, no estimados. También provee más información y estadísticas, por lo que es mucho más útil al resolver problemas.

Hay muchos métodos disponibles para abrir un plan de consultas en SQL Server.

Include Actual Execution Plan

La opción Include Actual Execution Plan está disponible en el menú de SQL Server Management Studio.

  1. Seleccione Query en el menú de SQL Server Management Studio
  2. Seleccione la opción Include Actual Execution Plan, o presione Ctrl + M en el teclado.

Ahora, cada vez que usted ejecute una consulta o procedimiento almacenado ad hoc, una pestaña adicional aparecerá en el panel de resultados, al lado de las pestañas Results y Messages.

Dialog showing an additional Execution plan tab appearing in the results pane

Si múltiples consultas SQL son ejecutadas, sus planes serán listados en la misma pestaña, una debajo de la otra. Cada elemento en el plan de consultas muestra una sugerencia con información adicional.

Dialog showing a tooltip with additional information for each item in the query plan

Los planes de ejecución SQL Server pueden ser grabados como XML o archivos sqlplan para análisis posterior. Para abrir un archivo sqlplan, haga doble clic en el archivo en el explorador de archivos y será automáticamente abierto en SQL Server Management Studio.

Los pasos son similares para usar la opción Display Estimated Execution Plan, excepto que la consulta no tiene que ser ejecutada.

Los planes de consultas SQL Server también pueden ser mostrados en el Editor de Consultas usando alguna de las siguientes opciones:

SHOWPLAN_XML

La opción SHOWPLAN_XML tiene que ser establecida usando T-SQL y muestra el plan de ejecución estimado. Este es el mismo plan que se muestra cuando la opción Display Estimated Execution Plan es seleccionada, cuando la consulta no es realmente ejecutada.

  1. Ejecute:

    Note que esta es la única sentencia aquí que puede ser ejecutada.

  2. Ejecute una consulta. La pestaña Results mostrará un enlace al plan de consultas. Note que los resultados de la consulta no son mostrados, ya que la consulta no es realmente ejecutada.

    Dialog showing the Results tab with a link to the query plan

  3. Haga clic en el enlace en la cuadrícula.

    Una nueva pestaña de consulta será abierta mostrando el plan de consultas.

    Dialog showing a new query tab with the query plan in it

  4. Para parar de mostrar el plan de consultas , corra:

Use el caché de la consulta

Como se mencionó en el artículo Planes de ejecución de consultas SQL Server – Básicos, los planes de consultas en SQL Server son grabados en el caché del plan de consultas, así que pueden ser reutilizados para ejecutar consultas más rápido. Una de las opciones para ver planes de consultas es consultar el contenido del caché del plan usando Vistas de Administración Dinámicas (Dynamic Management Views, DMVs).

La vista sys.dm_exec_cached_plans muestra una fila por cada plan de consultas almacenado en el caché de planes. La vista muestra texto de consultas, la memoria usada y cuántas veces el plan fue reutilizado.

La vista sys.dm_exec_sql_text muestra el texto SQL, identificado por sql_handle.

Para ver los planes para consultas ad hoc en el caché de planes:

Dialog showing one row for every query plan stored in the plan cache

Para abrir un plan, haga clic en el enlace en la columna de resultados query_plan y el plan será mostrado en la ventana de nueva Consulta.

Use las opciones STATISTICS y SHOWPLAN

La opción STATISTICS XML muestra el mismo plan de consultas que la opción Include Actual Execution Plan. A diferencia de las opciones SHOWPLAN que no ejecutan las consultas realmente, las opciones STATISTICS lo ejecutan y muestran los resultados.

Note que aparte del enlace al plan de consultas, los resultados de la consulta son también mostrados.

Dialog showing the Results tab using the STATISTICS XML option

Para apagar la opción, ejecute:

Otras opciones útiles son:

SHOWPLAN_XML – no ejecuta la consulta, así que no se muestran resultados. Muestra el enlace al igual que la opción STATISTICS XML.

SHOWPLAN_TEXT – no ejecuta la consulta, muestra el texto del plan de consultas estimado.

SHOWPLAN_TEXT results – shows the text of the estimated query plan

SHOWPLAN_ALL – no ejecuta la consulta, muestra el texto del plan de consultas estimado junto con el costo de la estimación.

SHOWPLAN_ALL results - shows the text of the estimated query plan along with the cost estimation

STATISTICS PROFILE – ejecuta la consulta, muestra los resultados y texto del plan de consultas real.

STATISTICS PROFILE results – shows the results and text of the actual query plan

Use SQL Server Profiler

Una plan de ejecución también puede ser capturado en un rastro de SQL Server y abierto en SQL Server Profiler.

  1. Inicie SQL Server Profiler
  2. En el menú File, seleccione New Trace
  3. En la pestaña Events Selection, seleccione Show all events
  4. Expanda el nodo Performance
  5. Seleccione Showplan XML

    Selecting the Showplan XML using SQL Server Profiler

  6. Ejecute la consulta para la que quiere ver el plan de consultas
  7. Pare el rastro. Esto es recomendado debido a razones prácticas – en bases de datos ocupadas es difícil filtrar el evento que desea rastrear
  8. Seleccione el plan de consultas en la cuadrícula

    El plan de consultas SQL Server es mostrado en el panel inferior. Es el mismo plan mostrado cuando la opción Include Actual Execution Plan es seleccionada. Usted puede ver sus detalles en la sugerencia que aparece cuando se pasa el ratón por encima o se graba todo el rastro como un archivo XML para su análisis posterior.

    Dialog showing details for the SQL Server query plan in the tooltip that appears on mouse over

Este método no es recomendado debido a muchas desventajas. SQL Server Profile añade algo de costo que afecta el desempeño de la consulta. Otra razón es que filtrar los eventos y encontrar el específico entre miles de registros no es fácil en SQL Server Profiler.

En este artículo mostramos cómo abrir un plan de ejecución de consultas SQL Server usando varios métodos. En el siguiente artículo, mostraremos cómo leer los planes, qué representan los objetos representados con íconos, y cómo usar estos planes en análisis de desempeño y solución de problemas.

Recursos:
Display an Actual Execution Plan
Graphical Execution Plan Icons (SQL Server Management Studio)
sys.dm_exec_query_stats (Transact-SQL)

Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT.

She has started with computer programming in high school and continued at University. She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic
Milena Petrovic
5,294 Views