A slow API and the meeting already fills with solutions before anyone has a measurement. More CPU and RAM show up, cache, queues, microservices, refactoring. Sometimes someone even proposes switching languages. Rarely is the first suggestion to open the execution plan.
That is why I start the investigation close to the data. Not because the database is the default culprit, but because so much passes through it and that check usually gives a fast signal. If the query is healthy, I rule the database out and follow the request flow.
Quick fixes can also hide expensive work
Cache and queues solve real problems. Bigger machines can also be the right call. When they arrive by reflex, though, those resources may only change the bill size while nobody knows which part of the request is holding the response.
More CPU reduces resource contention, cache takes some requests out of the path, and a queue absorbs a spike. Meanwhile, a query doing enormous work to return almost nothing stays expensive on every execution. The same goes for a loop firing one call per item.
Latency may drop for a while, the alert stops firing, and the team breathes. When load grows, the expensive operation reappears, now accompanied by larger infrastructure.
The question that must come before the architecture debate: how much work is this request producing to deliver the result?
We almost doubled the machine and the dashboard stayed slow
That is what happened with a dashboard I worked on. It loaded synchronously, and a single query did everything at once: fetched data, joined several pieces of information, and computed the displayed values. Since database CPU ran very high, we almost doubled CPU and RAM.
The database got bigger. The dashboard still took over a minute to load. The same query still concentrated all heavy work in a single execution.
That was when we opened the execution plan. The screen returned few indicators, but the query crossed relationships, formed a large intermediate volume, and spent CPU on aggregations before reaching them. The final answer was small. The work to produce it, enormous.
Doubling CPU and RAM had given the database more headroom, but the search still forced it to do everything at once. The plan showed the investigation had to enter the path traveled by the query.
What I need to see before touching the database
For the database to become a real suspect, I want the execution plan and metrics pointing that way. Query time, reads, and cardinality usually confirm or eliminate a hypothesis in minutes.
If those measures are healthy, I rule the database out. I have caught slow APIs with the query responding within expectations, while the delay sat in application processing and sequential remote calls. From there, continuing to hunt a database defect would only insist on the wrong layer.
Before going deeper, I run a short radar over the query and the code. One query to load the list followed by another per record calls for a query count. That N+1 shows up often when the ORM leaves relationships for the backend to fetch one by one.
A JOIN multiplying rows before aggregation calls for measuring intermediate volume. A missing index calls for the plan. A filter applying a function over the indexed column too.
In code, I look for remote calls or queries with await inside a for. Each wait may look small alone and still dominate total time when all run in a queue.
None of these signals closes the diagnosis. An N+1 on a route with two items may have irrelevant impact, a new index may help little on a low-selectivity column, and a voluminous join may be needed to produce the result. So I count queries, time the whole loop, and check in the plan how many rows and reads were produced.
That radar only picks the first measurement. The next investigation layer comes from the math.
One correct line can waste the index
One of those lines often passes review unnoticed. It returns a full day’s records.
WHERE CAST(datetime_column AS date) = @dateThe screen result looks correct. In the plan, the story may differ. Applying CAST to the column forces the query to transform values before comparison. With an index on datetime_column, that can prevent a direct range seek, raise reads substantially, and even lead to a scan.
When the request is for a full day’s records, I compute the boundaries outside the column.
WHERE datetime_column >= @start_of_day
AND datetime_column < @start_of_next_dayIf the start is July 16 at midnight, the next bound is July 17 at midnight. That includes every value on the 16th, including those with fractional seconds at the end, without depending on 23:59:59.999.
The result stays correct, but now there is a range the index can walk. Confirmation comes from comparing reads and the access operator in both plans. If the metric does not change, the hypothesis did not hold.
Read the plan by the work sequence
After comparing filter versions, I read the plan as a story of the work produced by the query. I start with total time and reads. If the screen returns ten indicators but execution performs hundreds of thousands of reads, there is a bill to explain.
Then I compare estimated vs actual cardinality. When the optimizer expected few rows and received many, it may have picked joins, memory, and aggregations for a far smaller scenario than it met at runtime.
Next I follow where data grows. I look for the operator where a JOIN takes thousands of rows to hundreds of thousands, just before a filter or GROUP BY shrinks everything again. The small final answer may hide an enormous intermediate volume. That is where aggregations and sorts start spending too much CPU.
I also do not take the plan’s displayed cost percentage as verdict. It serves to choose where to measure. I mark the expensive operator, how many rows enter and leave it, and how much time it consumes.
If cost shows up after row multiplication and before the few needed indicators, there is already a concrete hypothesis to test.
Split the query where cost grows
What fixed the dashboard was splitting the query and using indexes properly. The split did not happen arbitrarily. The plan itself showed where cost started growing.
We kept in the database the filters and indexed lookup of the needed records. Moving that slice to the application would make the server receive a larger set before it could discard it. It would also waste the path indexes already shortened.
Final indicator composition moved to the application. That stage came after the relationships and aggregations that raised intermediate volume and kept database CPU high. With data already sliced, the server could assemble screen values without concentrating all execution in a single query.
The database started reducing the set early. The application received the selected records and composed the indicators. After the change, the dashboard stopped depending on that heavy query and became more predictable, because the split followed the point where cost grew in the plan.
That boundary did not come from a preference for single queries or for more application logic. I marked where CPU, reads, and intermediate rows spiked after selective filters. Then I checked whether the expensive stretch could receive an already-reduced set outside the database.
Before moving that stage, two bills had to balance. The database should keep doing the slicing that used the indexes. The application should compose the result without fetching too many rows, creating per-item calls, or turning the network into the new bottleneck.
Validation had to show less database CPU and reads without raising route payload or latency. Without that improvement, the split would only transfer cost to another layer and the dashboard would stay expensive.
When the plan points outside the database
On another route, the plan showed a healthy query, with reads and time within expectations. Still, the API stayed slow. When I measured the whole flow, the delay appeared after the query, in application processing and sequential remote calls.
A stretch like this changes the math.
for (const item of items) {
await fetchDetail(item.id);
}With 50 items and 40 ms per call, the loop can add about two seconds to the route. Each await starts the next call only after the previous response. The 40 ms looks small alone. Total time shows fifty waits in a queue.
When profiling points at that path, I change the code and the flow design. I separate operations depending on the previous response from those that can run together. For the independent ones, I test bounded concurrency.
Firing fifty requests at once can also just move pressure to the integration. The change must be validated by total route time, error rate, and remote-service saturation. Improving one measure while another explodes does not fix the problem.
Two hours to locate the layer, one day to test
“We found the bottleneck” too often becomes “we fixed the slowness” too early. After locating a bad query or sequential stretch, the change, deploy, and production confirmation are still missing. Finding the layer only shortens the suspect list.
In the first two hours, I want to locate where to dig deeper: database, application, integration, or infrastructure. I open the plan, compare time and reads, count queries, and time the stretch running after the database.
If the plan is healthy and fifty 40 ms calls add up to about two seconds, I stop hunting indexes and measure the code. If reads explode before aggregation, the code waits while I test the query.
That window is an investigation ruler, not a guarantee. On the slow dashboard, I spent about three hours in the terminal looking at the plan, comparing metrics, and testing the query split. The rest of the day involved meetings, access, deploy, and production confirmation.
So on day one I want at least one tested hypothesis with before-and-after measurement. The test may rewrite the filter without CAST, split the query, or bound the loop’s concurrency. After deploy, I compare the metric that motivated the change: reads and CPU for the database; latency, errors, and saturation for the route.
With intermittent failures, that window grows. You must wait for the symptom to reappear with enough metrics to compare.
Confirmation happens after deploy
After some production mistakes, I stopped looking for a default culprit. Database, application, and infrastructure enter as hypotheses, each with a measure that can confirm or discard it.
When the plan shows high reads and cardinality far from reality, I work on the query. When the query answers well and profiling concentrates time after it, I move to code or integration.
Cache, queues, indexes, and bigger machines stay available. They are not forbidden decisions. They just should not arrive before we know which work is holding the response.
Before changing, I define the measure that must improve and the one that must not worsen. After deploy, I return to the plan, metrics, and profiling. If the target metric does not improve, the hypothesis did not hold. If the guardrail measure worsens, the fix created another problem.
I discard the hypothesis and return to the flow point where time still grows.
Originally published on LinkedIn on July 16, 2026.