¿Cómo obtengo un plan de ejecución de consultas?

En Microsoft SQL Server, ¿cómo puedo obtener un plan de ejecución de consultas para una consulta / procedimiento almacenado?

Hay una serie de métodos para obtener un plan de ejecución, uno para usar dependerá de sus circunstancias. Por lo general, puede usar SQL Server Management Studio para obtener un plan; sin embargo, si por alguna razón no puede ejecutar su consulta en SQL Server Management Studio, entonces puede serle útil obtener un plan a través de SQL Server Profiler o inspeccionarlo el caché del plan

Método 1 – Uso de SQL Server Management Studio

SQL Server viene con un par de características que hacen que sea muy fácil capturar un plan de ejecución, simplemente asegúrese de que la opción de menú “Incluir plan de ejecución real” (que se encuentra en el menú “Consultar”) esté marcada y ejecute su consulta de forma normal .

Incluir elemento del menú Plan de ejecución de acciones

Si está tratando de obtener el plan de ejecución para las declaraciones en un procedimiento almacenado, entonces debe ejecutar el procedimiento almacenado, así:

exec p_Example 42 

Cuando finalice la consulta, debería aparecer una pestaña adicional titulada “Plan de ejecución” en el panel de resultados. Si ejecutó muchas declaraciones, es posible que vea muchos planes en esta pestaña.

Captura de pantalla de un plan de ejecución

Desde aquí puede inspeccionar el plan de ejecución en SQL Server Management Studio, o hacer clic derecho en el plan y seleccionar “Guardar plan de ejecución como …” para guardar el plan en un archivo en formato XML.

Método 2: usar las opciones de SHOWPLAN

Este método es muy similar al método 1 (de hecho, esto es lo que SQL Server Management Studio hace internamente), sin embargo, lo he incluido para completarlo o si no tiene SQL Server Management Studio disponible.

Antes de ejecutar su consulta, ejecute una de las siguientes afirmaciones. La statement debe ser la única statement en el lote, es decir, no puede ejecutar otra instrucción al mismo tiempo:

 SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- The is the recommended option to use 

Estas son opciones de conexión, por lo que solo necesita ejecutar esto una vez por conexión. A partir de este punto, todas las declaraciones ejecutadas irán acompañadas de un resultado adicional que contiene su plan de ejecución en el formato deseado; simplemente ejecute su consulta como lo haría normalmente para ver el plan.

Una vez que haya terminado, puede desactivar esta opción con la siguiente statement:

 SET < 

Comparación de formatos de planes de ejecución

A menos que tenga una preferencia fuerte, mi recomendación es usar la opción STATISTICS XML . Esta opción es equivalente a la opción “Incluir plan de ejecución real” en SQL Server Management Studio y proporciona la mayor cantidad de información en el formato más conveniente.

  • SHOWPLAN_TEXT : muestra un plan de ejecución estimado basado en texto básico, sin ejecutar la consulta
  • SHOWPLAN_ALL : muestra un plan de ejecución estimado basado en texto con estimaciones de costos, sin ejecutar la consulta
  • SHOWPLAN_XML : muestra un plan de ejecución estimado basado en XML con estimaciones de costos, sin ejecutar la consulta. Esto es equivalente a la opción “Visualizar plan de ejecución estimado …” en SQL Server Management Studio.
  • STATISTICS PROFILE : ejecuta la consulta y muestra un plan de ejecución real basado en texto.
  • STATISTICS XML – Ejecuta la consulta y muestra un plan de ejecución real basado en XML. Esto es equivalente a la opción “Incluir plan de ejecución real” en SQL Server Management Studio.

Método 3: usar el Analizador de SQL Server

Si no puede ejecutar su consulta directamente (o su consulta no se ejecuta lentamente cuando la ejecuta directamente, recuerde que queremos que el plan de la consulta tenga un mal rendimiento), entonces puede capturar un plan utilizando un rastreo de SQL Server Profiler. La idea es ejecutar su consulta mientras se ejecuta un seguimiento que está capturando uno de los eventos “Mostrar plan”.

Tenga en cuenta que, dependiendo de la carga, puede utilizar este método en un entorno de producción; sin embargo, debe tener precaución. Los mecanismos de creación de perfiles de SQL Server están diseñados para minimizar el impacto en la base de datos, pero esto no significa que no habrá ningún impacto en el rendimiento. También es posible que tenga problemas para filtrar e identificar el plan correcto en su seguimiento si su base de datos está bajo uso intensivo. ¡Obviamente deberías consultar con tu DBA para ver si están contentos con que hagas esto en su preciosa base de datos!

  1. Abra el Analizador de SQL Server y cree un nuevo rastreo que se conecte a la base de datos deseada contra la cual desea registrar el rastreo.
  2. Debajo de la pestaña “Selección de eventos”, marque “Mostrar todos los eventos”, marque la fila “Rendimiento” -> “Mostrar plan XML” y ejecute el seguimiento.
  3. Mientras se ejecuta el rastreo, haga lo que sea necesario para que se ejecute la consulta de ejecución lenta.
  4. Espere a que la consulta se complete y detenga el rastreo.
  5. Para guardar el rastreo, haga clic derecho en el plan xml en SQL Server Profiler y seleccione “Extraer datos de evento …” para guardar el plan en un archivo en formato XML.

El plan que obtiene es equivalente a la opción “Incluir plan de ejecución real” en SQL Server Management Studio.

Método 4: inspeccionar el caché de consultas

Si no puede ejecutar su consulta directamente y tampoco puede capturar un rastreo de perfilador, puede obtener un plan estimado inspeccionando el caché del plan de consulta SQL.

Inspeccionamos el caché del plan al consultar los DMV de SQL Server. La siguiente es una consulta básica que listará todos los planes de consultas en caché (como xml) junto con su texto SQL. En la mayoría de las bases de datos, también deberá agregar cláusulas de filtrado adicionales para filtrar los resultados hasta los planes que le interesan.

 SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

Ejecute esta consulta y haga clic en el plan XML para abrir el plan en una nueva ventana: haga clic con el botón derecho y seleccione “Guardar plan de ejecución como …” para guardar el plan en un archivo en formato XML.

Notas:

Como hay tantos factores involucrados (desde el esquema de tabla e índice hasta los datos almacenados y las estadísticas de la tabla) siempre debe intentar obtener un plan de ejecución de la base de datos en la que está interesado (normalmente el que está experimentando un rendimiento). problema).

No puede capturar un plan de ejecución para procedimientos almacenados cifrados.

Planes de ejecución “reales” versus “estimados”

Un plan de ejecución real es aquel en el que SQL Server realmente ejecuta la consulta, mientras que un plan de ejecución estimado SQL Server determina qué haría sin ejecutar la consulta. Aunque es lógicamente equivalente, un plan de ejecución real es mucho más útil, ya que contiene detalles y estadísticas adicionales sobre lo que realmente sucedió al ejecutar la consulta. Esto es esencial cuando se diagnostican problemas donde las estimaciones de Servidores SQL están desactivadas (como cuando las estadísticas no están actualizadas).

  • Plan de ejecución estimado y real revisado

¿Cómo interpreto un plan de ejecución de consultas?

Este es un tema digno de un libro (gratuito) por derecho propio.

Ver también:

  • Conceptos básicos del plan de ejecución
  • SHOWPLAN Permiso y lotes de Transact-SQL
  • SQL Server 2008 – Usar hash de consulta y hash de plan de consulta
  • Analizando la caché del plan de SQL Server

Además de la respuesta integral ya publicada, a veces es útil poder acceder programáticamente al plan de ejecución para extraer información. El código de ejemplo para esto está debajo.

 DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID 

Ejemplo StartCapture Definition

 CREATE PROCEDURE StartCapture @Spid INT, @TraceID INT OUTPUT AS DECLARE @maxfilesize BIGINT = 5 DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36) EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL exec sp_trace_setevent @TraceID, 122, 1, 1 exec sp_trace_setevent @TraceID, 122, 22, 1 exec sp_trace_setevent @TraceID, 122, 34, 1 exec sp_trace_setevent @TraceID, 122, 51, 1 exec sp_trace_setevent @TraceID, 122, 12, 1 -- filter for spid EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid -- start the trace EXEC sp_trace_setstatus @TraceID, 1 

Ejemplo de definición de StopCapture

 CREATE PROCEDURE StopCapture @TraceID INT AS WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), CTE as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData, ObjectID, ObjectName, EventSequence, /*costs accumulate up the tree so the MAX should be the root*/ MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM fn_trace_getinfo(@TraceID) fn CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1) CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost', 'float') AS EstimatedTotalSubtreeCost FROM xPlan.nodes('//sql:RelOp') T(relop)) ca WHERE property = 2 AND TextData IS NOT NULL AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' ) GROUP BY CAST(TextData AS VARCHAR(MAX)), ObjectID, ObjectName, EventSequence) SELECT ObjectName, SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost FROM CTE GROUP BY ObjectID, ObjectName -- Stop the trace EXEC sp_trace_setstatus @TraceID, 0 -- Close and delete the trace EXEC sp_trace_setstatus @TraceID, 2 GO 

Suponiendo que está utilizando Microsoft SQL Server Management Studio

  • Para el plan de consulta estimado , puede presionar Ctrl + L o el siguiente botón.

enter image description here

  • Para Plan de consulta real , puede presionar Ctrl + M o el siguiente botón antes de ejecutar la consulta.

enter image description here

  • Para Live Query Plan , (solo en SSMS 2016) use el siguiente botón antes de ejecutar la consulta.

enter image description here

Además de los métodos descritos en las respuestas anteriores, también puede utilizar un plan de ejecución gratuito y una herramienta de optimización de consultas ApexSQL Plan (con la que me he topado recientemente).

Puede instalar e integrar ApexSQL Plan en SQL Server Management Studio, por lo que los planes de ejecución se pueden ver directamente desde SSMS.

Visualización de planes de ejecución estimados en el Plan ApexSQL

  1. Haga clic en el botón Nueva consulta en SSMS y pegue el texto de la consulta en la ventana de texto de la consulta. Haga clic derecho y seleccione la opción “Mostrar plan de ejecución estimado” en el menú contextual.

Nuevo botón de consulta en SSMS

  1. Los diagtwigs del plan de ejecución se mostrarán en la pestaña Plan de ejecución en la sección de resultados. A continuación, haga clic con el botón derecho en el plan de ejecución y, en el menú contextual, seleccione la opción “Abrir en ApexSQL Plan”.

Plan de ejecución

  1. El plan de ejecución estimado se abrirá en ApexSQL Plan y se puede analizar para la optimización de consultas.

Plan de ejecución estimado

Visualización de planes de ejecución reales en el Plan ApexSQL

Para ver el plan de ejecución real de una consulta, continúe desde el segundo paso mencionado anteriormente, pero ahora, una vez que se muestra el plan estimado, haga clic en el botón “Real” de la barra de cinta principal en ApexSQL Plan.

haga clic en el botón

Una vez que se hace clic en el botón “Real”, el plan de ejecución real se mostrará con una vista previa detallada de los parámetros de costo junto con otros datos del plan de ejecución.

Plan de ejecución real

Para obtener más información sobre cómo ver los planes de ejecución, siga este enlace .

Mi herramienta favorita para obtener y analizar profundamente los planes de ejecución de consultas es SQL Sentry Plan Explorer . Es mucho más fácil de usar, conveniente y completo para el análisis detallado y la visualización de planes de ejecución que SSMS.

Aquí hay una captura de pantalla de muestra para que tenga una idea de qué funcionalidad ofrece la herramienta:

Captura de pantalla de ventana de SQL Sentry Plan Explorer

Es solo una de las vistas disponibles en la herramienta. Observe un conjunto de tabs en la parte inferior de la ventana de la aplicación, que le permite obtener diferentes tipos de representación de su plan de ejecución e información adicional útil.

Además, no he notado ninguna limitación de su edición gratuita que impida usarla a diario u obligue a comprar la versión Pro eventualmente. Por lo tanto, si prefiere seguir con la edición gratuita, nada le prohíbe hacerlo.

ACTUALIZACIÓN: (Gracias a Martin Smith ) ¡Plan Explorer ahora es gratis! Consulte http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view para más detalles.

Los planes de consulta se pueden obtener a partir de una sesión de eventos ampliados mediante el evento query_post_execution_showplan . Aquí hay una sesión de ejemplo de XEvent:

 /* Generated via "Query Detail Tracking" template. */ CREATE EVENT SESSION [GetExecutionPlan] ON SERVER ADD EVENT sqlserver.query_post_execution_showplan( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)), /* Remove any of the following events (or include additional events) as desired. */ ADD EVENT sqlserver.error_reported( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.rpc_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_batch_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))), ADD EVENT sqlserver.sql_statement_completed( ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO 

Después de crear la sesión, (en SSMS) vaya al Explorador de objetos y profundice en Gestión | Eventos extendidos | Sesiones Haga clic con el botón derecho en la sesión “GetExecutionPlan” y comience. Haga clic derecho de nuevo y seleccione “Ver datos en vivo”.

A continuación, abra una nueva ventana de consulta y ejecute una o más consultas. Aquí hay uno para AdventureWorks:

 USE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Después de un momento o dos, debería ver algunos resultados en la pestaña “GetExecutionPlan: Live Data”. Haga clic en uno de los eventos query_post_execution_showplan en la grilla y luego haga clic en la pestaña “Plan de consulta” debajo de la grilla. Debe ser similar a esto:

enter image description here

EDITAR : El código de XEvent y la captura de pantalla se generaron a partir de SQL / SSMS 2012 con SP2. Si está utilizando SQL 2008 / R2, es posible que pueda modificar la secuencia de comandos para que se ejecute. Pero esa versión no tiene una GUI, por lo que tendrías que extraer el XML de showplan, guardarlo como un archivo * .sqlplan y abrirlo en SSMS. Eso es engorroso. XEvents no existía en SQL 2005 o anterior. Entonces, si no está en SQL 2012 o posterior, le recomendaría encarecidamente una de las otras respuestas publicadas aquí.

A partir de SQL Server 2016+, se introdujo la función Query Store para supervisar el rendimiento. Proporciona información sobre la elección y el rendimiento del plan de consulta. No es un reemplazo completo de eventos extendidos o de rastreo, pero a medida que evoluciona de una versión a otra, es posible que obtengamos un almacén de consultas totalmente funcional en futuras versiones de SQL Server. El flujo primario de Query Store

  1. Los componentes existentes de SQL Server interactúan con el almacén de consultas utilizando Query Store Manager.
  2. Query Store Manager determina qué tienda se debe usar y luego pasa la ejecución a esa tienda (Plan o Estadísticas de tiempo de ejecución o Query Wait Stats)
    • Plan Store: persistencia de la información del plan de ejecución
    • Runtime Stats Store: persistencia de la información de estadísticas de ejecución
    • Almacén de estadísticas de espera de consulta: información de estadísticas de espera persistente.
  3. Plan, Runtime Stats y Wait store usa Query Store como extensión de SQL Server.

enter image description here

  1. Habilitación de Query Store : Query Store funciona en el nivel de la base de datos en el servidor.

    • Query Store no está activo para nuevas bases de datos de forma predeterminada.
    • No puede habilitar el almacén de consultas para la base de datos master o tempdb .
    • DMV disponible

      sys.database_query_store_options (Transact-SQL)

  2. Recostackr información en la Tienda de consultas : Recostackmos toda la información disponible de las tres tiendas mediante Query Store DMV (Vistas de administración de datos).

    • Almacén de planes de consultas: persiste la información del plan de ejecución y es responsable de capturar toda la información relacionada con la comstackción de consultas.

      sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_query_text (Transact-SQL)

    • Almacén de estadísticas de tiempo de ejecución: persiste la información de las estadísticas de ejecución y es probablemente la tienda que se actualiza con más frecuencia. Estas estadísticas representan datos de ejecución de consultas.

      sys.query_store_runtime_stats (Transact-SQL)

    • Almacén de estadísticas de espera de consulta: persistencia y captura de información de estadísticas de espera.

      sys.query_store_wait_stats (Transact-SQL)

NOTA: El almacén de estadísticas de espera de consultas está disponible solo en SQL Server 2017+

Aquí hay algo importante que debe saber además de todo lo dicho anteriormente.

Los planes de consulta a menudo son demasiado complejos para ser representados por el tipo de columna XML incorporada que tiene una limitación de 127 niveles de elementos nesteds. Esa es una de las razones por las que sys.dm_exec_query_plan puede devolver NULL o incluso arrojar un error en versiones anteriores de MS SQL, por lo que generalmente es más seguro usar sys.dm_exec_text_query_plan . Este último también tiene una característica de bonificación útil al seleccionar un plan para una statement particular en lugar de todo el lote. Así es como lo usa para ver los planes de las declaraciones actualmente en ejecución:

 SELECT p.query_plan FROM sys.dm_exec_requests AS r OUTER APPLY sys.dm_exec_text_query_plan( r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS p 

Sin embargo, la columna de texto en la tabla resultante no es muy útil en comparación con una columna XML. Para poder hacer clic en el resultado para abrir en una pestaña separada como un diagtwig, sin tener que guardar su contenido en un archivo, puede usar un pequeño truco (recuerde que no puede usar CAST(... AS XML) ) , aunque esto solo funcionará para una sola fila:

 SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan FROM sys.dm_exec_text_query_plan( -- set these variables or copy values -- from the results of the above query @plan_handle, @statement_start_offset, @statement_end_offset) FOR XML EXPLICIT 

Al igual que con SQL Server Management Studio (ya explicado), también es posible con Datagrip como se explica aquí .

  1. Haga clic con el botón derecho en una statement de SQL y seleccione Explicar plan.
  2. En el panel de Salida, haga clic en Plan.
  3. Por defecto, verá la representación en árbol de la consulta. Para ver el plan de consulta, haga clic en el icono Mostrar visualización o presione Ctrl + Mayús + Alt + U