Diagnose database CPU saturation under load

WHAT THIS TESTS: Your systematic approach to a classic bottleneck, the database is pegged while the app is idle, and whether you root-cause before scaling.

A GOOD ANSWER COVERS: Start by finding what is consuming CPU. Use the database's own statistics, top queries by total CPU time, the slow-query log, or active-session sampling, to identify the heaviest statements. Pull their execution plans and look for full table scans, missing or unused indexes, inefficient join orders, sorts spilling to disk, or functions preventing index use. Look from the application side too: an N+1 pattern firing thousands of small queries, or missing pagination pulling huge result sets, can saturate the database while app CPU stays low. Once the culprit is clear, propose targeted fixes: add or correct indexes, rewrite the offending queries, cache hot read paths, introduce read replicas to offload reads, batch queries, or tune connection pooling. Vertical scaling is a stopgap, not a diagnosis.

COMMON WRONG ANSWERS: Immediately provisioning a larger instance, blaming the app CPU which is clearly not the bottleneck, or adding indexes blindly without reading the plan.

LIKELY FOLLOW-UPS: How do you read an execution plan? When does an index hurt rather than help? How do you detect an N+1 pattern? When are read replicas the right call versus sharding?

ONE CONCRETE EXAMPLE: Query stats show one report query consuming seventy percent of database CPU. Its plan reveals a full scan on a ten-million-row table because the filter column is unindexed and the app issues it once per user in a loop. You add a composite index matching the filter and fix the application to batch the lookups into a single query. Database CPU under the same load drops from one hundred percent to under thirty, and you never needed a bigger instance, which would only have delayed the same problem.