FullStack Engineering CourseLesson 13 of 20 17 min
Data: SQL Tuning, Indexing, EF Core Performance, Scaling and CDC
How the database runs a query
Most performance problems in business applications end up in the database. To fix them, it helps to know what happens when SQL Server (or PostgreSQL) receives a query:
- The query optimiser looks at the query, the available indexes and the statistics (summaries of how values are distributed in each column), and estimates the cost of many possible ways to run it.
- It picks the cheapest plan it found: the execution plan. The plan says which indexes to use, in which order to join tables, and how.
- The plan is stored in the plan cache and reused for later runs of the same query, because finding a plan is expensive.
Two consequences matter in practice:
- Out-of-date statistics lead to bad estimates and bad plans. SQL Server updates statistics automatically, but after a large data load it can be worth updating them yourself.
- Reusing one plan for every parameter value can go wrong. This is called parameter sniffing: the plan is built for the parameter values of the first call. If that call was for a small payer with 10 claims, the plan may be terrible for a large payer with 2 million.
The execution plan is the main diagnostic tool. In SSMS or Azure Data Studio, show the actual plan, and look for:
| Operator | Meaning | Usually |
|---|---|---|
| Index Seek | Jumps straight to the needed rows using an index | Good |
| Index Scan / Table Scan | Reads the whole index or table | Bad on large tables, unless most rows are needed |
| Key Lookup | Goes back to the table for columns the index does not have, once per row | Bad when it runs for many rows; fix with a covering index |
| Sort / Hash Match with spills | Not enough memory, so data is written to tempdb | Bad; often caused by wrong row estimates |
| Big gap between estimated and actual rows | The optimiser guessed wrong | The root cause of many slow queries: check statistics and parameter sniffing |
Query Store (on by default in Azure SQL Database) records every query's plans and run-time statistics over time. Use it to find the queries that use the most CPU or time, to see when a query's plan changed and got slower, and to force the last good plan as a quick fix while you work on a real one.
Indexing
An index is a sorted copy of some columns that lets the database find rows without reading the whole table, like the index at the back of a book. In SQL Server:
- Clustered index: the table itself, stored sorted by the clustered key (usually the primary key). A table has at most one.
- Nonclustered index: a separate sorted structure of chosen columns, with a pointer back to the full row.
- Covering index: a nonclustered index that contains every column a query needs (key columns plus INCLUDE columns), so there is no key lookup at all.
-- Query: a payer's recent claims, newest first.
SELECT ClaimId, Amount, Status, SubmittedAt
FROM Claims
WHERE PayerId = @payerId AND SubmittedAt >= @from
ORDER BY SubmittedAt DESC;
-- Equality column first, then the range/sort column; INCLUDE the rest so the index covers the query.
CREATE INDEX IX_Claims_Payer_SubmittedAt
ON Claims (PayerId, SubmittedAt DESC)
INCLUDE (Amount, Status);
-- Filtered index: small and fast when queries always target a subset.
CREATE INDEX IX_Claims_Pending
ON Claims (SubmittedAt)
INCLUDE (PayerId, Amount)
WHERE Status = 'Pending';Rules for designing indexes:
- Design for the real workload, not one query. Look at the most frequent and most expensive queries in Query Store.
- In a composite index, put equality columns first and range or sort columns after them. An index on (PayerId, SubmittedAt) helps "PayerId = x AND SubmittedAt > y", but not a query on SubmittedAt alone.
- Prefer a few well-chosen composite indexes whose leading columns serve several queries over many single-column indexes. SQL Server usually uses one index per table in a query.
- Every index slows down inserts, updates and deletes and takes storage. Remove indexes that are never used (sys.dm_db_index_usage_stats shows this).
- Treat the missing-index suggestions in plans and DMVs as hints, not orders. They often suggest near-duplicates of existing indexes.
- For reporting and analytics over millions of rows (sums, counts, group-bys), a columnstore index stores data by column and compresses it, and can be many times faster than row-based indexes for those queries.
Writing queries the optimiser can use
A predicate (a condition in the WHERE clause) is SARGable (Search ARGument-able) when the database can use an index to evaluate it. Many slow queries have a predicate that hides the column from the index:
| Not SARGable (scans) | SARGable (seeks) | Why |
|---|---|---|
| WHERE YEAR(SubmittedAt) = 2026 | WHERE SubmittedAt >= '2026-01-01' AND SubmittedAt < '2027-01-01' | A function on the column hides it from the index |
| WHERE Amount * 100 > 50000 | WHERE Amount > 500 | Calculate on the parameter side instead |
| WHERE ExternalRef = 12345 (column is varchar) | WHERE ExternalRef = '12345' | An implicit type conversion on the column prevents a seek |
| WHERE PatientName LIKE '%sharma' | WHERE PatientName LIKE 'sharma%' | A leading wildcard cannot use a sorted index; use full-text search for contains-style queries |
| WHERE ISNULL(Status, 'New') = 'New' | WHERE (Status = 'New' OR Status IS NULL) | Wrapping the column in a function again |
Implicit conversions are common in .NET code: if a varchar column is compared with an nvarchar parameter, SQL Server converts the column on every row. EF Core usually gets this right when the column type is mapped correctly (for example with [Unicode(false)] or HasColumnType("varchar(20)")).
Fixes for parameter sniffing, from lightest to heaviest:
- Check whether the data is simply very uneven; sometimes an extra index makes one plan good for everyone.
- SQL Server 2022 and Azure SQL (compatibility level 160) can keep several plans for one query, for different parameter ranges (parameter sensitive plan optimisation).
- OPTION (OPTIMIZE FOR UNKNOWN) builds a plan for an average value instead of the first one.
- OPTION (RECOMPILE) builds a fresh plan on every run. It is accurate, but it costs CPU, so keep it for queries that do not run constantly.
- Force a known good plan with Query Store.
Transactions, isolation levels and locking
A transaction groups several changes so they all happen or none do. The isolation level decides what one transaction can see of others that are running at the same time:
| Isolation level | Can read uncommitted changes | Repeated reads see the same data | Notes |
|---|---|---|---|
| Read uncommitted (NOLOCK) | Yes (dirty reads) | No | Can return rows twice or not at all; avoid for business data |
| Read committed (SQL Server default) | No | No | Readers wait for writers' locks |
| Read committed snapshot (RCSI) | No | No | Readers see the last committed version instead of waiting; on by default in Azure SQL Database |
| Repeatable read | No | Yes | Holds read locks until the end; more blocking |
| Snapshot | No | Yes, the whole transaction sees one point in time | Write conflicts are reported as errors |
| Serializable | No | Yes, and no new matching rows appear | Strongest; most blocking and deadlocks |
RCSI (in SQL Server) and MVCC (multi-version concurrency control, which PostgreSQL always uses) let readers see a consistent committed version without blocking writers. For most applications this is the best default, combined with optimistic concurrency for updates (lesson 5).
A deadlock happens when two transactions each hold a lock the other needs. The database detects it and cancels one of them (error 1205 in SQL Server). To reduce deadlocks:
- Keep transactions short, and never wait for a user or an external call inside one.
- Access tables and rows in the same order in every code path.
- Make sure updates and deletes use indexes, so they lock few rows instead of scanning and locking many.
- Treat a deadlock as a transient error and retry (EF Core's execution strategy does this, lesson 7).
EF Core performance
EF Core is productive, but it hides the SQL it runs. The most effective habits:
- See the SQL: log it in development (LogTo or the Microsoft.EntityFrameworkCore.Database.Command log category), or call ToQueryString() on a query.
- Read-only queries: use AsNoTracking(), so EF Core does not set up change tracking for results you will not save.
- Load only what you need: project into a DTO with Select instead of loading whole entities.
- Avoid N+1 queries (one query for a list, then one more per item). Do not enable lazy loading; load related data with Include or a projection.
- Many Includes of collections multiply the rows returned (a cartesian explosion). Use AsSplitQuery() to fetch them in separate queries.
- Update or delete many rows with ExecuteUpdateAsync and ExecuteDeleteAsync, which run one SQL statement without loading the rows.
- Insert large volumes with SqlBulkCopy (or a bulk-insert library), not one Add per row.
- Page with cursors on large tables (lesson 4).
- For very hot queries, use compiled queries (EF.CompileAsyncQuery) and DbContext pooling (AddDbContextPool) to cut overhead.
// Before: loads full entities with tracking, then filters and maps in memory.
var claims = await db.Claims.Include(c => c.Lines).ToListAsync(ct);
var pending = claims.Where(c => c.Status == ClaimStatus.Pending).Select(ClaimDto.From).ToList();
// After: filtering, projection and paging happen in SQL; no tracking; only the needed columns.
var pending2 = await db.Claims
.AsNoTracking()
.Where(c => c.PayerId == payerId && c.Status == ClaimStatus.Pending)
.OrderByDescending(c => c.SubmittedAt)
.Select(c => new ClaimDto(c.Id, c.Amount, c.SubmittedAt, c.Lines.Count))
.Take(50)
.ToListAsync(ct);
// Bulk change in one statement, no entities loaded.
int archived = await db.Claims
.Where(c => c.Status == ClaimStatus.Closed && c.ClosedAt < cutoff)
.ExecuteUpdateAsync(s => s.SetProperty(c => c.IsArchived, true), ct);Connection pooling
Opening a database connection is slow (a network handshake, TLS and authentication). ADO.NET keeps a pool of open connections per unique connection string. When code "opens" a connection, it borrows one from the pool; when code disposes it, the connection goes back to the pool, still open. The default maximum is 100 connections per pool.
Pool exhaustion shows up as errors saying a connection could not be obtained before the timeout expired. The usual causes:
- Connections that are not disposed: always use using or await using, or let EF Core manage them.
- Long-running work while holding a connection: a slow external call inside a transaction.
- Blocking async code (.Result, .Wait()), which holds connections while threads are stuck (lesson 1).
- Slow queries: if each query takes 2 seconds and 100 run at once, the pool is full.
- Connection strings that differ slightly (for example, built with different values), which create many separate pools.
Raising Max Pool Size rarely fixes the cause; fix the slow queries or the leaks first. The database also has its own limit on connections, so many app instances with large pools can overload it. PostgreSQL in particular handles many connections poorly, so it is often used with a separate pooler such as PgBouncer.
Scaling: replicas, partitioning and sharding
When one database is not enough, there are several options, from simplest to most complex:
| Approach | What it does | Good for | Costs |
|---|---|---|---|
| Scale up | A bigger database (more CPU, memory, faster storage) | The first step; no code changes | Has an upper limit, and the price rises steeply |
| Read replicas | Read-only copies that serve queries (in Azure SQL, add ApplicationIntent=ReadOnly to the connection string) | Reports, searches and dashboards that can be slightly behind | Replica lag: recent writes may not be visible yet |
| Table partitioning | Splits one large table into partitions by a key, usually date, inside one database | Very large tables; archiving old data quickly by switching out a whole partition | Queries must filter on the partition key to benefit |
| Sharding | Splits data across several databases by a shard key, such as tenant | Data or write volume beyond one database; tenant isolation | Queries across shards are hard; rebalancing is hard; much more operational work |
Choose the shard key carefully, because changing it later is very hard. A good shard key spreads the load evenly, keeps the data for one request in one shard (tenant ID is often ideal in a multi-tenant system), and does not create hot spots (a date as the shard key sends all new writes to one shard). Before sharding, consider Azure SQL Hyperscale, which scales storage to very large sizes, or a database per tenant (lesson 10).
When reading from replicas, remember read-your-own-writes: after a user saves something, read it from the primary, or they may not see their own change.
PostgreSQL in brief
Most of this lesson applies to PostgreSQL too. The main differences to know:
- Use EXPLAIN (ANALYZE, BUFFERS) to see the actual plan, row counts and I/O for a query.
- Because of MVCC, updates and deletes leave old row versions behind. The autovacuum process cleans them up; tables that change a lot may need it tuned, or they grow and slow down (called bloat).
- Index types beyond B-tree: GIN for JSONB and full-text search, BRIN for very large tables where values follow insert order (such as timestamps), and partial indexes (the equivalent of filtered indexes).
- JSONB columns store and index JSON efficiently, which is useful for semi-structured data such as payer-specific fields.
- Extensions add capabilities, such as pgvector for vector search (lesson 20) and Citus for sharding.
Change data capture (CDC)
Change data capture records every insert, update and delete made to a table, so other systems can react to changes without the application having to publish them. Uses include:
- Keeping a search index, cache or read model (CQRS, lesson 9) up to date.
- Feeding a data warehouse or analytics platform.
- Copying data from an old system to a new one during a migration (lesson 10).
- Turning database changes into events, as an alternative to the Outbox pattern.
-- SQL Server / Azure SQL: enable CDC for the database, then for one table.
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Claims', @role_name = NULL;
-- Read all changes between two log sequence numbers (positions in the transaction log).
DECLARE @from binary(10) = sys.fn_cdc_get_min_lsn('dbo_Claims'),
@to binary(10) = sys.fn_cdc_get_max_lsn();
SELECT __$operation, ClaimId, Status, Amount -- __$operation: 1 delete, 2 insert, 3/4 update before/after
FROM cdc.fn_cdc_get_all_changes_dbo_Claims(@from, @to, N'all');SQL Server CDC reads changes from the transaction log, so it adds little load to normal writes. Change Tracking is a lighter option that records only which rows changed, not their old values. Debezium is a popular open-source tool that reads the change logs of SQL Server, PostgreSQL and other databases and streams the changes to Kafka or Azure Event Hubs, where consumers process them like any other events.
SSIS (SQL Server Integration Services) and SSRS (SQL Server Reporting Services) are the classic SQL Server tools for moving data in batches (ETL: extract, transform, load) and for producing reports. They still run many systems. For new work in Azure, Azure Data Factory or Microsoft Fabric usually replace SSIS, and Power BI replaces SSRS for most reporting.
Check your understanding
| Question | Short answer |
|---|---|
| What is parameter sniffing? | A cached plan built for the first parameter values is reused for very different values, where it performs badly. |
| Index seek or scan? | A seek jumps to the needed rows; a scan reads everything. On large tables, a scan for a selective query usually means a missing or unusable index. |
| What is a covering index? | An index that contains every column a query needs, so no key lookups are required. |
| Which order for composite index columns? | Equality columns first, then range or sort columns. |
| What makes a predicate non-SARGable? | Functions or calculations on the column, implicit conversions, or a leading wildcard. |
| Why use RCSI? | Readers see the last committed version instead of waiting for writers' locks. |
| How do you reduce deadlocks? | Short transactions, the same access order everywhere, indexed updates, and retrying when they happen. |
| Name three EF Core performance habits. | AsNoTracking for reads, projecting into DTOs, and ExecuteUpdate/ExecuteDelete for bulk changes. |
| What causes connection pool exhaustion? | Undisposed connections, slow queries, blocking async code, or long work while holding a connection. |
| Partitioning or sharding? | Partitioning splits a table inside one database; sharding splits data across databases, with far more complexity. |
| What makes a good shard key? | Even load, one request's data in one shard, and no hot spots; tenant ID often fits. |
| What is CDC used for? | Streaming database changes to search indexes, read models, analytics or a new system. |
Summary
- Read execution plans and use Query Store to find the queries that matter; check estimates against actual rows.
- Index for the real workload: composite keys with equality columns first, INCLUDE to cover, and no unused indexes.
- Write SARGable predicates and match parameter types to column types.
- Prefer RCSI or MVCC with optimistic concurrency; keep transactions short and retry deadlocks.
- In EF Core, look at the SQL, use AsNoTracking and projections, avoid N+1 and use bulk operations.
- Dispose connections, keep queries fast and leave the pool size alone until the cause is fixed.
- Scale up first, then add read replicas and partitioning; shard only when nothing else works, with a carefully chosen key.
- Use CDC to stream changes to other systems without changing the application.
The next lesson covers the main Azure services for running .NET applications: App Service, Functions and Durable Functions, Logic Apps, AKS and Container Apps, API Management, Key Vault, CDN and load balancing.
Comments