Query Plan Cache
A query plan cache stores the compiled execution plan for a parameterized query, so the optimizer cost is paid once and subsequent executions reuse the plan, unless parameter values warrant.
Definition
A query plan cache stores the execution plan produced by the optimizer keyed by the parameterized query text, so that repeated executions of the same query shape skip optimization and reuse the plan, with parameter sniffing and adaptive plans handling the case where one plan does not fit all parameter values.
Context and scope
Applied in every relational database, where optimization cost is significant and most workloads repeat a small set of query shapes.
Common misconceptions
- A cached plan is always optimal — it is optimal for the parameters that produced it; parameter sniffing can cache a plan good for one value and bad for another
- Plan caching makes parameterization unnecessary — parameterization is what makes the cache hit possible; literal SQL misses the cache every time
Anti-patterns
- Sending literal SQL that differs per parameter value, so every execution is a cache miss and pays full optimization cost
- Caching a plan built with skewed parameter values, so every subsequent execution uses a plan wrong for its actual values
Related concepts
Authoritative source
https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide
What it is
A query plan cache stores the execution plan produced by the optimizer keyed by the parameterized query text, so that repeated executions of the same query shape skip optimization and reuse the plan, with parameter sniffing and adaptive plans handling the case where one plan does not fit all parameter values. Applied in every relational database, where optimization cost is significant and most workloads repeat a small set of query shapes. The data model and the access patterns are co-designed: a schema that is correct in the relational sense can be catastrophic for the queries the workload actually issues.
How it works
Indexing decisions follow the query shapes, not the column importance: a covering index turns a table scan into an index-only scan, but every index adds write amplification and storage cost. Partitioning aligns with the access dimension so a query touches one partition, not all. Transaction scope is minimized to hold locks briefly; long transactions are the root of contention and deadlock.
When to apply and pitfalls
Apply when data volume or query latency has a concrete business cost. The common pitfall is indexing for the current workload and ignoring the access patterns that growth will introduce — the index that helps today can serialize writes tomorrow. Another is treating the database as a black box, missing that the optimizer’s plan choice depends on statistics that drift as data changes.