Class Overview (What students will learn today)

Query optimization is the practice of making SQL Server do less work while returning the same correct results.
That “work” usually shows up as: too many page reads (logical/physical reads), too much CPU, large memory grants, spills to TempDB,
or blocking/waits. In this class, students learn to:
measure query cost, understand execution plans at a practical level, and apply
high-impact rewrites that improve performance without breaking correctness.

Learning Objectives (By the end of this class, students can…)

• Measure query performance using SET STATISTICS IO and SET STATISTICS TIME.
• Explain how SQL Server builds a plan: parse → optimize → execute.
• Read an execution plan at a practical level (scan vs seek, key lookup, hash match, sort, spool).
• Write SARGable predicates (so indexes can be used effectively).
• Reduce row processing by filtering early, selecting only needed columns, and avoiding expensive patterns.
• Identify common query anti-patterns (implicit conversions, functions on columns, non-sargable LIKE, OR explosions).
• Apply a safe “rewrite + validate” workflow that produces measurable improvement.

Part 1 — How SQL Server Executes a Query (10–15 min)

The 3 stages

1. Parse: SQL Server checks syntax and builds a logical representation.
2. Optimize: SQL Server chooses an execution plan based on estimated row counts and costs.
3. Execute: The storage engine reads pages, applies operators, returns results.

Part 2 — How to Measure Query Performance (Proof First) (10–15 min)

Before you rewrite anything, you need evidence. In SQL Server, the most beginner-friendly proof is:
reads and CPU time. Reads are often the best indicator of overall workload pressure.

-- Measurement baseline (use in dev/lab safely)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run the query here (before any changes)

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Part 3 — Execution Plans: What to Look For (15–25 min)

An execution plan is a map of what SQL Server decided to do. You do not need to memorize every operator to tune effectively.
You need to recognize the top operators that usually drive cost and slowdowns.

Part 4 — SARGability (The #1 Query Rewrite Skill) (15–20 min)

A predicate is SARGable when SQL Server can use an index efficiently (seek or efficient range scan).
Non-SARGable predicates force SQL Server to compute something for many rows, often turning seeks into scans.

Part 5 — Reduce Rows Early, Reduce Columns Always (10–15 min)

SQL Server has to process rows before it can return results. If your query reads 10 million rows to return 100 rows,
you have a row-reduction problem. Most of the time, performance improves by pushing filters earlier and selecting fewer columns.

Part 6 — Joins, Aggregations, and “Expensive” Operations (15–25 min)

Joins and aggregations are where SQL Server often spends the most time. The goal is to make joins join fewer rows,
use indexes correctly, and avoid unnecessary sorting/hashing.

Instructor Demo (Optional) — “Rewrite + Proof (Before/After)” (20–30 min)

Demo goal: take a slow query and improve it using a small rewrite (SARGability, fewer columns, better filtering),
then prove improvement with IO/TIME and plan changes.

-- Demo: measure first
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 1) Run the original query (capture reads + CPU time)
--    - Identify: scan? lookup? big sort? hash join? warnings?

-- 2) Apply ONE change (example: SARGable date range, remove function on column)
-- 3) Re-run query and compare:
--    - logical reads
--    - CPU time
--    - elapsed time
--    - plan operators

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Student Lab (25–40 min) — “Fix a Non-SARGable Query”

Lab Outcome

Students will identify a non-SARGable predicate, rewrite it into a SARGable form, and prove improvement using reads/time.
This lab trains students to tune safely and communicate results with evidence.

Tasks

1. Find a query using a function on a column, implicit conversion, or a leading-wildcard LIKE.
2. Measure baseline with STATISTICS IO/TIME.
3. Rewrite the predicate to be SARGable (date range, matching types, better pattern).
4. Re-run and capture before/after evidence.
5. Write a short explanation: “What changed and why it got faster.”

Common Beginner Mistakes (Query Optimization Edition)

• “Tuning” by adding hints or forcing plans without understanding why the plan is bad.
• Using functions on columns in WHERE clauses (kills index efficiency).
• Using SELECT * and returning huge columns (wide reads increase I/O and memory).
• Adding many indexes instead of fixing the predicate and row reduction first.
• Ignoring proof (no before/after IO/TIME metrics).
• Changing multiple things at once (no control, no learning, hard rollback).

Class Discussion Prompt

Homework Assignment

1. Pick one query you run often (report or API query).
2. Measure baseline (IO/TIME) and write down the results.
3. Apply one improvement:
   • Make a predicate SARGable, OR
   • Reduce columns returned, OR
   • Filter earlier, OR
   • Remove unnecessary DISTINCT/ORDER BY
4. Submit before/after metrics and a 6–10 sentence explanation.

Quick Knowledge Check (5 questions)

1. What does it mean for a predicate to be SARGable?
2. Why are logical reads often a better tuning metric than elapsed time alone?
3. Name two execution plan operators that often indicate heavy work.
4. Why can SELECT * be a performance problem?
5. What is the correct way to prove your rewrite improved performance?

Closing (What’s next)

In the next lesson, we move into Monitoring and Troubleshooting SQL Server Performance.
Query optimization improves individual queries, but DBAs must also diagnose system-wide bottlenecks using waits, DMVs,
Query Store, blocking analysis, and performance baselines.