Index Strategies

Comprehensive guide to SQL Server index design and optimization. Index advice must be workload-aware and constraint-aware: the best index for one query can be harmful for writes, storage, maintenance, partition switching, or other critical queries.

Mandatory Intake

Before recommending index DDL, collect or mark unknown:

• SQL Server version, edition, compatibility level, and Azure SQL tier.
• Query text, representative parameters, actual plan, row counts, and predicate selectivity.
• Existing clustered, nonclustered, filtered, columnstore, unique, disabled, duplicate, and overlapping indexes.
• Table DDL, constraints, data types, computed columns, compression, partitioning, and statistics.
• Write workload: insert/update/delete frequency, bulk loads, maintenance window, storage budget.
• Change constraints: can new indexes be added, can huge-table indexes be changed, is online/resumable index creation allowed, is partition switching required, are staging tables allowed, and who approves write overhead.

Use ../_shared/optimization-intake.md and ../_shared/assumption-tracker.md. Do not present missing-index DMV output as final design without existing-index and workload review.

Quick Reference