High CPU due to SQL

Hook: Interviewers love this because “high CPU” is often not a machine problem — it is a query asking the database to do far too much work.

Question: What do you do when a server has high CPU due to SQL?

Answer: High CPU usually means one or more queries are forcing the database to scan too many rows, sort too much data, or join tables in an expensive way. I would first find the top CPU query, inspect its execution plan, and look for full scans, non-sargable filters, bad joins, or repeated executions. Then I would fix the query shape, add the right index, and verify the change with a before/after plan.

Interview-Ready Answer: “I’d start by identifying the exact query consuming CPU, not guessing. Then I’d read the execution plan to see whether it’s scanning large tables, sorting, or doing a bad join, and I’d rewrite the predicate to be index-friendly, add or adjust an index, and recheck the plan. My mental model is: make the database search like an address lookup, not a room-by-room search through the whole building.”

What “high CPU due to SQL” really means

It means the database is busy computing, not mostly waiting. In practice, CPU burns when the engine has to inspect many rows, evaluate functions row by row, sort huge result sets, build hash tables, or execute the same expensive query many times. A small query result can still be expensive if the path to that result is bad.

How it works under the hood

1. The app sends SQL. The database parses it, checks syntax, and turns it into an internal form. If the same statement is executed often, a cached plan may be reused, but the execution cost can still be high.
2. The optimizer estimates row counts. The optimizer is the part that chooses a plan. It uses statistics — metadata about row counts and value distribution — to estimate cardinality, which is the number of rows it expects to read. If stats are stale, it may choose a bad plan.
3. The executor follows the plan. If the predicate is sargable — meaning searchable with an index — the engine can jump to the matching rows. If you wrap the column in a function, like DATE(created_at) or LOWER(email), the engine often has to evaluate that function for many rows first.
4. Expensive operators consume CPU. Full scans, sorts, hash joins, hash aggregates, and nested loops over large inputs are classic CPU hot spots. A full scan is roughly O(n); a sort is O(n log n); a bad nested-loop join can drift toward O(n*m).
5. Concurrency multiplies the pain. One query burning 200 ms of CPU might be fine, but 500 executions per second can peg a core or the whole box. The database does not care that each individual query looks “small” if the workload is noisy.
6. Memory limits can make it worse. If a sort or hash cannot fit in memory, it may spill to disk. That adds I/O and more CPU overhead. The fix is often not “more CPU”; it is “less work.”

How to think about fixes

The fastest path is usually: find the top SQL, inspect the plan, rewrite the predicate, and then add the right index. A useful index is one the query can actually use. If the query needs a lot of rows — for example, 30% to 40% of a table — a sequential scan can be cheaper than jumping through an index many times, so not every scan is a bug.

What to check first in a live incident

1. Top SQL by CPU. Find the query that burns the most CPU, not just the slowest one.
2. Execution plan. Look for scans, sorts, hash operators, and unexpectedly large row counts.
3. Statistics. If estimates are wildly wrong, refresh stats so the optimizer can make a better choice.
4. Indexes. Check whether the filter and join columns line up with an index, especially the left-most columns in a composite index.
5. Concurrency. If the query is fine alone but deadly under load, reduce frequency, batch requests, or use caching.

Important edge cases

• Adding an index does not always help. If most rows match, a scan may still be faster.
• A query can be CPU-heavy even if it returns only 10 rows, because it may have read millions first.
• Time zones matter for date filters. A half-open range like >= start_of_day and < next_day is safer than wrapping the column with DATE().
• High CPU can also come from many tiny queries, such as an N+1 pattern. In that case, the fix is fewer round trips, not a bigger server.

Memory hook: “Ask for an address, not a treasure hunt.” If the query is written like an exact address, the engine can go straight there. If it is written like a riddle, the engine has to search everything.

Real-world story

In a checkout service, support agents open an order-history page that runs every few seconds. One day the database CPU jumps from 20% to 95%, and the whole app feels “slow,” even though the servers are healthy. The bad query uses DATE(created_at) and LOWER(status) on an 80-million-row orders table, so the engine has to scan far more rows than needed.

What the outage looks like: API latency rises from 40 ms to 3-5 seconds, dashboard requests time out, and the database log shows long-running sequential scans with millions of rows filtered out. Users see spinner loops, stale order pages, and occasional 502/504 errors because the app server threads are waiting on the database.

What fixes it: rewrite the date filter as a half-open range, store or compare the status in a consistent case, add a composite index that matches the filter order, and if needed slow down the polling frequency. The big lesson is that SQL performance issues often look like infrastructure issues until you inspect the actual query shape.

Follow-up & Tricky Questions

• How do you prove the CPU issue is from SQL and not the app? I look at the top database queries, their execution plans, and the database wait/CPU metrics. If one statement is repeated heavily and the plan shows scans, sorts, or large joins, the SQL is the likely culprit.
• What is an execution plan? It is the database’s chosen recipe for running a query. It shows which tables are scanned, which indexes are used, and where the expensive steps are.
• Why doesn’t adding an index always fix high CPU? Because indexes help only when the query is selective enough and written in an index-friendly way. If most rows match, or the query wraps the indexed column in a function, the engine may still scan.
• How would you reduce CPU without changing schema? Rewrite non-sargable filters, remove unnecessary columns, limit the result set, batch work, and avoid repeated queries. I would also check whether the same query is being executed too often by the application.
• What is a covering index? It is an index that contains all columns needed by the query, so the engine may not need to visit the base table. That can reduce CPU by avoiding extra lookups.
• Tricky: if a query uses an index, can it still be CPU-heavy? Yes. If the index returns too many rows, or the plan still needs big sorts or many lookups, CPU can stay high even though an index appears in the plan.
• Tricky: should I always replace scans with indexes? No. A sequential scan can be the right choice when the query needs a large portion of the table. Good tuning is about the cheapest correct plan, not about forcing every index to be used.
• Tricky: does faster SQL always mean lower CPU? Usually, but not always. A query can get faster by using more parallel workers, which may lower latency while still using a lot of total CPU across cores.

Common Mistakes

• Adding indexes before reading the plan. Fix: identify the actual hot query and confirm which operator is expensive first.
• Putting functions on indexed columns. Fix: rewrite to a range or equality on the raw column, or use a functional index only when the pattern is stable and truly needed.
• Assuming every scan is bad. Fix: if the query returns a large fraction of rows, a scan may be cheaper than an index lookup storm.
• Ignoring concurrency. Fix: a query that is “okay once” can still melt the CPU when it runs hundreds of times per second.

Memory Hook: “Ask for an address, not a treasure hunt.” A good SQL predicate gives the database a direct path; a bad one makes it search the whole building.

Cheat Sheet:

• Find the top CPU SQL first.
• Read the execution plan, not just the query text.
• Look for scans, sorts, hash joins, and row explosions.
• Rewrite non-sargable filters into index-friendly ranges.
• Add or adjust indexes only after you know the access pattern.
• Verify with before/after metrics and the same workload.

Practice Tasks:

• Rewrite WHERE DATE(created_at) = CURRENT_DATE into a half-open range.
• Add a composite index to match a two-column filter and compare the two plans.
• Create a query that returns many rows and see when PostgreSQL chooses a sequential scan on purpose.