SQL Server Indexing

Online Class • Next Lesson

SQL Server Indexing (Performance Tuning Fundamentals)

Indexing is the most powerful and most misunderstood performance tool in SQL Server.
In this lesson, you will learn what indexes really are, how SQL Server uses them (seek vs scan),
how to design indexes that speed up reads without destroying writes, and how to avoid the most common
index mistakes that cause bloat, fragmentation, and slow insert/update performance.

Class Overview (What students will learn today)

An index is a data structure that helps SQL Server find rows faster, reduce page reads, and improve query response time.
But indexes are not “free.” Every index must be maintained on writes (INSERT/UPDATE/DELETE), and too many indexes can slow down
the system. In performance tuning, the goal is not “more indexes”—the goal is the right indexes.

Big Idea

Indexing is about controlling how many pages SQL Server must read to answer a question.
Great indexes reduce logical reads, improve seek access, and avoid expensive lookups and sorts.
Bad indexes create bloat, slow writes, and still don’t fix slow queries.

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

Explain the difference between heap tables and clustered tables.
Describe how clustered and nonclustered indexes work.
Understand seek vs scan, and why reducing reads improves performance.
Design indexes using key columns and INCLUDE columns for “covering” queries.
Recognize common index problems: duplicate indexes, unused indexes, high-maintenance indexes, and over-indexing.
Use a practical tuning process to decide when to add, when to adjust, and when to remove indexes.

Recommended Class Duration

75–120 minutes (concepts + demo + index design lab)

Prerequisites (Minimal)

Basic SQL (SELECT, WHERE, JOIN)
Understanding of pages/reads from the Architecture lesson is helpful

Part 1 — Why Indexes Matter (10–15 min)

SQL Server performance is often a “reading problem.” Slow queries usually read too many pages.
Indexes reduce the pages SQL Server must touch, which reduces CPU and I/O and improves concurrency.

Two query paths: Scan vs Seek

Index/Table Scan: SQL Server reads many pages to find matching rows (often expensive).
Index Seek: SQL Server jumps to the relevant key range and reads far fewer pages (often fast).

Key teaching point: A scan is not always “bad,” and a seek is not always “good.”
The goal is not to force seeks—it’s to minimize total work and reads for the real workload.

Part 2 — Heaps vs Clustered Tables (10–15 min)

In SQL Server, every table is either a heap (no clustered index) or a clustered table
(has a clustered index). This choice affects how data is physically organized and how nonclustered indexes find rows.

Heap (No Clustered Index)

Data is not stored in a logical order
Nonclustered indexes point to rows using RID lookups
Can perform well for some bulk load patterns, but often causes lookups and fragmentation challenges

Clustered Index (Defines the Table Order)

Data rows are stored in the order of the clustered key
Nonclustered indexes point to rows using the clustered key
The clustered key choice impacts every nonclustered index and many query patterns

Key teaching point: The clustered index is not “just another index.”
It defines how the entire table is organized and affects seek efficiency, range queries, and lookup cost.

Part 3 — Nonclustered Indexes, Lookups, and Covering (15–20 min)

A nonclustered index is a separate structure that stores the index key and a pointer back to the base table (heap or clustered).
If the query needs columns not stored in the index, SQL Server performs a lookup to fetch missing columns.
Too many lookups can become extremely expensive under concurrency.

Key columns vs INCLUDE columns

Key columns: Used for filtering, joining, sorting, and seeking.
INCLUDE columns: Stored only to satisfy SELECT output (helps make the index “cover” the query).

What is a “Covering Index”?

A covering index is an index that contains all columns needed by the query (filter + join + select output),
allowing SQL Server to return results without extra lookups.
Covering indexes often reduce reads dramatically and can stabilize performance.

Part 4 — Index Selectivity, Column Order, and Composite Indexes (15–20 min)

Index design is not random. SQL Server can only “seek” effectively when the index key order supports the query predicates.
The order of columns in a composite index matters because SQL Server typically navigates using the leading key columns first.

Practical Rules Students Must Know

Put the most selective filters early (often, but not always—depends on workload).
Equality predicates generally benefit more from leading keys than range predicates.
Index keys should support the query’s WHERE and JOIN conditions first.
If the query needs an ORDER BY, align keys to reduce sorting when appropriate.
Use INCLUDE columns to avoid lookups without bloating the key.

Key teaching point: The “best” index is the one that improves the real workload and costs the least to maintain.
Good tuning balances read speed and write cost.

Part 5 — Index Maintenance: Stats, Fragmentation, and Rebuild/Reorganize (10–15 min)

Index performance depends on more than the index definition.
Statistics influence plan choices. Fragmentation can increase I/O for some workloads. Maintenance must be based on evidence,
not habit. In modern SQL Server environments, statistics quality is often more important than fragmentation.

What students should understand

Statistics: Helps SQL Server estimate row counts (cardinality) and pick a plan.
Fragmentation: Can increase page reads and reduce range-scan efficiency (context matters).
Rebuild vs Reorganize: Different impact, logging, and locking behavior.

Key teaching point: Never run aggressive index rebuilds “because it’s Sunday.”
Maintenance should match workload, SLAs, and real evidence.

Instructor Demo (Optional) — “From Scan to Seek (and Fewer Reads)” (15–25 min)

Demo goal: show students how one targeted index can reduce logical reads and improve query duration.
Focus on proof: compare “before” vs “after” using reads, CPU time, and the execution plan.

Evidence Script (Safe)

Use these settings to show IO and timing output for demonstration queries:

-- Demo: show the proof (reads + CPU time)
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run your slow query here (before index)
-- Observe: logical reads, CPU time, duration, and plan operators

-- Create your targeted index here (in demo database)
-- Re-run query (after index) and compare results

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Student Lab (20–30 min) — “Design a Covering Index”

Lab Outcome

Students will analyze a query pattern, propose an index (key + INCLUDE), and explain tradeoffs (read gain vs write cost).
This is the exact skill expected in DBA interviews and real production troubleshooting.

Tasks

Pick one slow query (or a provided lab query).
Identify the filter/join columns and the output columns.
Propose an index:
- Key columns: for WHERE/JOIN/ORDER BY
- INCLUDE columns: for SELECT output
Explain why your index helps and what the maintenance cost might be.

Student Submission Template

Query pattern: (paste or describe)
Filters/JOINs: (list columns)
SELECT output columns: (list columns)
Proposed index: (keys + INCLUDE)
Expected improvement: (fewer reads, fewer lookups, less sorting)
Tradeoffs: (write overhead, storage, maintenance)

Common Beginner Mistakes (Indexing Edition)

Creating indexes “just to remove scans” without checking total reads and selectivity.
Over-indexing (too many indexes) which slows down writes and bloats storage.
Putting too many columns in the index key instead of using INCLUDE.
Ignoring the clustered index choice (it affects lookups and all nonclustered indexes).
Not measuring impact (no before/after proof with IO/time metrics).

Class Discussion Prompt

Prompt:
“What is worse in production: a missing index that causes high reads, or too many indexes that slow down writes?
Explain your answer with one real-world example (reporting system, e-commerce orders, banking transactions, etc.).”

Homework Assignment

Find one query in your environment that performs a scan and write a short explanation of why it scans.
Propose one index (keys + INCLUDE) that could improve it and explain the tradeoffs.
Optional: run SET STATISTICS IO/TIME and report before/after metrics if you can test safely.

Quick Knowledge Check (5 questions)

What is the difference between a clustered and a nonclustered index?
What does it mean for an index to “cover” a query?
Why can key column order in a composite index matter?
Why are indexes not “free” for INSERT/UPDATE/DELETE workloads?
What is one safe way to prove an index improved performance?

Closing (What’s next)

In the next lesson, we move into Query Optimization Techniques. Indexes are critical, but they are only one part of tuning.
You will learn how SQL Server chooses execution plans, how to read plans confidently, and how to rewrite queries to reduce work,
eliminate unnecessary reads, and prevent performance regressions.