Diagnose 100% CPU on a managed database

WHAT THIS TESTS This evaluates disciplined database performance troubleshooting: whether you find the root cause before throwing hardware at the symptom.

A GOOD ANSWER COVERS Start by establishing context: when did CPU spike, and what changed? Correlate the alert timeline with deploys, schema changes, batch jobs, or a traffic surge using monitoring metrics. Next identify the offenders: use the database's own diagnostics, performance insights or the query store and dynamic management views, to rank queries by CPU consumption and find the few that dominate. Examine those queries' execution plans for red flags: full table scans, missing or unused indexes, implicit type conversions, or a bad cached plan from parameter sniffing. Check for contention: blocking, long-running transactions, and lock waits can pin CPU. Verify it is not a connection storm or a runaway client. Once you have the cause, remediate appropriately: add or fix an index, rewrite the query, update statistics, or fix the application access pattern. Scaling vertically or adding read replicas is valid only if the workload genuinely outgrew the tier, not a substitute for fixing a pathological query that would just consume the bigger box too.

COMMON WRONG ANSWERS Immediately scaling up the instance, which masks the problem, costs more, and often saturates again under the same bad query. Restarting the database blindly. Blaming the cloud provider. Not using the engine's query diagnostics. Ignoring deploy correlation, which often points straight at a newly introduced query.

LIKELY FOLLOW-UPS Which views show the top CPU queries? What is parameter sniffing? When is scaling justified versus tuning? How do read replicas help, or fail to help, a write-heavy workload?

ONE CONCRETE EXAMPLE CPU pegs right after a deploy. Performance insights shows a single query dominating; its plan reveals a full scan on a newly filtered column. Adding the missing index drops that query's cost dramatically and CPU returns to normal with no instance resize, proving the root cause was a query, not a capacity shortfall.