A production endpoint using EF Core became slow after data grew. How would you investigate it?

Investigate a slow EF Core query in production

Investigates a growing-data EF Core slowdown through one correlated request, generated SQL, representative parameters, execution plan, waits, query shape, measured repair, and guarded rollout.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I start from one affected endpoint or trace and confirm when, where, and for whom latency grew. I compare total request time with database dependency time, command count, error rate, connection waits, and recent releases. Then I identify the exact EF query, capture its generated parameterized SQL safely, and reproduce it with representative data and parameter values. I do not start by adding AsNoTracking, changing Include, or creating an index from the LINQ alone.

Full answer below

Practice focus

Production debugging, generated SQL, telemetry, execution plans, query shape, indexes, regression checks, and rollout.

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
An EF endpoint can slow down because it sends too many commands, generates one expensive command, transfers too much data, waits on locks or connections, or materializes too much in the process. Evidence must separate those paths before optimization begins.
I start from one affected endpoint or trace and confirm when, where, and for whom latency grew. I compare total request time with database dependency time, command count, error rate, connection waits, and recent releases. Then I identify the exact EF query, capture its generated parameterized SQL safely, and reproduce it with representative data and parameter values. I do not start by adding AsNoTracking, changing Include, or creating an index from the LINQ alone.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I determine whether the problem is round trips, server execution, result transfer, or client materialization. Logs and traces can expose N+1 commands; projection and row counts reveal over-fetching; the actual database plan and runtime statistics show scans, seeks, joins, sorts, spills, estimates, and logical reads. I also check blocking, lock waits, connection-pool pressure, plan changes, and parameter-sensitive behavior because a fast isolated query may still wait under production concurrency. Query tags or a stable operation name help connect telemetry to SQL without logging sensitive parameter values.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I preserve a before snapshot: latency percentiles, execution count, rows returned, command count, logical reads, CPU, waits, timeouts, and the plan for representative fast and slow parameter shapes. Test data must resemble production cardinality and skew, since tiny databases produce different plans. The repair follows the cause: project fewer columns, bound the result, use stable keyset pagination, remove an N+1 path, reshape a join, split only when cartesian expansion is the problem, add or adjust an index with write cost understood, update statistics, or build a dedicated read model. Raw SQL is a considered last step when the needed shape cannot be expressed or translated well, not the first escape hatch. I test semantic equivalence, authorization filters, cancellation, and query count, then roll out with a feature flag or canary if blast radius is meaningful. Database migrations remain backward compatible and index creation is planned for lock, log, and resource impact. After release I compare the same evidence, watch for regressions in other query shapes and writes, and retain a lightweight performance guard around this hot path. That proves the bottleneck moved without merely making one local benchmark look faster.

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

Optimizing LINQ without capturing generated SQL and the database plan.
Reproducing only with tiny uniform local data.
Treating every slowdown as missing AsNoTracking or a missing index.
Measuring one query while ignoring blocking, connection waits, and command count.

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

Details

Has no structured investigation path.
Cannot connect endpoint telemetry to SQL.
Only talks about EF Core and ignores the database.
No post-fix monitoring or regression guard.

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 telemetry would you check first?
How would you reproduce the slow query safely?
What would prove the fix worked?

Continue practice

Move to the linked follow-up, next path step, prerequisite, or deeper variant.