Basta ya de investigar por todo internet porque has llegado al lugar justo, poseemos la respuesta que buscas sin complicarte.
Solución:
Existen varios métodos para obtener un plan de ejecución, el cual utilizar 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 resultarle útil poder obtener un plan a través de SQL Server Profiler o inspeccionando la caché del plan.
Método 1: uso de SQL Server Management Studio
SQL Server viene con un par de características interesantes que facilitan la captura de un plan de ejecución, simplemente asegúrese de que el elemento de menú “Incluir plan de ejecución real” (que se encuentra en el menú “Consulta”) esté marcado y ejecute su consulta como de costumbre. .
Si está intentando obtener el plan de ejecución para las declaraciones en un procedimiento almacenado, debe ejecutar el procedimiento almacenado, así:
exec p_Example 42
Cuando su consulta se complete, debería ver una pestaña adicional titulada “Plan de ejecución” aparecer en el panel de resultados. Si ejecutó muchas declaraciones, es posible que vea muchos planes en esta pestaña.
Desde aquí puede inspeccionar el plan de ejecución en SQL Server Management Studio, o hacer clic con el botón 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: uso de 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 completar o si no tiene SQL Server Management Studio disponible.
Antes de ejecutar su consulta, ejecute uno de las siguientes declaraciones. La declaración debe ser la única declaración en el lote, es decir, no puede ejecutar otra declaració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 ejecutarlas una vez por conexión. A partir de este momento, todas las declaraciones que se ejecuten irán acompañadas de un conjunto de resultados adicional que contenga 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 declaración:
SET <Comparación de formatos de planes de ejecución
A menos que tenga una preferencia fuerte, mi recomendación es utilizar el STATISTICS XML opción. 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 consultaSHOWPLAN_ALL– Muestra un plan de ejecución estimado basado en texto con estimaciones de costos, sin ejecutar la consultaSHOWPLAN_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 “Mostrar 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: uso de SQL Server Profiler
Si no puede ejecutar su consulta directamente (o su consulta no se ejecuta lentamente cuando la ejecuta directamente, recuerde que queremos un plan de la consulta que funcione mal), entonces puede capturar un plan usando un seguimiento de SQL Server Profiler. La idea es ejecutar su consulta mientras se ejecuta un seguimiento que captura uno de los eventos “Showplan”.
Tenga en cuenta que dependiendo de la carga pueden use este método en un entorno de producción, sin embargo, obviamente debe tener cuidado. 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á alguna impacto en el rendimiento. También puede tener problemas para filtrar e identificar el plan correcto en su seguimiento si su base de datos está bajo un uso intensivo. ¡Obviamente, debe consultar con su DBA para ver si está contento con que haga esto en su preciosa base de datos!
- Abra SQL Server Profiler y cree una nueva traza que se conecte a la base de datos deseada en la que desea registrar la traza.
- En la pestaña “Selección de eventos”, marque “Mostrar todos los eventos”, marque la fila “Rendimiento” -> “Showplan XML” y ejecute el seguimiento.
- Mientras se ejecuta el seguimiento, haga lo que sea necesario para que se ejecute la consulta de ejecución lenta.
- Espere a que se complete la consulta y detenga el seguimiento.
- Para guardar la traza, haga clic con el botón derecho en el xml del plan en SQL Server Profiler y seleccione “Extraer datos de eventos …” 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: inspección de la caché de consultas
Si no puede ejecutar su consulta directamente y tampoco puede capturar un seguimiento del generador de perfiles, aún puede obtener un plan estimado inspeccionando la caché del plan de consultas SQL.
Inspeccionamos el caché del plan consultando los DMV de SQL Server. La siguiente es una consulta básica que enumerará todos los planes de consulta almacenados 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 solo a 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 XML del plan 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:
Debido a que hay tantos factores involucrados (que van desde la tabla y el esquema de índice hasta los datos almacenados y las estadísticas de la tabla), debe siempre Intente obtener un plan de ejecución de la base de datos que le interesa (normalmente la que está experimentando un problema de rendimiento).
No puede capturar un plan de ejecución para procedimientos almacenados cifrados.
planes de ejecución “reales” frente a planes de ejecución “estimados”
Un real El plan de ejecución es aquel en el que SQL Server realmente ejecuta la consulta, mientras que un estimado plan de ejecución SQL Server resuelve lo que haría hacer 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 sucedió realmente al ejecutar la consulta. Esto es esencial cuando se diagnostican problemas en los que las estimaciones de los servidores SQL están desactivadas (por ejemplo, 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 lo suficientemente digno de un libro (gratuito) por derecho propio.
Ver también:
- Conceptos básicos del plan de ejecución
- Permiso SHOWPLAN y lotes de Transact-SQL
- SQL Server 2008: uso de hash de consulta y hash de plan de consulta
- Análisis de la caché del plan de SQL Server
Además de la respuesta integral ya publicada, a veces es útil poder acceder al plan de ejecución de manera programática para extraer información. A continuación se muestra un código de ejemplo para esto.
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 Definición
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 StopCapture Definición
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 Plan de consultas estimado puedes presionar Ctrl + L o el siguiente botón.
- Para Plan de consulta real, puedes presionar Ctrl + M o el siguiente botón antes de ejecutar la consulta.
- Para Plan de consulta en vivo, (solo en SSMS 2016) use el siguiente botón antes de ejecutar la consulta.
