Index Strategies

Comprehensive guide to SQL Server index design and optimization.

Quick Reference

Index Types

Type Description Best For

Clustered Table data order Primary access path, range scans

Nonclustered Separate structure Specific query patterns

Columnstore Column-based storage Analytics, aggregations

Filtered Partial index Well-known subsets

Covering All columns needed Avoiding key lookups

Clustered Index Guidelines

Ideal Clustered Key:

• Narrow (small data type)

• Unique or mostly unique

• Ever-increasing (identity, sequential GUID)

• Static (rarely updated)

-- Good: Identity column CREATE CLUSTERED INDEX CIX_Orders ON Orders(OrderID);

-- Good: Sequential GUID CREATE TABLE Orders ( OrderID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED );

-- Avoid: Wide composite keys, frequently updated columns, GUIDs (NEWID)

Nonclustered Index Design

-- Basic index CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID);

-- Covering index (avoids key lookup) CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover ON Orders(CustomerID) INCLUDE (OrderDate, TotalAmount, Status);

-- Filtered index (partial) CREATE NONCLUSTERED INDEX IX_Orders_Active ON Orders(CustomerID, OrderDate) WHERE Status = 'Active';

-- Descending order CREATE NONCLUSTERED INDEX IX_Orders_DateDesc ON Orders(OrderDate DESC, OrderID DESC);

Index Selection Guide

By Query Pattern

Pattern Recommended Index

WHERE Col = value

Nonclustered on Col

WHERE Col = v1 AND Col2 = v2

Nonclustered on (Col, Col2)

WHERE Col = v ORDER BY Col2

Nonclustered on (Col, Col2)

WHERE Col BETWEEN x AND y

Col as leftmost key

SELECT * WHERE Col = v

Clustered or covering NC

Large aggregations Columnstore

Specific subset queries Filtered index

Column Order in Composite Keys

-- Order matters! Left-to-right matching CREATE INDEX IX_Example ON Table(A, B, C);

-- These queries CAN use the index: WHERE A = 1 WHERE A = 1 AND B = 2 WHERE A = 1 AND B = 2 AND C = 3 WHERE A = 1 AND B > 5 ORDER BY B

-- These queries CANNOT use index seek: WHERE B = 2 -- A not specified WHERE B = 2 AND C = 3 -- A not specified WHERE A = 1 AND C = 3 -- B skipped (partial match only)

Columnstore Indexes

Clustered Columnstore

-- Best for data warehousing CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON FactSales;

-- Ordered columnstore (SQL 2022+) CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON FactSales ORDER (DateKey, ProductKey);

Nonclustered Columnstore

-- Hybrid OLTP/OLAP CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis ON Orders(OrderDate, ProductID, Quantity, Amount) WHERE Status = 'Completed';

Columnstore Best Practices

• Load batches >= 102,400 rows - Creates compressed segments

• Order data by filtered columns - Better segment elimination

• Use REORGANIZE, not REBUILD - More efficient maintenance

• Avoid frequent small updates - Causes deltastore fragmentation

• Partition by date - Enables partition elimination

-- Maintenance ALTER INDEX CCI_FactSales ON FactSales REORGANIZE;

-- Check fragmentation SELECT object_name(object_id) AS TableName, index_id, avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');

Filtered Indexes

-- Index active orders only CREATE NONCLUSTERED INDEX IX_Orders_Active ON Orders(CustomerID, OrderDate) WHERE Status = 'Active';

-- Index non-NULL values CREATE UNIQUE INDEX IX_Users_Email ON Users(Email) WHERE Email IS NOT NULL;

-- Constraints: -- - Cannot use variable in filter -- - Query WHERE must match or be subset of filter WHERE -- - May cause parameter sniffing issues

Covering Indexes

-- Eliminate key lookups -- Original: Index on CustomerID, query selects OrderDate, Amount -- Execution plan shows Key Lookup

-- Solution: Covering index CREATE INDEX IX_Orders_CustomerID_Cover ON Orders(CustomerID) INCLUDE (OrderDate, Amount, Status);

-- INCLUDE columns: -- - Not in key (not sorted) -- - Stored at leaf level only -- - Don't contribute to 900-byte key limit -- - Perfect for frequently selected columns

Index Maintenance

Fragmentation Guidelines

Fragmentation % Action

< 5% None needed

5-30% REORGANIZE

30% REBUILD

-- Reorganize (online, minimal locking) ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;

-- Rebuild (offline by default, more thorough) ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;

-- Online rebuild (Enterprise Edition) ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (ONLINE = ON);

-- Resumable rebuild (SQL 2017+) ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

-- Resume interrupted rebuild ALTER INDEX IX_Orders_CustomerID ON Orders RESUME;

Statistics Update

-- Update after index changes UPDATE STATISTICS Orders;

-- Full scan for accurate stats UPDATE STATISTICS Orders WITH FULLSCAN;

-- Check last update SELECT OBJECT_NAME(object_id) AS TableName, name AS StatsName, STATS_DATE(object_id, stats_id) AS LastUpdated FROM sys.stats WHERE object_id = OBJECT_ID('Orders');

Performance Monitoring

Index Usage Stats

SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 ORDER BY ius.user_seeks + ius.user_scans DESC;

Missing Index Recommendations

SELECT migs.avg_user_impact AS ImpactPercent, mid.statement AS TableName, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_groups mig JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle ORDER BY migs.avg_user_impact DESC;