SQL Server Interview Question #198

A production query suddenly becomes slow. What would you check first?

Security, Backup, Recovery, Administration & Real-World Scenarios Senior Advanced

Detailed Explanation

Start by confirming the slowdown with measurements rather than immediately changing code or adding indexes. Compare current behavior with the previous baseline if available.

Inspect the actual execution plan, Query Store history, logical reads, CPU and elapsed time, blocking, waits, parameter values, cardinality estimates, statistics, recent deployments, data growth, plan changes, memory grants, spills, and index changes.

The correct fix depends on the cause. A plan regression may require different action from blocking, stale statistics, parameter sensitivity, or a missing index.

Code Example

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Execute the affected query here.

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;