Query Optimization — Complete Guide
Query Optimization — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of SQL Server Tutorial on Toolliyo Academy.
On this page
SQL Server Tutorial · Lesson 40 of 100
Query Optimization
SQL basics ✓ → Queries → Advanced
Queries · 2 — JOINs · ~6 min · SQL — Indexing & Performance
What is this?
Deeper optimization uses actual execution plans, seeks vs scans, lookups, joins type, and sargable predicates. You change indexes or queries based on evidence.
Why should you care?
“Query Optimization Basics” measured IO; here you fix the plan shape — eliminate scans, reduce lookups, avoid functions on columns.
See it live — copy this example
Run in SQL Server Management Studio (SSMS) or Azure Data Studio.
USE DataVerse;
-- Bad: non-sargable
SELECT * FROM dbo.Orders WHERE YEAR(OrderDate) = 2026;
-- Better: range seek-friendly
SELECT OrderId, City, Amount
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';What happened?
- YEAR(OrderDate) blocks a simple index seek on OrderDate.
- A closed-open date range keeps the predicate sargable so an index on OrderDate can seek.
Practice next
- Run both queries with actual plans.
- Compare estimated costs and operators.
- Ensure an index exists that supports the range.
- Find a Key Lookup and cover it with INCLUDE.
- Try OPTION (RECOMPILE) only as a temporary diagnose tool.
Remember
Read actual plans. Prefer sargable predicates. Fix the query and index together.
Year filter slowed reporting
DataVerse report used YEAR(OrderDate); rewritten to a range.
Outcome: Plan switched from scan to seek; CPU dropped.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
