SQL Server has plenty of cached plans, but that does not tell you who uses the memory. Breaking down plan cache memory by store and object type gives you a more useful picture. Start with the accounting, then investigate reuse and the workload that creates the plans.
Break Plan Cache Memory Into Its Stores
The plan cache contains different categories of cached work. CACHESTORE_SQLCP holds SQL plans associated with ad hoc and prepared statements. CACHESTORE_OBJCP holds object plans such as stored procedures. CACHESTORE_PHDR contains bound-tree information. Their totals help explain where memory sits, but they do not identify a guilty application by themselves.
I check the categories first when someone says the cache is too large. The next query returns pages_kb and entries_count from the cache counters. These values describe current cache-store accounting. They change as plans enter and leave the cache, so capture a timestamp with the result.
Avoid treating every entry as a separately useful business query. Cache structures, contexts, and object types differ. The purpose of this first result is comparison within the instance and across comparable workload windows. Memory is expensive, but a cached plan is not automatically an unwanted guest.
SELECT SYSUTCDATETIME() AS CapturedAt,type,name,pages_kb,entries_count,
CONVERT(decimal(18,2),pages_kb/1024.0) AS CacheMB
FROM sys.dm_os_memory_cache_counters
WHERE type IN ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')
ORDER BY pages_kb DESC;Separate plan cache memory from the other allocations before comparing it with a server-wide memory total.
Compare Clerks Without Adding Everything Together
Memory clerks describe SQL Server memory allocation through another accounting view. Comparing relevant clerk types with cache counters helps confirm the general pattern. The views do not promise identical totals or perfectly matching sampling times. Their scope and timing differ.
The next query groups clerk pages by type. Do not add these totals to cache-store totals and call the sum overall cache memory. That would mix overlapping perspectives. Keep the results beside each other and investigate large changes in a particular category.
I also compare these figures with overall memory pressure, workload activity, and instance limits. A large cache on a healthy busy server can be useful. A shrinking cache during sustained pressure tells a different story. What changed in the application's query submissions or workload? A single snapshot answers where memory sits now, not why it accumulated there.
SELECT type,SUM(pages_kb) AS ClerkPagesKB,
CONVERT(decimal(18,2),SUM(pages_kb)/1024.0) AS ClerkMB
FROM sys.dm_os_memory_clerks
WHERE type IN ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')
GROUP BY type
ORDER BY ClerkPagesKB DESC;Rank Plan Cache Memory by Object Type
sys.dm_exec_cached_plans exposes individual cached-plan entries. Grouping size_in_bytes by objtype reveals whether ad hoc, prepared, or object plans dominate the visible plan entries. Cast to bigint before summing so the arithmetic is appropriate for a large cache.
The result includes both entry counts and memory. Many small plans and a few large plans need different follow-up. Check the byte total before concluding that a large entry count explains the pressure. Object type also describes how SQL Server cached the plan, not whether the query was well designed.
The last column highlights entries whose current usecounts equals one. This is a candidate indicator, not a precise lifetime execution count. Prepared plans and other cache behaviors require care when interpreting reuse. Save the output and inspect representative entries next. The query provides evidence for that inspection rather than a list of things to delete.
SELECT objtype,COUNT_BIG(*) AS PlanEntries,
SUM(CONVERT(bigint,size_in_bytes)) AS PlanBytes,
SUM(CASE WHEN usecounts=1 THEN CONVERT(bigint,size_in_bytes) ELSE 0 END) AS SingleUseBytes
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY PlanBytes DESC;
Find the Statements Behind the Large Category
If ad hoc entries dominate, inspect whether the application sends many literal variations of the same logical request. Different constants can create many cached texts and plans. Inconsistent session settings or database contexts also divide reuse. Do not assume literals are the only explanation.
The next query returns a bounded set of large single-use ad hoc entries. Read the statement text in a trusted administrative context. Query text can contain sensitive values, so retain only the evidence your investigation requires. The TOP clause keeps the result manageable, but ordering still examines the relevant cache entries.
Look for a repeatable pattern: the same statement shape with different values, or completely unrelated reporting requests. Parameterizing the first pattern addresses its cause. The second pattern needs a different discussion about query generation and frequency. One legitimate one-time administrative query is not evidence that the application has a cache problem.
SELECT TOP (20) cp.size_in_bytes,cp.usecounts,st.dbid,st.text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype='Adhoc' AND cp.usecounts=1
ORDER BY cp.size_in_bytes DESC;Choose Reuse Changes With Their Tradeoffs
Consistent parameterized statements help plans be reused. The correct parameter types and lengths matter too. A reuse change should preserve query semantics and handle data skew appropriately. One reused plan that performs badly for important parameter values is not a complete improvement.
The optimize for ad hoc workloads setting stores a small stub for the first execution of an eligible ad hoc batch rather than its full plan. That can reduce memory devoted to unreused ad hoc work. It does not remove the initial compilation or repair the application's query design. Review the evidence before changing an instance-wide setting.
Use representative workload windows to compare the result. Track cache categories, compilation behavior, and important query performance together. A smaller cache accompanied by excessive compilation is not automatically better. The goal is productive memory use and reliable execution, with the change focused on the workload that caused the waste.
Leave Cache Eviction to a Supported Diagnosis
SQL Server shrinks cache stores under memory pressure. Cache size also changes after restarts, recompilations, and invalidations. Record those conditions when comparing snapshots. Otherwise, two samples describe different lifetimes and invite an incorrect conclusion about growth.
DBCC FREEPROCCACHE removes cached plans and forces subsequent work to compile again. Clearing the cache in production is not a fix for excessive plan cache memory. It erases useful evidence and creates a different workload problem while the plans return. Do not schedule cache clearing as routine memory housekeeping.
Revisit the same breakdown after a tested reuse change. Check whether single-use bytes declined during comparable activity and whether query behavior remained acceptable. Keep the cache counters, clerk comparison, and plan-type totals as complementary evidence. The investigation ends with an understood workload pattern, not merely an empty cache screenshot.
Related reading on this blog: Removing a Bad Plan From Cache Without Clearing Everything and When to Turn On Optimize for Ad Hoc Workloads?.
A full plan cache is not a diagnosis, it is memory use that needs context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
