Cardinality Estimator Behavior, Correlation, Parameter Sensitivity, and Misestimates

Learning outcomes

Good statistics do not eliminate uncertainty. The cardinality estimator (CE) must combine information about predicates, joins, correlation, unknown values, and parameters that can have radically different selectivity. ServiceHub deliberately has correlated columns—N01 rows mostly have priority 1—and a single customer dominates. This lets you observe why “parameter sniffing” is normal compilation behavior and why the real production problem is harmful parameter sensitivity: one reusable plan is poor for materially different parameter populations.

Lab bootstrap for an independently runnable lesson

Run this only when lab10.WorkOrderFact does not already exist. The controlled skew is essential to the CE and parameter-sensitivity demonstrations.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab10') IS NULL EXEC(N'CREATE SCHEMA lab10 AUTHORIZATION dbo;');IF OBJECT_ID(N'lab10.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab10.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab10_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (30000)           ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab10.WorkOrderFact(customer_id,region_code,status,priority,opened_at,closed_at,amount,notes)  SELECT CASE WHEN n <= 18000 THEN 1 ELSE 2 + n % 1999 END,         CASE WHEN n <= 21000 THEN 'N01' WHEN n <= 27000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 20 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 21000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n % 5=0 THEN DATEADD(minute,n+90,'2026-01-01T00:00:00') END,         CAST(20 + (n % 5000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 37=0 THEN REPLICATE('x',160) END  FROM n;  CREATE INDEX IX_lab10_RegionStatusOpened    ON lab10.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);  CREATE INDEX IX_lab10_Customer    ON lab10.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);END;GO

1. Cardinality estimation combines incomplete evidence

The CE predicts row counts at plan operators from statistics, relational rules, constraints, parameters, and model assumptions. No compact summary can preserve every relationship in the raw data. Columns that are strongly correlated can therefore be difficult when only separate single-column statistics are available. Modern CE models also use assumptions more nuanced than a simple “multiply all selectivities” rule, so do not memorize one formula as universal behavior.

USE ServiceHubLab;GOSELECT region_code,priority,COUNT_BIG(*) AS rows_in_groupFROM lab10.WorkOrderFactGROUP BY region_code,priorityORDER BY region_code,priority;GOSELECT COUNT_BIG(*) AS actual_rowsFROM lab10.WorkOrderFactWHERE region_code='N01' AND priority=1;GO

This describes the data truth. Now compare it with the estimated/actual rows in a plan for the same predicate. The gap—not a theoretical formula—is the evidence that matters to the query.

2. Multi-column statistics provide extra density evidence, not a multidimensional histogram

A multicolumn statistics object can provide density information for key prefixes, but the histogram is still on its first key column. Depending on the query shape and CE model, that extra density can improve some equality estimates; it does not encode every arbitrary relationship between columns.

IF EXISTS (SELECT 1 FROM sys.stats WHERE object_id=OBJECT_ID(N'lab10.WorkOrderFact')  AND name=N'ST_lab10_RegionPriority')  DROP STATISTICS lab10.WorkOrderFact.ST_lab10_RegionPriority;GOCREATE STATISTICS ST_lab10_RegionPriorityON lab10.WorkOrderFact(region_code,priority) WITH FULLSCAN;GODBCC SHOW_STATISTICS (N'lab10.WorkOrderFact',N'ST_lab10_RegionPriority')WITH DENSITY_VECTOR,HISTOGRAM;GOSET STATISTICS XML ON;SELECT COUNT_BIG(*)FROM lab10.WorkOrderFactWHERE region_code='N01' AND priority=1;SET STATISTICS XML OFF;GO

Record CardinalityEstimationModelVersion, estimated rows and actual rows. Do not claim the new statistic “must” make the estimate exact; optimizer use depends on the statement and model.

3. Compatibility level affects optimizer/CE behavior; engine build is separate

SQL Server 2025 is engine major version 17.x, while ServiceHubLab uses database compatibility level 170. Compatibility level controls many optimizer behaviors and feature gates without changing the installed engine binary. Lowering an entire production database to chase one plan can disable newer behaviors for every query. Microsoft recommends evaluating queries with Query Store and using narrower interventions when a specific query regresses under a newer CE.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('Edition') AS edition;SELECT name,compatibility_levelFROM sys.databasesWHERE database_id=DB_ID();SELECT name,valueFROM sys.database_scoped_configurationsWHERE name IN ('LEGACY_CARDINALITY_ESTIMATION','QUERY_OPTIMIZER_HOTFIXES');GO

An optional single-query diagnostic can use documented USE HINT controls such as FORCE_LEGACY_CARDINALITY_ESTIMATION. Treat that as a comparison experiment, not as proof that the legacy model is generally better.

4. Parameter sniffing is normal; sensitivity is the problem

When a parameterized stored procedure is compiled, SQL Server can use the parameter value available at compilation to estimate cardinality and choose a plan. That is parameter sniffing. Reusing the plan is normally beneficial. It becomes harmful when data is skewed enough that a plan ideal for a rare value performs poorly for a common value, or vice versa.

CREATE OR ALTER PROCEDURE lab10.GetWorkByCustomer  @customer_id intASBEGIN  SET NOCOUNT ON;  SELECT work_order_id,region_code,status,opened_at,amount  FROM lab10.WorkOrderFact  WHERE customer_id=@customer_id  ORDER BY opened_at DESC;END;GO-- Compile with a rare customer.EXEC sys.sp_recompile N'lab10.GetWorkByCustomer';EXEC lab10.GetWorkByCustomer @customer_id=1999;EXEC lab10.GetWorkByCustomer @customer_id=1;GO-- Recompile with the dominant customer and compare again.EXEC sys.sp_recompile N'lab10.GetWorkByCustomer';EXEC lab10.GetWorkByCustomer @customer_id=1;EXEC lab10.GetWorkByCustomer @customer_id=1999;GO

Capture actual plans and local I/O for both sequences. The exact plan may or may not change on a 30,000-row lab. If it does not, that is still useful: parameter skew exists, but the optimizer may judge one plan adequate at this scale. Chapter 11 covers Parameter Sensitive Plan (PSP) optimization, available from compatibility level 160, which can maintain multiple plan variants for eligible parameter-sensitive queries.

5. Local variables and “unknown” estimates are not a magic cure

Copying a parameter into a local variable can hide the sniffed value and force the optimizer to use more generic estimates. OPTIMIZE FOR UNKNOWN has a similar intent. A generic plan can reduce worst-case sensitivity, but it can also make every execution mediocre. Likewise, OPTION (RECOMPILE) can tailor a plan to each execution but pays compilation cost and changes cache/telemetry behavior. These are interventions with tradeoffs, not best-practice incantations.

DECLARE @customer_id int=1;SELECT COUNT_BIG(*)FROM lab10.WorkOrderFactWHERE customer_id=@customer_id;GODECLARE @input int=1;DECLARE @local int=@input;SELECT COUNT_BIG(*)FROM lab10.WorkOrderFactWHERE customer_id=@local;GODECLARE @customer_id int=1;SELECT COUNT_BIG(*)FROM lab10.WorkOrderFactWHERE customer_id=@customer_idOPTION (RECOMPILE);GO

Use actual plan properties and measured CPU/reads over representative executions. Do not evaluate only a single fast run.

6. Ascending values and changing distributions need time context

A histogram is a snapshot. Newly inserted date/identity values can move beyond its previous high key; auto-update thresholds and CE heuristics determine when/how estimates adapt. For fast-moving tables, inspect statistics freshness and the current data boundary before concluding that the CE “cannot estimate recent rows.” SQL Server 2025 also includes modern Intelligent Query Processing feedback capabilities, but those have explicit compatibility/Query Store prerequisites and belong to Chapter 11 rather than being assumed active here.

Authoritative references

• Cardinality Estimation — CE models, compatibility and comparison workflow
• Parameter Sensitive Plan optimization — multi-plan behavior from compatibility 160
• Query hints — RECOMPILE, OPTIMIZE FOR and USE HINT controls
• Cardinality Estimation feedback — Query Store/compatibility prerequisites
• SQL Server 2025 build versions — CU/build servicing baseline
• SSMS 22 release notes — current client-tool baseline