Optimize for Ad Hoc Workloads: Shrinking a Bloated Plan Cache

Optimize for ad hoc workloads is a server setting that stores a tiny stub, not a full plan, the first time a query runs. Run the query again and SQL Server keeps the full plan. On a server buried in one-off queries, that saves a lot of plan cache memory.

What the default does

By default, every ad hoc query gets a full compiled plan in the cache, even if it never runs again. I will build a tiny table, confirm the setting is off, then run two queries. The first runs once and the second runs twice. EXEC sends each string as its own batch, so each query gets its own plan.

DROP TABLE IF EXISTS dbo.AdHocDemo;
CREATE TABLE dbo.AdHocDemo (DemoId int PRIMARY KEY, Note varchar(20) NOT NULL);
INSERT dbo.AdHocDemo VALUES (1, 'one'), (2, 'two'), (3, 'three');
SELECT name, value_in_use FROM sys.configurations WHERE name = 'optimize for ad hoc workloads';

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 1');
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 2');
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 2');
SELECT p.cacheobjtype, p.usecounts, p.size_in_bytes, t.text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE 'SELECT Note FROM dbo.AdHocDemo%'
ORDER BY t.text;

Both queries hold a full Compiled Plan of 16,384 bytes on my server, with use counts of 1 and 2. That is fine for a query you run all day. It is a waste for one you will never see again.

Two hundred one-off queries

Now the realistic case. An app pastes literal values into SQL text, so every value looks like a brand new query. This loop sends 200 of them, then totals what landed in the cache.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SET NOCOUNT ON;
CREATE TABLE #Sink (Note varchar(20));
DECLARE @i int = 1, @sql nvarchar(200);
WHILE @i <= 200
BEGIN
    SET @sql = N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = ' + CAST(@i AS nvarchar(10));
    INSERT #Sink EXEC (@sql);
    SET @i += 1;
END;
DROP TABLE #Sink;
SELECT p.cacheobjtype, COUNT(*) AS Plans, SUM(CAST(p.size_in_bytes AS bigint)) AS TotalBytes
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE 'SELECT Note FROM dbo.AdHocDemo WHERE DemoId =%'
GROUP BY p.cacheobjtype;
SET NOCOUNT OFF;

The cache now holds 200 full plans, 3,276,800 bytes, for queries that will never repeat. Sizes vary by server and version, but the shape holds: one full plan per literal.

Check your own server first

Do not flip the switch blind. This query counts plans that ran exactly once, by type, with their size in kilobytes.

SELECT objtype, COUNT(*) AS SingleUsePlans, SUM(CAST(size_in_bytes AS bigint)) / 1024 AS SizeKB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND cacheobjtype = 'Compiled Plan' AND objtype IN ('Adhoc', 'Prepared')
GROUP BY objtype
ORDER BY objtype;

A big Adhoc count with a big size is the signal. My count includes the 200 plans I just created, so yours should be far smaller on a quiet dev box. If your single-use plans are a small slice of the cache, leave the setting alone. Run it after a day of real traffic, because a freshly cleared cache tells you nothing.

Turn it on and watch the stubs

The demo below changes this server setting and puts it back at the end, so try it on a dev box. It repeats the same experiment with the setting on.

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 1');
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 2');
EXEC (N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = 2');
SELECT p.cacheobjtype, p.usecounts, p.size_in_bytes, t.text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE 'SELECT Note FROM dbo.AdHocDemo%'
ORDER BY t.text;

The query that ran once now holds a Compiled Plan Stub of 136 bytes, with a use count of 1. The query that ran twice has a full 16,384 byte plan, but its use count is 1. The second run replaced the stub, so SQL Server compiled that query a second time. After that it is cached like normal.

Now the 200-query loop again.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
SET NOCOUNT ON;
CREATE TABLE #Sink (Note varchar(20));
DECLARE @i int = 1, @sql nvarchar(200);
WHILE @i <= 200
BEGIN
    SET @sql = N'SELECT Note FROM dbo.AdHocDemo WHERE DemoId = ' + CAST(@i AS nvarchar(10));
    INSERT #Sink EXEC (@sql);
    SET @i += 1;
END;
DROP TABLE #Sink;
SELECT p.cacheobjtype, COUNT(*) AS Plans, SUM(CAST(p.size_in_bytes AS bigint)) AS TotalBytes
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.text LIKE 'SELECT Note FROM dbo.AdHocDemo WHERE DemoId =%'
GROUP BY p.cacheobjtype;
SET NOCOUNT OFF;

This time the cache holds 200 stubs totaling 27,200 bytes, down from 3,276,800. Same workload, roughly a hundredth of the memory. You still get one cache entry per query, so you save bytes, not entries.

What it does not fix

The setting trims memory, not compile work. Every new literal still compiles, and a query that repeats compiles one extra time before its plan sticks. The real cure is parameters. Use parameterized queries or sp_executesql so one plan serves every value. I treat this setting as a safety net, not a repair.

Between runs I cleared the cache for this database only, with ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE. I would never run DBCC FREEPROCCACHE on a production server just to test an idea.

EXEC sys.sp_configure 'optimize for ad hoc workloads', 0;
RECONFIGURE;
DROP TABLE IF EXISTS dbo.AdHocDemo;

Look at your single-use plans first, and change the setting only if the numbers justify it.

The plan cache is not a museum, it is a workbench.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.