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

Index Design Workflow

1. Start from Query Shape

Map query columns by role:

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:

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:

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.

Partition Alignment Analysis

Use this reference when recommending indexes or rewrites for partitioned SQL Server tables. Partitioning improves manageability and can improve performance only when predicates, indexes, and constraints allow elimination or aligned maintenance.

Required Facts

Collect:

• Partition function name, data type, boundary values, and RANGE LEFT vs RANGE RIGHT.
• Partition scheme and destination filegroups.
• Table partitioning column and whether clustered and nonclustered indexes are aligned.
• Query predicates and parameter data types.
• Actual or estimated partition access evidence.
• Operational requirements: sliding-window loads, partition switching, compression, archival, online rebuild support.

Metadata Queries

SELECT
    pf.name AS PartitionFunction,
    pf.boundary_value_on_right,
    prv.boundary_id,
    SQL_VARIANT_PROPERTY(prv.value, 'BaseType') AS BoundaryType,
    prv.value AS BoundaryValue
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv
  ON prv.function_id = pf.function_id
ORDER BY pf.name, prv.boundary_id;

SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.index_id,
    ps.name AS PartitionScheme,
    c.name AS PartitionColumn,
    p.partition_number,
    p.rows
FROM sys.tables AS t
JOIN sys.indexes AS i
  ON i.object_id = t.object_id
JOIN sys.partitions AS p
  ON p.object_id = i.object_id
 AND p.index_id = i.index_id
JOIN sys.data_spaces AS ds
  ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps
  ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.index_columns AS ic
  ON ic.object_id = i.object_id
 AND ic.index_id = i.index_id
 AND ic.partition_ordinal = 1
LEFT JOIN sys.columns AS c
  ON c.object_id = ic.object_id
 AND c.column_id = ic.column_id
WHERE t.object_id = OBJECT_ID(N'dbo.PartitionedTable')
ORDER BY i.index_id, p.partition_number;

Alignment Checks

An index is aligned when it uses the same partition scheme and compatible partitioning column as the base table. Alignment matters for partition switching, partition-level maintenance, and predictable elimination.

Check:

• Clustered index or heap partitioned on the expected column.
• Nonclustered indexes either aligned for partition maintenance or intentionally nonaligned for targeted query needs.
• Unique indexes on partitioned tables include the partitioning column when required by SQL Server.
• Incremental statistics are configured where useful for large partitioned tables.

Predicate Safety

Safe elimination patterns usually compare the partition column directly with typed boundaries:

WHERE FactDate >= @StartDate
  AND FactDate < @EndDate;

Risky patterns:

WHERE CONVERT(date, FactDateTime) = @DateValue;
WHERE DATEDIFF(day, FactDateTime, @DateValue) = 0;
WHERE FactDateString >= '20260101';
WHERE RelatedDate >= @StartDate AND RelatedDate < @EndDate;
WHERE FactDate = @DateValue OR @DateValue IS NULL;

Risk reasons include functions on the partitioning column, data type mismatches, filtering a non-partition column, catch-all predicates, and conversions from string literals.

Elimination Proof

Use actual execution plans when possible. In plan properties, inspect actual partitions accessed or partition count. In ShowPlan XML, search for partition-related properties as described in ../../execution-plan-analysis/references/showplan-xml-checklist.md.

If plan evidence is unavailable, estimate with partition metadata and boundary logic, but label it unverified.

Index Recommendation Guidance

For partitioned tables:

• Put equality predicates before range predicates unless partition maintenance or ordered scans require a different design.
• Include the partitioning column in keys when needed for alignment, switching, uniqueness, or elimination proof.
• Avoid adding a large nonaligned index unless the performance gain justifies maintenance and switch limitations.
• Consider filtered indexes for hot partitions only when predicates are stable and query predicates imply the filter.
• For columnstore, order data by common elimination and segment-elimination columns when supported and appropriate.

Reporting Template

Partition alignment finding:
- Partition function/scheme: pf_FactDate / ps_FactDate, RANGE RIGHT, date boundaries monthly.
- Base table partition column: FactDate date.
- Query predicate: CONVERT(date, FactDateTime) = @d; not on partition column.
- Evidence: actual plan accessed 24 partitions, not 1.
- Recommendation: rewrite predicate to direct FactDate range or add verified constraint/computed column strategy; do not assume partition elimination from RelatedDate.