What is the plan cache, and how does plan reuse work?
Quick Answer
The plan cache stores compiled execution plans so repeated queries skip the expensive optimization step. Parameterised queries and stored procedures reuse plans well. Non-parameterised ad-hoc SQL (values baked into the text) gets a separate plan per distinct string, bloating the cache and wasting CPU on compilation.
Every query has to be compiled into a plan, which is CPU-intensive, so SQL Server caches the plan and reuses it when the same query comes back. Stored procedures and properly parameterised queries reuse plans nicely. The problem is ad-hoc SQL where the literal values are concatenated into the string — each distinct value is a different query text, so you get thousands of single-use plans filling the cache. “Optimize for ad hoc workloads” helps by caching just a stub until a query proves it's repeated. For actually managing plans in production I use Query Store rather than clearing the cache.
- “Optimize for ad hoc workloads” stores a lightweight stub on first execution, a full plan only on the second.
- Forced/simple parameterization can auto-parameterise trivial ad-hoc queries.
- Plans are evicted under memory pressure or invalidated by schema/stats changes.
- Prefer Query Store over
DBCC FREEPROCCACHEfor diagnosing and forcing plans in production.