The query was slow earlier, but the cached estimate doesn't show its actual row flow. The last actual execution plan can retain that runtime evidence. SQL Server 2019 and later expose it through an opt-in database setting and a plan-cache function.
Enable the Last Actual Execution Plan Before the Event
LAST_QUERY_PLAN_STATS enables retention of the most recent runtime plan information for eligible cached plans originating in the database. It uses lightweight query profiling infrastructure. Turn it on before the execution you want to investigate.
Enabling it afterward doesn't reconstruct earlier operator counts. The setting provides future evidence, not a recorder that can rewind an incident which already passed.
I check the database setting first when somebody asks for yesterday's actual rows. Use an approved test database for the command below. The feature has a small profiling and retention cost, so evaluate it under your workload.
Record the original setting before changing it. Database scope makes a focused evaluation possible without assuming that every database on the instance needs the same capture choice.
SELECT name,value
FROM sys.database_scoped_configurations
WHERE name = N'LAST_QUERY_PLAN_STATS';
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;Produce a Plan You Can Find
Execute a tagged sample query after enabling capture. The comment makes its text easier to locate in the cache. A very simple query can return a reduced plan without all the operator details you expect.
Use a representative query with meaningful work for evaluation. The tiny sample below exercises catalog data without creating business tables or claiming any measured runtime result.
Keep the session's database context correct. The setting applies to work originating in that database. A query launched elsewhere against a three-part name needs careful interpretation.
Also note whether recompilation or another execution replaced the captured plan. The function keeps recent evidence tied to cached plan handles. It isn't a history table of every execution or a complete incident timeline.
SELECT o.type_desc,COUNT_BIG(*) AS ObjectCount
FROM sys.objects AS o
WHERE o.is_ms_shipped = 0
GROUP BY o.type_desc
/* last_plan_demo */;Join Cache Handles to the Last Actual Execution Plan
The sys.dm_exec_cached_plans view supplies plan handles. The sys.dm_exec_sql_text function returns the text, and sys.dm_exec_query_plan_stats returns the retained runtime plan. OUTER APPLY keeps a cache row visible even if the plan function supplies no useful plan.
That absence deserves interpretation. Capture must be enabled, the plan must remain cached, and the execution must provide the supported runtime information. In a test database with capture on, the tagged query came back with ActualRows values on its operators.
The inventory query uses a split search string so it doesn't find itself merely through its own literal. The required server-state permission differs by version. On SQL Server 2022 and later, relevant diagnostic views require VIEW SERVER PERFORMANCE STATE.
Use a properly authorized diagnostic account. Query text can contain sensitive literals. Keep that output within the approved operational review instead of attaching it to public reports.
DECLARE @Needle nvarchar(100) = N'last_' + N'plan_demo';
SELECT cp.plan_handle,cp.objtype,cp.usecounts,st.text,ps.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
OUTER APPLY sys.dm_exec_query_plan_stats(cp.plan_handle) AS ps
WHERE st.text LIKE N'%' + @Needle + N'%';
Compare Actual Rows With Estimates
Open the returned plan XML in SSMS. Inspect the first substantial difference between estimated and actual rows, then follow it upstream. An estimate error can affect join choice, memory grant, and downstream work.
Check predicates, parameters, and statistics around that point. Don't start with the largest displayed cost percentage. That percentage comes from estimated costs and doesn't directly report runtime duration.
I keep the parameter values with the plan when they are available under the diagnostic policy. A reused plan can serve different input distributions. The captured last execution then describes one request, while the cached estimate came from compilation assumptions.
That comparison is useful precisely because they are different. Avoid treating the saved runtime row count as the permanent size of the table or every future request.
Respect the Lightweight Detail Limits
The retained plan includes useful runtime row counts and can expose totals, memory information, and spill warnings for eligible executions. It doesn't provide a complete wait-statistics narrative. It also doesn't include the full per-thread timing detail available through richer profiling paths.
Don't infer those missing measurements from operator icons. The documented lightweight capture boundary is part of what the result means.
Total CPU or elapsed information where available isn't a substitute for every thread's timing or wait history. Parallel work needs particular care in interpretation. A slow request can spend time blocked or waiting outside the explanation supplied by row-flow evidence.
Keep session diagnostics or other approved capture alongside the plan when that is the question. One useful plan doesn't need to pretend it contains every possible measurement.
Know When the Cache Loses the Last Actual Execution Plan
Eviction, service restart, or plan changes can remove the handle and its retained evidence. A later execution replaces the last runtime information. That makes this feature useful for prompt investigation, with clear retention limits.
Save the last actual execution plan locally when needed under the diagnostic policy. Don't keep refreshing the query during a review and assume you are still looking at the incident's original request.
Which execution does the saved plan actually describe? Ask that before drawing a conclusion. If a later fast request ran with different parameters, it can replace the slow request's evidence.
The most recent receipt is useful, but it doesn't become last night's receipt because you need it. Capture timing and cache identity belong with every explanation of the result.
Choose Another Capture for Another Question
Query Store preserves historical plans and aggregated runtime information over intervals. It is a better starting point for regression history, but its stored plan isn't a full per-execution actual plan. An actual execution plan collected in SSMS is useful for a controlled reproduction. It gives the runtime evidence from the request you deliberately executed, with the overhead of that chosen capture path.
Use the last actual execution plan when recent eligible work remains in cache and operator row flow answers the question. Add historical or detailed diagnostics when the problem needs them. Preserve the setting, handle, input context, and capture time.
That makes the evidence reviewable. A recent runtime plan describes actual work. Respect its limits instead of assigning it a history it never recorded.
Related reading on this blog: Why Query Cost Percentages in a Plan Mislead You and Is Query from Cache? Execution Plan Property.
A retained runtime plan is not an execution archive, it is recent evidence attached to a cached plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
