Cardinality Estimation and Parameter Sniffing in SQL Server Explained

Every plan the optimizer builds rests on a guess: how many rows will this operator produce? When that guess is wrong, the whole plan can be wrong — and parameter sniffing is the single most common way it happens.

Cardinality Estimation, in One Sentence

The Cardinality Estimator uses statistics (histograms on indexed/queried columns) to predict row counts at each step of a plan, and picks join algorithms, index usage, and memory grants based on those predictions — not the real data, which it hasn’t seen yet.

Parameter Sniffing: A Concrete Example

This is the exact mechanism previewed back in the Developers & DBAs course’s stored-procedure lesson — the precompilation that makes procedures fast is the same precompilation that causes this.

CREATE PROCEDURE dbo.usp_GetOrdersByStatus @status NVARCHAR(20)
AS
SELECT * FROM dbo.OrderLog WHERE order_status = @status;

-- First call: 'cancelled' matches 50 rows out of 5 million — compiles a Seek-based plan
EXEC dbo.usp_GetOrdersByStatus @status = 'cancelled';

-- Second call: 'pending' matches 3 million rows — REUSES the seek-based plan, now terrible
EXEC dbo.usp_GetOrdersByStatus @status = 'pending';

What’s Actually Happening

Diagnosing It

-- Compare estimated vs actual rows in the plan (Ctrl+M in SSMS)
-- A huge gap on a parameterized proc call is the signature of parameter sniffing

-- Force a fresh compile per call to test the theory (not a permanent fix, a diagnostic)
EXEC dbo.usp_GetOrdersByStatus @status = 'pending' WITH RECOMPILE;

If the RECOMPILE version is fast, you’ve confirmed parameter sniffing. Real fixes include OPTION (RECOMPILE) on the specific statement, query hints, or splitting into separate procedures for genuinely different data distributions — each with real trade-offs in CPU cost vs plan stability.