Parameter Sniffing Lab

One prepared query. One healthy-looking fingerprint. Four very different execution realities.

This interactive lab models a parameter-sensitive query plan failure: a small tenant compiles an index-seek plan, the database caches it, and a whale tenant later inherits it. Every query succeeds and the fingerprint-wide average stays at 24 ms, while the whale parameter bucket reaches 1,252 ms p99.

The simulation itself is executable SQLite SQL. A dependency-free Python HTTP adapter exposes it to a responsive browser workbench where you can change the workload, tenant cardinalities, data drift, planner economics, and latency SLO.

What the default scenario shows

Strategy	Fingerprint avg	Whale p99	Max estimate error	Compile CPU	Plan cache
Sniffed seek plan	24 ms	1,252 ms	6,250×	3 ms	1
Generic scan plan	88 ms	88 ms	38×	3 ms	1
Recompile every execution	14 ms	91 ms	1×	108,000 ms	0
Parameter variants	11 ms	88 ms	1×	9 ms	3

The apparently excellent fingerprint average is not a lie. It is an aggregation that merges cheap small-tenant executions with a rare, very expensive parameter bucket.

Why telemetry matters

Query text identifies the question; it does not fully identify the work that ran. The decisive context is:

trace_id
+ query_fingerprint
+ plan_hash
+ parameter_bucket
+ estimated_rows
+ actual_rows
+ statistics_version

Keeping those fields together lets telemetry.sh distinguish plan reuse from ordinary downstream latency, reveal the estimate/actual cardinality gap, and compare mitigations without losing the affected tenant class inside a global average.

The four modeled choices mirror real planner tradeoffs:

• Sniffed seek reuses the plan compiled for the first small parameter.
• Generic scan avoids parameter sensitivity but makes every tenant scan.
• Recompile each chooses well per execution at a severe compile-CPU cost.
• Variant dispatch keeps a small number of plans for cardinality buckets.

Run it

Requirements: SQLite 3 with JSON functions and Python 3.

make run

Open http://localhost:8080.

Run the SQL model directly:

sqlite3 -batch -bail :memory: < src/model.sql | jq

Override any input using SQLite parameters:

sqlite3 -batch -bail :memory: \
  -cmd '.parameter init' \
  -cmd '.parameter set @whale_tenant_rows 500000' \
  -cmd '.parameter set @whale_percent 8' \
  < src/model.sql | jq

Or use the container:

docker build -t parameter-sniffing-lab .
docker run --rm -p 8080:8080 parameter-sniffing-lab

Verify it

The checks cover deterministic SQL output, parameter clamping, the HTTP contract, security headers, invalid-input handling, JavaScript syntax, and the responsive page structure.

make check
make container-test

The GitHub Actions workflow runs both the native and container suites.

Model boundaries

This is a deterministic teaching model, not a database benchmark. Its relational equations make the causal chain inspectable and repeatable:

1. tenant cardinality drifts after statistics were learned;
2. a cached seek plan retains the small-tenant estimate;
3. actual rows determine runtime work;
4. traffic-weighted aggregation hides the rare bucket;
5. alternate planning policies trade latency, scanned rows, compile CPU, and cache size.

SQLite executes the simulation; it is not being presented as an example of SQLite's own query-plan cache behavior.

Further reading

• PostgreSQL PREPARE: generic and custom plan selection
• SQL Server parameter-sensitive plan optimization
• SQLite JSON functions

Built to demonstrate why high-cardinality execution context is essential for understanding production systems with telemetry.sh.