Convertir un escenario de Analyze Actual Execution Plan en una hipótesis

Un escenario de Analyze Actual Execution Plan señala una diferencia que merece investigación. Para convertir esa señal en una hipótesis necesitamos proponer una explicación, anticipar qué debería cambiar si fuera correcta y ejecutar una prueba que pueda contradecirla.

Partir de una observación concreta

La consulta utilizada en la entrada anterior filtra los pedidos a partir de una variable local:

DECLARE @Desde DATE = '2025-12-30';

SELECT
    p.Estado,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS ImporteTotal,
    AVG(p.Total) AS ImportePromedio
FROM dbo.PedidoLaboratorio AS p
WHERE p.FechaPedido >= @Desde
GROUP BY p.Estado
ORDER BY ImporteTotal DESC;

Activa Include Actual Execution Plan con Ctrl+M, ejecuta la consulta y abre Execution plan. El Index Seek entrega 136 filas, pero el plan había estimado 30000. La diferencia es la observación; todavía no es una causa.

Una hipótesis es una explicación provisional que produce una predicción comprobable. En este caso podemos plantearla así:

La estimación se separa porque el optimizador no conoce el valor de la variable local cuando compila la instrucción. Si compila con el valor utilizado en esta ejecución, la estimación debería acercarse a las 136 filas observadas.

La compilación es la etapa en la que SQL Server transforma la instrucción en un plan. La información disponible en ese momento influye en la cardinalidad estimada y, con ella, en las alternativas que compara el optimizador.

Probar la información disponible al compilar

La segunda consulta conserva el filtro, las columnas, la agrupación y el orden. La única diferencia es OPTION (RECOMPILE):

DECLARE @DesdePrueba DATE = '2025-12-30';

SELECT
    p.Estado,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS ImporteTotal,
    AVG(p.Total) AS ImportePromedio
FROM dbo.PedidoLaboratorio AS p
WHERE p.FechaPedido >= @DesdePrueba
GROUP BY p.Estado
ORDER BY ImporteTotal DESC
OPTION (RECOMPILE);

RECOMPILE pide crear un plan nuevo para esta ejecución. En esta prueba permite al optimizador considerar el valor actual de @DesdePrueba; no se presenta como una configuración permanente para la consulta.

Ejecuta ambas instrucciones en el mismo lote con el plan real activado. En Execution plan, SSMS muestra los dos planes uno debajo del otro.

1. 1
   Variable local con compilación ordinaria

   El Index Seek produce 136 filas frente a 30000 estimadas.

2. 2
   Prueba con RECOMPILE

   El mismo acceso produce 136 filas y estima 136 para el valor utilizado en esta ejecución.

La predicción se cumple: cuando el valor está disponible al compilar, la estimación del acceso coincide con las filas observadas. El resultado respalda la hipótesis para esta ejecución; no demuestra que RECOMPILE sea la mejor solución para todas las ejecuciones ni para valores distintos.

Revisar la hipótesis de las estadísticas

Analyze Actual Execution Plan también menciona estadísticas diferentes o desactualizadas. Esa posibilidad necesita evidencia propia.

Las estadísticas describen la distribución de valores de una columna o conjunto de columnas. El optimizador las utiliza para estimar cuántas filas puede devolver un predicado. sys.dm_db_stats_properties permite revisar cuándo se actualizaron, cuántas filas representan, cuántas se muestrearon y cuántas modificaciones se registraron después.

Ejecuta esta consulta y abre Results:

SELECT
    s.name AS Estadistica,
    sp.last_updated AS UltimaActualizacion,
    sp.rows AS Filas,
    sp.rows_sampled AS FilasMuestreadas,
    sp.modification_counter AS Modificaciones
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.PedidoLaboratorio')
  AND s.name = N'IX_PedidoLaboratorio_FechaPedido';

1. 1
   Estado observado

   La estadística representa 100000 filas, muestreó las 100000 y registra cero modificaciones posteriores en esta prueba.

Filas indica cuántas filas representaba la estadística cuando se actualizó. FilasMuestreadas indica cuántas participaron en su construcción; que ambas cantidades coincidan significa que el histograma se creó con un muestreo completo. Modificaciones cuenta cambios registrados desde la actualización, no la edad de la estadística ni la calidad de cada estimación.

Estos datos reducen la probabilidad de que una estadística desactualizada explique esta diferencia inmediata. No demuestran que el histograma represente con precisión todos los valores posibles ni descartan otros problemas de distribución.

Comprobar si cambió el trabajo realizado

Una estimación más cercana no equivale automáticamente a una consulta más rápida. Antes de ejecutar cada una de las dos consultas, activa las lecturas con:

SET STATISTICS IO ON;

Después de ejecutar ambas instrucciones, desactiva la opción:

SET STATISTICS IO OFF;

Abre Messages y compara las líneas de PedidoLaboratorio.

1. 1
   Consulta original

   Registra un conteo de exploración y cuatro lecturas lógicas.

2. 2
   Consulta recompilada

   Conserva un conteo de exploración y cuatro lecturas lógicas.

En esta ejecución cambió la estimación, pero no el número de páginas leídas. Ambos planes utilizaron un Index Seek y devolvieron el mismo resultado. La evidencia permite explicar la diferencia de cardinalidad; no permite afirmar que la recompilación produjo una mejora de rendimiento perceptible.

Separar resultado e interpretación

La prueba puede registrarse sin saltar directamente a una corrección:

• Observación: el acceso produjo 136 filas y estimó 30000 con una variable local.
• Hipótesis: el valor no estaba disponible durante la compilación ordinaria.
• Predicción: compilar con el valor actual acercaría la estimación a 136.
• Resultado: la prueba con RECOMPILE estimó y produjo 136 filas.
• Evidencia adicional: la estadística se construyó con todas las filas, no registra modificaciones posteriores y ambas variantes realizaron cuatro lecturas lógicas.
• Conclusión limitada: la información disponible al compilar explica la diferencia observada en esta ejecución; falta evaluar valores representativos y el costo de recompilar antes de elegir un cambio permanente.

Lo que todavía falta comprobar

Una decisión operativa necesitaría repetir la medición con fechas que devuelvan pocas, muchas y cantidades intermedias de filas. También habría que revisar frecuencia de ejecución, CPU de compilación, estabilidad del plan y comportamiento bajo la carga real.

OPTION (RECOMPILE), un procedimiento parametrizado, SQL dinámico o mantener el plan actual pueden responder de manera distinta a ese conjunto de condiciones. Esta entrada no selecciona una alternativa: establece qué evidencia necesitamos para compararlas.

Siguiente comprobación razonable

La siguiente entrada estudia una sugerencia Missing Index: qué información conserva, dónde aparece en SSMS y qué preguntas debemos resolver antes de aceptar el índice propuesto.

Relacionado con

• Recorrer Analyze Actual Execution Plan: ubica el escenario que inicia esta investigación.
• Filas estimadas y filas reales: explica las dos cantidades comparadas.
• Lecturas con SET STATISTICS IO: interpreta las cuatro lecturas lógicas de la prueba.
• OPTION y query hints: revisa el alcance de RECOMPILE y otros hints.
• Revisar estadísticas: amplía la comprobación de edad y modificaciones.