---

Class Overview (What students will learn today)

Performance tuning is not “making SQL faster” by random tweaks. It’s a disciplined process that produces measurable improvements
and predictable results.

---

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

• Define performance using measurable terms: latency, throughput, concurrency, and resource usage.
• Explain the three “layers” of tuning: server, database, and query.
• Use a simple, repeatable tuning methodology: baseline → identify → test → validate.
• Identify common symptoms (slow reports, timeouts, blocking, high CPU, high I/O) and map them to likely root causes.
• Understand why guessing fails and why baseline + evidence always wins.

---

Part 1 — What “Performance” Means (5–10 min)

What students often say

• “The database is slow.”
• “The server is bad.”
• “SQL Server is lagging.”

How a performance tuner translates it

• “This report takes 45 seconds instead of 5 seconds.”
• “The API endpoint returns in 1.8 seconds; SLA is 300 ms.”
• “CPU is consistently 90%+ during peak time.”
• “Blocking occurs every morning at 9:00–9:15 AM.”

Core performance metrics (the language of tuning)

• Latency (response time): How long one request takes.
• Throughput: How many requests/transactions per second can be completed.
• Concurrency: How many users can work at once without collapse.
• Resource utilization: CPU, memory, disk I/O, network.
• Stability and predictability: Consistent performance matters more than occasional speed.

---

Part 2 — Why SQL Server Performance Problems Happen (10–15 min)

SQL Server performance issues usually come from one (or more) of the categories below. In production, the winning approach
is to identify which category is dominating right now, then apply the smallest safe fix that produces measurable improvement.

1. Workload changes
   • More users, more transactions
   • A new feature ships
   • A new report runs daily
   • Data grows (tables get huge)
2. Query patterns
   • Missing indexes or wrong indexes
   • Non-SARGable predicates (functions on columns, implicit conversions)
   • Bad join strategy, huge scans, poor filtering
   • Parameter sniffing / plan reuse problems
3. Resource constraints
   • CPU saturation
   • Memory pressure (buffer cache not large enough, excessive memory grants)
   • Storage bottlenecks (slow disks, high I/O waits)
   • TempDB contention
4. Concurrency & blocking
   • Long transactions
   • Poor isolation design
   • Hot spots (same rows/pages updated repeatedly)
   • Lock escalation patterns
5. Configuration and maintenance gaps
   • Outdated statistics
   • Fragmentation (sometimes important, sometimes not)
   • Poor TempDB setup
   • Misconfigured max server memory, MAXDOP, cost threshold
   • No monitoring / no baselines

---

Part 3 — The Performance Tuning Mindset (10 min)

The #1 rule: Don’t guess

Guessing creates fragile systems and “fixes” that break later. Professional tuning is evidence-first.

• Unnecessary index creation (bloat + slower writes)
• Random “hints” that break later
• Changing settings without understanding impact
• Fixing symptoms, not root cause

The #2 rule: Baseline before you change anything

A baseline answers:

• What is normal CPU/memory/I/O?
• What are normal wait types?
• What are the top queries by duration/CPU/reads?
• When does the system slow down (time-of-day patterns)?

The #3 rule: One change at a time, validate every change

If you change five things and performance improves, you don’t know why.
If you change one thing and measure, you learn and control risk.

---

Part 4 — A Repeatable Tuning Workflow (15–20 min)

This workflow becomes the foundation for every future lesson (indexing, execution plans, waits, troubleshooting).
Your job is not to “try fixes,” but to follow a controlled process that produces proof.

---

Part 5 — “Where Do I Start?” (The Three Layers of Tuning) (10 min)

---

Instructor Demo (Optional) — “Evidence-First Tuning” (10–15 min)

Demo Goal

Show students the difference between “guessing” and “measuring.”

1. Run a slow query (or a query that does a large scan).
2. Show: duration, logical reads, CPU time.
3. Show an execution plan at a high level (scan vs seek).
4. Explain: “We do not fix until we know what resource is being burned and why.”

If you want a simple baseline script to demonstrate “collect evidence,” use this:

-- Quick baseline snapshot (safe, read-only)
SELECT 
  GETDATE() AS captured_at,
  cpu_count,
  hyperthread_ratio,
  physical_memory_kb/1024 AS physical_memory_mb
FROM sys.dm_os_sys_info;

-- Top waits (since last restart) - basic view for class discussion
SELECT TOP (15)
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

---

Student Lab (15–20 min) — “Your First Performance Baseline”

Lab Outcome

Students will capture a basic “before” snapshot of their SQL Server and store it as evidence.

Tasks

• Capture CPU count and memory
• Capture current top waits
• Answer:
  • What are the top 3 waits you see?
  • What do you think they might indicate?
  • What would you check next before changing anything?

Lab script (safe, read-only):

-- Lab: capture baseline evidence (safe)
SELECT GETDATE() AS captured_at;

SELECT 
  cpu_count,
  scheduler_count,
  physical_memory_kb/1024 AS physical_memory_mb,
  sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT TOP (20)
  wait_type,
  wait_time_ms,
  waiting_tasks_count,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;

---

Common Beginner Mistakes (Teach this early)

• Adding indexes blindly (creates storage bloat and slows INSERT/UPDATE/DELETE).
• Chasing one metric (e.g., CPU) without checking blocking, I/O, or memory.
• Ignoring concurrency (a query that is “fast alone” may fail under load).
• Tuning without a baseline (no proof of improvement, no rollback confidence).
• Copying “best settings” from the internet (MAXDOP and memory settings are context-specific).

---

Class Discussion Prompt

---

Homework Assignment

1. Write a 1-page Performance Problem Definition for a fictional scenario:
   • Symptom
   • When it happens
   • SLA target
   • What changed recently
   • What evidence you’d collect first
2. Submit your baseline results:
   • CPU count, memory MB
   • Top 5 waits

---

Quick Knowledge Check (5 questions)

1. What is the difference between latency and throughput?
2. Why is a baseline necessary before tuning?
3. Name the three layers of tuning (server/database/query).
4. Why is “one change at a time” important?
5. A query is slow only during peak time. What does that suggest about the problem?

---

Closing (What’s next)

In the next lesson, we will go deeper into Database Architecture and how SQL Server processes queries
internally (storage engine vs query processor). Once you understand what SQL Server is doing under the hood, performance
tuning becomes much easier and far more consistent.