Quick Scan Report – Optimize for Ad Hoc Workloads

What this check looks for

The optimize for ad hoc workloads setting in sys.configurations, reported when it is at its default of 0.

Why it matters

Every query SQL Server compiles gets its plan cached, whether or not that plan will ever be used again. On a workload with ad hoc SQL, most of them never are.

An ad hoc query is one sent as literal text with the values embedded, rather than as a parameterized statement. Change one literal and it is a different query with a different hash and a different cached plan:

SELECT * FROM Orders WHERE CustomerID = 1234;   -- one plan
SELECT * FROM Orders WHERE CustomerID = 1235;   -- another plan
SELECT * FROM Orders WHERE CustomerID = 1236;   -- and another

Ten thousand customers means ten thousand cached plans, each used exactly once, each occupying memory. A plan can be tens of kilobytes; a complex one can be several hundred. It adds up to gigabytes on a busy instance.

What that costs:

• Memory taken from the buffer pool. Plan cache and data pages compete for the same memory. Every gigabyte of single use plans is a gigabyte of data not cached, which shows up as more physical reads and lower page life expectancy.
• Cache lookup and eviction work. A very large plan cache makes the internal hash tables longer to search, and the eviction sweeps have more to walk.
• Useful plans get evicted. Under memory pressure SQL Server removes plans by cost, and the sheer volume of single use plans can push out the ones that were actually being reused.
• RESOURCE_SEMAPHORE_QUERY_COMPILE waits, in the worse cases.

What the setting does is elegantly simple. With it enabled, the first time an ad hoc batch is compiled, SQL Server caches only a small stub of a few hundred bytes rather than the full plan. If the same batch is executed a second time, the stub is replaced with the real compiled plan. So:

• Plans used once cost a few hundred bytes instead of tens of kilobytes.
• Plans used more than once are cached normally, from the second execution onwards.
• The only cost is one extra compilation for each batch that turns out to be executed twice.

This is why it is widely recommended as a default for new instances. The downside is a single recompile for genuinely reused ad hoc batches, and the upside is potentially gigabytes of memory.

It does not affect stored procedures or parameterized queries. Those are cached in full on first execution as they always were. The setting only changes the handling of ad hoc and prepared plan types.

And it is not a fix for the underlying problem. The real answer to a plan cache full of single use plans is parameterized queries, which also fix the security exposure that literal concatenation usually implies. This setting is the mitigation, not the cure.

How to confirm it yourself

The setting:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'optimize for ad hoc workloads';

How much of your plan cache is single use, which is the number that decides whether this matters here:

SELECT [objtype],
       COUNT(*)                                            AS [plans],
       SUM(CASE WHEN [usecounts] = 1 THEN 1 ELSE 0 END)    AS [single_use_plans],
       CAST(SUM([size_in_bytes]) / 1048576.0 AS DECIMAL(12,1))                       AS [total_mb],
       CAST(SUM(CASE WHEN [usecounts] = 1 THEN [size_in_bytes] ELSE 0 END)
            / 1048576.0 AS DECIMAL(12,1))                  AS [single_use_mb]
  FROM sys.dm_exec_cached_plans WITH (NOLOCK)
 GROUP BY [objtype]
 ORDER BY [single_use_mb] DESC;

Read the Adhoc row. If single_use_mb there is a meaningful fraction of your memory, the setting will recover most of it.

The same as a proportion, which is the quickest read:

SELECT CAST(SUM(CASE WHEN [usecounts] = 1 THEN [size_in_bytes] ELSE 0 END) * 100.0
            / NULLIF(SUM([size_in_bytes]), 0) AS DECIMAL(5,1))  AS [single_use_pct],
       CAST(SUM([size_in_bytes]) / 1048576.0 AS DECIMAL(12,1))  AS [plan_cache_mb]
  FROM sys.dm_exec_cached_plans WITH (NOLOCK);

Above 50 percent single use with a plan cache of several gigabytes is a clear case. A small plan cache dominated by stored procedure plans is not, and enabling the setting there changes almost nothing.

Plan cache against total memory, for context:

SELECT [type],
       CAST(SUM([pages_kb]) / 1024.0 AS DECIMAL(12,1)) AS [mb]
  FROM sys.dm_os_memory_clerks WITH (NOLOCK)
 WHERE [type] IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'MEMORYCLERK_SQLBUFFERPOOL')
 GROUP BY [type];

CACHESTORE_SQLCP is the ad hoc and prepared plan cache, which is what this setting affects. CACHESTORE_OBJCP is stored procedures, which it does not.

And the worst offenders, which tells you whether an application change is available:

SELECT TOP (20)
       cp.[objtype],
       cp.[usecounts],
       cp.[size_in_bytes] / 1024 AS [size_kb],
       LEFT(st., 200)      AS [query_text]
  FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(cp.[plan_handle]) AS st
 WHERE cp.[usecounts] = 1 AND cp.[objtype] = 'Adhoc'
 ORDER BY cp.[size_in_bytes] DESC;

Look for the same query shape repeated with different literals. That pattern names the application that should be parameterizing.

How to fix it

Enable it. It is immediate, reversible, and needs no restart.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

It applies to newly compiled plans from that moment. Plans already in cache are unaffected, so the benefit appears gradually as the cache turns over. If you want to see the effect immediately on a test instance you can clear the cache, but do not do that on production during working hours: every query recompiles at once, which is a CPU spike at the worst possible moment.

Measure afterwards. Run the proportion query above a day later and again a week later. On a workload with heavy ad hoc SQL the plan cache typically drops substantially and page life expectancy improves, because the memory goes back to the buffer pool.

Then address the cause, which is where the real improvement is:

1. Parameterize the application queries. A parameterized query compiles once and is reused for every value, which removes the problem entirely and closes the injection exposure that string concatenation usually brings with it.
2. Use stored procedures where that fits the application.
3. Consider forced parameterization as a last resort, at the database level:

ALTER DATABASE [YourDatabase] SET PARAMETERIZATION FORCED;

That one needs care. It makes SQL Server parameterize queries it would otherwise treat as ad hoc, which fixes the cache bloat and can introduce parameter sniffing problems on queries where the literal value genuinely mattered to the plan. Test it, and prefer fixing the application.

Do not schedule DBCC FREEPROCCACHE as a workaround. Clearing the cache periodically trades a memory problem for a recompilation storm, and it destroys the plan reuse statistics you need to diagnose anything.

How long it takes

About two hours, most of it measuring before and after and reviewing the worst offenders. The setting change itself is seconds.

Related reports

Related checks

Frequently asked questions

Is there a downside? One extra compilation for each ad hoc batch that turns out to be executed a second time. On a workload where most ad hoc queries run once, that cost is tiny compared with the memory saved.

Does it affect stored procedures? No. Stored procedure and parameterized plans are cached in full on first execution, unchanged.

Do I need to restart or clear the cache? Neither. It applies to newly compiled plans, and the benefit appears as the cache turns over.

Should I enable it on every instance? It is a reasonable default, and harmless on an instance with little ad hoc SQL. Check the single use proportion first if you want to know what it will actually gain you.