Concept questionHard~9 min

Interview question

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.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I would be careful because plan fixes can help one case and hurt another. I would collect the slow parameter values, actual plan, runtime metrics, row counts, and frequency before changing anything. If data skew is the issue, I might consider query hints only as a last resort, or use query shape changes, filtered indexes, plan forcing with review, or separate paths for extreme tenants. I would keep monitoring after the fix because data distribution changes over time.

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.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain what cardinality means.
Does not know where to find estimate mismatches.
Ignores skewed data.
Applies a risky hint as the first fix.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

What does a spill indicate?
How would skewed tenant size affect a plan?
When would updating statistics help?

Continue practice

No explicit flow link is set yet, so these are nearby drills from the same path or taxonomy.

Avoid Fanout TotalsCoding problem / Medium / ~25 minContinue Daily Order KPIsCoding problem / Medium / ~25 minContinue Running BalanceCoding problem / Medium / ~25 minContinue

Opening drill

Concept questionHard~9 min

Interview question

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

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.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I would be careful because plan fixes can help one case and hurt another. I would collect the slow parameter values, actual plan, runtime metrics, row counts, and frequency before changing anything. If data skew is the issue, I might consider query hints only as a last resort, or use query shape changes, filtered indexes, plan forcing with review, or separate paths for extreme tenants. I would keep monitoring after the fix because data distribution changes over time.

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.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain what cardinality means.
Does not know where to find estimate mismatches.
Ignores skewed data.
Applies a risky hint as the first fix.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

What does a spill indicate?
How would skewed tenant size affect a plan?
When would updating statistics help?

Continue practice

No explicit flow link is set yet, so these are nearby drills from the same path or taxonomy.

Avoid Fanout TotalsCoding problem / Medium / ~25 minContinue Daily Order KPIsCoding problem / Medium / ~25 minContinue Running BalanceCoding problem / Medium / ~25 minContinue

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

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