SQL Server Execution-Plan Lab

Five SQL Server performance pathologies, each reproduced against a seeded two-million-row database, with the execution plan captured before and after and the page counts to go with it.

Every number in the table below is generated by the harness and regenerated on every CI run. None of it is typed by hand. If a pathology stops reproducing, the build fails rather than publishing a stale claim.

Results

Pathology	Logical reads	Reads saved	Median time	Runs
01-implicit-conversion	3,376 to 6	563x	120 ms to under 0.01 ms	5
02-non-sargable	44,447 to 4,427	10x	121 ms to 4.10 ms	5
03-parameter-sniffing	44,447 to 93	478x	22 ms to under 0.01 ms	5
04-missing-covering-index	44,447 to 397	112x	27 ms to 12 ms	5
05-key-lookup-tipping	1,611,166 to 44,075	37x	3926 ms to 2640 ms	5

Why logical reads and not milliseconds

Logical reads are the primary metric here on purpose.

They are near-deterministic: the same plan against the same data reads essentially the same number of pages anywhere. The figures below were measured on a laptop and reproduce on a clean CI runner to within a fraction of a percent, the residue coming from read-ahead and parallelism decisions. Compare that with milliseconds, which move with cache state, CPU contention, and whatever else the machine happens to be doing.

Pathology 05 makes the case on its own. On the CI runner its two variants finished within eight milliseconds of each other, which would suggest the fix does nothing. The forced seek read 1.6 million pages to return exactly what the scan returned in 44,000. Time said “no difference”; the page counts said 37x.

There is also a resolution problem. A warm single-row index seek finishes in well under a microsecond, which is below what SYSUTCDATETIME can resolve, so it times as zero and the speedup comes out as either infinite or undefined. The same seek reads a stable 6 pages against the scan’s 3,376, and that ratio is the real story. Timings are still reported, as a median of five runs after a discarded warm-up, because latency is what people actually feel. They are context, not proof.

Measurements are taken cold (DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS) except for 03-parameter-sniffing, which depends on a warm plan cache and carries a KEEP_PLAN_CACHE marker saying so.

These are relative figures from one machine. They show that a plan change moves work in the stated direction and magnitude. They are not a benchmark of SQL Server.

Reproduce

Requires Docker. Nothing else: no local SQL Server, no ODBC driver, no connection string. Every query runs through docker exec against sqlcmd inside the container, because a performance claim nobody can reproduce is not evidence.

make run

That starts SQL Server 2022, seeds the schema, measures every pathology, and rewrites the table above. The first run pulls a 1.5 GB image and seeds three million rows, so budget about five minutes.

The pathologies

Directory	What it demonstrates
01-implicit-conversion	An NVARCHAR literal against a VARCHAR column converts the column, not the literal, and the seek collapses
02-non-sargable	Rendering a date to text to compare it cannot seek; the half-open range can
03-parameter-sniffing	A plan cached for an outlier parameter punishes every typical call
04-missing-covering-index	Equality-then-range key order, with the aggregated column in INCLUDE
05-key-lookup-tipping	Above the tipping point, a forced seek plus lookups loses badly to a scan

Read 05 first if you only read one. It is the counterexample to “seeks good, scans bad”, and it is the one that changes how people read a plan.

Two of these directories document a mistake made while building this repository, because the wrong version is often more instructive than the right one. 02 explains why the usual CAST(col AS DATE) example proves nothing. 04 explains why index creation must stay out of the timed statement.

Layout

schema/        Tables, baseline indexes, and a deterministic skewed seed
pathologies/   One directory each: setup, the query, and an explanation
lab/           The harness. sqlcmd wrapper, measurement, reporting
results/       Captured plan XML, committed, openable in SSMS

Captured plans in results/ are committed on purpose. Open them in SSMS or Azure Data Studio and check the operators yourself rather than taking the table’s word for it. CI also uploads them as build artifacts.

License

MIT.

This site is open source. Improve this page.