How can stale statistics or bad cardinality estimates make a SQL query slow?

Explain statistics, cardinality, and bad query plans

Explains how statistics and cardinality estimates affect plan choice, joins, memory grants, and slow queries.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

The optimizer uses statistics to estimate how many rows a predicate or join will return. If those estimates are wrong, it may choose the wrong join type, scan too much data, request too much or too little memory, or pick an index that is bad for the real parameter value. I would compare estimated rows with actual rows in the execution plan to spot this.

Full answer below

Practice focus

Statistics, cardinality estimation, plan choice, join algorithms, memory grants, spills, and diagnosis.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
This drill moves beyond visible SQL syntax into how the optimizer estimates work. It is a strong signal for production database troubleshooting.
The optimizer uses statistics to estimate how many rows a predicate or join will return. If those estimates are wrong, it may choose the wrong join type, scan too much data, request too much or too little memory, or pick an index that is bad for the real parameter value. I would compare estimated rows with actual rows in the execution plan to spot this.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
Stale statistics can happen after large data changes, and skewed data can be hard even with fresh statistics. A query that returns one customer's rows may behave very differently for a huge customer than for a small one. Bad estimates can lead to nested loops over too many rows, hash joins where a seek would be better, spills to disk, or slow sorts. The fix might be updating statistics, rewriting predicates, changing indexes, or handling skewed parameters differently.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Looking only at query text and ignoring estimated versus actual rows.
Updating statistics as a ritual without confirming the issue.
Assuming one plan is equally good for all parameter values.
Using hints before understanding the workload.

How can stale statistics or bad cardinality estimates make a SQL query slow?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue practice

Tech stacks

Skill areas

Suggest an improvement