What Is In the Plan Cache and How to Look

The SQL Server plan cache stores reusable plans so the engine doesn’t compile every request from scratch. Inspect it to understand reuse and memory consumption, while remembering that it provides temporary evidence rather than permanent workload history.

Look at the Shape of the Cache

Start with a summary rather than retrieving every plan as XML. The cache contains different object types, including ad hoc and prepared work. Grouping by type shows where the space is going. It also keeps one large plan from distracting you from a larger pattern.

SELECT
    objtype, cacheobjtype,
    COUNT_BIG(*) AS cached_objects,
    SUM(CONVERT(bigint, size_in_bytes)) / 1048576.0 AS cache_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype, cacheobjtype
ORDER BY cache_mb DESC;

This measures the objects currently visible in the cache. It doesn’t show every query the instance has executed. Plans can leave for several reasons. Save a timestamp and the server startup time when you retain the result for comparison.

Find Single-Use Ad Hoc Plans

SELECT
    COUNT_BIG(*) AS single_use_ad_hoc_plans,
    SUM(CONVERT(bigint, size_in_bytes)) / 1048576.0 AS cache_mb
FROM sys.dm_exec_cached_plans
WHERE objtype = N'Adhoc'
  AND cacheobjtype = N'Compiled Plan'
  AND usecounts = 1;

Single-use ad hoc plans can occupy memory without delivering much reuse. Their presence isn’t automatically a fault. An occasional administrative query is ordinary work. Look at the total footprint and the pattern over time before deciding that the application needs a change.

Usecounts describes cache-object lookups and isn’t a universal execution counter for every plan type. Don’t use it as the total number of business requests. For completed query execution statistics, inspect the relevant statistics view and understand its own lifetime limits.

If the application embeds a different literal in every batch, parameterization can improve reuse. Keep parameter data types consistent too. A query that changes its text or parameter declaration unnecessarily can create more distinct cache entries than you expected.

Inspect Candidates Without Dumping Everything

SELECT TOP (20)
    cp.usecounts, cp.size_in_bytes,
    cp.plan_handle,
    st.text AS batch_text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype = N'Adhoc'
  AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

Read enough text to identify the source pattern. Store captures securely because literals can contain private information. The largest objects are candidates for review, not a list of things to delete. Determine which application or job produced them and whether reuse is expected.

Retrieving every full plan repeatedly can add unnecessary collection overhead. Narrow the candidate set before opening plan XML. Keep the query used for collection with the saved output so later readers know what was filtered out.

Understand What the Ad Hoc Option Does

SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = N'optimize for ad hoc workloads';

When enabled, optimize for ad hoc workloads stores a small compiled-plan stub for an eligible ad hoc batch’s first use. A later matching execution can lead to a full cached plan. The option targets wasted cache space from one-time work.

It doesn’t remove the initial compilation cost or parameterize unsafe dynamic SQL for you. It also means a full execution plan isn’t available from that first-use stub. Consider that diagnostic tradeoff when deciding whether the setting fits your workload.

Changing the setting applies to subsequent cache behavior. It doesn’t transform every existing cached plan immediately. Don’t clear the whole cache to make a screenshot of the setting look more persuasive. Observe its effect through an appropriate workload period.

Know Why Plans Disappear

A service restart clears the cache. Memory pressure can cause eviction, and configuration or schema changes can invalidate relevant plans. Recompilation creates another boundary for statistics. Explicit cache-clearing commands can remove evidence abruptly, which is one reason to avoid them as routine tuning.

A cached plan can also be replaced while you investigate it. Treat a missing handle as a lifetime issue before assuming the capture was wrong. If the question needs durable history, Query Store is usually the more suitable place to start when available and configured.

Plan cache size and churn can be symptoms of a workload pattern rather than a problem to solve alone. Connect the observations with compilation rates and application behavior. More reuse is useful only when the reused plans remain appropriate for the requests.

Change the Cause You Can Demonstrate

Compare a representative period before and after a targeted change. Keep query semantics unchanged while testing parameterization or application fixes. Record the edition and build because behavior can differ across versions. Use the diagnostic permissions required for your release rather than assuming every login sees the same information.

I use the cache as a working notebook with pages that can disappear. It is valuable while the evidence is there. Preserve what matters, make a focused decision, and use a durable capture when the investigation must survive a restart.

The plan cache is not an execution diary, it is a temporary collection of work the engine hopes to reuse.

This post was rewritten from scratch in September 2026. The original, published on 2011-11-19, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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