Data & Integrity

SQL Performance Tuning

Intermediate

The database is where most real performance problems live, and where a query that is instant on dev data becomes an outage on production volumes. Tuning SQL is a skill you can learn, and it is led by evidence: read the execution plan, index for your real queries, write sargable set-based SQL, and measure on realistic data. Guessing wastes effort. The plan tells you the truth.

We use Dapper over SQL Server, so the SQL we write is the SQL that runs. There is no ORM to blame or to save us. The good news is that database performance is one of the easiest areas to diagnose. SQL Server shows you exactly how it ran a query (the execution plan), where the time and rows went, and whether it used your indexes. Tuning is reading that, making a fix, and measuring again.

This goes deeper than the Gotchas: SQL and Database Design pages, and connects to Performance & Resource Use, Data Modelling & Persistence, and Caching. The recurring theme: a missing index, a non-sargable predicate, an N+1 pattern, or an unbounded query is usually behind a slow page. All of these can be found and fixed.

Diagnose before you tune

DoRead the actual execution plan to see what the query really did: scans versus seeks, estimated versus actual rows, and where time is spent. Do not guess.
DoTest on production-like data volumes. Performance changes completely between 100 rows and 100 million (see Test Data & Environments).
DoFind the queries that are actually slow from real signals (slow-query logs, query stats, monitoring) and fix the worst first, not whatever you assume is slow (see Observability).
DoMeasure before and after every change so you know it helped. Watch the whole workload too, since fixing one query can shift load elsewhere.
ConsiderA big gap between estimated and actual rows in the plan. It often points to stale statistics or a query the optimiser cannot reason about well.

Apply the high-value fixes

DoIndex for your real query patterns. Cover the columns you filter, join, and order on (including the tenant key), and use covering indexes for hot read paths (see Database Design).
DoKeep predicates sargable. Do not wrap an indexed column in a function (WHERE UPPER(Email)=...) or use leading wildcards, which prevent index use.
DoSelect only the columns and rows you need, and page large results (OFFSET/FETCH). Never use SELECT * or pull a whole table to the app (see Data Modelling & Persistence).
DoPrefer one set-based query over per-row loops or N+1 round trips. SQL Server is built for sets and is far faster at them.
DoParameterise queries. As well as preventing injection, it lets SQL Server reuse query plans (see Data Modelling & Persistence).
ConsiderCaching results that are genuinely expensive and rarely change (with correct, tenant-aware keys), and read replicas for heavy reporting, instead of over-tuning a query (see Caching Strategy, Reporting & Data Exports).
AvoidOver-indexing. Every index slows writes and adds maintenance, so add them based on evidence from the plan, not by reflex.

Non-sargable, unindexed, SELECT *

SELECT * FROM Customers
WHERE YEAR(CreatedUtc) = 2024 AND UPPER(Email) = @e;
-- no useful index; functions on the columns force a full scan

Functions wrapped around the columns stop any index being used, so SQL Server scans the whole table. This is fine on dev, but an outage on a large tenant. SELECT * also pulls back columns you do not need.

Sargable, indexed, explicit

SELECT Id, Email, Status FROM Customers
WHERE TenantId=@t AND Email=@e
  AND CreatedUtc >= @from AND CreatedUtc < @to;
-- index: (TenantId, Email) and/or (TenantId, CreatedUtc)

Predicates compare the columns directly, so indexes can seek. Only the needed columns are returned, and tenant scoping is both correct and indexed. This stays fast and flat as data grows.

Self-review checklist

AskHave I looked at the execution plan, or am I guessing what is slow?
AskAre the filter, join, and order columns indexed, and are my predicates sargable (no functions on indexed columns)?
AskAm I selecting only the needed columns and rows, set-based, with no N+1, and tested on real volumes?
AskDid I measure before and after, and check that I did not just move the load elsewhere?

Why it matters: The database is the most common source of production slowness and outages, and unlike many performance problems it is easy to diagnose. The execution plan removes the guesswork. Engineers who can read a plan and apply the few high-value fixes (indexing, sargable predicates, set-based and bounded queries) keep the platform fast as it grows. That is core to being genuinely good at this work.