What the Cardinality Estimator Does and When It Is Wrong

The cardinality estimator predicts how many rows each part of a query will process. Those predictions guide joins, access methods, and memory grants. A wrong estimate can send a valid query down an expensive path.

An Estimate Is a Planning Input

SQL Server must choose a plan before it knows every result of executing that plan. It uses information about the tables and predicates to predict intermediate row counts. The optimizer then compares possible strategies. Cardinality estimation is one input to that choice, not the entire optimization process.

A nested loops join can make sense for a small input and become expensive for a much larger one. A low estimate can also contribute to an undersized memory grant. Those are possibilities to investigate. A large estimate error doesn’t prove that changing it will improve the whole query.

SELECT
    name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Statistics Describe the Data Indirectly

Statistics summarize a distribution rather than recording every possible relationship among columns. Their histogram describes the first statistics key column. Density information adds other useful detail. The estimator must still make assumptions when combining predicates or working with information the summary doesn’t capture.

Inspect the statistics that support the query before blaming the estimator model. Check when they were updated and how much of the table was sampled. A recent update isn’t automatically a useful distribution for every predicate. Data can change sharply after a load.

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS statistics_name,
    p.last_updated, p.rows, p.rows_sampled,
    p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
JOIN sys.tables AS t ON t.object_id = s.object_id;

Use the output as evidence about the available statistics. Don’t interpret modification count as a complete measure of how relevant a histogram remains. A small number of strategically placed changes can matter to one predicate. A large number of changes can leave another distribution similar.

Watch the Estimate at the First Divergence

Capture an actual plan for a representative execution in a suitable test environment. Compare estimated and actual rows at the operators, accounting for execution counts. Find where the difference begins. A later operator can inherit an error created much earlier in the plan.

Check the parameter values used for compilation and execution. A cached plan can reflect one part of a skewed distribution while the current request asks for another. Also inspect implicit conversions and expressions around filtered columns. They can make the available statistical information harder to use.

Don’t read a graphical percentage as measured elapsed time. Estimated operator cost is a model value. Use runtime evidence for runtime conclusions. The plan is most useful when each suspicious property leads to a focused question you can test.

Know Which Model You Are Using

SQL Server 2014 introduced the newer cardinality estimator under compatibility level 120. Later releases added further behavior and improvements. The legacy estimator can still be selected in supported configurations. Check both database settings and the plan’s cardinality estimation model version.

SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN
    (N'LEGACY_CARDINALITY_ESTIMATION',
     N'PARAMETER_SNIFFING',
     N'QUERY_OPTIMIZER_HOTFIXES');

A database setting doesn’t reveal every query-level hint or forced behavior. Inspect the actual statement and plan as well. Avoid changing compatibility level across a busy database merely to test one troublesome query. That change can affect many statements at once.

Use Query Store to preserve a comparison when reviewing a compatibility change. Test representative workloads rather than a single favorite query. The right model for one statement doesn’t settle the best database-wide policy.

Repair the Information Before Forcing the Answer

If statistics are stale, test an appropriate statistics update. If the query hides a searchable column behind an expression, consider rewriting that predicate. If columns are correlated, investigate statistics and query design that better represent the relationship. Each action should address observed evidence.

Full-scan statistics updates have a cost and don’t solve every estimation problem. They still summarize data and don’t reveal every correlation. Likewise, rebuilding every index is a broad action with unrelated effects. Choose the smallest test that can confirm or reject the current explanation.

For a controlled comparison, record IO and timing alongside the plan. The following enables those measurements for the next statements in your session. Run your representative query, then turn the options off when the comparison is complete.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run the representative query here.
-- After recording its output, disable both options.
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Treat Hints as Decisions to Maintain

A targeted hint or plan choice can provide a practical mitigation when the cause is understood. Record why it exists and which workload was tested. Review it when data shape or the engine version changes. A permanent hint based on a temporary distribution can become tomorrow’s problem.

I don’t need every estimate to be exact. I need the plan to make good choices for the work that matters. Keep the comparison tied to measured behavior. An improved estimate that doesn’t improve the workload is interesting evidence, not a finished tuning result.

A row estimate is not a promise, it is a prediction whose effect you can test.

This post was rewritten from scratch in September 2026. The original, published on 2014-04-26, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.