Measuring and Improving SQL Server Query Plan Cache Efficiency

The query plan cache hit ratio in SQL Server indicates the percentage of queries that are executed using an already generated and available execution plan stored in the SQL Server memory cache. If no suitable plan exists for a query submitted to SQL Server, the query optimizer must create a new plan, which consumes valuable resources and time—a situation known as a "cache miss".  This article though does not cover how SQL Server decides whether a suitable matching plan already exists for reuse. 

Some level of cache misses are expected, especially right after the SQL Server instance starts. However, as the system continues running and the cache fills, cache misses should become rare. A high plan cache hit ratio, typically 90% or above, is desirable and indicates efficient plan reuse. On a highly OLTP system I personally prefer to see that number even higher like 98% or even close to 100%, as a higher ratio means better performance due to reduced CPU and memory usage from plan recompilation.

Conversely, a low hit ratio, such as 75% or worse, may indicate that queries are not being optimized for reuse or that the plan cache size or overall system memory is not enough. You may need to review query patterns or consider increasing the plan cache size by adding more memory to the server (because there is no direct configuration option in SQL Server to explicitly set or increase the plan cache size).

The first step in tackling plan cache efficiency is to measure the plan cache hit ratio:

/*
This query calculates the Plan Cache Hit Ratio as a percentage 

in SQL Server. The Plan Cache Hit Ratio measures how often SQL 

Server is able to reuse execution  plans from the plan cache, 

rather than having to compile new ones. 

A higher ratio indicates better performance, as reusing plans saves 

CPU and memory resources.

How: 
1. The query pulls two values from the dynamic management 

   view sys.dm_os_performance_counters:

- Cache hit ratio: The number of times a cached plan was found and reused.
- Cache hit ratio base: The total number of cache lookups

2. It divides the hit count by the total lookups and multiplies by 

   100 to get a percentage.

3. The RTRIM(object_name) is used to ensure trailing spaces 

   in the object_name column do not affect the filter.

 Output:
    - Returns a single value: the plan cache hit ratio as a percentage.
    - A high value (close to 100%) means most query plans are being reused.
    - A low value may indicate frequent query recompilations, 

      which can impact performance.

*/

SELECT
    100 * 
    (
        -- Numerator: Get the current value of the 'Cache hit ratio' counter
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE 
            RTRIM(object_name) LIKE '%:Plan Cache'  -- Filter for the Plan Cache object
            AND counter_name = 'Cache hit ratio'    -- Select the 'Cache hit ratio' counter
            AND instance_name = '_Total'            
    ) 
    /
    (
        -- Denominator: Get the current value of the 'Cache hit ratio base' counter
        SELECT cntr_value
        FROM sys.dm_os_performance_counters
        WHERE 
            RTRIM(object_name) LIKE '%:Plan Cache'      
            AND counter_name = 'Cache hit ratio base'   -- Select the base counter for the ratio
            AND instance_name = '_Total'                
    ) AS [Plan cache hit ratio %];

Tips For Improving Plan Cache Hit Ratio

While the following is by no means a complete or exhaustive list, here are some steps you can take to improve plan reusability and, in turn, increase the plan hit ratio.

When Writing Queries:

• Use Stored Procedures as much as possible.  Their precompiled nature promotes plan reuse and reduces compilation overhead

• Avoid writing Ad-Hoc queries and minimize dynamic SQL. If unavoidable, use sp_executesql with parameters instead of EXEC to enable parameterization. Require or at least suggest application developers to utilize query parameterization feature in application code

• Standardize query formatting for consistent casing, spacing, and punctuation. Even trivial variations (e.g., extra spaces) generate new plan hashes.  SQL Server performs a binary comparison of the incoming query's text with cached queries. Any difference in whitespace, casing, comments, or literals will result in a different plan hash and prevent reuse

• Consider using a plan guide or a query hint to force parameterization for a specific ad hoc query that generates too many cached execution plans due to variations in the literal values each time it is executed.

Minimize Plan Recompilations

Yes, plan recompilations do factor into cached plan ratio calculations and will lower the overall plan cache hit ratio.

Here are the primary triggers or reasons for plan recompilations:

Trigger Type	Example/Description
Schema change	ALTER TABLE, DROP INDEX, etc.
Statistics update	UPDATE STATISTICS, auto stats update
SET options change	Changing ANSI_NULLS, etc.
sp_recompile	Manual plan removal for object
Query hint	OPTION (RECOMPILE), WITH RECOMPILE
Cache clearing	DBCC FREEPROCCACHE
Query text change	Any modification to the SQL text
Dependent object change	Altering tables/views referenced by procedures

Apart from those, when SQL Server experiences memory pressure—either because the plan cache itself grows too large (local memory pressure) or because the overall system is low on memory (global memory pressure)—it will proactively remove execution plans from the plan cache to free up memory.

For most systems, the most common reasons involve frequent (and justified) index rebuilds or reorganizations, as well as statistics updates (manual or automatic). This is because it is considered a performance best practice to regularly rebuild indexes and update statistics to keep them compact and current. To that end, we typically schedule a daily/nightly job for index and statistics maintenance.

When you modify, drop, or rebuild an index on a table, while SQL Server does not immediately remove related execution plans from the cache, it does mark them as invalid. The next time a query that references the changed table is executed, SQL Server detects the schema change, recompiles the query, and generates a new execution plan, which is then stored in the cache. So unless you have to, scheduled your index maintenance jobs during off peak hours.

In fact, Creating or dropping indexes can indirectly affect plan cache efficiency. If queries are frequently recompiled due to index changes or if many single-use plans are generated, this can lead to plan cache bloat, reducing cache efficiency and potentially degrading performance.

Same goes for the index statistics. SQL Server query optimizer, being 'cost-based', fundamentally relies on cardinality estimation, using per-attribute summary statistics (histograms) to generate efficient execution plans. When statistics are updated—especially if AUTO_UPDATE_STATISTICS is enabled—SQL Server typically invalidates existing cached plans that depend on those statistics. This triggers recompilation the next time the query runs, ensuring the new plan reflects the updated data distribution. If statistics are stale or missing, SQL Server may generate suboptimal plans, and these can remain in cache until statistics are refreshed.

Query Optimization

If you have a low plan cache hit ratio then you may also have poorly performing queries. There maybe things you can do to identify and improve those queries. 

You should also review the overall system configuration - including the database, instance, and server - for optimization. While application design typically falls outside the DBA's direct control (even though many DBAs have development experience), if you have thoroughly optimized everything within your scope and documented your efforts, it should not be difficult to make a strong case for an application redesign.

Resources:

sys.dm_exec_cached_plans

SQL Server Plan Cache Limits

Forced Parameterization

Can Forced Parameterization Go Wrong

Additional Queries:

-- Get top 10 queries by average elapsed time (in milliseconds)
-- Only include queries that have been executed at least 5 times
SELECT TOP 10
    SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(qt.TEXT)
              ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2)+1) as query_text,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_worker_time/1000 AS total_worker_time_ms,
    qs.total_elapsed_time/1000 AS total_elapsed_time_ms,
    (qs.total_elapsed_time / qs.execution_count) / 1000 avg_elapsed_time_ms,
    qs.last_execution_time ,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 4 
ORDER BY avg_elapsed_time_ms DESC;

-- Get size of the plan cache by plan type

;WITH cte_plan_cache_usage_by_obj_type AS 
(
	SELECT COALESCE(objtype, '-- ALL Plans') objtype,
           Sum(Cast(size_in_bytes AS BIGINT)) / 
		              1024 / 1024 size_mb
    FROM   sys.dm_exec_cached_plans
    GROUP  BY rollup ( objtype )
)
SELECT objtype,
       size_mb,
	   'percent' = 
		   ( size_mb * 100 ) / 

                       (SELECT size_mb
			FROM   cte_plan_cache_usage_by_obj_type
			WHERE  objtype = '-- ALL Plans'
		   ) 
FROM   cte_plan_cache_usage_by_obj_type
ORDER  BY size_mb DESC