Diagnosing Database Performance Problems

Performance problems on a busy system rarely announce themselves as one big failure. They announce themselves as slow page loads, background workers falling behind, and intermittent CPU pressure on the database. By the time the symptom is visible, the actual cause is usually one or two queries among hundreds.

The first step is not to start changing things. It is to find out which queries are actually doing real work, and which queries are quietly consuming more than their share of the database.

• Look at the queries that the database is actually spending time on - not the ones that look expensive.
• Look at the queries that are called often, even if individually they look fast.
• Look at the queries that scan large ranges without an obvious reason.
• Look at the queries that line up with the symptoms - slow pages, falling-behind workers, CPU spikes.

The next step is to look at query plans against the real dataset, not at the query text. The query text tells you what was asked. The plan tells you what the database actually did.

• Which indexes were used, and which were not.
• Estimated vs. actual row counts - a wide gap usually means a stale statistic or a missing filter.
• Where the cost concentrated - sort, scan, join, key lookup.
• Implicit conversions that silently turned a seek into a scan.

Indexing is one useful lever when the execution plan and access pattern support it. Compare it with query changes and smaller result sets, accounting for selectivity, storage, and write costs.

• Add the index against the actual query, against the actual data volume.
• Prefer covering indexes where the access pattern is predictable.
• Watch for indexes that look helpful but are not used by the plan.
• Remember that every index is a write-time cost - don't index speculatively.

N+1 is the failure mode that hides the best, because every individual query looks small. A list of N orders, with one query to fetch them and N more to fetch their lines, makes N+1 queries. The extra round-trips can become costly as the number of orders grows.

• Look for code that issues queries inside loops.
• Look for ORMs that lazy-load related collections.
• Look for endpoints that return lists where each item triggers a separate query.
• Consider eager loading, projections, joins, or batched follow-up queries. Check the resulting SQL and row counts before using caching to reduce repeated work.

A query projection selects only the fields a caller needs, such as an EF Core Select expression. A separately maintained read model is a different design choice: it stores a read shape and introduces update and consistency concerns.

• Use projections when the access pattern is stable and the read cost is real.
• For a separately maintained read model, choose synchronous or asynchronous updates according to its consistency requirements.
• Query projections do not inherently introduce eventual consistency. For asynchronously updated read models, document the staleness window and recovery behaviour.
• Evaluate projections together with indexes, query shape, and the data the caller actually needs.

Caching can reduce repeated work, but introduces consistency, invalidation, and operational concerns. Evaluate the expected benefit against the freshness requirements and failure behaviour of the specific access path.

• Cache only data the system can tolerate being slightly out of date.
• Prefer caching the result of a query, not the query itself.
• Make the staleness window visible - callers should know what they are getting.
• Don't cache to hide a missing index. Fix the index.

Treated as a discipline, database performance investigation produces a small set of habits that change how the team reasons about slow systems.

• Plans against real data are the only honest picture of cost.
• The most expensive queries are not always the ones that look expensive.
• N+1 patterns are easy to introduce and easy to miss in review.
• Caching is the right tool only when staleness is genuinely acceptable.
• Fetch only what is required - columns, rows, joins.
• Understand query and data volume before choosing a lever.