Understanding SQL Server’s Query Plan Cache

by

The query plan cache (also called the plan cache or procedure cache) is an area of SQL Server’s memory that stores compiled execution plans for queries and stored procedures. When you execute a query, SQL Server compiles it into an execution plan (basically, a set of instructions for retrieving and processing data). Now, instead of recompiling that plan every time the same query runs, SQL Server stores it in the plan cache for reuse.

This caching mechanism significantly improves performance. Compiling a query plan requires CPU time and resources. The optimizer must analyze statistics, evaluate indexes, consider join orders, and make numerous decisions about the most efficient execution strategy. By caching plans, SQL Server avoids repeating this work for queries that execute repeatedly.

How the Plan Cache Works

When you execute a query, SQL Server first checks if a matching plan already exists in the cache. If it finds one, it reuses that plan immediately. If not, it compiles a new plan, executes the query, and stores the plan in the cache for future use.

The matching process looks at the query text and other factors like database context, SET options, and schema versions. Queries must match exactly for SQL Server to reuse a cached plan. Even differences in whitespace, capitalization, or comments can prevent plan reuse in some cases.

What Gets Cached

The plan cache stores several types of execution plans:

  • Ad-hoc queries – One-time SQL statements submitted directly by applications or users. Each unique query text gets its own plan.
  • Prepared statements – Parameterized queries submitted through APIs like ADO.NET or JDBC. These tend to cache more efficiently because parameter values don’t affect the cached plan.
  • Stored procedures – Compiled once when first executed, then reused for all subsequent executions. This is one of the main performance benefits of stored procedures.
  • Triggers – Compiled and cached similarly to stored procedures.
  • User-defined functions – Scalar and table-valued functions are compiled and cached when first called.

Viewing the Plan Cache

To see what’s currently in the plan cache:

SELECT 
    objtype,
    cacheobjtype,
    usecounts,
    size_in_bytes,
    LEFT(text, 100) AS query_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
ORDER BY usecounts DESC;

The usecounts column shows how many times each plan has been reused. Plans with high use counts are good candidates for optimization since they execute frequently.

Plan Cache Size and Memory Pressure

The plan cache uses a portion of SQL Server’s buffer pool memory. SQL Server dynamically adjusts the plan cache size based on memory pressure. When the server needs memory for other operations, it can remove plans from the cache to free up space.

Plans are removed using a cost-based algorithm that considers factors like:

  • How expensive the plan was to compile
  • How frequently it’s been used
  • How recently it was used

Expensive plans that are used frequently are less likely to be evicted than cheap plans that haven’t been used recently.

To see current plan cache memory usage:

SELECT 
    type,
    name,
    pages_kb / 1024.0 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'CACHESTORE_PHDR')
ORDER BY pages_kb DESC;

This query shows memory usage for different plan cache stores. In this case we check the following:

  • CACHESTORE_SQLCP contains ad-hoc SQL and prepared statement plans
  • CACHESTORE_OBJCP contains compiled objects like stored procedures and triggers, and
  • CACHESTORE_PHDR contains algebraized trees for views and derived tables.

The results show how much memory each cache store is consuming, helping you identify whether plan cache memory usage is excessive or where most cached plans are stored.

Parameter Sniffing and the Plan Cache

One challenge with plan caching is parameter sniffing. When SQL Server compiles a plan for a parameterized query, it examines the first set of parameter values it receives and creates a plan optimized for those specific values. This plan is then cached and reused for all subsequent executions (even if later parameter values have very different characteristics).

For example, if the first execution searches for a common status value that returns 10,000 rows, the optimizer might choose a table scan. But if later executions search for rare status values that return 10 rows, that same table scan plan is likely to be very inefficient. The cached plan was optimized for the first set of parameters, not the current ones.

Clearing the Plan Cache

It’s possible to clear plans from the cache. You might need to do this after updating statistics, adding indexes, or troubleshooting parameter sniffing issues.

To clear the entire plan cache for all databases:

DBCC FREEPROCCACHE;

Warning: This removes all cached plans and forces recompilation for every query, which can cause a temporary performance impact as plans are rebuilt.

You can use the following to prevent the information message from being displayed:

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

To clear the plan cache for a specific database:

DECLARE @database_id INT = DB_ID('YourDatabaseName');
DBCC FLUSHPROCINDB(@database_id);

To remove a specific plan:

DBCC FREEPROCCACHE(0x06000100B78E1B0250899A2E0400000001000000000000000000000000000000000000000000000000000000);

Replace the hex value with the actual plan_handle from sys.dm_exec_cached_plans.

Plan Cache vs Query Store

All this talk about caching/storing query plans might have you wondering whether this has anything to do with Query Store. But the plan cache and Query Store are two different things, and serve different purposes.

  • The plan cache is a temporary memory structure focused on performance. It stores plans for immediate reuse but loses them when SQL Server restarts or when memory pressure forces eviction.
  • Query Store, introduced in SQL Server 2016, provides persistent storage of query plans and execution statistics on disk. It retains historical data even after restarts and allows you to compare plan performance over time.

You can think of the plan cache as short-term memory for fast execution, while Query Store is long-term storage for analysis and troubleshooting. Both work together. Queries use cached plans for execution while Query Store separately records historical performance data.

Common Plan Cache Issues

Here are some common issues you may occasionally run into:

  • Plan cache bloating – Applications that generate unique query text for every execution (concatenating values into queries instead of using parameters) fill the cache with single-use plans. This wastes memory and reduces the benefit of caching.
  • Poor plan reuse – Queries that should share plans but don’t, often due to differences in SET options, database context, or minor text variations.
  • Stale plans – Cached plans that were optimal when compiled but become suboptimal after data distribution changes or index additions. Clearing the cache forces recompilation with current statistics.
  • Parameter sniffing problems – Cached plans optimized for one set of parameters that perform poorly with different parameters.

Optimizing Plan Cache Usage

Here are some tips to help you get the most benefit from plan caching:

  • Use parameterized queries instead of concatenating values into SQL text. This allows plan reuse across different parameter values.
  • Keep SET options consistent across connections to enable plan sharing.
  • Use stored procedures for frequently executed operations, as they cache efficiently and compile once.
  • Monitor plan cache metrics to identify queries with low use counts that might indicate poor parameterization.
  • Update statistics regularly so that cached plans are based on current data distributions.

The plan cache is one of SQL Server’s core performance features. Understanding how it works helps you write queries that cache effectively and troubleshoot situations where caching isn’t working as expected.