A Study in SQL Server Ad hoc Query Plans

Finding about 30 GB in the plan cache will get a DBA’s attention. That happened during a 2023 investigation, on a SQL Server instance with more than 2 TB of memory available. Thirty gigabytes might sound less alarming on a server that large, but my next question was still the same: how much of that memory was earning its keep?

Plan caching is useful because reusing a suitable execution plan avoids repeating compilation work, but retaining plans that never get reused consumes memory without delivering that reuse benefit. (Microsoft’s query processing guide) The goal is not simply a smaller cache. I want a cache that supports the workload efficiently, without paying unnecessarily in either memory or compilation work.

In this article, I’ll walk through the original findings, explain what ad hoc plans and compiled plan stubs actually tell us, and show how I would investigate the same situation today. I’ll also explain why I recommend enabling optimize for ad hoc workloads when substantial memory is tied up in full single-use ad hoc plans.

The environment that started the investigation

This was a large analytical and reporting environment, not a small transactional application. The investigation involved SQL Server 2019 CU18, a database of approximately 60 TB, and more than 2 TB of memory and 112 CPU cores available to the instance.

Application changes were not a practical short-term option, and my assignment was to investigate and recommend changes, not implement them. That distinction matters: this is a diagnostic case study, not a before-and-after account of a production change.

The original cache-memory snapshot looked like this:

Cache object type	Reported memory, MB
All displayed entries	31,257
Adhoc	29,594
Prepared	882
Proc	506
View	269
Rule	2

These are historical, rounded values recorded during that investigation, not results produced by the revised queries below. The Adhoc entries accounted for approximately 95% of the reported cache memory.

That is a useful finding, but it does not mean 95% of the workload’s executions, CPU consumption, or elapsed time came from ad hoc queries; the measurement is cache memory. I would investigate the concentration of memory before deciding whether it represented waste, pressure, or a perfectly reasonable working cache.

And no, my preferred fix is not to convince management that every SQL Server needs 2 TB of RAM. I would rather start by understanding what we are keeping in memory and why.

Ad hoc does not mean single-use

Before looking at more numbers, let’s clear up an easy misunderstanding: Adhoc describes a cached object’s type, not how often its plan has been used. An ad hoc plan can be reused, and the DMV exposes object type and lookup count as separate attributes. (Microsoft’s cached-plan DMV documentation)

Two columns are especially useful here:

• objtype: Identifies categories such as Adhoc, Prepared, and Proc. 
• cacheobjtype: Identifies the cached representation, including Compiled Plan and Compiled Plan Stub. 

Also, the tool submitting a query does not decide its caching behavior by itself: parameterized calls, stored procedures, automatic parameterization, and the relevant execution context all affect plan reuse. “It came from SSMS” is therefore not the diagnosis I would stop at.

The question I care about is more specific: are we retaining a substantial amount of full ad hoc plan memory for batches that rarely, or never, benefit from reuse?

Start with a read-only cache inventory

I would start directly with sys.dm_exec_cached_plans, separating object types and distinguishing full plans from stubs. Its size_in_bytes and usecounts columns let us inspect memory consumption and cache lookups without first joining to statement-level statistics.

For these server-wide DMV examples, SQL Server 2019 requires VIEW SERVER STATE; SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE. Those permissions are separate from permission to change server configuration. 

-- Read-only: inventory currently cached entries.
;WITH CacheGroups AS
(
    SELECT
        objtype,
        cacheobjtype,
        COUNT_BIG(*) AS cache_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,
    cache_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;
GO

The byte totals are aggregated before conversion to MB, and the percentage uses the total bytes reported by this query as its denominator. It is not a percentage of all SQL Server memory.

Start with the Adhoc / Compiled Plan row, then compare it with Adhoc / Compiled Plan Stub. Those are different cache representations, so a large number of small stubs is not the same finding as a large amount of memory occupied by full plans. 

Be careful with “used once”

I deliberately called the last column single_lookup_mb, not executed_once_mb: usecounts records cache-object lookups and is not a universally reliable execution count. Microsoft documents exceptions, including parameterized plan matches and Showplan-related behavior. 

I use that bucket as a practical starting point for investigation, not a complete workload history. Take samples across representative reporting or ETL cycles and review the actual batches before drawing a conclusion about their reuse.

Here is a read-only query to inspect the largest full ad hoc plans with one recorded cache lookup:

SELECT TOP (50)
    cp.plan_handle,
    cp.objtype,
    cp.cacheobjtype,
    cp.usecounts AS cache_lookup_count,
    cp.size_in_bytes,
    st.text AS batch_text
FROM sys.dm_exec_cached_plans AS cp
OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype = N'Adhoc'
  AND cp.cacheobjtype = N'Compiled Plan'
  AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC, cp.plan_handle;
GO

Notice that the full-plan filter uses cacheobjtype, not objtype; Compiled Plan Stub is a value of the former column. Also, treat the returned batch text as potentially sensitive when sharing diagnostic results.

Why not add up execution counts?

sys.dm_exec_query_stats contains statement-level statistics, so summing execution_count for a plan handle does not reliably tell us how many times the containing batch was submitted. Its rows describe statements within cached plans, and its statistics reflect completed executions. 

For example, imagine a batch containing three statements, each executed once. Adding their execution counts gives three, even though the batch was submitted only once.

An earlier version of this article included a result of approximately 28 GB selected by low-activity and age conditions. On review, that query aggregated statement execution counts, and its stub-exclusion predicate checked the wrong column. I have replaced that approach with the cache-entry measurements above; the earlier result should not be read as a clean measurement of single-use full plans or as a savings forecast.

For a cache-memory inventory, keep the measurement at the cache-entry level. Bring in statement statistics separately when the question is about statement activity.

What optimize for ad hoc workloads actually changes

With optimize for ad hoc workloads enabled, SQL Server still compiles and executes an eligible ad hoc batch on its first invocation, but retains a small compiled plan stub instead of the full compiled plan. The setting reduces first-use cache memory; it does not eliminate that first compilation. (Microsoft’s configuration guidance)

The sequence is:

1. First invocation: SQL Server compiles and executes the batch, then caches a stub rather than the full plan.
2. Next matching invocation, while the stub remains cached: SQL Server compiles the batch again and replaces the stub with a full compiled plan.
3. Later matching invocations: The full cached plan is available for reuse, subject to the normal rules for matching, invalidation, and eviction. 

The stub lets SQL Server recognize a previously compiled batch, but it does not contain a full execution plan that you can retrieve as graphical or XML plan output. That is worth remembering when troubleshooting a query whose only cached representation is a stub.

This is a trade-off, not free performance: a batch that returns for a second matching invocation needs another compilation before its full plan is retained. That is why I measure compilation activity alongside memory rather than declaring victory as soon as the cache gets smaller.

My recommendation

When substantial memory is tied up in full single-use ad hoc plans, I recommend enabling optimize for ad hoc workloads through the normal change-control process. It is designed specifically to reduce the cache-memory footprint of ad hoc batches that are not reused.

I regard this as a practical best practice for that workload pattern, not merely an obscure option to keep in the back pocket. However, a high Adhoc percentage alone would not persuade me; I would establish how much memory full low-reuse plans occupy.

Microsoft’s explicit recommendation discusses OLTP servers, whereas this case involved an analytical workload. My recommendation for this environment is to apply the same memory-saving mechanism, then validate the benefit against its reporting and ETL patterns.

I would check:

• Memory: Are full low-reuse ad hoc plans occupying less memory over a representative workload cycle?
• Compilation: Has compilation activity or compilation-related CPU become more expensive?
• Workload performance: Are important reports and ETL jobs running at least as well as before?
• Troubleshooting: Can the team collect the execution-plan evidence it needs when only a stub remains cached?

I would not promise a specific number of gigabytes saved before measuring the result. A configuration setting deserves a recommendation based on evidence and a follow-up based on evidence.

Making an approved configuration change

The following is an instance-wide configuration change, not a diagnostic query; use it only after approval on the intended instance. A disposable database on a shared instance does not isolate a server configuration change.

Record the existing configured and running values first. Changing configuration through sp_configure and RECONFIGURE requires appropriate server permissions, including ALTER SETTINGS. 

-- Read-only: save this output in the change record.
SELECT
    name,
    value AS configured_value,
    value_in_use AS running_value
FROM sys.configurations
WHERE name IN
(
    N'show advanced options',
    N'optimize for ad hoc workloads'
);
GO

For an approved change, this block enables the option and restores show advanced options to its previous running state. Use it only when no other configuration change is in progress; review any pre-existing differences between configured and running values first.

-- CHANGE SCRIPT: approved instance only.
DECLARE @OriginalShowAdvanced int;

SELECT @OriginalShowAdvanced = CONVERT(int, value_in_use)
FROM sys.configurations
WHERE name = N'show advanced options';

IF @OriginalShowAdvanced = 0
BEGIN
    EXEC sys.sp_configure N'show advanced options', 1;
    RECONFIGURE;
END;

EXEC sys.sp_configure N'optimize for ad hoc workloads', 1;
RECONFIGURE;

IF @OriginalShowAdvanced = 0
BEGIN
    EXEC sys.sp_configure N'show advanced options', 0;
    RECONFIGURE;
END;

SELECT
    name,
    value AS configured_value,
    value_in_use AS running_value
FROM sys.configurations
WHERE name IN
(
    N'show advanced options',
    N'optimize for ad hoc workloads'
);
GO

If the script stops with an error, recheck both settings rather than assuming the final restoration ran. For rollback, use the recorded original setting through the same approved configuration process.

Enabling the option affects newly cached plans; it does not convert existing full plans into stubs. I would normally allow the cache to turn over with the workload rather than clear it simply to make a before-and-after screenshot look more dramatic.

A small lab makes the behavior easier to see

During my testing for the 2023 investigation, I submitted comparable queries as an ad hoc batch, a parameterized sp_executesql call, and a stored procedure. I observed a 456-byte ad hoc stub alongside 155,648-byte full plans for the prepared and procedure examples; those are observations from that particular test, not fixed sizes to expect elsewhere.

To focus on the stub lifecycle, use an isolated test instance with the setting enabled. Record its original configuration before changing anything, and restore it after the exercise if needed.

Run the following as its own batch, once. If you have already used this marker in your lab, change its suffix before starting, then keep the complete batch text unchanged throughout the test.

/* SQLPAL_AdHocStub_Demo_A01 */
SELECT name
FROM sys.databases
WHERE name = N'tempdb';
GO

Now inspect the cache separately:

-- The split marker avoids matching this inspection batch itself.
SELECT
    cp.plan_handle,
    cp.objtype,
    cp.cacheobjtype,
    cp.usecounts AS cache_lookup_count,
    cp.size_in_bytes,
    st.text AS batch_text
FROM sys.dm_exec_cached_plans AS cp
OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%SQLPAL_' + N'AdHocStub_Demo_A01%'
ORDER BY cp.objtype, cp.cacheobjtype;
GO

For an eligible first-use ad hoc entry, look for objtype = Adhoc and cacheobjtype = Compiled Plan Stub; then run the exact original test batch again and inspect the cache a second time. If the matching stub was still present, the documented next step is a full compiled plan. 

If your output differs, check the running setting, whether the batch was already cached, and whether parameterization or execution context changed its representation or matching behavior. Automatic parameterization can introduce reusable prepared plans, so not every literal-containing query is a clean demonstration of the same cache path.

The point is to observe the cache object, not force a particular byte count or treat usecounts as a complete execution history. There is no cache-clearing command in this exercise.

What I would not do

Turn this into a scheduled cache-cleaning job

I would not routinely evict plans just because they are several hours old or have low observed activity. DBCC FREEPROCCACHE removes cached plans, and subsequent requests can incur compilation costs to replace them. 

Consider a daily report: its plan may look idle most of the day and still be useful at the next scheduled execution, provided it remains valid and cached. Frequency alone does not determine whether a cached plan can be reused.

Targeted eviction can have a place in approved troubleshooting, but I would not present it as routine housekeeping or an equivalent substitute for first-use stubs. My default is to diagnose the cause, not schedule the symptom to disappear.

Treat the setting as a substitute for parameterization

optimize for ad hoc workloads changes what is retained after first use; parameterization can allow suitable executions with different parameter values to share a plan. They address different parts of the problem. 

Where application changes are possible, I would also review parameterized calls, consistent parameter definitions, and whether the shared plan performs well across representative values. Where they are not immediately possible, I would still pursue a justified server-side improvement rather than wait indefinitely.

The takeaway

The historical snapshot told me where the cache memory was going, but it did not prove how often every batch executed or how much memory a configuration change would save. My role in that investigation was to recommend changes, so I do not have a measured production before-and-after result to claim here.

My recommendation remains straightforward: enable optimize for ad hoc workloads when substantial memory is being consumed by full single-use ad hoc plans, and validate the outcome against the actual workload. The setting’s purpose is to reduce that first-use memory footprint, not to make every query faster or eliminate compilation. 

A smaller cache is not the finish line, and a larger cache is not automatically a problem. I want useful reuse, sensible memory consumption, and reports that finish when the business needs them.

For the broader diagnostic approach, continue with Measuring and Improving SQL Server Query Plan Cache Efficiency. Use this case study to understand the ad hoc memory problem, then use the companion article to put that finding into the wider performance picture.