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. It's the kind of number you'd happily paste into a status report. Before calling it good news, though, I want to know what the server is actually doing with all those reused plans.

First, a quick refresher. Before SQL Server runs a query, the query optimizer works out how to run it and produces an execution plan. Building that plan is called compilation, and it mostly costs CPU. To avoid paying that cost every time, SQL Server keeps plans in an area of memory called the plan cache, so a matching query can reuse an existing plan instead of compiling a new one. The plan-cache hit ratio tells you how often SQL Server looked for a plan in the cache and found one.

So far, so good. But a high hit ratio answers only one question: did SQL Server find a plan? It doesn't tell you whether that plan was a good fit for this particular execution. A plan built for one set of parameter values can be a poor fit for another, and reusing it just lets SQL Server skip compilation and get straight to the slow part. The ratio also says nothing about how much memory is tied up by plans that ran once and may never run again.

That's why I treat plan-cache efficiency as a balancing act between CPU and memory. Reuse the plans that are worth reusing, so less CPU goes to compilation, and keep single-use plans from crowding the cache, so that memory is available for other work. The goal is a workload that performs better, not a prettier percentage.

We'll look at what the hit ratio actually measures, how to collect numbers you can act on, and which changes are worth considering when the evidence points to a real problem. Everything here targets SQL Server 2022 and later.

What the hit ratio actually tells us

The cache-hit ratio is the number of cache hits divided by the number of cache lookups for one cache instance. In plain terms: when SQL Server looked in this cache, how often did it find what it wanted?

That isn't the same as "this percentage of application queries ran without compiling." The difference matters most when you look at _Total, which covers more than query plans. The plan cache counters are split into several cache instances. This article uses three of them:

  • SQL Plans: ad hoc, auto-parameterized, and prepared SQL plans.
  • Object Plans: plans for stored procedures, functions, and triggers.
  • _Total: every cache-instance type combined, including things like bound trees and temporary-table information. A healthy-looking _Total can hide a struggling SQL Plans instance.

I wouldn't use 90%, 95%, or 99% as a universal pass/fail line. Ask better questions instead. Has the number moved away from this workload's normal behavior? Has compilation activity changed along with it? Are users actually seeing slower queries?

Before you run the queries

Every query in this article only reads dynamic management views (DMVs). None of them change configuration or clear the cache. On SQL Server 2022 and later, the DMVs used here require the VIEW SERVER PERFORMANCE STATE permission on the server.

Start with a quick measurement

This query takes a single snapshot of the plan cache counters. It's a useful first look, but it doesn't tell you what happened in the last few seconds, or during the incident you're investigating.

T-SQL
;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(ROUND(100.0 * hits / NULLIF(lookups, 0), 0) AS int)
        AS snapshot_hit_ratio_pct
FROM CacheCounters
ORDER BY object_name, cache_instance;
GO

Two small details are doing real work here. The 100.0 prevents integer-only division, which would otherwise truncate most ratios to 0 or 100 before any rounding happens. NULLIF prevents a divide-by-zero error. If there were no lookups, the result is NULL rather than a misleading 0%.

The result is rounded to a whole percent for readability. Keep in mind that 99.5% or higher displays as 100, so a 100 doesn't necessarily mean there were no misses. If you need finer detail, look at the raw hits and lookups columns.

Many values in sys.dm_os_performance_counters are running totals that start over when the SQL Server service restarts; the /sec counters are documented that way. Not every counter works like that, though. Some show only the most recent value. The cache-hit ratio and its base sit in between. Microsoft describes them as total hits compared with total lookups, but doesn't say explicitly that those totals reach all the way back to startup. (If you want to know how far back they could go, sqlserver_start_time in sys.dm_os_sys_info shows when the instance last started.) Either way, a single snapshot can blend days of normal activity with the ten minutes you actually care about, so even a sharp drop during an incident may barely move it. That's why I don't stop at the snapshot. The next question is: "What happened during the interval I actually care about?"

Measure recent activity

In sys.dm_os_performance_counters, the raw values for /sec counters are running totals. To get a rate, take two samples and divide the difference by the elapsed seconds. For a recent cache-hit ratio, use the same idea: divide the change in hits by the change in lookups over the interval. Don't subtract one percentage from another.

The sampler below takes two snapshots, @SampleSeconds apart (15 by default), and returns two result sets: recent cache-hit ratios, then batch and compilation rates. It stores its samples in table variables and does nothing else.

T-SQL
-- Sampling interval in seconds. Allowed range: 1 to 3600.
DECLARE @SampleSeconds int = 15;

IF @SampleSeconds IS NULL OR @SampleSeconds NOT BETWEEN 1 AND 3600
    THROW 50000, N'@SampleSeconds must be between 1 and 3600.', 1;

-- WAITFOR DELAY accepts a variable in hh:mm:ss format.
DECLARE @Delay char(8) =
    CONVERT(char(8),
            DATEADD(second, @SampleSeconds, CAST('00:00:00' AS time(0))),
            108);

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 @Delay;

    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
    CAST(ROUND(@Seconds, 0) AS int) 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(ROUND(100.0 * hits / NULLIF(lookups, 0), 0)
                   AS int)
    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
    CAST(ROUND(@Seconds, 0) AS int) AS elapsed_seconds,
    counter_name,
    delta_value AS interval_events,
    CASE WHEN delta_value >= 0
         THEN CAST(ROUND(delta_value / NULLIF(@Seconds, 0), 0)
                   AS bigint)
    END AS events_per_second
FROM @Deltas
WHERE object_name LIKE N'%:SQL Statistics'
ORDER BY counter_name;
GO

Pick an interval that fits the question. A short sample is a quick pulse check; a longer one smooths out bursts but takes longer to come back. Either way, it isn't a baseline. Repeat the collection during representative periods. On a busy server the actual wait can run a little longer than requested, which is why the script measures the real elapsed time instead of trusting @SampleSeconds. If a counter is missing or the sample looks inconsistent, investigate or resample. Missing data is not the same as zero activity.

Here's how to read the second result set:

  • Batch Requests/sec: incoming T-SQL batches per second. A batch can contain many statements, so this isn't a statement count.
  • SQL Compilations/sec: how often SQL Server enters the compilation code path. This includes compilations caused by statement-level recompiles.
  • SQL Re-Compilations/sec: how many statement recompiles are triggered per second.

Don't add compilations and recompilations together. The compilation counter already includes the recompiles, so adding them counts the same work twice. 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

It's tempting to call any plan-building a "recompile." Keeping the terms separate makes troubleshooting much easier:

  • Compilation: SQL Server builds a plan because it can't reuse a suitable existing one.
  • Recompilation: SQL Server rebuilds a statement's plan. This can happen after a relevant statistics or schema change, or because someone explicitly asked for it.
  • Eviction or clearing: a plan is removed from the cache, so a later execution may need a fresh compilation.
  • A different cache entry: a change in query text or execution context (such as SET options) can create a separate cached plan rather than recompiling the original.

Recompilation isn't automatically a problem. It may be needed for correctness, or it may let SQL Server choose a better plan based on newer information. If recompiles look excessive, use the sql_statement_recompile Extended Event. Its recompile_cause field tells you why each recompile happened, which beats guessing from the hit ratio.

The useful question isn't "How do I make the recompile counter smaller?" It's "Are we paying for recompiles we could avoid?"

Check how much memory cached entries use

Plan reuse and plan-cache memory are related, but they're different measurements. sys.dm_exec_cached_plans shows each entry's object type, cache-object type, size, and lookup count, so you can look at memory on its own.

This query groups entries by plan category (objtype) and cache-object type (cacheobjtype). That keeps full compiled plans apart from the much smaller compiled plan stubs.

T-SQL
;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(ROUND(total_bytes / 1048576.0, 0) AS bigint) AS size_mb,
    CAST(ROUND(100.0 * total_bytes
               / NULLIF(SUM(total_bytes) OVER (), 0), 0)
         AS int) AS pct_of_reported_cache_bytes,
    CAST(ROUND(single_lookup_bytes / 1048576.0, 0) AS bigint)
        AS single_lookup_mb
FROM CacheGroups
ORDER BY total_bytes DESC, objtype, cacheobjtype;
GO

Look closely at Adhoc rows where cacheobjtype = 'Compiled Plan' and single_lookup_mb is large. That's memory held by full ad hoc plans that have been looked up only once. Don't assume the row with the most entries is also the one using the most memory. A pile of small entries can weigh less than a handful of big ones. Sizes and percentages are rounded to whole numbers, so a small group can show 0 MB or 0% even though it isn't empty. Its entry_count will still show it's there.

One naming detail matters here. usecounts counts cache lookups, not executions. There are two exceptions: it isn't incremented when parameterized queries find a plan in the cache, and Showplan activity can increment it more than once. That's why the column is called single_lookup_mb and not "executed once." On a server with a very large plan cache, this DMV can return a lot of rows, so the first time you run it, consider doing so in a quieter period.

Choose the fix that fits

Once you've measured reuse, compilation, and memory, choose a targeted change. Change one thing at a time. If you change three settings at once and things improve, you'll never know which one deserves the credit.

Make parameterization consistent

For SQL generated by an application, explicit parameters improve plan reuse by separating the values that change from statement text that stays the same. Dynamic SQL isn't the villain here. The pattern worth investigating is repeatedly generating slightly different, literal-heavy statements.

Here's a sample pattern using a fictional dbo.Orders table. It assumes the table has OrderID, OrderDate, and an integer CustomerID; adapt it to your schema.

T-SQL
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 value but keep the statement text and parameter definition the same. That gives SQL Server the chance to reuse the plan. Concatenating literal values into a string and passing that to sp_executesql doesn't count. It's still literal SQL, just wearing a parameterized costume.

Keep parameter data types consistent too, including string lengths, precision, and scale. In one of Microsoft's own examples: nvarchar(10) on one call and nvarchar(20) on the next creates two separate plans. This is worth mentioning to the application team even if they already use parameterized commands.

Stored procedures can also encourage reuse, but they aren't precompiled into execution plans when you create them. SQL Server normally compiles a procedure on its first execution and reuses the cached plan after that. Use them where they fit the application; "convert everything to a stored procedure" isn't the only answer.

Finally, don't confuse matching text with query_hash or query_plan_hash. Those hashes group queries with similar logic or similar plans, such as queries that differ only by literal values. They don't mean the text was identical. And even when the text does match exactly, a difference in SET options or other cache-key context can still produce a separate plan.

Use optimize for ad hoc workloads where it fits

If a large share of plan-cache memory is held by single-use ad hoc plans, I recommend enabling optimize for ad hoc workloads. 

With the option on, the first time an ad hoc batch is compiled, SQL Server caches a small compiled plan stub instead of the full plan. The stub is basically a note saying "I've seen this before." That keeps plans that are never reused from taking up much memory. Judge the opportunity by memory, not plan count: 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:

  • Second use: when the batch runs again, SQL Server compiles it again, removes the stub, and caches the full plan.
  • Existing entries: the setting affects only new plans. The existing plans are affected only after the cache is cleared or the instance restarts. I'd let them age out naturally rather than clear a production cache just to see the effect sooner.
  • Troubleshooting: a stub has no execution plan attached, so you won't get a graphical or XML plan for it.

Because this is a memory-efficiency setting, don't judge it by the hit ratio. 

Be selective with forced parameterization

When changing application code isn't practical, database-level forced parameterization or a TEMPLATE plan guide may be worth evaluating for eligible statements. The PARAMETERIZATION FORCED query hint can only be used inside a plan guide. You can't just add it to an ordinary query.

Test with representative parameter values before widening reuse, because forced parameterization can expose parameter-sensitivity problems. Fewer cached plans isn't a win if important queries get slower.

Know what Parameter Sensitive Plan optimization can and can't do

Some background first. When SQL Server compiles a parameterized query, it looks at ("sniffs") the parameter values from that first call and builds a plan sized for them. That's usually a good thing. It becomes a problem when the data is skewed: a plan built for a customer with 10 orders can be a terrible fit for a customer with a million orders. That's the classic parameter sniffing problem.

Parameter Sensitive Plan (PSP) optimization, introduced in SQL Server 2022, is Microsoft's built-in attempt to soften it. It turns on automatically for databases at compatibility level 160 or higher, with no code changes. Here's how it works:

  1. During the first compilation, SQL Server uses the statistics histograms to find parameterized predicates whose values are unevenly distributed. It evaluates up to three of them.
  1. Instead of one plan, it caches a dispatcher plan. It splits each chosen predicate's expected row counts into three ranges: small, medium, and large.
  1. At run time, the dispatcher estimates how many rows the incoming value will return and sends the execution to the matching query variant. Each variant has its own plan, and each one is compiled only when it's first needed.

That's a real improvement for the pattern it targets: an equality predicate such as CustomerID = @CustomerID on a skewed column. PSP probably helps some but not by much and not enough. It isn't a cure for parameter sniffing, though, and the limits are worth knowing:

  • Sniffing still happens inside each bucket. Each variant is compiled for whichever value reaches it first. In one published test by Brent Ozar, the medium bucket covered estimates from 100 to 1,000,000 rows, and two values in that range differed by 21 times. Whichever one compiled first got a plan that fit the other one poorly.
  • It only handles equality predicates. Range searches such as OrderDate BETWEEN @Start AND @End aren't covered. In Brent Ozar's tests, a filter that reached the skewed column indirectly, through a join to a lookup table, didn't qualify either.
  • It doesn't always kick in. In SQL Server 2022, when several predicates on the same table qualify, PSP evaluates only the most skewed one. It also skips queries whose data isn't skewed enough. The parameter_sensitive_plan_optimization_skipped_reason Extended Event tells you why it skipped a query.
  • It changes what your monitoring tools see. Variant queries show up differently in the plan cache and Query Store.
  • Patch level matters. There was a Query Store access-violation issue tied to PSP. It was resolved in SQL Server 2022 CU7, so stay current on cumulative updates.

SQL Server 2025 at compatibility level 170 extends PSP to INSERT, UPDATE, DELETE, and MERGE statements, and gives more consideration to multiple qualifying predicates on the same table.

For plan cache work, the practical point is that a query running under PSP can legitimately have several cached plans. SQL Server caps the number of variants to prevent cache growth, but the threshold isn't documented. So before you flag those extra plans as bloat, check whether they're PSP variants doing their job.

If PSP makes a specific query worse, you can turn it off for that query with the DISABLE_PARAMETER_SENSITIVE_PLAN hint, or for a whole database with ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF. Treat it as a useful safety net for one common pattern, not a replacement for the usual tools: Query Store plan forcing, targeted hints, better indexing, and query changes.

Keep useful maintenance

Rebuilding a rowstore index updates its statistics. When statistics are updated, the query plans that reference them are recompiled. Reorganizing an index doesn't update statistics. That recompile might well produce a better plan, so "avoid every recompile" isn't a sensible maintenance goal.

Rather than assuming every index needs a nightly rebuild, choose maintenance based on demonstrated workload benefit and resource cost. Schedule heavy work during quieter periods where you can, but don't turn off useful statistics maintenance just to protect a percentage.

Before adding memory or clearing the cache

Plans stay in the cache while there's enough memory for them. Under memory pressure, SQL Server uses a cost-based approach to decide which plans to remove. So a low hit ratio alone doesn't prove the server needs more RAM. Look for other signs of memory pressure, and check what's actually filling the cache first.

I'd also avoid using DBCC FREEPROCCACHE as a routine "clean things up" step. Throwing out useful plans forces their queries to compile again on their next run. That's a lot of extra CPU in exchange for a nicer-looking number. Never clear a production plan cache just to get a fresh measurement.

Check that execution actually improved

Compilation is only part of the picture. After a targeted change, go back to the question the application cares about: are queries completing efficiently?

This query shows the 10 statements in the current cache with the highest average elapsed time, counting only statements with at least five completed executions. It isn't a history of everything that ran on the server. Use it as a starting point for investigating execution performance, not as proof of compilation overhead.

T-SQL
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(ROUND(qs.total_elapsed_time
               / (1000.0 * NULLIF(qs.execution_count, 0)), 0)
         AS bigint) AS avg_elapsed_ms,
    CAST(ROUND(qs.total_worker_time
               / (1000.0 * NULLIF(qs.execution_count, 0)), 0)
         AS bigint) 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 qs.total_elapsed_time * 1.0 / qs.execution_count DESC;
GO

The time columns are reported in microseconds, which is why the query divides by 1,000 to get milliseconds. The averages are rounded to whole milliseconds, so very fast statements can show 0. These rows disappear when their plans leave the cache, and they reflect completed executions only, not requests still running.

For before-and-after comparisons over time, use Query Store. It keeps query, plan, and runtime history beyond what's currently in the cache, subject to its capture and retention settings. Starting with SQL Server 2022, Query Store is on by default for newly created databases. Databases that were upgraded or restored from older versions may not have it on, so check rather than assume.

Here's a hypothetical example of plan cache efficiency improvement. After improving parameterization, compilation activity and single-use plan memory drop, and representative response times improve. But the overall hit ratio barely moves.

I'd still call that progress. The goal was to cut unnecessary work, not to win a percentage contest.

A practical checklist

When investigating plan-cache efficiency, work through these questions in order. Tie every measurement to the same workload period so the comparisons stay meaningful.

  1. What's the symptom: high CPU, slow queries, heavy compilation, or memory pressure?
  1. Is the hit-ratio change unusual for this workload and this cache instance?
  1. What do recent compilation and recompilation rates show?
  1. Which cached entries are using the memory?
  1. Does the evidence point to parameterization, recompile causes, memory pressure, or poor plans?
  1. What's the smallest change you can test safely?
  1. Did CPU, 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.

Useful links and further reading

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.
  • Optimize for Ad Hoc Workloads: When compiled plan stubs can reduce cache memory usage, and the trade-offs to understand before enabling this option.
  • Index Maintenance Guidance: Differences between reorganizing and rebuilding indexes, their effects on statistics, and how to evaluate maintenance benefits.