Plan Caching & Parameter Sniffing

## Why a plan cache exists Turning SQL text into a physical plan is expensive. Parsing is cheap, binding is moderate, but cost-based optimization can dominate: the optimizer enumerates access paths and join orders and prices each with statistics, and that search grows quickly with the number of joined relations. For an OLTP statement that reads one row by primary key, compiling can easily cost more CPU than running it - sometimes by an order of magnitude. The fix is obvious: compile once, run many times. The **plan cache** (also called the statement cache, procedure cache, or shared SQL area) holds compiled plans in shared memory so subsequent executions of the same statement skip straight to execution. ## What an entry holds A cache entry typically carries: - the **physical plan** - the operator tree with chosen access paths, join algorithms, join order and memory grants; - **dependency metadata** - the catalog objects referenced, so DDL on any of them can invalidate the entry; - **statistics versions** used at compile time, so a statistics refresh can trigger recompilation; - **usage counters** - execution count, average duration, memory - used both for eviction policy and for diagnostics; - for parameterized statements, the parameter shapes and possibly the values used at first compile. The cache is a memory-bounded shared structure with an eviction policy - typically some cost-aware LRU that considers how expensive an entry was to build and how often it is used. ## The cache key: text plus context The crucial and often-misunderstood part is the key. Engines key on the **statement text**, matched essentially exactly. Practical implications: - extra whitespace, a line break, or different capitalization of keywords can produce a different key; - different literal values produce different text and therefore different keys; - comments embedded in the statement count as text. Text alone is not sufficient, though, because the same text can legitimately mean different things in different sessions. So the key also includes context that could change binding or plan choice: - **schema search order / default schema** - `SELECT * FROM orders` may bind to different tables for different users; - **the executing user**, where privileges or row-level policies affect the plan; - **session settings** that influence the optimizer or semantics. Engines do not normalize text aggressively because doing it safely is itself work, and because two texts that differ only in a literal do not always deserve the same plan (see parameter sniffing). ## Why parameters are the real fix A statement written as `SELECT * FROM orders WHERE customer_id = 8814` produces a distinct cache key for every customer ID an application ever uses. The same statement written with a bind parameter - `... WHERE customer_id = ?` - is a single, stable text, so: - one entry serves every execution; - compilation happens once, not per distinct value; - the cache stays small and the hit rate stays high. With a **prepared statement**, the client explicitly asks the server to parse and bind once and returns a handle; subsequent executions send only the handle plus values. That makes plan reuse deliberate rather than incidental. Note it also *forces* the question of which value's plan you got - the topic of parameter sniffing. ## Server-side auto-parameterization Because unparameterized applications are so common, several engines will, for simple statements, automatically replace literals with parameters to create a shared entry. This helps but is deliberately conservative: engines apply it only where they judge that the plan is unlikely to depend on the literal, since replacing a literal that *does* matter would trade a compile cost for a plan-quality problem. ## What a healthy cache looks like Operationally you want: - **high hit ratio** for the repetitive OLTP workload; - **a bounded number of distinct entries** relative to the number of distinct statement shapes in the application - if you have thousands of entries that differ only in literals, the application is not parameterizing; - **low compile rate** under steady load - a high compile rate means either churn from literals, or repeated invalidation. ## When you do *not* want reuse Plan caching assumes one plan is good for all executions of that text. That assumption breaks when a predicate's selectivity varies wildly with the parameter value, or for large one-off analytical queries where the compile cost is negligible next to runtime and a value-specific plan is worth far more. In those cases the right answer is to make the engine re-plan rather than to reuse - which is exactly the tradeoff the generic-versus-custom-plan decision formalizes. ## Summary for an interview Cache the expensive artefact (the plan), key it on the thing that determines it (text plus binding context), and control the key deliberately with bind parameters. Everything else about plan caching - sniffing, pollution, recompilation - follows from those three sentences.