A query that ran in 40 ms has taken 40 seconds since Tuesday. The query and the code are unchanged. What happened?

What the interviewer is testing

Whether you know that identical queries can have different execution plans, and what causes a plan to change.

The likely causes

Statistics refreshed and the plan changed. The optimiser chooses a plan from estimated row counts. When statistics update, the estimate changes, and the optimiser may switch from an index scan to a sequential scan — or from a nested loop join to a hash join. Same query, different plan, wildly different cost.

A data volume threshold crossed. The table grew past the point where the optimiser judges a full scan cheaper than an index lookup. This produces exactly this discontinuous jump at an arbitrary time.

Index no longer used because it was dropped, became invalid, or a data type or collation change made it inapplicable.

Parameter sniffing. The plan was cached for one parameter value and is now being reused for a very different one — fine for a value matching 10 rows, catastrophic for one matching 10 million.

Data distribution shifted. A new large tenant, a seasonal skew, a bulk import changed the selectivity of a predicate.

The diagnosis

Get the execution plan now and compare it to what it should be. Most databases can show the plan and, in some cases, the plan history. This immediately tells you whether the plan changed or whether the same plan is now processing far more data.

Then check: table and index sizes over time, when statistics were last updated, and whether the distribution of the filtered column has shifted.

The remedies

Update statistics if they are stale, or if a recent refresh produced a worse plan, investigate why the estimate is wrong.

Add or correct an index if the optimiser's choice is reasonable given what is available.

Pin or hint the plan as a short-term fix, understanding that a pinned plan becomes wrong later.

Rewrite the query if it is structured in a way that prevents good planning — a function applied to an indexed column being the classic case, since it prevents index use and prevents partition pruning.

What a strong answer adds

The monitoring that would have caught it: alert on query plan changes for critical queries, and track query performance per statement rather than only aggregate database latency. A single query regressing is invisible in an aggregate and obvious per statement.

Common weak answers

Adding an index without looking at the plan. Scaling the database, which pays for a bad plan rather than fixing it.