In this lesson, you will learn one of the most important performance concepts in SQL Server:
cardinality estimation—SQL Server’s prediction of how many rows will flow through each step of a query plan.
These predictions are driven primarily by statistics. If the estimates are wrong, SQL Server often chooses the wrong plan:
wrong join type, wrong memory grant, wrong parallelism decision, and the result is slow or unstable performance.
Big Idea: Many performance problems that look like “index issues” or “CPU/memory issues” are actually
estimate issues. When SQL Server guesses the wrong row counts, everything downstream becomes risky:
sorts spill, hash joins spill, nested loops explode in cost, and queries are fast one day and slow the next.
You will learn:
120–180 minutes (deep concepts + demos + lab)
SQL Server’s optimizer must choose a plan before it runs the query. That means it must predict how many rows will match your filters,
how many rows will be joined, and how expensive each operator will be. The optimizer cannot scan the whole table every time
just to “find out,” so it uses statistics as a fast summary of the data distribution.
Key teaching point: Indexes often automatically have statistics, but statistics can also exist without indexes.
Sometimes creating statistics alone improves performance.
Cardinality estimation is SQL Server predicting how many rows will flow through each operator in the plan.
These estimates influence:
Why this matters: If the optimizer estimates 10 rows but the actual result is 10 million,
it may choose nested loops and cause massive work. If it estimates 10 million but actual is 10,
it may choose a hash join and waste memory/time. Both lead to poor performance.
One of the easiest ways to detect a stats/estimation problem is to compare:
Estimated Number of Rows vs Actual Number of Rows in an execution plan.
When those numbers are far apart, the optimizer made a bad guess. Bad guesses usually lead to a bad plan.
Key teaching point: Statistics problems are not always “update stats.” Sometimes you need
a better index, a query rewrite, or different predicate patterns to make estimation accurate.
When tables change heavily (many inserts/updates/deletes), stats can become outdated.
If SQL Server is not updating stats effectively, estimates may reflect yesterday’s data, not today’s.
If one value is extremely common and others are rare (example: Status = ‘Active’ for 95% of rows),
the optimizer may mis-estimate certain filters, especially with parameterized queries.
SQL Server can compile a plan based on the first parameter values it sees, then reuse that plan for other values.
If the first parameter is “rare” but later parameters are “common,” the reused plan may be wrong for typical workload.
When you wrap columns in functions or cause implicit conversions, the optimizer can struggle to use stats effectively,
leading to poor estimates and scans.
The optimizer often assumes independence between predicates unless it has statistics that reflect correlation.
When columns are correlated (City + ZipCode, Category + SubCategory), estimates can be incorrect.
The goal is not “update stats daily.” The goal is: keep statistics accurate enough to produce stable good plans
without wasting maintenance time or causing unnecessary plan churn.
Key teaching point: Statistics maintenance is a balancing act:
accuracy vs overhead vs plan stability.
These scripts help you inspect statistics, find when they were updated, and identify candidates for stats maintenance.
They are safe and do not modify data.
/* Stats last updated + row counts (current DB) */ SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name, OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps, sp.modification_counter FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp ORDER BY sp.last_updated ASC;
How to interpret: Look for large tables with old last_updated dates or high modification_counter.
Those are common candidates for stats review.
/* Replace dbo.YourTable and YourStatName */
DBCC SHOW_STATISTICS ('dbo.YourTable', 'YourStatName');
How to interpret: This command shows the histogram and density details.
It helps you understand skew (very common values) and whether sampling may be missing important distribution details.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name, OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, i.name AS index_name, s.auto_created, s.user_created FROM sys.stats s LEFT JOIN sys.indexes i ON s.object_id = i.object_id AND s.name = i.name ORDER BY schema_name, table_name, stats_name;
How to interpret: Some stats align with indexes. Others are auto-created stats.
Both can matter, but index stats are often most critical for plan selection.
Use when stats are stale and you have evidence of estimate mismatch. Target the specific table/statistics that matter
to your slow query, not the entire database blindly.
Sometimes stats are fine, but the query still must scan too much data. The best fix is a supporting index
that allows SQL Server to seek/filter earlier.
If a predicate uses functions on columns or implicit conversions, the optimizer may not be able to use stats effectively.
Rewriting often improves estimation and index usage.
When one plan cannot fit all parameter values, you may need techniques to improve stability. The correct approach is evidence-based:
confirm plan variation using Query Store and test carefully. (We will cover plan stability strategies in later lessons and case studies.)
Students will inspect statistics freshness, identify stale/high-change stats, and explain how stats influence plan quality.
Prompt:
“You see a query plan where Estimated Rows = 1, but Actual Rows = 800,000 on the first operator.
What types of problems can this cause later in the plan (joins, memory grants, spills)?
What is the first evidence you would check to decide whether stats are the issue?”
In the next lesson, we will cover Database Engine Tuning Advisor in SQL Server.
You will learn how to use tuning recommendations safely, how to evaluate suggested indexes, and how to avoid
creating index bloat or harming write performance.
Not a member yet? Register now
Are you a member? Login now