SQL Server Query Optimizer – Indexing Strategies

Why Index?

Indexes are the primary mechanism the optimizer uses to locate rows efficiently. A well‑designed set of indexes can reduce I/O, lower CPU consumption, and improve query response times dramatically.

When to Add an Index

• Frequent scans of large tables
• Slow SELECT queries with WHERE clauses on non‑key columns
• JOIN operations that lack matching key columns
• ORDER BY or GROUP BY causing expensive sorts

Clustered vs. Non‑Clustered

A table can have only one clustered index, which determines the physical order of rows. Non‑clustered indexes store a copy of the indexed columns plus a pointer to the data row.

Covering Indexes

A covering index contains all columns required by a query, allowing the optimizer to satisfy the request from the index alone.

CREATE NONCLUSTERED INDEX IX_Order_CustomerDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount, Status);

Filtered Indexes

Filtered indexes target a subset of rows, reducing size and maintenance cost.

CREATE NONCLUSTERED INDEX IX_ActiveOrders
ON dbo.Orders (OrderDate)
WHERE IsActive = 1;

Columnstore Indexes

Ideal for analytical workloads. They store data column‑wise and compress it heavily.

CREATE CLUSTERED COLUMNSTORE INDEX CX_Sales
ON dbo.Sales;

Maintenance & Statistics

Regular index maintenance keeps performance consistent.

• Rebuild fragmented indexes (>= 30% fragmentation).
• Reorganize less fragmented indexes (5‑30%).
• Update statistics after large data modifications.

Best Practices Checklist

• ✔ Choose the right key order (most selective first).
• ✔ Keep index width reasonable; avoid large VARCHAR columns.
• ✔ Use INCLUDE for covering columns.
• ✔ Consider filtered indexes for sparse data.
• ✔ Monitor sys.dm_db_index_usage_stats regularly.