Plan Cache Size in SQL Server: List Every Cached Plan

Plan cache size is the memory each cached query plan uses. One query lists every plan with its size, its text and its execution count. The answer comes from three views, and they report two different sizes.

Two Sizes for One Plan

When SQL Server compiles a query, it keeps the plan in memory so the next call can reuse it. The plan cache holds those plans. Each plan has a size, and SQL Server reports it in two places.

The view sys.dm_exec_cached_plans reports size_in_bytes, the size of the whole cache entry. The memory object behind the entry reports the size of the plan itself. That second number matches the Cached plan size in the plan properties in Management Studio. The plan XML carries it too, in the attribute CachedPlanSize, in kilobytes.

The demo shows both. The first script creates a database named PlanCacheListDemo with one small table and one procedure. Run it on a test server.

IF DB_ID(N'PlanCacheListDemo') IS NULL CREATE DATABASE PlanCacheListDemo;
GO
USE PlanCacheListDemo;
GO
DROP TABLE IF EXISTS dbo.Plants;
CREATE TABLE dbo.Plants (PlantID int IDENTITY(1,1) PRIMARY KEY, PlantName nvarchar(40) NOT NULL, Shelf int NOT NULL);
INSERT INTO dbo.Plants (PlantName, Shelf) VALUES (N'Basil', 1), (N'Mint', 2), (N'Sage', 1), (N'Thyme', 3);
GO
CREATE OR ALTER PROCEDURE dbo.ListPlantsOnShelf @Shelf int
AS
SELECT PlantID, PlantName FROM dbo.Plants WHERE Shelf = @Shelf;

The next script fills the cache. Each batch is separate, so each one is cached on its own. The procedure runs three times. Two queries differ only in a text value. A parameterized call comes last.

EXEC dbo.ListPlantsOnShelf @Shelf = 1;
GO
EXEC dbo.ListPlantsOnShelf @Shelf = 2;
GO
EXEC dbo.ListPlantsOnShelf @Shelf = 3;
GO
SELECT PlantName FROM dbo.Plants WHERE PlantName = N'Basil';
GO
SELECT PlantName FROM dbo.Plants WHERE PlantName = N'Mint';
GO
EXEC sys.sp_executesql N'SELECT COUNT(*) FROM dbo.Plants WHERE Shelf = @s', N'@s int', @s = 1;
GO
EXEC sys.sp_executesql N'SELECT COUNT(*) FROM dbo.Plants WHERE Shelf = @s', N'@s int', @s = 2;

List the Plans With Size, Text and Use Count

The listing query reads the cache for the current database only. It joins the entry to its memory object, so both sizes appear side by side. The text column keeps the first 60 characters on one line. A filter keeps the setup statements and this query itself out of the list. To list every plan of the database, delete the line that starts with AND st.text in each query. Reading these views needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

SELECT cp.objtype AS PlanType,
       cp.cacheobjtype AS CacheType,
       cp.usecounts AS UseCount,
       cp.size_in_bytes / 1024 AS EntryKB,
       mo.pages_in_bytes / 1024 AS PlanKB,
       LEFT(REPLACE(REPLACE(st.text, NCHAR(13), N' '), NCHAR(10), N' '), 60) AS QueryText
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_os_memory_objects AS mo ON mo.memory_object_address = cp.memory_object_address
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID()
  AND st.text LIKE N'%Plants%' AND st.text NOT LIKE N'%dm_exec_cached_plans%' AND st.text NOT LIKE N'%INSERT INTO%'
ORDER BY cp.objtype, cp.usecounts DESC, QueryText;

Read the table from the bottom. The procedure plan was used three times, once for each call. The parameterized call used one plan twice, even though the value changed. The two text queries show something else. SQL Server saw that they differ only in a literal and shared one prepared plan. Its UseCount is 2. Each text also left a small ad hoc entry of its own, with a use count of 1.

The two size columns differ on every row. EntryKB is larger than PlanKB, because the entry carries more than the plan. Judge the cost of a plan by PlanKB, which is the number the plan properties show. Add EntryKB when you add up what the cache holds.

The next query reads the size from the plan XML and compares it with the memory object. It looks at the procedure and the prepared plans, which are the ones that hold a plan.

SELECT cp.objtype AS PlanType,
       cp.size_in_bytes / 1024 AS EntryKB,
       mo.pages_in_bytes / 1024 AS PlanKB,
       qp.query_plan.value(N'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (//p:QueryPlan/@CachedPlanSize)[1]', N'int') AS PlanSizeInXml,
       LEFT(REPLACE(REPLACE(st.text, NCHAR(13), N' '), NCHAR(10), N' '), 40) AS QueryText
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_os_memory_objects AS mo ON mo.memory_object_address = cp.memory_object_address
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID() AND cp.objtype IN (N'Proc', N'Prepared')
  AND st.text LIKE N'%Plants%' AND st.text NOT LIKE N'%dm_exec_cached_plans%' AND st.text NOT LIKE N'%INSERT INTO%'
ORDER BY cp.objtype, QueryText;

PlanKB and PlanSizeInXml agree on every row. That is the figure to compare when someone asks for the size of one plan.

What the Columns Do Not Tell You

The use count starts when the plan enters the cache. A recompile, a memory clean-up or a restart resets it. A high count means the plan is popular, not that it is good. Pair the list with timing data before you decide to tune anything.

The old version of this query also returned the plan itself. Add cp.plan_handle to the list. After you filter, add OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp and return qp.query_plan. A click on the XML in Management Studio opens the plan.

Sort the list by EntryKB, largest first, when you want to know where the plan cache size goes. Many large entries with a use count of 1 are single-use ad hoc plans. Those plans fill memory and never pay it back. Parameterized queries and procedures avoid that waste.

Plans that no longer fit in memory leave the cache. The list shows what is cached now, not everything that ran. Some servers use the option optimize for ad hoc workloads. It caches a small stub on the first run of an ad hoc query. A stub has no plan inside it, so the plan column is NULL until the second run. The demo does not show this, because the setting belongs to the server.

The cache also does not know who ran a query. A plan is shared by every login that runs the same text with the same settings. To tie a statement to a login, capture it with an Extended Events session that records the user name.

You could argue that Query Store is the better source, because it survives a restart. It is. It also needs setup and storage. The cache query needs no setup and no storage. It does cost work on a big cache. The first query is the cheap one. The second query reads the plan XML for every plan it reaches, so filter first on a large server.

What to Remember

Plan cache size comes in two sizes. Use PlanKB for one plan and EntryKB for the memory the cache holds. Read the use count together with the plan type, because shared plans and small stubs distort a quick count. Filter by database and by text, so the list stays short.

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

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

A cached plan is not free memory, it is a size you can read.

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