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.
- Seleccione Query en el menú de SQL Server Management Studio
- 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.
|
1 2 3 |
SELECT * FROM person.PersonPhone WHERE PhoneNumber LIKE '%697%' |
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.
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.
-
Ejecute:
1SET SHOWPLAN_XML ONNote que esta es la única sentencia aquí que puede ser ejecutada.
-
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.
-
Haga clic en el enlace en la cuadrícula.
Una nueva pestaña de consulta será abierta mostrando el plan de consultas.
-
Para parar de mostrar el plan de consultas , corra:
1SET SHOWPLAN_XML OFF
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:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT qp.query_plan, CP.usecounts, cp.cacheobjtype, cp.size_in_bytes, cp.usecounts, SQLText.text FROM sys.dm_exec_cached_plans AS CP CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP WHERE objtype = 'Adhoc' and cp.cacheobjtype = 'Compiled Plan' |
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.
|
1 |
SET STATISTICS XML ON |
Note que aparte del enlace al plan de consultas, los resultados de la consulta son también mostrados.
Para apagar la opción, ejecute:
|
1 |
SET STATISTICS XML OFF |
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_ALL – no ejecuta la consulta, muestra el texto del plan de consultas estimado junto con el costo de la estimación.
STATISTICS PROFILE – ejecuta la consulta, muestra los resultados y texto del plan de consultas real.
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.
- Inicie SQL Server Profiler
- En el menú File, seleccione New Trace
- En la pestaña Events Selection, seleccione Show all events
- Expanda el nodo Performance
-
Seleccione Showplan XML
- Ejecute la consulta para la que quiere ver el plan de consultas
- 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
-
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.
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)
Ella ha estado trabajando con SQL Server desde 2005 y tiene experiencia con SQL 2000 hasta SQL 2014.
Su tema favorito de SQL Server son las recuperaciones de desastres, la auditoría y el monitoreo de desempeño.
Vea todas las entradas de Milena “Millie” Petrovic
- Una guía de DBA para la solución de problemas de rendimiento de SQL Server – parte 2 – utilización de la supervisión - May 7, 2019
- Planes de ejecución de consultas – Entendiendo y leyendo los planes - April 19, 2018
- Mala indexación de base de datos – El asesino de las consultas SQL – recomendaciones - April 18, 2018