27. Indexes & Performance Tuning -- Speeding Up Queries

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• What indexes do (speed up searches)
• Clustered vs nonclustered indexes
• CREATE INDEX syntax
• Composite indexes (multiple columns)
• Indexes on foreign keys
• Execution plans (estimate query cost)
• Identify missing indexes
• Index fragmentation
• Common indexing mistakes
• Best practices

Queries scan tables. Indexes speed up searches. This article teaches creating indexes and reading execution plans.

Why Indexes Matter for Backend Developers

Performance impacts user experience:

Without index:
WHERE StudentId = 5 -> Scans all 100K rows -> Slow

With index:
WHERE StudentId = 5 -> Index lookup -> Fast (microseconds)
    v
Queries run 100x faster
APIs respond instantly

Indexes are table of contents. Dramatically speed searches.

Index Concepts

Clustered Index: Physical row order. One per table (usually PRIMARY KEY).

StudentId (clustered): Rows sorted by StudentId physically

Nonclustered Index: Fast lookup pointer. Many per table.

Name (nonclustered): Index points to rows, keeps them unsorted physically

Example 1: Create Nonclustered Index

Speed up student lookup by name:

CREATE NONCLUSTERED INDEX IX_Student_Name 
ON Student (Name);

-- Query now fast:
SELECT * FROM Student WHERE Name = 'Ravi Kumar';

Before: Table scan (all rows). After: Index seek (direct to row).

Naming convention: IX_TableName_Columns

Example 2: Composite Index (multiple columns)

Index on class and marks:

CREATE NONCLUSTERED INDEX IX_ExamResult_StudentId_MarksObtained 
ON ExamResult (StudentId, MarksObtained DESC);

-- Fast query:
SELECT * FROM ExamResult 
WHERE StudentId = 5 
ORDER BY MarksObtained DESC;

Composite index: (StudentId, MarksObtained). First column filters, second orders.

Example 3: Foreign Key Index

Index on ForeignKey (required for JOINs):

-- Foreign key should always be indexed
CREATE NONCLUSTERED INDEX IX_Subject_TeacherId 
ON Subject (TeacherId);

-- Fast JOIN:
SELECT s.Name, t.Name
FROM Subject s
INNER JOIN Teacher t ON s.TeacherId = t.TeacherId;

Rule: Index all foreign keys. JOINs use them constantly.

Example 4: Execution Plan (estimate cost)

Check query performance:

-- Enable actual plan
SET STATISTICS IO ON;

SELECT s.Name, r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE s.ClassName = '10-A';

-- Check output: Logical Reads (lower = faster)
-- Logical Reads: 2 (good)

Cost indicators:

• Low: <1 (instant)
• Medium: 1-10 (fast)
• High: >10 (slow, needs index)

Look for: Table Scan (slow) vs Index Seek (fast).

Example 5: Missing Index Analysis

Find missing indexes in SQL Server:

-- Query slow without index:
SELECT * FROM ExamResult WHERE MarksObtained > 80;

-- SQL Server suggests missing index:
-- Missing Index: ON ExamResult (MarksObtained)

-- Create it:
CREATE NONCLUSTERED INDEX IX_ExamResult_MarksObtained 
ON ExamResult (MarksObtained);

-- Re-run query: Now fast (index seek)

Check execution plan for "Missing Index" warning.

Example 6: Covering Index (INCLUDE columns)

Include columns to avoid lookup:

-- Without covering index:
SELECT Name, MarksObtained
FROM ExamResult er
WHERE er.StudentId = 5;
-- Index has StudentId, but needs to look up Name (extra I/O)

-- With covering index:
CREATE NONCLUSTERED INDEX IX_ExamResult_StudentId 
ON ExamResult (StudentId)
INCLUDE (MarksObtained);
-- Index has both StudentId and MarksObtained (no lookup needed)

-- Query now faster: Index covers it

Covering index: INCLUDE other columns needed by query. Eliminates extra lookups.

Example 7: Index Fragmentation

Check and rebuild fragmented index:

-- Check fragmentation:
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY Fragmentation DESC;

-- Result (fragmented):
TableName | IndexName              | Fragmentation
Student   | IX_Student_Name        | 25%

Fragmentation > 10%: Rebuild.

-- Rebuild fragmented index:
ALTER INDEX IX_Student_Name ON Student REBUILD;

-- Check again: Fragmentation now ~0%

Example 8: Drop Unused Index

Remove unnecessary indexes:

-- Find unused indexes:
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS TimesUsed
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND (s.user_seeks + s.user_scans + s.user_lookups = 0 OR s.user_seeks IS NULL)
ORDER BY TimesUsed;

-- Drop unused:
DROP INDEX IX_OldUnusedIndex ON TableName;

Remove indexes never used. Save space, improve INSERT/UPDATE.

---

Common Indexing Mistakes

Mistake 1: Index every column

Wrong:

CREATE INDEX ON Student (StudentId);
CREATE INDEX ON Student (Name);
CREATE INDEX ON Student (ClassName);
CREATE INDEX ON Student (RollNumber);
-- Too many indexes: slow INSERT/UPDATE, wastes space

Result: 4 indexes x 100K rows = bloat.

Fix: Index strategically:

-- Only frequently searched columns
CREATE INDEX ON Student (StudentId);  -- JOIN, WHERE
CREATE INDEX ON Student (ClassName);  -- Filter, GROUP BY
-- Name, RollNumber: less frequently searched

Mistake 2: Index low-cardinality columns

Wrong:

CREATE INDEX ON Student (Gender);
-- Only 2 values (M/F): Index scan as fast as table scan

Result: Wasted space.

Fix: Index high-cardinality (many unique values):

CREATE INDEX ON Student (RollNumber);  -- Unique per student
CREATE INDEX ON ExamResult (MarksObtained);  -- Many values

Mistake 3: Not indexing foreign keys

Wrong:

CREATE TABLE Subject (
    SubjectId INT PRIMARY KEY,
    TeacherId INT,  -- No index!
    Name VARCHAR(50)
);

-- JOIN slow:
SELECT s.Name, t.Name FROM Subject s
JOIN Teacher t ON s.TeacherId = t.TeacherId;

Result: Table scan on every JOIN.

Fix: Index foreign keys:

CREATE INDEX ON Subject (TeacherId);

Mistake 4: Composite index with columns in wrong order

Wrong:

-- Query: WHERE MarksObtained > 80 AND StudentId = 5
CREATE INDEX ON ExamResult (MarksObtained, StudentId);
-- Index ordered wrong: First scans all Marks > 80, then filters StudentId (slow)

Fix: Filter column first:

CREATE INDEX ON ExamResult (StudentId, MarksObtained);
-- First narrows to StudentId = 5, then scans Marks (fast)

Most selective column first.

Mistake 5: Ignoring fragmentation

Wrong:

-- Index fragmented 80%, but not maintained
SELECT * FROM Student WHERE ClassName = '10-A';
-- Reads 80% extra pages (fragmented)

Result: Slow queries over time.

Fix: Rebuild periodically:

ALTER INDEX IX_Student_ClassName ON Student REBUILD;

Or defragment if light fragmentation:

ALTER INDEX IX_Student_ClassName ON Student REORGANIZE;

---

Best Practices for Indexes

1. Index columns in WHERE clauses -- Speed up filters
2. Index columns in JOINs (FK) -- Required for performance
3. Index columns in ORDER BY -- Speed up sorts
4. High cardinality first -- Unique columns
5. Filter columns before sort -- Composite order matters
6. Avoid too many indexes -- Slows INSERT/UPDATE
7. Monitor fragmentation -- Rebuild when > 10%
8. Drop unused indexes -- Free space, improve writes

---

🎯 Q1: What's the difference between clustered and nonclustered index?

Clustered: Physical row order (one per table).

Primary Key is usually clustered
Rows sorted by StudentId physically

Nonclustered: Pointer to rows (many per table).

Speed up lookups without changing physical order
Multiple indexes on one table

Clustered: Changes physical order. Nonclustered: Pointer only.

🎯 Q2: When should I create a composite index?

When query filters on multiple columns:

SELECT * FROM ExamResult 
WHERE StudentId = 5 AND MarksObtained > 80;

-- Composite index:
CREATE INDEX ON ExamResult (StudentId, MarksObtained);

Order matters: Most selective first:

-- If StudentId = 5 returns 10 rows
-- MarksObtained > 80 returns 50K rows
-- Index (StudentId, MarksObtained): Filter StudentId first (10 rows), then Marks

Filter first, sort second.

🎯 Q3: Should I index all foreign keys?

Yes, almost always:

-- Foreign key
CREATE TABLE Subject (SubjectId INT, TeacherId INT FOREIGN KEY REFERENCES Teacher(TeacherId));

-- Index:
CREATE INDEX ON Subject (TeacherId);

JOINs use foreign keys constantly. Index them.

Exception: Rarely-used relationships can skip.

🎯 Q4: How do I read an execution plan?

Look for:

• Index Seek: Fast (good)
• Table Scan: Slow (needs index)
• Estimated vs Actual: If differ greatly, stats stale (UPDATE STATISTICS)

Cost: Higher = slower.

If slow: Look for scans, add indexes.

🎯 Q5: How do I know if an index helps?

Compare execution plan before/after:

-- Before (no index):
SELECT * FROM ExamResult WHERE MarksObtained > 80;
-- Table Scan (cost 100)

-- After (index created):
CREATE INDEX ON ExamResult (MarksObtained);
SELECT * FROM ExamResult WHERE MarksObtained > 80;
-- Index Seek (cost 5)

If cost drops significantly: Index helps.

🎯 Q6: When should I rebuild vs reorganize an index?

Fragmentation:

• 0-10%: No action
• 10-30%: REORGANIZE (online, faster)
• 30-100%: REBUILD (offline, thorough)

-- Light fragmentation:
ALTER INDEX IX_Name ON TableName REORGANIZE;

-- Heavy fragmentation:
ALTER INDEX IX_Name ON TableName REBUILD;

Check fragmentation regularly.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Indexes and performance tuning in SQL Server. Try these prompts:

• "Show how to create index: speed up WHERE StudentId = 5"
• "What's a composite index? When should I use one?"
• "How do I read execution plan to identify slow queries?"
• "When should I create a covering index (INCLUDE)?"
• "Quiz me: ask 5 questions about indexes, fragmentation, execution plans"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

28. Transactions & Concurrency Control -- Data Integrity ->