Search This Blog

Tuesday, June 24, 2025

Measuring and Improving SQL Server Query Plan Cache Efficiency

Measuring and Improving SQL Server Query Plan Cache Efficiency

A plan-cache hit ratio of 99% looks reassuring. Before calling that good news, though, I want to know what the server is doing with those reused plans.

Improving plan-cache efficiency is about making better use of SQL Server’s CPU and memory: reducing unnecessary compilation work while avoiding a cache filled with plans that are rarely reused. Reusing suitable plans saves compilation resources, and reducing the memory footprint of single-use plans can help relieve memory pressure.

But reuse alone is not the whole story. A reused plan is not necessarily an efficient plan for every execution, so the goal is better workload performance, not just a better-looking percentage.

Let’s look at what the plan-cache hit ratio actually measures, how to collect useful numbers, and which changes are worth considering when the evidence points to a problem. Along the way, we’ll connect plan reuse, compilation overhead, and cache memory usage to the outcome that matters: how well the workload performs.


What the plan-cache hit ratio actually tells us

Cache-hit ratio is the ratio between cache hits and cache lookups for the selected cache instance. When SQL Server looked in this cache, how often did it find a match?

That is not the same as saying, “This percentage of application queries ran without compilation,” especially when looking at performance counter _Total, which includes more than SQL execution plans. These are the three instances:

  • SQL Plans: Ad hoc, auto-parameterized, and prepared SQL plans. 
  • Object Plans: Plans associated with stored procedures, functions, and triggers. 
  • _Total: All cache-instance types, including categories like bound trees and temporary-table-related cache information.
I would not use 90%, 95%, or 99% as a universal pass/fail threshold. Instead, ask if the number has changed from the workload’s normal behavior, whether compilation activity has changed with it, and whether users are actually experiencing slower queries.


Start with a quick measurement

Start with this accumulated-counter view. It is a useful first look, but it is not a measurement of just the last few seconds.

;WITH CacheCounters AS
(
    SELECT
        RTRIM(object_name) AS object_name,
        RTRIM(instance_name) AS cache_instance,
        MAX(CASE WHEN counter_name = N'Cache hit ratio'
                 THEN cntr_value END) AS hits,
        MAX(CASE WHEN counter_name = N'Cache hit ratio base'
                 THEN cntr_value END) AS lookups
    FROM sys.dm_os_performance_counters
    WHERE RTRIM(object_name) LIKE N'%:Plan Cache'
      AND instance_name IN (N'_Total', N'SQL Plans', N'Object Plans')
      AND counter_name IN (N'Cache hit ratio', N'Cache hit ratio base')
    GROUP BY object_name, instance_name
)
SELECT
    object_name,
    cache_instance,
    hits,
    lookups,
    CAST(100.0 * hits / NULLIF(lookups, 0) AS decimal(9, 2))
        AS accumulated_hit_ratio_pct
FROM CacheCounters
ORDER BY object_name, cache_instance;
GO


The 100.0 keeps the calculation from using integer-only division, and NULLIF protects against division by zero. If the denominator is zero, the result is NULL, not a meaningful 0% hit ratio.

For troubleshooting, I would not stop here. An accumulated ratio can hide a change that began during the current incident, so the next question is: “What happened during the interval I actually care about?”

Measure recent activity, not just accumulated totals

The raw values for /sec counters in sys.dm_os_performance_counters are accumulated counts; to calculate a rate, take two samples and divide the difference by elapsed seconds. For a recent cache-hit ratio, use the change in hits divided by the change in lookups over that same interval, rather than subtracting two accumulated percentages.

This sampler waits approximately 15 seconds and returns two result sets: recent cache-hit ratios, followed by batch and compilation rates. It only reads system DMVs and stores its samples in local table variables; it does not clear the cache or change configuration.

DECLARE @Samples TABLE
(
    sample_no tinyint,
    object_name nvarchar(128),
    counter_name nvarchar(128),
    instance_name nvarchar(128),
    counter_value bigint
);

DECLARE @SampleNo tinyint = 1;
DECLARE @StartTime datetime2(3), @EndTime datetime2(3);

WHILE @SampleNo <= 2
BEGIN
    IF @SampleNo = 1
        SET @StartTime = SYSUTCDATETIME();
    ELSE
        SET @EndTime = SYSUTCDATETIME();

    INSERT INTO @Samples
    SELECT @SampleNo, RTRIM(object_name), RTRIM(counter_name),
           RTRIM(instance_name), cntr_value
    FROM sys.dm_os_performance_counters
    WHERE
        (
            RTRIM(object_name) LIKE N'%:Plan Cache'
            AND instance_name IN
                (N'_Total', N'SQL Plans', N'Object Plans')
            AND counter_name IN
                (N'Cache hit ratio', N'Cache hit ratio base')
        )
        OR
        (
            RTRIM(object_name) LIKE N'%:SQL Statistics'
            AND counter_name IN
                (N'Batch Requests/sec', N'SQL Compilations/sec',
                 N'SQL Re-Compilations/sec')
        );

    IF @SampleNo = 1
        WAITFOR DELAY '00:00:15';

    SET @SampleNo += 1;
END;

DECLARE @Seconds decimal(19, 3) =
    DATEDIFF(millisecond, @StartTime, @EndTime) / 1000.0;

DECLARE @Deltas TABLE
(
    object_name nvarchar(128),
    counter_name nvarchar(128),
    instance_name nvarchar(128),
    delta_value bigint
);

INSERT INTO @Deltas
SELECT
    s2.object_name, s2.counter_name, s2.instance_name,
    s2.counter_value - s1.counter_value
FROM @Samples AS s1
JOIN @Samples AS s2
  ON s2.object_name = s1.object_name
 AND s2.counter_name = s1.counter_name
 AND s2.instance_name = s1.instance_name
 AND s2.sample_no = 2
WHERE s1.sample_no = 1;

-- Result 1: recent cache-hit ratios.
;WITH CacheDeltas AS
(
    SELECT object_name, instance_name,
        MAX(CASE WHEN counter_name = N'Cache hit ratio'
                 THEN delta_value END) AS hits,
        MAX(CASE WHEN counter_name = N'Cache hit ratio base'
                 THEN delta_value END) AS lookups
    FROM @Deltas
    WHERE object_name LIKE N'%:Plan Cache'
    GROUP BY object_name, instance_name
)
SELECT
    @Seconds AS elapsed_seconds,
    object_name,
    instance_name AS cache_instance,
    hits AS interval_hits,
    lookups AS interval_lookups,
    CASE WHEN hits >= 0 AND lookups > 0 AND hits <= lookups
         THEN CAST(100.0 * hits / NULLIF(lookups, 0)
                   AS decimal(9, 2))
    END AS interval_hit_ratio_pct,
    CASE
        WHEN hits IS NULL OR lookups IS NULL
            THEN N'Missing counter; investigate'
        WHEN hits < 0 OR lookups < 0 OR hits > lookups
            THEN N'Reset or inconsistent sample; resample'
        WHEN lookups = 0
            THEN N'No lookups; ratio is undefined'
        ELSE N'Compare with workload baseline'
    END AS interpretation
FROM CacheDeltas
ORDER BY object_name, instance_name;

-- Result 2: recent batch and compilation rates.
SELECT
    @Seconds AS elapsed_seconds,
    counter_name,
    delta_value AS interval_events,
    CASE WHEN delta_value >= 0
         THEN CAST(delta_value / NULLIF(@Seconds, 0)
                   AS decimal(19, 2))
    END AS events_per_second
FROM @Deltas
WHERE object_name LIKE N'%:SQL Statistics'
ORDER BY counter_name;
GO


Treat 15 seconds as a quick diagnostic sample, not a workload baseline. Repeat the collection during representative periods; if counters are missing or the sample is inconsistent, investigate or resample rather than interpreting missing data as zero activity.

Here is how to read the second result set:

  • Batch Requests/sec: The number of incoming Transact-SQL batches per second, not the number of individual statements. 
  • SQL Compilations/sec: How often SQL Server enters the compilation code path, including compilations caused by statement-level recompilation. 
  • SQL Re-Compilations/sec: The number of statement recompilations triggered per second.

Do not add compilations and recompilations together: the compilation counter already includes statement-level recompilations. I would compare these rates with the workload’s normal batch activity, CPU usage, and response times before deciding that compilation is the problem.

Compilation, recompilation, and eviction are different things

It is tempting to call everything a “recompile” whenever SQL Server has to build a plan. Keeping the terms separate makes troubleshooting much easier.

  • Compilation: SQL Server builds a plan when it cannot reuse a suitable existing one. 
  • Recompilation: SQL Server regenerates a statement’s plan, for example after a relevant statistics or schema change, or because recompilation was explicitly requested. 
  • Eviction or clearing: A plan is removed from cache; a later execution may then need compilation.
  • A different cache entry: A variation in query text or relevant execution context can require a separate cached plan, rather than recompiling the original one.
Recompilation is not automatically a problem: it may be necessary for correctness or allow SQL Server to choose a better plan using updated information. If recompilations appear excessive, use the sql_statement_recompile Extended Event to identify the causes instead of trying to infer them from the hit ratio alone. 

My question would be, “Are we paying for avoidable recompilations?” That is more useful than simply asking how to make the recompile counter smaller.

Check how much memory the cached entries use


Plan-cache reuse and plan-cache memory are related, but they are not the same measurement. The cached-plan DMV exposes object type, cache-object type, size, and lookup count, allowing us to examine the footprint separately. 

This query groups entries by both plan category and cache-object type. That keeps full compiled plans separate from smaller compiled plan stubs.

;WITH CacheGroups AS
(
    SELECT
        objtype,
        cacheobjtype,
        COUNT_BIG(*) AS entry_count,
        SUM(CONVERT(bigint, size_in_bytes)) AS total_bytes,
        SUM(CASE WHEN usecounts = 1
                 THEN CONVERT(bigint, size_in_bytes)
                 ELSE CONVERT(bigint, 0) END) AS single_lookup_bytes
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype, cacheobjtype
)
SELECT
    objtype,
    cacheobjtype,
    entry_count,
    CAST(total_bytes / 1048576.0 AS decimal(19, 2)) AS size_mb,
    CAST(100.0 * total_bytes
         / NULLIF(SUM(total_bytes) OVER (), 0)
         AS decimal(9, 2)) AS pct_of_reported_cache_bytes,
    CAST(single_lookup_bytes / 1048576.0 AS decimal(19, 2))
        AS single_lookup_mb
FROM CacheGroups
ORDER BY total_bytes DESC, objtype, cacheobjtype;
GO


Pay particular attention to the memory in Adhoc entries with cacheobjtype = 'Compiled Plan' and low reuse, I recommend using optimize for ad hoc workloads configuration option when single-use ad hoc plans consume a significant portion of memory on an OLTP server. Do not assume that the category with the most entries is also the category using the most memory.

There is a small but important naming detail here: usecounts counts cache-object lookups, not an exact number of executions, and has documented exceptions for parameterized queries and Showplan activity. That is why the query labels the result single_lookup_mb rather than claiming every entry was executed exactly once.

Choose the fix that matches the evidence


Once we have measured reuse, compilation activity, and memory, the next step is choosing a targeted change. I would avoid changing several settings at once; otherwise, it becomes difficult to tell what actually helped.

Make parameterization consistent


For application-generated SQL, explicit parameters can improve plan reuse by separating changing values from stable statement text. Dynamic SQL is not automatically the problem; repeatedly generating unnecessarily different, literal-heavy statements is what I would investigate.

For example, the following is sample pattern using a fictional dbo.Orders table. It assumes the table has OrderID, OrderDate, and an integer CustomerID; adapt it to your own schema.

EXEC sys.sp_executesql
    N'SELECT OrderID, OrderDate
      FROM dbo.Orders
      WHERE CustomerID = @CustomerID;',
    N'@CustomerID int',
    @CustomerID = 101;
GO

On the next call, pass a different parameter value while keeping the statement and parameter definition consistent; this gives SQL Server an opportunity to reuse the plan. Simply putting a concatenated string containing literal values inside sp_executesql is not the same as passing those values as parameters. 

Keep parameter data types, string lengths, precision, and scale consistent as well, because differences in those definitions can create separate cached plans. This is worth discussing with the application team even when they are already using parameterized commands.

Stored procedures can also promote reuse, but they are not precompiled into execution plans when created: SQL Server normally compiles them on first execution and can reuse the cached plan afterward. Use them where they fit the application, rather than treating “convert everything to a stored procedure” as the only answer.

Text consistency matters, but do not confuse text matching with query_hash or query_plan_hash: those hashes identify similar query logic or execution plans, not simply identical text. Relevant SET options and other cache-key context can also affect reuse even when the text matches. 

Enable optimize for ad hoc workloads when single-use plans consume substantial memory


If your plan cache contains a substantial amount of memory tied up in single-use ad hoc plans, I recommend enabling optimize for ad hoc workloads; this matches Microsoft's recommendation for OLTP servers with that memory-usage pattern. I would treat it as a recommended practice for that workload pattern, rather than an unconditional rule for every instance.

The setting addresses this problem by storing a small compiled plan stub instead of the full plan the first time an ad hoc batch is compiled, reducing the memory footprint of plans that may never be reused. (Microsoft configuration guidance) The important distinction is memory consumption, not just the number of plans: check how much memory the full single-use ad hoc plans occupy before deciding how much benefit to expect.

Keep a few trade-offs in mind. This is primarily a memory-efficiency recommendation, so do not judge its success by the hit ratio alone.

  • Second use: SQL Server compiles the batch again and replaces the stub with a full compiled plan. 
  • Existing entries: Enabling the option affects new plans, not full plans already sitting in cache. 
  • Troubleshooting: A stub does not contain a graphical or XML execution plan to inspect. 


Enable it for the right reason, then measure the memory benefit over representative workload periods. Continue improving parameterization as well; reducing the footprint of unnecessary plans and avoiding their creation are complementary goals.

Be selective with forced parameterization


When changing application code is not practical, database-level forced parameterization or a TEMPLATE plan guide may be worth evaluating for eligible statements. The PARAMETERIZATION FORCED query hint is only allowed inside a plan guide; it is not a hint you can simply append to a normal query. 

Test representative parameter values before widening reuse, because forced parameterization can expose parameter-sensitivity problems. Fewer cached plans is not a win if important queries become slower.

Also remember that Parameter Sensitive Plan optimization, available starting with SQL Server 2022 under the appropriate compatibility level and eligibility conditions, can intentionally maintain multiple plan variants. Multiple plans can be useful, so investigate their purpose before calling them waste.

Keep useful maintenance; remove unnecessary work


Statistics updates and index rebuilds can lead to recompilation of affected queries, while index reorganization does not update statistics. A resulting recompile may produce a better plan, so avoiding every recompile is not a sensible maintenance objective. 

Rather than assuming every index needs a nightly rebuild, choose maintenance based on demonstrated workload benefits and its resource cost. Schedule necessary resource-intensive work during quieter periods where practical, but do not disable useful statistics maintenance just to protect a percentage.

Before adding memory or clearing the cache


SQL Server adjusts cache allocation dynamically and can remove plans under memory pressure. A low hit ratio alone does not establish that the server needs more RAM, so I would look for corroborating memory-pressure evidence and inspect what is occupying the cache first.

I would also avoid using DBCC FREEPROCCACHE as a routine way to “clean things up”: removing useful cached plans can force subsequent executions to compile again. Do not clear the production plan cache merely to get a fresh measurement.


Check whether query execution actually improved


Compilation is only part of the picture. Once you have made a targeted change, return to the question that matters to the application: are queries completing efficiently?

The following query provides a current-cache view of statements with at least five completed executions, ranked by average elapsed time; it is not a history of every query that ran on the server. Use it as a starting point for execution-performance investigation, not as proof of compilation overhead.


SELECT TOP (10)
    SUBSTRING
    (
        st.text,
        (qs.statement_start_offset / 2) + 1,
        (
            (CASE WHEN qs.statement_end_offset = -1
                  THEN DATALENGTH(st.text)
                  ELSE qs.statement_end_offset END
             - qs.statement_start_offset) / 2
        ) + 1
    ) AS statement_text,
    qs.creation_time AS plan_creation_time,
    qs.last_execution_time,
    qs.execution_count,
    CAST(qs.total_elapsed_time
         / (1000.0 * NULLIF(qs.execution_count, 0))
         AS decimal(19, 2)) AS avg_elapsed_ms,
    CAST(qs.total_worker_time
         / (1000.0 * NULLIF(qs.execution_count, 0))
         AS decimal(19, 2)) AS avg_cpu_ms,
    qs.total_logical_reads,
    qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count >= 5
ORDER BY avg_elapsed_ms DESC;
GO


These DMV rows disappear when their associated plans leave the cache, and the statistics reflect completed executions rather than requests still running. For before-and-after analysis over time, Query Store can retain query, plan, and runtime-statistics history beyond the current plan-cache contents, subject to its capture and retention settings. 

Consider a hypothetical before-and-after comparison: after improving parameterization, compilation activity and single-use plan memory fall, while representative query response times improve. The overall cache-hit ratio barely changes.

I would still call that progress. The objective was to reduce unnecessary work, not to win a percentage contest.

A useful checklist


When investigating plan-cache efficiency, I would work through these questions in order. Keep the measurements tied to the same workload period so the comparison remains useful.

  • What is the symptom: high CPU, slow queries, compilation activity, or memory pressure?
  • Is the hit-ratio change unusual for this workload and cache instance?
  • What do recent compilation and recompilation rates show?
  • Which cached entries consume the memory?
  • Does the evidence point to parameterization, recompilation causes, memory pressure, or poor execution plans?
  • What is the smallest change we can test safely?
  • Did CPU usage, response time, or memory use improve afterward?


Plan-cache efficiency is about avoiding unnecessary work while keeping useful plans available. Start with the hit ratio if you like, but finish with workload evidence: measure, make a targeted change, and verify that the application benefited.


Want to dig a little deeper? These Microsoft references cover the counters, DMVs, and tuning options discussed in this article.

  • sys.dm_exec_cached_plans : Details about cached-plan types, memory usage, and the important limitations of interpreting usecounts.
  • sys.dm_exec_query_stats : Execution statistics for currently cached statements, including CPU time, elapsed time, logical reads, and execution counts.
  • Index Maintenance Guidance : Differences between reorganizing and rebuilding indexes, their effects on statistics, and how to evaluate maintenance benefits.