Mahdi MolaviWhen an API slows down at peak hours, the culprit is usually a handful of specific queries, not "the whole database". This guide walks the practical path from a slowness report to a fix: finding expensive queries with Query Store, reading the execution plan, indexing with the write cost in mind, taming parameter sniffing and locks, fixing costly EF Core patterns, and batch writes with SqlBulkCopy.
From "it's slow" to a specific query
"It's slow at peak" is not enough to start on; you need to know which endpoint, under what load and with which query. At Gozine2, before exam peaks, I load-tested the busiest APIs with Locust to find bottlenecks early, and then tuned the SQL Server queries, the caching paths and those same APIs. That work is described in the exam-peak case study, and I cover designing the load scenario in the Locust load testing guide. A load test reproduces the symptom; SQL Server's own tools show where the time goes.
The first tool is Query Store. It has existed since SQL Server 2016, and since SQL Server 2022 it is on by default for new databases; in a database created earlier it may still be off. It keeps the history of queries, plans and runtime statistics inside the database itself, so restarts do not wipe it. In the Top Resource Consuming Queries report in SSMS, choose the Total statistic: a query that takes a few milliseconds but runs hundreds of times a second costs more in total than a multi-second query that runs once an hour.
If Query Store is off, sys.dm_exec_query_stats gives cumulative statistics for the plans in the plan cache; when a plan leaves the cache, its statistics go with it. Since SQL Server 2022, reading it requires the VIEW SERVER PERFORMANCE STATE permission (previously VIEW SERVER STATE).
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
(CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2 + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;
Times in this DMV are in microseconds, which is why they are divided by 1000. The avg_logical_reads column is better than time for comparing before and after a fix, because it depends less on how busy the server is at the moment. The plan this query returns is the cached plan and does not include actual row counts.
Reading the execution plan
Capture the actual plan in SSMS with Include Actual Execution Plan or SET STATISTICS XML ON; SET STATISTICS IO ON also shows the logical reads for each table. Take the query EF Core generated from the log or with ToQueryString(), and run it through sp_executesql with the same parameters; a local variable (like the DECLARE statements that ToQueryString() writes) or a literal value may produce a different plan. Match ARITHABORT as well: it is on by default in SSMS and off in an application connection, and because it is part of the plan cache key, the same query can be fast in SSMS and slow in the application. In the plan I look for these signs:
- Scan versus Seek. A Seek goes straight to the range it needs; a Scan reads the whole index or a large part of it. A Scan on a large table in a hot path is a warning sign. Two common causes, even when an index exists, are a function on the column (
YEAR(CreatedAt) = 2025) and implicit type conversion. For example, comparing avarcharcolumn with annvarcharparameter prevents a Seek under SQL collations (such asSQL_Latin1_General_CP1_CI_AS) and usually does not under Windows collations. EF Core takes the parameter type from the column mapping, so setIsUnicode(false)in the model for avarcharcolumn. - Key Lookup. The nonclustered index found the row, but the remaining columns are read from the clustered index separately for each row; on a heap the same operator is called RID Lookup. It is cheap for a few rows and not for thousands.
- Estimated versus actual rows. A large gap means the plan was chosen on a wrong assumption: stale statistics, parameter sniffing or a predicate that is hard to estimate. On the inner side of a Nested Loops join, the estimated number is per execution and the actual number is the total across all executions; multiply the estimate by the number of executions before comparing (SSMS 18.5 and later show this figure as Estimated Number of Rows for All Executions).
- Warnings. A yellow triangle usually means a Sort or Hash spilling to tempdb, or an implicit conversion. The green Missing Index suggestion is only a lead: it comes from the estimate for a single query, does not settle the order of the key columns, never suggests a filtered index and may overlap with existing indexes.
Indexes: covering, filtered, and the cost on writes
A covering index contains every column the query needs: the search, join and sort columns in the key and the rest in INCLUDE. INCLUDE columns are stored only at the leaf level, do not count toward the 32-column and 1,700-byte key limits, and their order has no effect on performance. The result is a Seek without a Key Lookup.
-- Hot query (example):
-- SELECT QuestionId, ChoiceId, UpdatedAt FROM dbo.Answers WHERE AttemptId = @attemptId;
CREATE NONCLUSTERED INDEX IX_Answers_AttemptId
ON dbo.Answers (AttemptId)
INCLUDE (QuestionId, ChoiceId, UpdatedAt);
-- Only open attempts are searched often (Status 0 = InProgress)
CREATE NONCLUSTERED INDEX IX_Attempts_Open
ON dbo.Attempts (ExamId, Deadline)
WHERE Status = 0;
The first index fully covers the sample query above. The second is filtered and holds only open exam attempts; it is smaller, has more accurate statistics and is maintained only when that subset changes. Its limitations: it accepts only simple comparison operators, not LIKE; the query's predicate must be a subset of the index filter; and if the filter value comes from a parameter (Status = @status), the optimizer usually does not use it, because a cached plan has to be valid for any value (OPTION (RECOMPILE) solves this). In EF Core, a constant written in the LINQ expression itself, such as an enum member, becomes a literal, and a variable becomes a parameter. A connection with QUOTED_IDENTIFIER or ANSI_NULLS turned off that modifies data covered by such an index gets an error.
Indexes have a cost, though. Every nonclustered index is updated on every INSERT and DELETE, and on any UPDATE of its columns, with extra logging and locking. According to the SQL Server index design guide, creating many indexes just to give the optimizer options slows down data modification and can cause concurrency problems. On a table that takes heavy writes at peak, such as the answers table during an exam, that cost is paid at exactly the worst moment. In sys.dm_db_index_usage_stats, compare the sum of user_seeks, user_scans and user_lookups with user_updates. These counters reset on restart, so look at a period that covers at least one peak. Adding an INCLUDE column to an existing index is usually better than creating a near-duplicate.
Parameter sniffing: when one plan does not fit all
SQL Server compiles a parameterized query the first time with the value of that execution and reuses the plan for later values. With skewed data this causes trouble. For example, if a nationwide exam has tens of thousands of candidates and each class exam a few dozen, a plan built for a small exam (Nested Loops and Key Lookup) is very slow for the large one. The symptom is a query that is sometimes fast and sometimes slow, whose behavior changes suddenly after a restart or a statistics update. Query Store shows such a query with several very different plans in the Queries With High Variation or Regressed Queries report.
Microsoft's documentation treats hints as a last resort, so check statistics and indexes first. After that, you have these options:
OPTION (RECOMPILE): builds a fresh plan for every execution and then discards it. It is accurate, but you pay the compilation cost every time; it suits heavy, infrequent queries.OPTIMIZE FOR: builds the plan for a representative value (OPTIMIZE FOR (@examId = 42)) or from average selectivity (OPTIMIZE FOR UNKNOWN). It is stable, but not the best plan for values far from the average.- Plan forcing:
sp_query_store_force_planpins one of the plans Query Store has recorded for that query. It needs revisiting as data volume and distribution change. - Query Store hints (SQL Server 2022 and later):
sys.sp_query_store_set_hintsapplies a hint such asOPTION(RECOMPILE)without changing the query text, which makes it a clean option for SQL generated by EF Core. - PSP optimization (SQL Server 2022 and later): Parameter Sensitive Plan optimization is on by default at compatibility level
160. It picks up to three parameterized equality predicates on skewed data and, depending on the parameter value, keeps several variants of the query, each with its own plan. SQL Server 2025 extends it to DML statements at level170. A database that has been restored or attached keeps its previous compatibility level, so upgrading the server alone is not enough.
Locks and waits: blocking and READ_COMMITTED_SNAPSHOT
Sometimes CPU is low and requests are still slow, which means queries are waiting for something. The blocking_session_id and wait_type columns in sys.dm_exec_requests show which session is waiting on which, and LCK_M_* waits mean locks. Since SQL Server 2017, Query Store also keeps wait statistics per query.
In SQL Server, READ_COMMITTED_SNAPSHOT is off by default and READ COMMITTED works with shared locks, so a reader waits behind a writer. With the option on, each statement reads, from row versions, the latest data committed before the statement started, and readers and writers stop blocking each other, although writers still block writers. In Azure SQL Database the option is on by default. When you run ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON, there must be no other active connections to the database, although single-user mode is not required.
The cost: row versions are kept in tempdb, or in the database itself if Accelerated Database Recovery (available since SQL Server 2019) is on, and a long-running transaction keeps them alive. The semantics of reads change as well. Code that read a value such as remaining capacity and then wrote based on it no longer waits behind an in-flight change; it sees the previous version. That pattern was not safe without RCSI either. Make it atomic (UPDATE … SET Remaining = Remaining - 1 WHERE Id = @id AND Remaining > 0 and check the number of affected rows) or add UPDLOCK to the read. Either way, keep transactions short, do not call external services in the middle of one, and index the columns used in UPDATE and DELETE predicates.
EF Core on hot paths
EF Core's own overhead is small in most applications; the real cost comes from patterns that are easy to write. Turn on SQL logging in a test environment and count the queries per request. Then check the following:
- N+1: lazy loading, or a loop that runs a query for each item, turns one request into dozens of round trips. Bring related data in the same query with
Includeor a projection. - Projection with
Select: read only the columns you need, so less data moves and a covering index becomes possible. A result that contains no entities is not tracked at all. AsNoTracking: removes the change tracker's cost for read-only full entities; but because there is no identity resolution, a row that appears several times in the result becomes several separate instances.AsSplitQuery(): prevents a Cartesian explosion when youIncludetwo sibling collections, but it costs more round trips, and without a snapshot or serializable transaction, consistency between the queries is not guaranteed.- Compiled queries and pooling:
EF.CompileAsyncQueryandAddDbContextPoolreduce EF's overhead, but the gain is small next to network and database time; reach for them only after measuring. - Parameters instead of constants: building expressions dynamically with
Expression.Constantproduces new SQL and a new plan for every value. In EF Core 10,Containsover a list is also translated by default into multiple separate parameters with padding, to keep the number of plans bounded.
// Read path: only the needed columns; no entity in the result, so nothing is tracked
var answers = await db.Answers
.TagWith("GET /attempts/{id}/answers")
.Where(a => a.AttemptId == attemptId)
.Select(a => new AnswerDto(a.QuestionId, a.ChoiceId, a.UpdatedAt))
.ToListAsync(ct);
// One UPDATE statement (EF Core 7+), no entities loaded
var now = DateTime.UtcNow;
int expired = await db.Attempts
.Where(a => a.ExamId == examId
&& a.Status == AttemptStatus.InProgress
&& a.Deadline < now)
.ExecuteUpdateAsync(s => s
.SetProperty(a => a.Status, AttemptStatus.Expired)
.SetProperty(a => a.ClosedAt, now), ct);
The first query uses TagWith to add a comment to the start of the SQL, which shows up in logs and in the full batch text in sys.dm_exec_sql_text. The result is a DTO, so nothing is tracked, and the IX_Answers_AttemptId index covers its three columns. The second statement uses ExecuteUpdateAsync (EF Core 7 and later) to send a single UPDATE without loading any entities. The status condition is a constant and becomes a literal, so if InProgress is 0, the optimizer can use the filtered index above. This method bypasses the change tracker: entities already loaded in the same context are not updated, and it does not open a transaction of its own, so do not mix it with SaveChanges on the same rows.
Set-based writes and SqlBulkCopy
Row-by-row INSERTs in a loop mean one round trip and one autocommit transaction per row, and with full durability, which is the default, every commit waits for the log write. SaveChanges sends statements in batches, but for thousands of rows SqlBulkCopy is a better fit: it loads data from a DataTable or an IDataReader into the table over the bulk copy protocol. My pattern is to load the batch into a temporary table and merge it into the main table with one set-based statement.
In the watch-time pipeline for a large Persian-language online learning platform, the request path of the ingest service (a minimal API on .NET 10) only puts the event on a bounded in-memory channel and returns 202, or 503 if the channel is full. A background service loads batches into temporary tables and runs a set-based upsert in a single transaction; the analysis results are written with SqlBulkCopy on a connection with MARS turned off. In a Locust benchmark on a 12-core workstation, with 10,000 events and 50 concurrent users, the service accepted about 2,450 requests per second with a median of 13 ms and a 95th percentile of 21 ms, and its memory peaked at about 108 MB. These numbers measure the ingest path, and that is the point of the design: the request does not wait for SQL Server. The details are in the note on real watch time. A generic version of the pattern:
const string UpsertSql = """
UPDATE t SET t.Seconds = t.Seconds + b.Seconds
FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
JOIN #Batch AS b ON b.UserId = t.UserId AND b.LessonId = t.LessonId;
INSERT dbo.WatchTotals (UserId, LessonId, Seconds)
SELECT b.UserId, b.LessonId, b.Seconds FROM #Batch AS b
WHERE NOT EXISTS (SELECT 1 FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
WHERE t.UserId = b.UserId AND t.LessonId = b.LessonId);
""";
await using var conn = new SqlConnection(connectionString); // MARS is off by default
await conn.OpenAsync(ct);
await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(ct);
await using (var create = new SqlCommand(
"CREATE TABLE #Batch (UserId int NOT NULL, LessonId int NOT NULL, Seconds int NOT NULL);",
conn, tx))
await create.ExecuteNonQueryAsync(ct);
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tx))
{
bulk.DestinationTableName = "#Batch";
await bulk.WriteToServerAsync(batch, ct); // DataTable or IDataReader, one row per key
}
await using (var upsert = new SqlCommand(UpsertSql, conn, tx))
await upsert.ExecuteNonQueryAsync(ct);
await tx.CommitAsync(ct);
Everything runs on one connection, because #Batch is visible only in that session, and in one transaction that is also passed to SqlBulkCopy (without it you get an error), so the batch is applied either completely or not at all. The assumption is that the batch has one row per key and that (UserId, LessonId) is a unique key. UPDLOCK, HOLDLOCK is there for concurrent writers: it takes range locks even on keys that do not exist yet, so two transactions cannot insert the same new key at the same time and lose one of the increments. Deadlocks are possible in this mode, and the victim transaction has to be retried. With a single-instance writer there is no contention. MARS is off by default in SqlClient, and this code does not need it.
The whole sequence in one sentence: reproduce with a load test, find the expensive query, read the actual plan, fix the index or the query, check parameter sniffing and waits, and finally rerun the same load scenario. If your API slows down at peak and you would like a second pair of eyes, the Work with me page explains how I can help.
Frequently asked questions
What is the first step in finding a slow query in SQL Server?
Turn on Query Store and sort queries by total CPU or total duration; since SQL Server 2022 it is on by default for new databases. If it is not available, sys.dm_exec_query_stats gives similar statistics for the plans in the plan cache.
What is parameter sniffing?
SQL Server builds the plan for a parameterized query from the parameter value of the first execution and reuses it for later values. If the data distribution is skewed, that same plan can be very slow for some values. Fixes range from OPTION (RECOMPILE) and OPTIMIZE FOR to forcing a plan in Query Store and the PSP optimization in SQL Server 2022 and later.
Is it safe to turn on READ_COMMITTED_SNAPSHOT?
It is a common choice for most OLTP applications and the default in Azure SQL Database, but it changes the semantics of reads and needs space for row versions. Before turning it on, review any code that relies on reads waiting behind locks.
Do I always need AsNoTracking?
Only when you return full entities for reading. If you project to a DTO with Select and the result contains no entities, EF Core does not track anything at all.
When should I use SqlBulkCopy instead of SaveChanges?
When you write thousands of rows at once, such as batches of events. A common pattern is to load the batch into a temporary table with SqlBulkCopy and then merge it into the main table with a set-based upsert.