An application builds SQL by concatenating literal values directly into the statement text instead of sending bind parameters. Beyond the SQL-injection risk, describe what this does to the database's execution plan cache and to server CPU under load.
answer
- one literal = one cache key = one compile
- bounded cache: garbage evicts hot plans
- high CPU, low I/O, high hard-parse rate
- cache latch contention under concurrency
- metrics fragment: no statement looks expensive
basics
~20 sEvery distinct literal produces a distinct cache key, so the plan cache fills with thousands of near-identical single-use entries. Useful plans get evicted, the hit rate collapses, and the server burns CPU re-optimizing the same query shape over and over, adding latency to every call.
solid answer
~60 sThe plan cache is keyed on statement text, so `WHERE id = 41` and `WHERE id = 42` are different entries. Concatenating literals therefore turns one logical statement into as many cache entries as the application has distinct values. The damage compounds: - **Cache pollution.** Memory fills with single-use entries; the cache is bounded, so genuinely hot plans are evicted by garbage. - **Compile CPU.** Every new literal forces a full parse, bind and cost-based optimization. Optimization is the expensive part, and for a short OLTP query it can exceed execution cost several times over. Under concurrency this shows up as high CPU with unremarkable I/O. - **Latency and contention.** Compilation takes latches/locks on shared cache structures, so many sessions compiling at once serialize on them - a classic "CPU pegged, queries simple" incident. - **Lost diagnostics.** Per-statement metrics fragment across thousands of entries, so no single query looks expensive even though the shape dominates the workload. The fix is bind parameters everywhere - one stable text, one entry, one compile. Server-side auto-parameterization mitigates simple cases but is deliberately conservative and should not be relied on.
code
sql · 6 lines-- application-side concatenation: N entries, N compiles
SELECT id, total FROM orders WHERE customer_id = 8814 AND status = 'PAID';
SELECT id, total FROM orders WHERE customer_id = 9021 AND status = 'PAID';
-- bound values: 1 entry, 1 compile
SELECT id, total FROM orders WHERE customer_id = ? AND status = ?;go deeper
Say the cache is keyed on text, so each literal makes a new entry and forces another compile; bind parameters keep one entry.
Add eviction of hot plans, rising CPU from repeated optimization, and the fact that metrics fragment so the real cost hides.
Describe the diagnosis path - entry counts, hard-parse rate, cache contention waits - fix at the data-access layer, and note that parameterizing moves you into the sniffing tradeoff.
Treat compile CPU and shared-cache memory as capacity inputs: set client-library standards, budget cache pressure across tenants, and decide where value-specific planning is deliberately preserved.
## The mechanism A plan cache is keyed on statement text plus binding context, matched near-exactly. An application that writes `"SELECT ... WHERE customer_id = " + id` therefore emits a *different statement* for every customer. The database has no way to know these are the same shape; it sees new text, misses the cache, and compiles. Compilation means parse, bind against the catalog, rewrite, and cost-based optimization. The last step dominates: the optimizer enumerates access paths and join orders and prices them against statistics. For a single-row lookup, compiling can cost several times what executing costs. Doing that once is fine. Doing it on every request is a self-inflicted CPU tax proportional to your request rate. ## The four symptoms **1. Cache pollution and eviction.** The cache is a bounded shared-memory region. Filling it with entries that will each be used exactly once forces eviction of the entries that are used constantly. So it is not merely wasted memory: it actively degrades the statements that were behaving well, and the degradation is nonlinear once the cache starts thrashing. **2. Compile CPU.** Steady-state CPU rises with no matching rise in rows read or I/O. Monitoring shows a high compilation or hard-parse rate. The workload looks trivial per statement, yet the server is saturated. This is the signature symptom. **3. Concurrency contention.** Inserting into and evicting from the shared cache requires short-term exclusive access to shared structures. When hundreds of sessions compile simultaneously, they queue on those internal latches/mutexes. Throughput then collapses faster than CPU utilization alone would predict, because sessions are waiting on each other rather than doing work. **4. Blind diagnostics.** Workload analysis groups by statement. With literals inlined, the top-statement report shows thousands of statements at 0.03% each instead of one statement at 40%. The dominant cost is invisible precisely because it is spread thin. Engines that expose a normalized statement digest help, but only if you look for it. ## Why bind parameters fix all four Sending `WHERE customer_id = ?` plus a value keeps the text constant: - one cache entry per statement shape, so cache size scales with the *application's* code, not with its data; - one compilation, then pure execution; - no repeated latch contention on cache structures; - clean per-statement metrics. And, as a bonus that is usually stated first, values travel outside the SQL text, so they can never be interpreted as SQL - eliminating injection for those inputs. ## Auto-parameterization: a mitigation, not a solution Many engines will substitute parameters for literals automatically in simple statements to create a shareable entry. It is deliberately conservative because collapsing a literal that genuinely changes the best plan trades a compile saving for a much larger execution regression. Depend on it and you get inconsistent behaviour across statement complexity and engine versions. Treat it as a safety net for legacy code, not as a design. ## The legitimate exceptions Parameterizing is not universally right, and a strong answer says so: - **Highly skewed predicates.** If one value matches 80% of rows and others match a handful, a single shared plan will be wrong for one class of executions. Here you *want* value-specific plans - either by re-planning per execution or by splitting into distinct statements. - **Large analytical queries.** When a query runs for a minute, spending 200ms optimizing precisely for the actual value is trivially worth it, and reuse buys nothing. - **Structural variation.** Column lists, table names, sort direction and `IN` list arity cannot be parameters. Building the *shape* dynamically is legitimate; the fix there is to keep the number of distinct shapes small - for example by normalizing the `IN` list length into buckets - and still bind the values. ## What to say when asked to fix it Measure first: cache entry count versus distinct application statements, compile/hard-parse rate, cache hit ratio, and top-wait analysis for cache-related contention. Then fix at the source - the data-access layer or ORM configuration - because a database-side workaround only masks the client behaviour. Finally, keep an eye on what plan reuse now gives you: once you parameterize a skewed predicate you have moved the problem into the parameter-sniffing regime, and that must be evaluated rather than assumed away.
- How would you confirm this is happening on a running server rather than guessing?Compare the number of distinct cached statements with the number of distinct statements your application actually contains - a large gap means literals are being inlined. Corroborate with the compilation or hard-parse rate, the cache hit ratio, and top waits showing contention on plan-cache structures. Engines that expose a normalized statement digest let you group the near-duplicates and see the true cost of the shape.
- Are there cases where you deliberately do not want the same plan reused for every value?Yes - when a predicate's selectivity varies dramatically across values, such as a tenant column where one tenant holds most rows, a single shared plan will be wrong for one class of executions. Large analytical queries are another case, since optimization time is negligible compared with runtime, so a value-specific plan is worth compiling every time. Those cases call for re-planning per execution or splitting the statement, not for going back to concatenated literals.
Like reprinting an entire assembly manual every time one part number changes, then storing all the near-identical copies in a shelf that pushes out the manuals you actually use daily.
saying these in an interview costs you the question
- Treating literal concatenation as purely a security issue with no performance dimension.
- Believing the database normalizes literals automatically so it does not matter.
- Assuming the only cost is memory, missing the compile CPU and cache-latch contention.
- Proposing to enlarge the plan cache instead of fixing the client's parameterization.
- Claiming parameterizing is always correct, with no awareness of skewed predicates.