Turn an Analyze Actual Execution Plan scenario into a hypothesis

An Analyze Actual Execution Plan scenario points to a difference that deserves investigation. Turning that signal into a hypothesis requires a proposed explanation, a prediction of what should change if it is correct, and a test that could contradict it.

Start from a specific observation

The query used in the previous entry filters orders through a local variable:

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;

Enable Include Actual Execution Plan with Ctrl+M, run the query, and open Execution plan. The Index Seek returns 136 rows, while the plan estimated 30000. That difference is the observation; it is not yet a cause.

A hypothesis is a provisional explanation that produces a testable prediction. Here we can state it as follows:

The estimate differs because the optimizer does not know the local variable value when it compiles the statement. If it compiles with the value used for this execution, the estimate should move closer to the 136 observed rows.

Compilation is the stage in which SQL Server turns the statement into a plan. The information available at that moment affects estimated cardinality and therefore the alternatives considered by the optimizer.

Test the information available at compile time

The second query preserves the filter, columns, grouping, and ordering. Its only difference is 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 requests a new plan for this execution. In this test it lets the optimizer consider the current value of @DesdePrueba; it is not presented as a permanent setting for the query.

Run both statements in the same batch with the actual plan enabled. In Execution plan, SSMS displays the plans one below the other.

1. 1
   Local variable with ordinary compilation

   The Index Seek returns 136 rows against an estimate of 30000.

2. 2
   Test with RECOMPILE

   The same access returns 136 rows and estimates 136 for the value used in this execution.

The prediction holds: when the value is available at compile time, the access estimate matches the observed rows. This result supports the hypothesis for this execution; it does not prove that RECOMPILE is the best solution for every execution or for different values.

Review the statistics hypothesis

Analyze Actual Execution Plan also mentions different or outdated statistics. That possibility needs its own evidence.

Statistics describe the distribution of values in one or more columns. The optimizer uses them to estimate how many rows a predicate may return. sys.dm_db_stats_properties shows when they were updated, how many rows they represent, how many were sampled, and how many modifications were recorded afterward.

Run this query and open 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
   Observed state

   The statistic represents 100000 rows, sampled all 100000, and records no later modifications in this test.

Filas reports how many rows the statistic represented when it was updated. FilasMuestreadas reports how many participated in its construction; matching counts mean that the histogram was built with a full scan. Modificaciones counts recorded changes since the update, not the age of the statistic or the quality of every estimate.

These values make an outdated statistic less likely to explain this immediate difference. They do not prove that the histogram represents every possible value accurately, nor do they rule out other distribution problems.

Check whether the performed work changed

A closer estimate does not automatically mean a faster query. Before running each of the two queries, enable read measurements with:

SET STATISTICS IO ON;

After running both statements, disable the option:

SET STATISTICS IO OFF;

Open Messages and compare the PedidoLaboratorio lines.

1. 1
   Original query

   It records one scan count and four logical reads.

2. 2
   Recompiled query

   It keeps one scan count and four logical reads.

The estimate changed in this execution, but the number of pages read did not. Both plans used an Index Seek and returned the same result. The evidence explains the cardinality difference; it does not show that recompilation produced a noticeable performance improvement.

Keep result and interpretation separate

The test can be recorded without jumping directly to a correction:

• Observation: the access returned 136 rows and estimated 30000 with a local variable.
• Hypothesis: the value was not available during ordinary compilation.
• Prediction: compiling with the current value would move the estimate toward 136.
• Result: the RECOMPILE test estimated and returned 136 rows.
• Additional evidence: the statistic was built from every row, records no later modifications, and both variants performed four logical reads.
• Limited conclusion: information available at compile time explains the observed difference in this execution; representative values and recompilation cost still need evaluation before choosing a permanent change.

What still needs to be tested

An operational decision would require repeating the measurement with dates that return few, many, and intermediate numbers of rows. Execution frequency, compilation CPU, plan stability, and behavior under the real workload would also need review.

OPTION (RECOMPILE), a parameterized procedure, dynamic SQL, or keeping the current plan may respond differently to those conditions. This entry does not choose among them; it establishes the evidence needed to compare them.

Next reasonable check

The next entry examines a Missing Index suggestion: what information it preserves, where it appears in SSMS, and which questions must be answered before accepting the proposed index.

Related topics

• Walk through Analyze Actual Execution Plan: locate the scenario that starts this investigation.
• Estimated rows and actual rows: understand the two quantities being compared.
• Reads with SET STATISTICS IO: interpret the four logical reads in the test.
• OPTION and query hints: review the scope of RECOMPILE and other hints.
• Review statistics: extend the check of age and modifications.