Part VIII's capstone. Every tool you've picked up in this Part is useless until you know how to actually see what's happening — this lesson is about looking, first, before reaching for any of them.
An endpoint that used to return in 80ms now takes 4 seconds. Somewhere in this Part you've learned a fix for almost every possible cause: split queries and compiled queries for query shape problems, transaction and concurrency tuning for contention, connection pool sizing for exhaustion under load, ExecuteUpdate/ExecuteDelete for bulk-change overhead, caching for repeated expensive reads, raw SQL for the rare confirmed hot path. That's a full toolbox — and a full toolbox is exactly useless if you reach into it before you know which tool the actual problem needs.
233's profiling lesson taught you this discipline at the whole-application level: profile first, optimize precisely, verify with a benchmark. This capstone applies that exact discipline specifically to the database layer — because "the database" is often blamed for a slowdown that turns out to live somewhere else entirely, and even when the database genuinely is the problem, guessing which of this Part's eight lessons' worth of tools to reach for wastes real time compared to just looking.
In this lesson, you'll learn how to actually see the SQL EF Core generates, how to tell whether a slow endpoint's real bottleneck is the database at all, the concrete red flags that show up in query logs and connection metrics, and a prioritized decision framework for reaching for the right specific fix from this entire Part — instead of applying every technique everywhere.
Database performance troubleshooting is the disciplined process of finding out, with actual evidence, whether a slow application is slow because of the database at all — and if so, exactly which specific thing about the database access is slow — before changing any code. It's the same "measure first" instinct 233 built, aimed specifically at the layer where a huge share of real-world slowdowns actually live.
Concretely, this means: making EF Core's generated SQL visible (.ToQueryString(), EF Core's logging category, or a query-logging interceptor built on 272's interceptor hook), separating database time from application and network time so you know which layer to even look at, and recognizing the specific, well-known symptoms of the most common real database performance problems — N+1 query patterns, missing indexes, and connection pool exhaustion — directly in that evidence, rather than by guessing.
When an endpoint is slow, "the database" is one of the easiest, most reflexive things to blame — and it's frequently wrong, or only half right. The real cause might be application code doing unrelated expensive work, a slow downstream HTTP call, network latency that has nothing to do with SQL, or — inside the database layer itself — any one of several genuinely different problems (N+1 queries, a missing index, pool exhaustion, an unnecessarily wide projection) that each need a completely different fix. Applying the wrong fix doesn't just waste time; it can leave the real problem untouched while everyone believes it's been addressed.
EF Core doesn't hide what it's doing — .ToQueryString(), its structured logging, and interceptors all exist specifically to make the generated SQL, the query timing, and the connection behavior directly observable. Once that evidence is in front of you, matching it to the right fix from this Part stops being a guess and becomes a lookup.
IQueryable<Order> query = context.Orders
.Where(o => o.CustomerId == customerId)
.Include(o => o.LineItems);
string sql = query.ToQueryString();
Console.WriteLine(sql);.ToQueryString() renders the exact SQL a given IQueryable<T> would produce, without executing it — the quickest way to sanity-check a suspicious-looking query while you're still writing it, and exactly the tool 281 pointed to for deciding whether EF Core's generated SQL is really the problem before reaching for raw SQL.
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging(); // shows parameter VALUES too — dev/staging only, never productionOr, wired through standard ASP.NET Core logging configuration, filtering the Microsoft.EntityFrameworkCore.Database.Command category specifically. This is the tool for seeing the real, full sequence of queries an endpoint issues under realistic conditions — which is exactly what reveals an N+1 pattern: not one query looking wrong in isolation, but the same query shape repeating, once per loop iteration, in the log.
272 introduced DbCommandInterceptor as a hook into EF Core's command pipeline without yet giving it a concrete job. Here's that job: a custom interceptor that times every command and logs the ones that cross a threshold, giving you a built-in, always-on slow-query log without any external tooling.
public class SlowQueryLoggingInterceptor(ILogger<SlowQueryLoggingInterceptor> logger) : DbCommandInterceptor
{
private static readonly TimeSpan Threshold = TimeSpan.FromMilliseconds(500);
public override ValueTask<DbDataReader> ReaderExecutedAsync(
DbCommand command,
CommandExecutedEventData eventData,
DbDataReader result,
CancellationToken cancellationToken = default)
{
if (eventData.Duration > Threshold)
{
logger.LogWarning("Slow query ({DurationMs}ms): {Sql}",
eventData.Duration.TotalMilliseconds, command.CommandText);
}
return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
}
}
// Registered once, at DbContext configuration — exactly like 272's example:
options.AddInterceptors(new SlowQueryLoggingInterceptor(logger));Unlike .ToQueryString(), which you point at one query deliberately, this interceptor watches everything, all the time — a permanent, low-effort early warning system for exactly the kind of regression that turned an 80ms endpoint into a 4-second one.
Before touching a single query, separate where the endpoint's time is actually going. The query log from the tools above already gives you the database's own share directly — sum the durations of every query logged for one request, and compare that total against the endpoint's overall response time:
// From the interceptor/log for one request:
// Query 1: 12ms
// Query 2: 9ms
// Query 3: 14ms
// Total DB time: 35ms
//
// Endpoint's total response time (from request logging / APM): 2,400ms35ms of database time inside a 2,400ms response is a loud, unambiguous signal: the database is not where this endpoint's time is going, no matter how instinctively "check the queries" felt like the right first move. This is 233's "profile first" discipline applied concretely — dotnet-counters/dotnet-trace or an APM tool would point you at the application code, an external call, or serialization instead, and no amount of query tuning would have touched the real 2,365ms.
Conversely, if that same log showed 2,100ms of the 2,400ms spent in database queries, you'd have exactly the opposite, equally clear signal — and now it's worth looking at which queries, and why.
SELECT * FROM Orders WHERE CustomerId = @p0 -- 1 query, returns 200 orders
SELECT * FROM Customers WHERE Id = @p0 -- runs once...
SELECT * FROM Customers WHERE Id = @p0 -- ...and again...
SELECT * FROM Customers WHERE Id = @p0 -- ...and again, 200 times totalThis is 205's N+1 problem, seen directly as evidence rather than described in the abstract: one query, then the exact same query shape repeated once per row of the first result. The fix is exactly what 205 taught — .Include(o => o.Customer) or an equivalent single projection, never trying to speed up the 200 individual queries themselves.
At a conceptual level (the exact tool for viewing a plan is database-specific — SQL Server's Actual Execution Plan, PostgreSQL's EXPLAIN ANALYZE, and so on), the symptom to recognize is the same everywhere: a query that should touch a handful of rows instead shows a full table scan or a full clustered-index scan — the database reading every single row in the table, in order, to find the few that match, instead of jumping directly to them via an index. A query filtering on a column with no supporting index is the classic cause; adding the right index turns that full scan into a fast, targeted lookup.
This one rarely shows up as a slow query at all — it shows up as requests failing or timing out specifically while trying to acquire a connection, under real concurrent load, even though any individual query runs fine in isolation. This ties directly back to 277's connection pool sizing lesson: if the pool's maximum size is too small for genuine concurrent demand, requests queue waiting for a connection to free up, and past a certain wait, they time out entirely. The fix isn't query tuning at all — it's revisiting pool size, DbContext lifetime, and whether connections are being held longer than necessary, exactly as 277 covered.
"The database is slow" from a gut feeling is like diagnosing a patient purely from "I feel tired" — technically a real symptom, but nowhere near enough to prescribe anything specific. Query logs and execution plans are the test results: a repeated identical blood-panel request (N+1), a scan of the entire body instead of the one affected area (a missing index), a waiting room with more patients than available doctors (pool exhaustion). Each specific result points at a specific, different treatment — and a good doctor orders the right test before prescribing anything, rather than trying every available treatment at once and hoping one of them helps.
Not necessarily, and the Simple Example above showed exactly why — a request can spend the overwhelming majority of its time somewhere that has nothing to do with the database at all. Confirming where the time actually goes, first, is what separates real troubleshooting from a guess that happens to involve a query log.
It gives you parameter values inline in the log, which is genuinely useful for local debugging — but those values can include real customer data, which is exactly the kind of thing that shouldn't be sitting in a production log. Reserve EnableSensitiveDataLogging() for development and staging; a production slow-query interceptor like the one above should log the parameterized command text and timing, not raw parameter values.
Adding a compiled query, a cache layer, and an index, all in the same change, "just in case," without evidence any specific one was the actual problem — now a future regression is far harder to attribute, because three things changed at once. Apply the one fix the evidence points to, then measure again, exactly as Step 4 describes — one variable at a time.
Seeing a long list of queries in a log and assuming that volume alone means something is wrong, without comparing actual durations against a known-good baseline or against the endpoint's total response time. A busy-looking log with low total duration is often completely fine — judge by measured time, not by how much text scrolled past.
Seeing timeouts under load and immediately suspecting individual query performance, when the actual symptom — failures acquiring a connection at all, with individually fast queries once one is obtained — points specifically at 277's pool-sizing territory instead. Distinguish "a query is slow" from "the request never even got a connection in time" — they need completely different fixes.
| Situation | What to do |
|---|---|
| An endpoint feels slow, cause unknown | Step 1 — confirm whether the database is even where the time goes, before touching any query |
| Repeated near-identical queries in the log, one per loop iteration | N+1 — fix with Include()/projection, not per-query tuning |
| One query dominates total time, plan shows a full/clustered-index scan | Add the missing index (or narrow an over-wide projection) |
| Timeouts/failures specifically acquiring a connection under load | Revisit pool sizing and DbContext lifetime (277) — not query tuning |
| A confirmed hot, unchanging query shape at genuinely high volume | A compiled query, from earlier in this Part |
| A confirmed, profiled hot path where EF Core's generated SQL specifically is the bottleneck | Raw SQL, selectively (278, 281) — after everything above has been ruled out |
This closes out Part VIII — these questions pull together the whole Part's tools through the lens of the diagnostic workflow that ties them together.
1. A request takes 2,400ms total. Query logging shows only 35ms spent across all its database queries combined. What should happen next?
Correct: B
Why B is correct: 35ms out of 2,400ms is a clear, measured signal that the database is not where this request's time is going — the honest next step is to look at application code, downstream calls, or network, not the database.
Why A is incorrect: Indexing wouldn't meaningfully move a 2,400ms total when the database only accounts for 35ms of it — this would be effort spent on a part of the system that isn't the problem.
Why C is incorrect: The evidence shows the opposite of "EF Core is the bottleneck" — the database layer is fast; rewriting it in raw SQL addresses nothing real here.
Why D is incorrect: Pool exhaustion shows up as failures/delays acquiring a connection, not as fast queries inside a slow overall request — nothing in this scenario points at pool sizing.
Reinforcement: Confirming where time actually goes, first, is what separates real diagnosis from a reflexive database-shaped guess.
2. A query log shows one query fetching 300 orders, immediately followed by the exact same "SELECT * FROM Customers WHERE Id = @p0" query shape repeated 300 times. What does this pattern indicate, and what's the correct fix?
Correct: B
Why B is correct: One query followed by the same query shape repeated once per row of the first result is the textbook N+1 signature from 205 — the fix reshapes the query to fetch everything in one round trip, via Include() or a projection, never by optimizing the repeated individual queries.
Why A is incorrect: A missing index would show up as one slow query with a scan in its plan, not as the same fast query shape repeated many times — this is a query-count problem, not a query-speed problem.
Why C is incorrect: Pool exhaustion shows up as failures acquiring a connection, not as a specific repeating query pattern like this one.
Why D is incorrect: Caching could mask this symptom for previously-seen customer IDs, but it doesn't fix the underlying N+1 shape — new, uncached customers would still trigger the same repeated-query pattern.
Reinforcement: The repeated-query-shape pattern in a log is the concrete, visible evidence of N+1 — recognizing it directly in real logs is the whole point of this capstone.
3. Under real production load, requests start failing with timeouts specifically while trying to obtain a database connection — but any individual query, once a connection is obtained, runs quickly. Which lesson's territory does this point to?
Correct: B
Why B is correct: Failures specifically acquiring a connection, with fast queries once one is obtained, is exactly the signature of pool exhaustion under load — the fix is revisiting pool size and how long connections are held, straight from 277.
Why A is incorrect: Stored procedures address query logic, not the separate problem of connections themselves being unavailable — this symptom isn't about what the queries do once running.
Why C is incorrect: Caching reduces how often queries run, which could indirectly reduce connection demand, but it doesn't address a pool that's genuinely undersized for real concurrent load — and it's not the diagnosis this specific symptom points to.
Why D is incorrect: N+1 shows up as many repeated queries in a log, not as failures specifically acquiring a connection — these are different symptoms with different root causes.
Reinforcement: "Can't get a connection" and "query is slow" are different symptoms with genuinely different fixes — telling them apart correctly is exactly what this capstone is built to teach.
4. Which best describes the prioritized workflow this capstone — and this whole Part — recommends?
Correct: B
Why B is correct: This is the exact four-step workflow the lesson closes with — measure, diagnose from real evidence, apply the one specific fix, verify — mirroring 233's profile-then-optimize-then-verify discipline, applied to the database layer.
Why A is incorrect: The lesson explicitly warns against this in Common Mistakes — applying multiple techniques speculatively wastes effort and makes it impossible to know what actually helped.
Why C is incorrect: 281 already established raw SQL as the selective exception for confirmed cases, not a starting point — this directly contradicts that lesson's judgment-based approach.
Why D is incorrect: 233 established that intuition about performance is famously, repeatedly wrong — this capstone builds directly on that same "measure, don't guess" principle.
Reinforcement: Measure → diagnose → fix precisely → verify is the single thread connecting every lesson in this entire Part.
5. What is the primary diagnostic advantage of the query-logging interceptor shown in this lesson, compared to calling .ToQueryString() on individual queries?
Correct: B
Why B is correct: An interceptor built on 272's hook runs continuously, watching every command the application issues and flagging slow ones automatically — a standing early-warning system, versus .ToQueryString()'s targeted, one-query-at-a-time check on something you already suspect.
Why A is incorrect: That's specifically what .ToQueryString() does, not the interceptor — the interceptor observes queries as they actually execute, with real timing, rather than rendering SQL without running it.
Why C is incorrect: The interceptor only observes and logs — it takes no automatic corrective action; fixing an N+1 pattern it reveals is still a manual code change.
Why D is incorrect: Connection pool exhaustion is a separate concern from query duration — this interceptor doesn't monitor pool/connection-acquisition behavior at all.
Reinforcement: .ToQueryString() is a targeted, deliberate check; a logging interceptor is a standing, always-on one — different tools for different moments in the workflow.
That's Part VIII complete. You went from "correct EF Core" to genuinely production-ready judgment — query optimization, compiled queries, transactions, concurrency, connection management, stored procedures, bulk operations, caching, honest SQL-vs-EF-Core reasoning, and now the discipline to diagnose a real, slow, misbehaving application by evidence instead of instinct. Measure first. Every time.
dotnetmadeeasy.com — Learn C# and .NET, the right way.