When a query is slow, the temptation is to start changing things - add an index here, rewrite a join there - and hope. Effective tuning is the opposite: you find the real bottleneck with evidence, fix that one thing, and measure the difference. This guide covers the techniques that consistently move the needle in SQL Server, starting with the tool that shows you the truth.

Reading execution plans

The execution plan is SQL Server’s roadmap for running your query, and it’s where every investigation should begin. There are two kinds, and the distinction matters:

  • The estimated plan shows what the optimiser expects to do, based on statistics, without running the query.
  • The actual plan shows what really happened, including actual row counts alongside the estimates.

The gap between estimated and actual rows is often the smoking gun. When the optimiser expects 10 rows and gets 2 million, it likely chose the wrong strategy - a nested loop where a hash join was needed, or a scan where a seek would have won. Look for thick arrows (lots of rows moving), scans on large tables, key lookups, and warnings about spills to tempdb. Those are your targets.

Writing SARGable queries

A predicate is SARGable (Search ARGument able) when SQL Server can use an index seek to satisfy it. The most common performance killer is wrapping an indexed column in a function or expression, which forces the engine to evaluate every row and fall back to a scan. Here’s the classic non-SARGable pattern and its SARGable rewrite:

sql
-- NON-SARGable: the function on OrderDate blocks an index seek
SELECT OrderId, TotalAmount
FROM dbo.SalesOrder
WHERE YEAR(OrderDate) = 2026;          -- forces a scan

-- SARGable: a range on the bare column lets SQL Server seek
SELECT OrderId, TotalAmount
FROM dbo.SalesOrder
WHERE OrderDate >= '2026-01-01'
  AND OrderDate <  '2027-01-01';         -- uses the index

Both queries return the same rows, but only the second can seek an index on OrderDate. The same principle applies to WHERE UPPER(Email) = ..., WHERE col + 0 = ... or an implicit type conversion caused by comparing an NVARCHAR column to a VARCHAR literal - each quietly defeats your index. Keep the indexed column bare on one side of the comparison.

Don’t guess where the time goes - read the actual plan, find the one expensive operator, and fix that. Everything else is noise. - The Solution On IT database team

Avoid SELECT *

SELECT * is convenient and costly. It returns columns the application doesn’t need, moves more data over the network, and - most importantly - prevents a non-clustered index from covering the query, because the index rarely contains every column. Naming just the columns you need often lets a covering index satisfy the whole query without touching the base table. It also makes your code resilient: adding a column to the table won’t silently change what your query returns.

Parameter sniffing and its remedies

SQL Server caches the plan for a parameterised query and reuses it. On first execution it sniffs the parameter values and builds a plan tuned for them. That’s usually good - until the first values are unrepresentative. A plan built for a customer with three orders may be reused for a customer with three million, and performance collapses. Common remedies:

  • OPTION (RECOMPILE) - compiles a fresh plan on every execution, ideal when values vary wildly and recompilation cost is acceptable.
  • OPTIMIZE FOR - pins the plan to a chosen typical value, or OPTIMIZE FOR UNKNOWN to use average distribution rather than the sniffed value.
  • Keep statistics current - accurate statistics make the optimiser’s estimates, and therefore its plans, more trustworthy in the first place.

Keeping statistics current

Statistics describe the distribution of values in your columns and indexes, and the optimiser leans on them entirely to estimate how many rows a step will produce. Stale statistics lead to bad estimates, which lead to bad plans - the wrong join type, the wrong index, memory grants that are far too small or too large. SQL Server updates them automatically, but on large or rapidly changing tables the automatic threshold can lag behind. Scheduled UPDATE STATISTICS (or an index rebuild, which refreshes them) keeps the optimiser working from an accurate picture.

Set-based thinking over RBAR

SQL Server is built to operate on sets of rows, not one row at a time. Cursors and WHILE loops that process rows individually - often called RBAR, “row by agonising row” - are usually dramatically slower than a single set-based statement that lets the engine optimise the whole operation. Before reaching for a cursor, ask whether the same result can be expressed as one UPDATE, INSERT ... SELECT or a query with window functions. Nine times out of ten it can, and it runs orders of magnitude faster.

Joins, tempdb pressure and wait statistics

Choose joins deliberately and let indexes support them: the optimiser picks nested loop, merge or hash joins based on data size and available indexes, and a missing index on a join column is a frequent cause of a bad choice. Watch tempdb pressure too - large sorts, hash joins and spills all spill to tempdb, and a query that spills because its memory grant was underestimated can slow to a crawl; that often traces back to the stale statistics above.

To find bottlenecks across the whole server rather than one query, read wait statistics. SQL Server records what sessions spend time waiting on - I/O, locks, memory, CPU - and those waits point straight at the dominant constraint. Tuning the thing the server actually waits on is far more effective than optimising a query that was never the problem. For very large data changes, batch your updates into chunks of a few thousand rows rather than one giant transaction, to keep the log, locking and tempdb under control.

Common mistakes to avoid

  • Tuning without the actual plan. Guessing wastes effort; the plan shows exactly where the time goes.
  • Functions on indexed columns. The number-one cause of accidental scans - keep predicates SARGable.
  • SELECT * everywhere. It blocks covering indexes and ships needless data.
  • Cursors for set operations. Row-by-row processing where a single set-based statement would do.
  • Ignoring statistics. Out-of-date statistics quietly produce bad plans no rewrite can fix.

Best practices to follow

  1. Start from the actual execution plan. Let evidence, not intuition, choose your target.
  2. Keep predicates SARGable. Never wrap indexed columns in functions or force implicit conversions.
  3. Select only the columns you need. It enables covering indexes and reduces I/O.
  4. Prefer set-based statements. Reserve cursors for the rare cases that truly need them.
  5. Keep statistics fresh and read wait stats. Tune the constraint the server actually hits.

Tuning and indexing go hand in hand - a SARGable query still needs the right index to seek, so pair this with our SQL Server indexing guide. When queries need to get fast fast, our SQL Server development service profiles your workload and delivers measurable, provable improvements.

Frequently asked questions