JosiahSiegel/index-strategies

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

| Type | Best for | Cautions | |---|---|---| | Clustered | Primary table order, range access, narrow stable key | Expensive to change; clustering key is included in nonclustered indexes. | | Nonclustered | Query-specific seeks, joins, ordering | Adds write and storage overhead. | | Covering | Avoiding repeated key lookups | INCLUDE bloat can hurt cache and writes. | | Filtered | Stable, selective subsets | Query predicate must imply filter; parameters can block use. | | Columnstore | Analytics, scans, aggregations, compression | Small updates and singleton lookups can suffer. | | Unique | Enforcing business rules and optimizer proof | Must match real semantics. |

Index Design Workflow

1. Start from Query Shape

Map query columns by role:

| Role | Index design implication | |---|---| | Equality predicates | Usually first key columns, ordered by selectivity and workload reuse. | | Join keys | Useful as seek keys and join order support. | | Range predicates | Usually after equality keys; only one range can be deeply seekable. | | ORDER BY / GROUP BY | Consider key order to avoid sorts or stream aggregates. | | Selected columns | INCLUDE only when lookup cost justifies storage/write cost. |

Example:

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

2. Validate Data Types and SARGability

An index cannot fully help if predicates are non-SARGable or types mismatch. Confirm parameter, temp-table, and source column types before adding indexes. Fix CONVERT_IMPLICIT on join/filter columns first when possible.

3. Compare Existing Indexes

Before adding a new index:

• Check whether an existing index can be extended safely.
• Merge overlapping missing-index requests.
• Identify duplicates with same leading keys.
• Evaluate lookup count and selected columns before adding INCLUDE columns.
• Consider whether filtered or narrower indexes solve the hot path with less write cost.

4. Account for Change Constraints

Classify the recommendation:

| Constraint | Safer response | |---|---| | Cannot add indexes | Query rewrite, stats, hints, temp staging, or Query Store hints. | | Cannot change huge-table indexes | Add narrow filtered index, indexed staging table, or reduce rows before touching table. | | Online index not allowed | Plan maintenance window and blocking risk; consider resumable where supported. | | Partition switching required | Prefer aligned indexes; avoid nonaligned indexes unless explicitly accepted. | | Heavy write workload | Minimize key width and INCLUDE list; prove read benefit. | | Storage constrained | Consolidate duplicates and avoid speculative covering indexes. |

Clustered Index Guidelines

Ideal clustered keys are narrow, unique, static, and usually ever-increasing for OLTP insert patterns.

CREATE CLUSTERED INDEX CIX_Orders ON dbo.Orders(OrderID);

Avoid wide composite clustered keys unless they match a deliberate access pattern and write trade-off. Random GUID clustering can cause page splits; consider NEWSEQUENTIALID(), a surrogate key, or fill-factor strategy when appropriate.

Covering Indexes

Covering indexes avoid lookups when the lookup cost is significant.

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

Use INCLUDE columns for output-only columns, not for filtering or ordering. Keep INCLUDE lists minimal; wide includes can be worse than occasional lookups.

Filtered Indexes

Filtered indexes are strong for stable subsets:

CREATE NONCLUSTERED INDEX IX_Orders_Open_ByCustomer
ON dbo.Orders(CustomerID, OrderDate)
WHERE Status = 'Open';

Requirements:

• Query predicate must imply the filter.
• Parameterized queries may need recompilation or literal-specific dynamic SQL to match reliably.
• Filter column may need to be included when it is not obvious to the optimizer.
• Validate parameter sniffing risk and plan cache behavior.

Columnstore Indexes

Use columnstore for analytic scans, aggregations, and compression.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders_Analysis
ON dbo.Orders(OrderDate, ProductID, Quantity, Amount)
WHERE Status = 'Completed';

Best practices:

1. Load batches of at least 102,400 rows where possible.
2. Order data by common elimination columns for better segment elimination.
3. Use REORGANIZE to compress delta rowgroups and merge rowgroups.
4. Avoid high-frequency singleton updates on pure columnstore designs.
5. Pair with partitioning for manageability when large fact tables require sliding windows.

Partition Alignment Checks

For partitioned tables, index design must account for elimination and maintenance.

Verify:

• Partition function/scheme, boundary type, and RANGE LEFT vs RANGE RIGHT.
• Base partitioning column vs query predicate column.
• Whether each important nonclustered index is aligned or intentionally nonaligned.
• Whether unique indexes include the partitioning column when required.
• Actual partition elimination from the execution plan.
• Unsafe predicates: functions on partition column, mismatched types, filtering a related non-partition date, OR catch-all predicates, or remote sources.

Do not claim partition elimination from a date filter unless it targets the partitioning column in a compatible, SARGable form or a trusted constraint proves equivalence. See references/partition-alignment-analysis.md.

Maintenance and Operations

Fragmentation rules are workload-dependent, but a common starting point is:

| Fragmentation | Typical action | |---|---| | Less than 5% | No action. | | 5-30% | Reorganize if page count and workload justify it. | | More than 30% | Rebuild if maintenance window, edition, and blocking allow it. |

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

Update statistics after meaningful index changes when auto-created stats or sampled stats are insufficient:

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

Useful Diagnostics

Index usage since last restart or database attach:

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    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 AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
  ON i.object_id = ius.object_id
 AND i.index_id = ius.index_id
 AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY COALESCE(ius.user_seeks, 0) + COALESCE(ius.user_scans, 0) DESC;

Missing-index candidates:

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 AS mig
JOIN sys.dm_db_missing_index_group_stats AS migs
  ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
  ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

Recommendation Format

Provide:

1. Evidence: plan operator, reads, lookups, estimates, missing-index request, or workload metric.
2. DDL: proposed index with schema-qualified name and minimal keys/includes.
3. Why: access path, ordering, coverage, or elimination benefit.
4. Cost: storage, writes, blocking, maintenance, partition implications.
5. Validation: before/after plan, logical reads, CPU, elapsed time, write impact.
6. Rollback or alternative: especially for huge tables.

References

• ../_shared/optimization-intake.md - pre-optimization intake.
• ../_shared/assumption-tracker.md - assumption status tracking.
• references/partition-alignment-analysis.md - partition scheme and elimination analysis.