SQL Server Query Optimization: Index Strategies

I’ve spent countless hours staring at execution plans. Indexes are the single biggest lever you have for query performance, but they’re often misunderstood. Here’s what actually works.

The Basics

An index is like a book’s index—it helps you find data without scanning every page. Without indexes, SQL Server reads every row (table scan). With the right indexes, it jumps directly to the data you need.

Clustered vs Non-Clustered

• Clustered Index: Determines the physical order of data. You get one per table. Usually the primary key.
• Non-Clustered Index: A separate structure that points to the data. You can have many.

-- Clustered index (usually created with PK)
CREATE CLUSTERED INDEX IX_Orders_OrderId ON Orders(OrderId);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId 
ON Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount);

The INCLUDE Clause

This is underused. INCLUDE adds columns to the leaf level of an index without affecting the key. It creates a “covering index” that can satisfy a query without touching the base table:

-- This query
SELECT CustomerId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 123;

-- Is fully covered by this index
CREATE INDEX IX_Orders_CustomerId 
ON Orders(CustomerId) 
INCLUDE (OrderDate, TotalAmount);

Index Column Order Matters

For composite indexes, the column order is critical. The index can only be used efficiently if your query filters on the leftmost columns:

-- Index on (CustomerId, OrderDate)
CREATE INDEX IX_Orders_Customer_Date ON Orders(CustomerId, OrderDate);

-- ✓ Uses index: WHERE CustomerId = 123
-- ✓ Uses index: WHERE CustomerId = 123 AND OrderDate > '2019-01-01'
-- ✗ Scan: WHERE OrderDate > '2019-01-01' (no CustomerId filter)

Index Maintenance

Indexes aren’t free:

• They slow down INSERTs, UPDATEs, and DELETEs
• They consume disk space
• They need regular maintenance (rebuild/reorganize)

-- Check fragmentation
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
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;

Common Mistakes

• Too many indexes: Each one slows writes
• Duplicate indexes: Check for overlapping indexes
• Missing INCLUDE columns: Causes key lookups
• Wrong column order: Index can’t be used efficiently

References

• SQL Server Index Architecture
• Brent Ozar – Query Plans