High CPU

Symptom

Server CPU at 90%+; the whole system is slow and the load average is high.

1. Verify the DB is actually consuming the CPU

Use top/mpstat at the OS level to verify that the process consuming CPU is really the DB. Sometimes a neighboring process or a backup/antivirus is the consumer. If iowait is high, the problem is disk, not CPU.

2. Find the queries using the most CPU

Extract the top queries sorted by CPU time. Usually a few queries (bad plan, full scan, missing index) burn the CPU.

Oracle

SELECT sql_id, cpu_time/1e6 cpu_sec, executions, sql_text FROM v$sql ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

PostgreSQL

SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

MySQL

SELECT digest_text, count_star, sum_timer_wait/1e12 sec FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;

SQL Server

SELECT TOP 10 total_worker_time/execution_count avg_cpu, execution_count, SUBSTRING(t.text,1,200) FROM sys.dm_exec_query_stats q CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t ORDER BY total_worker_time DESC;

3. Check for a plan / index problem

Get the plan of the offending query. Full table scans, nested loop explosions, hash join spills, or bad plans caused by parameter sniffing/stale statistics burn CPU. Add an index or gather statistics.

4. Relieve immediately

Kill the runaway query and, if needed, limit it with Resource Manager/Resource Governor. For a load that started suddenly, review the latest deploy/parameter change.

5. Fix permanently

Fix the query/index, ensure plan stability and schedule the statistics gathering job. If CPU is constantly at 100%, scaling (adding CPU, distributing reads to read replicas) may be needed.