The instance had 128 GB of memory and the buffer pool could not keep fifteen minutes of hot data in cache. The memory clerk breakdown told the story: 41 GB sat in the plan cache, and when I bucketed the cached plans by execution count, ninety-two percent of them had run exactly once. The application was building SQL by string concatenation with every order id baked into the text, and SQL Server was dutifully compiling and storing a fresh plan for every single execution of the same query shape.
Plan cache pollution is one of those failures that looks like a memory problem, bills you like a CPU problem, and is actually an application problem. Here is how I measure it and which of the three levers — the stub option, forced parameterization, or the app fix — to pull on SQL Server 2016 through 2022.
How does unparameterized SQL flood the plan cache?
Every distinct statement text gets its own cache entry. SELECT with WHERE OrderId = 41823 and the identical statement with 41824 are two different plans, because SQL Server hashes the batch text to find a cached plan before compiling — change one literal and the hash misses, the engine compiles again, and stores the result as an Adhoc plan. Simple parameterization rescues a small subset, the trivial shapes whose literals the engine feels safe swapping for parameters, but anything past the simplest forms is disqualified, and ORMs that inline values or build dynamic IN lists never qualify. The cache fills with thousands of single-execution clones of one query, each paying compile CPU once and cache memory indefinitely.
The compounding part is that compiles are not free either. A workload spending twenty percent of its CPU compiling plans it will never reuse is paying the tax twice: once on the processor, once on the memory that should have held data pages.
How do I measure the single-use bloat?
sys.dm_exec_cached_plans, grouped by objtype and bucketed by usecounts, with size_in_bytes summed. The objtype value Adhoc is the smoking gun. Prepared plans with usecounts of one tell a subtler story: parameterization happened but reuse did not, which means the client is changing the statement shape itself — a dynamic column list, a varying IN-list arity — not just the literal values.
SELECT cp.objtype,
CASE WHEN cp.usecounts = 1 THEN 'single-use'
WHEN cp.usecounts < 10 THEN 'low reuse'
ELSE 'reused' END AS reuse_bucket,
COUNT(*) AS plan_count,
SUM(cp.size_in_bytes) / 1024 / 1024 AS size_mb
FROM sys.dm_exec_cached_plans AS cp
GROUP BY cp.objtype,
CASE WHEN cp.usecounts = 1 THEN 'single-use'
WHEN cp.usecounts < 10 THEN 'low reuse'
ELSE 'reused' END
ORDER BY size_mb DESC;Read the output against the buffer pool, not in isolation. The plan cache does not get its own memory region — it draws from the same pool your data pages live in, so 41 GB of single-use plans is 41 GB of buffer pool that does not exist. On the CPU side, sys.dm_os_performance_counters has SQL Compilations/sec; when that counter sits near Batch Requests/sec, nearly every request is paying for a compile.
What does optimize for ad hoc workloads actually do?
It stores a stub instead of a plan on first execution. The option is instance-level, set through sp_configure: the first time a batch compiles, the engine caches only a compiled plan stub — a few hundred bytes holding the text hash — and only on the second execution of the identical text does it compile and cache the full plan. Single-use plans shrink from tens of kilobytes to a few hundred bytes apiece. The cost is one extra compile for statements that genuinely run exactly twice, which in an ad-hoc-heavy workload is noise.
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;This is the first lever I pull on any instance serving a string-concatenation application — it is off by default everywhere, Azure SQL Database included, so it stays a deliberate change — and Microsoft's own guidance has recommended it for ad-hoc-heavy workloads for years, which tells you how the product team feels about it. One honest limitation: the stub still requires the compile, so the option fixes memory, not compile CPU. If your pain is processor burn from compilations, the stub alone will not save you.
Should I turn on forced parameterization?
Only after measuring, because forced parameterization trades cache pressure for plan quality risk. At the database level — ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED — the engine replaces nearly all literals with parameters at compile time, so every literal variant of a query shares one plan. The flood stops cold. But the surviving plan is compiled against whatever parameter values the engine saw first, and that is parameter sniffing, the same disease I keep a whole toolkit for in my parameter sniffing notes. A plan compiled for Status = 'Open' can be catastrophic for Status = 'Closed' when the data is skewed, and forced parameterization makes that bet for every query in the database at once.
My default stack: optimize for ad hoc workloads at the instance level for safety, forced parameterization per database only when the cache is still drowning and the query shapes are uniform, and template plan guides — sp_get_query_template plus sp_create_plan_guide — as the scalpel for the handful of query shapes that need forcing without inflicting it on everything else.
How do I get relief before the app ships a fix?
DBCC FREESYSTEMCACHE can evict selectively: FREESYSTEMCACHE('SQL Plans') clears the ad-hoc and prepared cache while leaving object plans — procedures, functions, triggers — warm, and on current versions you can scope it to a specific Resource Governor pool. It is relief, not treatment; the flood resumes with the next request. But it is exactly the right move when the plan cache is strangling the buffer pool at 3 PM on a Tuesday and the app fix is a sprint away. Paired with the stub option, the bleeding usually stops being an incident the same afternoon.
What is the real cure?
The cure lives in the application, and it is unglamorous: parameterize the queries. sp_executesql with real parameters, parameterized command objects in the client library, ORM configurations that stop inlining values — some frameworks inline in specific patterns even when you think they do not — and a code review rule that any literal in generated SQL needs a written reason. Until that lands, run the full stack: stub option on, plan cache size on the dashboard, forced parameterization only where earned. The trend line to watch is megabytes of cache with usecounts of one, not raw cache size. A large plan cache full of reused plans is the engine working exactly as designed; a large one full of single-use plans is a memory leak the compiler maintains for you.
Plan cache pressure with MonPG when SQL Server support ships
What I want graphed is the split, not the sum: cache megabytes by objtype, the single-use fraction over time, and compilations per second next to batch requests per second, all against buffer pool size so the theft is visible. MonPG monitors PostgreSQL in production today; SQL Server support is in active development and on the roadmap, and the SQL Server monitoring (coming soon) page says where that stands. Until then, the measurement query above running on a schedule into a table you chart yourself is a perfectly good monitoring stack.