PrismitaLEARN · PRACTICE · GROW

How do you optimise a slow SQL query?

SQL Server Query Tuning Advanced 3–5 Yrs5+ Yrs Scenario 2 min read

Quick Answer

Measure first: capture the actual execution plan and STATISTICS IO/TIME. Then look for the usual culprits — a scan where a seek should happen (missing or unusable index), non-sargable predicates (functions on columns), key lookups (need a covering index), bad row estimates (stale statistics), SELECT * pulling too much, implicit conversions, and N+1 queries from the application. Fix one thing, re-measure.

I don't guess — I get the actual execution plan and turn on statistics IO and time so I have a baseline in logical reads and milliseconds. Then I read the plan for the obvious problems: a scan on a big table, key lookups that a covering index would remove, sorts, and a big gap between estimated and actual rows which points at stale statistics. A really common one is a non-sargable predicate — a function on the column in the WHERE clause — so I rewrite it as a range. After that it's usually adding the right covering index, updating stats, returning fewer columns, and checking the application isn't doing N+1 queries. I change one thing at a time and re-measure so I know what actually helped.

A practical order of attack:

  1. Reproduce and measure — get the actual plan, SET STATISTICS IO, TIME ON, note logical reads and duration. Know your target.
  2. Read the plan — highest-cost operators, fat arrows, scans, key lookups, sorts, spills, estimate-vs-actual gaps.
  3. Make predicates sargable — WHERE YEAR(OrderDate) = 2026 can't seek; rewrite as OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'. Avoid wrapping the column in functions or implicit casts.
  4. Fix indexing — add a covering index for the query's filter + returned columns; remove a redundant index that's winning wrongly.
  5. Update statistics — if estimates are far from actuals, UPDATE STATISTICS / rebuild; consider the sample size.
  6. Return less — replace SELECT * with needed columns; paginate; filter earlier.
  7. Reduce round trips — collapse an application N+1 into a single set-based query or a join; batch.
  8. Watch parameter sniffing — a plan compiled for an atypical parameter; options include OPTIMIZE FOR, RECOMPILE, or fixing the index so all values are fine.
  9. Re-measure and repeat.

SQL — non-sargable vs sargable 
-- SLOW: function on the column -> forces a scan
SELECT * FROM dbo.Orders
WHERE  YEAR(OrderDate) = 2026 AND Status = 1;

-- FAST: range predicate can use an index seek, and select only what you need
SELECT OrderId, CustomerId, Total, OrderDate
FROM   dbo.Orders
WHERE  OrderDate >= '2026-01-01'
  AND  OrderDate <  '2027-01-01'
  AND  Status = 1;

-- supporting index
CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate
    ON dbo.Orders (Status, OrderDate)
    INCLUDE (CustomerId, Total);

  • Measure-first avoids fixing the wrong thing
  • Sargable predicates let the engine seek instead of scan
  • A covering index removes key lookups entirely
  • Fresh statistics give the optimiser accurate estimates
  • Collapsing N+1 application queries often beats any index change

Practise the whole thread the way an interviewer walks it — each question builds on the last.

  1. What is an index in SQL Server?
  2. What is the difference between a clustered and a non-clustered index?
  3. When should you create an index?
  4. Can indexes hurt performance?
  5. What is an execution plan in SQL Server?
  6. How do you optimise a slow SQL query?You are here
All SQL Server questions More on Query Tuning