Class Overview (What students will learn today)

SQL Server performance tuning becomes much easier when you understand the database architecture.
This lesson focuses on the components that impact speed the most: the storage engine, the query processor,
memory (buffer pool), transaction log, TempDB, and execution pipeline.

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

• Explain how SQL Server stores data using pages, extents, and indexes.
• Describe the difference between the query processor and storage engine and how they work together.
• Understand the role of buffer pool (memory) and why “logical reads” matter.
• Explain how transactions and the transaction log impact performance and recovery.
• Recognize why TempDB exists and when queries spill to it.
• Use architecture concepts to predict root causes for common symptoms (high I/O, high CPU, blocking, slow reads).

Part 1 — SQL Server Architecture: The Two Engines (10–15 min)

1) Query Processor (Relational Engine)

• Parses your SQL and builds a logical plan
• Optimizes the plan (chooses joins, order, index usage, parallelism)
• Produces an execution plan the storage engine can run

2) Storage Engine

• Reads and writes pages to/from memory and disk
• Manages locks, latches, and physical access methods
• Executes operators that require data access (scans, seeks, lookups)

Part 2 — How SQL Server Stores Data (Pages, Extents, Files) (10–15 min)

SQL Server stores table and index data in 8 KB pages. Pages are grouped into extents.
When a query reads data, it is reading pages — not “rows.” This is why the most important tuning metric is often
logical reads.

Part 3 — Memory (Buffer Pool) and Logical Reads (10–15 min)

SQL Server tries to keep frequently accessed pages in memory (the buffer pool). If the page is in memory,
the engine uses it immediately (fast). If it isn’t, SQL Server must fetch it from disk (slow), which increases I/O waits.

Two important definitions

• Logical reads: Pages read from memory (buffer cache).
• Physical reads: Pages read from disk (storage).

Part 4 — Transactions, Locks, and the Log (10–15 min)

SQL Server is a transactional database. Transactions provide correctness, but they also create waiting:
locks (who can access data) and log writes (durability). Many production “slowdowns” are not CPU problems — they’re
blocking problems.

Part 5 — TempDB: The Performance Battleground (10–15 min)

TempDB is a shared system database used for temporary work. Many operations become slow when they spill to TempDB,
especially under concurrency. If TempDB is misconfigured or overloaded, the whole instance can slow down.

Common operations that use TempDB

• Large sorts and hash joins (when memory isn’t enough)
• Index rebuild operations
• Temporary tables and table variables (workload dependent)
• Row versioning (depending on isolation settings)

Instructor Demo (Optional) — “Seeing Architecture Through a Query” (10–15 min)

Demo goal: show students how a simple query touches multiple architectural components (plan selection, memory usage, reads, and waiting).
In your walkthrough, focus on: logical reads, physical reads, and what the plan is forcing SQL Server to do.

-- Server start time + core info (architecture context)
SELECT
  sqlserver_start_time,
  cpu_count,
  scheduler_count,
  physical_memory_kb/1024 AS physical_memory_mb
FROM sys.dm_os_sys_info;

-- Database files (data vs log) - where the pages and log live
SELECT
  DB_NAME(database_id) AS database_name,
  name AS logical_name,
  type_desc,
  physical_name,
  (size*8)/1024 AS size_mb
FROM sys.master_files
ORDER BY database_name, type_desc;

Student Lab (15–20 min) — “Map Your SQL Server Architecture”

Lab Outcome

Students will capture a simple architecture snapshot and explain what it means in plain English.
This builds confidence and prepares students for indexing and execution plan lessons.

Tasks

• Capture server start time, CPU count, memory MB
• List your database files (data vs log)
• Answer:
  • Where does SQL Server store table/index pages?
  • Where does SQL Server store log records?
  • What is the buffer pool and why does it matter?

Lab script (safe, read-only):

-- Lab: architecture snapshot (safe)
SELECT GETDATE() AS captured_at;

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

SELECT
  DB_NAME(database_id) AS database_name,
  name AS logical_name,
  type_desc,
  physical_name,
  (size*8)/1024 AS size_mb
FROM sys.master_files
ORDER BY database_name, type_desc;

Common Beginner Mistakes (Architecture Edition)

• Assuming a slow query is always a “query problem” (often it’s I/O, memory, or blocking).
• Not understanding that SQL Server reads pages — leading to poor assumptions about scans and seeks.
• Ignoring the transaction log (log write speed impacts write-heavy workloads).
• Blaming CPU without checking if queries are waiting (locks, I/O, TempDB spills).
• Not learning the difference between work and waiting.

Class Discussion Prompt

Homework Assignment

1. Write a short “architecture explanation” (8–12 sentences):
   • Explain pages and extents in your own words
   • Explain the difference between data files and the log file
   • Explain what the buffer pool does and why it speeds up reads
2. Submit your lab outputs:
   • Server info (CPU/memory/start time)
   • Database file listing (data vs log)

Quick Knowledge Check (5 questions)

1. What is the difference between the query processor and the storage engine?
2. What is a SQL Server page and why does it matter for performance?
3. What is the buffer pool and how does it affect logical reads?
4. Why can a query be slow with low CPU usage?
5. Give one example of an operation that can spill work into TempDB.

Closing (What’s next)

In the next lesson, we will move into SQL Server Indexing. Indexing is the most common performance lever in SQL Server,
but it only makes sense after you understand how SQL Server stores and reads pages. Once the architecture is clear, indexing becomes logical,
not guesswork.