Cached Plan Reuse: How to Tell if a Query Used a Cached Plan

Cached plan reuse means SQL Server compiled a query once and ran the later calls from the stored plan. The result set never says so. Three checks do: a plan property, the plan cache and a time comparison.

Read the Plan Property

Run a query with the actual execution plan switched on, then right-click the leftmost operator and open Properties. A query with parameters has a Parameter List. It shows two values for each parameter: the one the plan was compiled for and the one this run used. When the two differ, the plan came from an earlier call.

The OptimizerStatsUsage section lists the statistics the optimizer read when it built the plan. It says nothing about the cache. Compile time and cached plan size also describe how the plan was built. They stay the same on every reuse.

Build a Query That Gets Cached

The demo database has an orders table with an index on the customer. The setup clears the plan cache of this database only, so the first run must compile. The clear statement needs SQL Server 2019 or later. The query uses a parameter, because a parameterized statement is the easiest kind to reuse.

IF DB_ID(N'PlanReuseDemo') IS NULL CREATE DATABASE PlanReuseDemo;
GO
USE PlanReuseDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (OrderID int IDENTITY(1,1) PRIMARY KEY, CustomerID int NOT NULL, Amount decimal(10,2) NOT NULL);
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (20000) ABS(CHECKSUM(NEWID())) % 200 + 1, 10 + ABS(CHECKSUM(NEWID())) % 90
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID) INCLUDE (Amount);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Ask the Plan Cache

Each cached plan has a row in sys.dm_exec_cached_plans. Its usecounts column grows by one for every run. The row has no timestamp, so the query joins sys.dm_exec_query_stats, which records when the plan was created. The script notes the time before the call. A plan created before that moment was reused, and a plan created after it is new.

DECLARE @start datetime = GETDATE();
EXEC sys.sp_executesql N'SELECT COUNT(*) AS Orders, SUM(Amount) AS Total FROM dbo.Orders WHERE CustomerID = @c', N'@c int', @c = 7;
SELECT CASE WHEN qs.creation_time < @start THEN N'reused' ELSE N'new plan' END AS Verdict, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE st.dbid = DB_ID() AND cp.objtype = N'Prepared' AND st.text LIKE N'%CustomerID = @c%';

The first call after a cache clear compiles, so the verdict is a new plan with one use. Now run the same call for another customer. Only the parameter value changes.

DECLARE @start datetime = GETDATE();
EXEC sys.sp_executesql N'SELECT COUNT(*) AS Orders, SUM(Amount) AS Total FROM dbo.Orders WHERE CustomerID = @c', N'@c int', @c = 8;
SELECT CASE WHEN qs.creation_time < @start THEN N'reused' ELSE N'new plan' END AS Verdict, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = cp.plan_handle
WHERE st.dbid = DB_ID() AND cp.objtype = N'Prepared' AND st.text LIKE N'%CustomerID = @c%';

The plan was created before this call began, and usecounts moved to 2. That is cached plan reuse. The cache also holds an ad hoc entry for the batch text itself, with one use. That entry is why the query filters on Prepared. The cache views need VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

Run the second block again and usecounts climbs to 3, and then 4. The plan keeps its creation time, so the verdict stays reused. A recompile of a statement raises plan_generation_num in sys.dm_exec_query_stats, which is another sign that the plan changed under you.

See It in the Plan

Now take a third call and read its actual plan. Switching on SET STATISTICS XML returns the plan as XML after the results.

SET STATISTICS XML ON;
EXEC sys.sp_executesql N'SELECT COUNT(*) AS Orders, SUM(Amount) AS Total FROM dbo.Orders WHERE CustomerID = @c', N'@c int', @c = 9;
SET STATISTICS XML OFF;

The plan XML holds this line, which is the same Parameter List you read in the properties window.

<ColumnReference Column="@c" ParameterDataType="int" ParameterCompiledValue="(7)" ParameterRuntimeValue="(9)" />

The plan was compiled for customer 7, and this run used customer 9. The first call set the plan, and every later call borrowed it. The attribute RetrievedFromCache is true on both the first and the later call, so it does not answer the question. The check has a limit. A reused plan can show equal values when two calls use the same parameter. Equal values prove nothing, so confirm with the cache.

When a Plan Is Not Reused

A different query text is a different cache entry. Another SET option in the session can also make a second entry. A plan also leaves the cache after a restart or under memory pressure. A large change in statistics forces a recompile too.

You could argue that reuse is always a good thing. It saves compile time, but it can also hold a plan that suits one value and hurts another. When a query is fast for some customers and slow for others, look at the two values first. A compiled value far from the runtime value is the clue.

Save a Good Plan

A saved plan cannot be put back into the cache with a statement, for example after a restart. Three tools come close. The USE PLAN hint takes plan XML for one query. A plan guide attaches that hint without editing the query. Query Store can force a plan, and it keeps that choice inside the database across restarts. Query Store needs no hint in the query text.

What to Remember

Check cached plan reuse in the plan properties first. A compiled value that differs from the runtime value means an earlier call built the plan. Then confirm in the cache that the plan was created before the run and that its use count grew.

Remove the demo database when you finish.

USE master;
GO
ALTER DATABASE PlanReuseDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE PlanReuseDemo;

A query result is not proof of a fresh plan, it is the answer from whichever plan was waiting.

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