Recuperar el plan de ejecución almacenado

Una sentencia aparece entre las de mayor CPU, lecturas o duración. Para entender cómo SQL Server decidió ejecutarla, queremos recuperar el plan asociado que todavía se encuentra en caché.

Qué necesitamos distinguir

Compilar significa transformar la consulta en una estrategia ejecutable. Una recompilación vuelve a producir esa estrategia y puede reemplazar el plan conservado en caché.

El plan almacenado es la representación compilada que SQL Server conserva para reutilizar. No contiene las métricas reales de una ejecución concreta y puede desaparecer o ser reemplazado después de una recompilación.

Consulta

/*
    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

Parámetros o permisos

@TextoBuscado debe ser suficientemente específico para no revisar toda la caché sin necesidad. Reemplaza @IdentificadorPlan por el valor binario obtenido en el primer resultado; el literal incluido es solo un marcador. Se necesita VIEW SERVER STATE, o VIEW SERVER PERFORMANCE STATE desde SQL Server 2022. En SSMS puedes abrir la celda PlanXml para ver el plan gráfico.

Cómo leer el resultado

El plan muestra operadores, orden de ejecución, estimaciones, accesos a objetos, predicados y decisiones como paralelismo. Empieza por entender el flujo y compara filas estimadas con evidencia de una ejecución controlada cuando sea posible. Si PlanXml es NULL, el plan pudo salir de caché entre ambos pasos, la sentencia quizá no se almacena o el XML excede límites de representación. El identificador no debe conservarse como una referencia permanente.

Qué conclusión sí permite obtener

Permite examinar la estrategia compilada asociada a una sentencia cuyo plan permanece disponible y formular comprobaciones concretas sobre accesos, estimaciones y operadores.

Qué conclusión todavía no permite obtener

No es un plan de ejecución real con tiempos y filas observados. Tampoco demuestra por sí solo qué operador causó una demora ni incluye necesariamente planes internos de SQL dinámico o funciones llamadas.

Siguiente comprobación razonable

Obtén un plan real en una ejecución segura o usa telemetría existente para comparar estimaciones con filas observadas. Antes de crear un índice, contrasta el plan con las sugerencias de índices faltantes y con los índices actuales.

Buscar en SQL Server