Last Known Actual Plan in SQL Server: Query Plan Stats

The last known actual plan is the plan of the latest run of a cached query, with real row counts. SQL Server 2019 and later can keep it for you. You read it from one view.

Why You Want It

A plan in the cache is an estimated plan. It shows what SQL Server expected when it compiled the query. To see what happened, you used to run the query again with the actual plan turned on. That is not always possible. The query can be a nightly job. It can run once a month. The slow run can be an hour old.

The last known actual plan closes that gap. SQL Server keeps the plan of the latest run next to the cached plan. The actual row counts are filled in. The view sys.dm_exec_query_plan_stats returns it. Compare estimated rows with actual rows, and you see where the estimate went wrong.

Turn It On for One Database

The feature is off by default. The setting LAST_QUERY_PLAN_STATS is a database scoped option, so it affects one database only. Trace flag 2451 does the same for the whole instance, and it changes every database on the server. Prefer the scoped option. Collecting the plan costs a little work on every run. Measure that cost before you turn the option on in a busy production database.

The demo creates a database named LastActualPlanDemo. It holds a table of 3,000 visits and a procedure that counts the visits of one kind. One visit in ten is a Tour, and the rest are a Walk. A small view wraps the query that reads the plan, so the demo can read it twice.

IF DB_ID(N'LastActualPlanDemo') IS NULL CREATE DATABASE LastActualPlanDemo;
GO
USE LastActualPlanDemo;
GO
DROP TABLE IF EXISTS dbo.Visits;
CREATE TABLE dbo.Visits (VisitID int IDENTITY(1,1) PRIMARY KEY, Kind varchar(10) NOT NULL, Minutes int NOT NULL);
INSERT INTO dbo.Visits (Kind, Minutes)
SELECT CASE WHEN n % 10 = 0 THEN 'Tour' ELSE 'Walk' END, n % 90
FROM (SELECT TOP (3000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS t;
GO
CREATE OR ALTER PROCEDURE dbo.CountVisits @Kind varchar(10)
AS
SELECT COUNT(*) AS Visits, AVG(Minutes) AS AvgMinutes FROM dbo.Visits WHERE Kind = @Kind;

CREATE OR ALTER VIEW dbo.LastPlanRows
AS
SELECT OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, cp.usecounts AS UseCount,
       qps.query_plan.value(N'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:RelOp[contains(@PhysicalOp,"Scan") or contains(@PhysicalOp,"Seek")]/p:RunTimeInformation/p:RunTimeCountersPerThread/@ActualRows)[1]', N'int') AS ActualRows,
       qps.query_plan.value(N'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:RelOp[contains(@PhysicalOp,"Scan") or contains(@PhysicalOp,"Seek")]/@EstimateRows)[1]', N'float') AS EstimatedRows
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 qps
WHERE st.dbid = DB_ID() AND st.objectid = OBJECT_ID(N'dbo.CountVisits');

The view starts from the cached plans. It uses OUTER APPLY for the new function, so a procedure with no last known actual plan still shows up. Now run the procedure once with the option off and read the plan.

EXEC dbo.CountVisits @Kind = 'Tour';

SELECT ObjectName, UseCount, ActualRows, EstimatedRows FROM dbo.LastPlanRows;

With the option off, the view returns only a shell. It carries the statement text, but no operators and no row counts. Now turn the option on. Changing it clears the cached plans of that database, so the next call compiles again.

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

Read the Row Counts

Call the procedure twice. The first call, Tour, builds the plan for about 300 rows. The second call, Walk, reuses the plan and returns 2,700 rows. Then read the view again.

EXEC dbo.CountVisits @Kind = 'Tour';

EXEC dbo.CountVisits @Kind = 'Walk';

SELECT ObjectName, UseCount, ActualRows, EstimatedRows FROM dbo.LastPlanRows;

The plan estimated 300 rows and found 2,700. It was built for Tour, and the last run was Walk. That gap is parameter sniffing, seen from the outside. In Management Studio, a query that returns the plan column shows a link. Click the XML, and the graphical plan opens with the same row counts on every operator.

The view is built for this demo. It reads the row counts of the first scan or seek in the plan, which fits a one statement procedure. A real plan has many operators, so for real work return qps.query_plan itself and open the XML. Filter on st.dbid = DB_ID(). On a large cache, collect the plan handles first and read the plans second. The function is slow when it runs for every cached plan.

When the Plan Is Missing

The view returns NULL when no plan is cached, and when the plan is too large to store. A NULL does not mean the query never ran. With the option off, only the shell comes back, as the first read showed. A query that has not run again since you turned the option on shows the same.

The plan is not complete. It carries the actual row counts, but not every runtime figure that a real actual plan has. Use it to find the operator that misjudged its rows. Use a real actual plan when you need more, such as the time spent in each operator.

A cached plan from the last run is also handy for a query that you did not write. You can see how it behaved without asking anyone to run it again. On SQL Server 2017 and earlier, the view does not exist, and two other tools come close. The view sys.dm_exec_query_statistics_xml shows the plan of a query that is still running. Query Store keeps plans over time, but not actual row counts.

You could argue that you can run the query again with the actual plan turned on. Sometimes you can. A nightly job or a monthly report does not wait for you. The view keeps the run that already happened.

What to Remember

Turn on LAST_QUERY_PLAN_STATS for the database you investigate, not for the whole server. Read the last known actual plan after the slow run. Compare the estimated and the actual rows. Turn the option off again when you are done.

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = OFF;

When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'LastActualPlanDemo') IS NOT NULL
BEGIN
    ALTER DATABASE LastActualPlanDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE LastActualPlanDemo;
END;

An estimate is not a result, it is a promise the last run can break.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.