In one line: an application that writes values into its SQL text makes every value a new query, with its own compilation and its own cached plan, used once. Parameters fix it; optimize for ad hoc workloads only makes each single-use plan smaller.
Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/plan-cache-bloat on GitHub.
What you’ll see
- Thousands of
Adhocplans withusecounts = 1insys.dm_exec_cached_plans. In the demo, 1,000 values of one join took 88 MB of plan cache. - Many plans sharing one
query_hash: different text, same query. - High compilations per second compared with batch requests per second.
- Two prepared plans for one simple query,
(@1 tinyint)and(@1 smallint), when SQL Server parameterized it itself.
Why it happens
The plan cache finds a plan by its exact text. WHERE CustomerID = 42 and WHERE CustomerID = 43 are two texts, so two compilations and two plans.
SQL Server parameterizes some queries itself. Simple parameterization only covers simple queries (a single-table query did, the join didn’t), takes the parameter’s type from the literal, so 42 and 420 make two plans, and still caches a small shell for every text. Forced parameterization covers more queries, in the whole database, and also leaves a shell per text.
optimize for ad hoc workloads caches a stub of under half a KB the first time a text runs, and the full plan only the second time. Every text is still compiled.
What to do
- Fix the application: send values as parameters (
sp_executesql, or the parameter objects of the client library). In the demo: one plan, used 1,000 times, 88 KB. - Turn on
optimize for ad hoc workloadswhen single-use plans are using memory you need. It’s an instance setting, and changing it doesn’t need a restart. - Consider forced parameterization for a database whose application you can’t change, after testing: it changes every query, including ones that needed a plan for their own values.
What doesn’t help
- Clearing the plan cache. It fills up again with the same plans, and every other query has to be compiled again.
- Relying on simple parameterization. It skips joins and most real queries, and makes a plan per parameter type when it does apply.
Check your server
-- Single-use ad hoc plans, and the queries that make most of them
-- (one query_hash with many plans).
SELECT COUNT(*) AS single_use_plans,
SUM(CAST(size_in_bytes AS bigint)) / 1048576 AS mb
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1;
SELECT TOP (10) qs.query_hash, COUNT(*) AS cached_plans,
SUM(qs.execution_count) AS executions,
MIN(SUBSTRING(st.text, 1, 200)) AS sample_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY qs.query_hash
HAVING COUNT(*) > 1
ORDER BY cached_plans DESC;
See it happen
The plan-cache-bloat demo sends one query 1,000 times with a different value each time, and counts what the plan cache holds:
- as ad hoc SQL
- with simple parameterization, and the two plans it makes
- with
optimize for ad hoc workloads - with forced parameterization
- with
sp_executesql
It changes optimize for ad hoc workloads for the instance while it runs, and puts it back at the end.
Related: parameter sniffing · slow in the app, fast in SSMS · filtered indexes
Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5) and SQL Server 2022 RTM-CU25 (16.0.4255.1), Linux containers. Your numbers will differ; the behaviour shouldn’t.
Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.
