Retrieve the stored execution plan

A sentence appears among those with the highest CPU, readings or duration. To understand how SQL Server decided to run it, we want to retrieve the associated plan that is still cached.

What we need to distinguish

Compilation transforms a query into an executable strategy. Recompilation produces that strategy again and may replace the plan retained in cache.

The stored plan is the compiled representation that SQL Server retains for reuse. It does not contain the actual metrics for a particular run and may disappear or be replaced after a rebuild.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Paso 1: localizar la sentencia y obtener el identificador del plan
DECLARE @TextoBuscado NVARCHAR(200) = N'%dbo.Pedido%';

SELECT TOP (20)
    qs.plan_handle AS IdentificadorPlan,
    qs.execution_count AS Ejecuciones,
    qs.last_execution_time AS UltimaEjecucion,
    SUBSTRING
    (
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    ) AS Sentencia
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE @TextoBuscado
ORDER BY qs.last_execution_time DESC;
GO

-- Paso 2: recuperar el XML del plan
DECLARE @IdentificadorPlan VARBINARY(64) = 0x0000000000000000;

SELECT
    qp.dbid AS IdBaseDatos,
    qp.objectid AS IdObjeto,
    qp.encrypted AS EstaCifrado,
    qp.query_plan AS PlanXml
FROM sys.dm_exec_query_plan(@IdentificadorPlan) AS qp;
GO

Parameters or permissions

@SearchText should be specific enough to not check the entire cache unnecessarily. Replace @PlanIdentifier with the binary value obtained in the first result; the included literal is just a marker. VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022, is required. In SSMS you can open the PlanXml cell to see the graphical plan.

How to read the result

The plan shows operators, execution order, estimates, object accesses, predicates, and decisions as parallelism. Start by understanding the flow and compare estimated queues with evidence of controlled execution when possible. If PlanXml is NULL, the plan may have exited the cache between both steps, the statement may not be stored, or the XML may exceed rendering limits. The identifier should not be retained as a permanent reference.

What this does establish

It allows you to examine the compiled strategy associated with a statement whose plan remains available and to formulate specific checks on accesses, estimates and operators.

What this still does not establish

It is not a real execution plan with observed times and queues. It also does not on its own prove which operator caused a delay nor does it necessarily include internal dynamic SQL plans or called functions.

Next reasonable check

Get a real plan in a safe run or use existing telemetry to compare estimates with observed rows. Before creating an index, check the plan against the missing index suggestions and the current indexes.

Search SQL Server