Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Performance & Design

Query Optimization

Most slow queries share a handful of fixable causes. Learn to spot them and rewrite queries so indexes can do their job.

Advanced 5 min Lesson 25 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:Execution Plans
0/31 in SQL Server
On this page · 5 min · Advanced

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Advanced
Updated
Oct 27, 2026

Related tutorials

Interview questions

In simple words#

Optimising a query is like packing for a trip: take only what you need (columns), go straight to the right shelf (indexes), and avoid repeated trips (row-by-row loops).

SARGable
A filter that can use an index (no function on the column).
SELECT only needed columns
Less data to read and send.
Set-based
One statement for many rows, not loops.

Learning objectives#

  • Write SARGable WHERE clauses
  • Avoid implicit conversions
  • Prefer set-based operations to loops
  • Recognise parameter sniffing

Explanation#

A predicate is SARGable (Search ARGument-able) when SQL Server can use an index seek. Wrapping the column in a function — YEAR(OrderDate) = 2026 — usually prevents it.

Comparing columns to values of a different type causes implicit conversion, which can force scans. Match parameter types to column types.

Return only the columns and rows you need, and process data in sets rather than row-by-row cursors or loops.

Parameter sniffing: a procedure’s plan is optimised for the first parameter values it sees, which may suit later values badly. Options include OPTIMIZE FOR, RECOMPILE for rare cases, or Query Store plan forcing.

Code example: Making a date filter SARGable#

SQLMaking a date filter SARGable Illustrative
-- ✗ Not SARGable: the function on the column forces a scan
SELECT OrderId FROM dbo.Orders WHERE YEAR(OrderDate) = 2026;


-- ✓ SARGable: compare the bare column with a range
SELECT OrderId FROM dbo.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';
Expected output
OrderId
101
102
103
104

-- Same rows; with an index on OrderDate the second query uses an Index Seek.

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. Both queries return all four 2026 orders.
  2. The first must compute YEAR() for every row, so it cannot seek.
  3. The second leaves OrderDate untouched, so an index on OrderDate can seek to the range.
  4. Using < next year’s date (not <= 31 Dec) also handles time values safely.

Real-world use case#

Rewriting a single non-SARGable report query often cuts its runtime from minutes to milliseconds.

Common mistakes#

ISNULL or functions on indexed columns

WHERE ISNULL(Status, '') = 'Delivered' prevents seeks; WHERE Status = 'Delivered' already excludes NULLs.

Cursors for bulk updates

Row-by-row loops are often hundreds of times slower than one set-based UPDATE.

Best practices#

  • Keep indexed columns bare in WHERE and JOIN
  • Match data types between parameters and columns
  • Select only needed columns
  • Measure with actual plans and logical reads

Remember this#

Don’t wrap indexed columns in functions in WHERE.

Select only needed columns.

Think in sets, not loops.

Practice exercise#

Your task: Rewrite WHERE LEFT(Name, 3) = 'Lap' to be SARGable.

Show solution
SQL Illustrative
WHERE Name LIKE 'Lap%'

Mini quiz#

Query Optimization quizMultiple choice Question 1 of 1

Which predicate can use an index seek on OrderDate?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.