klod68/index-design-strategy

---

name: index-design-strategy description: "Use when designing indexes for new tables, diagnosing slow queries that are not using indexes efficiently, reviewing index fragmentation and maintenance, or when the current indexing strategy results in key lookups, table scans, or missing index warnings. Covers clustered index key selection (narrow, unique, ever-increasing), non-clustered index design for query patterns, covering indexes with INCLUDE columns, filtered indexes for subset queries, composite index column ordering, DMV-based monitoring for missing and unused indexes, and rebuild vs reorganize maintenance thresholds. Domain: Database, Performance. Level: Intermediate. Tags: index, sql-server, covering-index, filtered-index, performance, dmv, maintenance."

Index Design Strategy

Problem

The KB's only indexing guidance is "add indexes for every foreign key column" — a single bullet in ef-core.md. This is dangerously incomplete. Missing indexes cause table scans on million-row tables; redundant indexes waste storage and slow writes; wrong clustered key choices fragment the entire table. Indexing is the single highest-impact performance lever in any SQL Server database.

Clustered Index Key Selection

The clustered index determines the physical order of data on disk. Choose carefully — you get exactly one per table.

The NUSE rule — a good clustered key is:

• Narrow: fewer bytes = more rows per page = fewer I/O operations
• Unique: avoids the 4-byte uniquifier SQL Server adds to non-unique clustered keys
• Static: never updated — updates to the clustered key cause physical row movement
• Ever-increasing: minimizes page splits on INSERT (IDENTITY, SEQUENCE, NEWSEQUENTIALID())

Decision matrix:

| Key Type | Narrow | Unique | Static | Ever-increasing | Verdict | |---|---|---|---|---|---| | INT IDENTITY | ✅ 4 bytes | ✅ | ✅ | ✅ | Best default | | BIGINT IDENTITY | ✅ 8 bytes | ✅ | ✅ | ✅ | Good for >2B rows | | UNIQUEIDENTIFIER (NEWID()) | ❌ 16 bytes | ✅ | ✅ | ❌ Random | Worst — page splits, fragmentation | | UNIQUEIDENTIFIER (NEWSEQUENTIALID()) | ❌ 16 bytes | ✅ | ✅ | ✅ | Acceptable if GUID required | | VARCHAR(50) natural key | ❌ Variable | Maybe | ❌ May change | ❌ Random | Avoid — use as non-clustered UNIQUE | | Composite (A, B, C) | ❌ Wide | Depends | Depends | ❌ Usually not | Rarely appropriate for clustered |

Show a ❌ bad vs ✅ good example:

-- ❌ Bad: GUID as clustered key — 16 bytes, random, causes page splits
CREATE TABLE dbo.OrderGuidBad (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWID(),
    Title NVARCHAR(200)
);

-- ✅ Good: INT IDENTITY clustered + GUID as non-clustered unique
CREATE TABLE dbo.OrderGood (
    OrderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    PublicId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    Title NVARCHAR(200),
    INDEX IX_Order_PublicId UNIQUE NONCLUSTERED (PublicId)
);

Covering Indexes

A covering index contains ALL columns needed by a query — the optimizer never touches the base table (no key lookup).

-- Query: frequently executed
SELECT OrderId, Title, Status FROM dbo.[Order] WHERE CustomerId = @CustomerId AND Status = 'Active';

-- ❌ Non-covering: index on CustomerId alone → key lookup for Title and Status
CREATE NONCLUSTERED INDEX IX_Order_CustomerId ON dbo.[Order] (CustomerId);

-- ✅ Covering: INCLUDE columns satisfy the SELECT list without key lookup
CREATE NONCLUSTERED INDEX IX_Order_CustomerId_Status ON dbo.[Order] (CustomerId, Status)
    INCLUDE (Title);

Column placement rules:

• Key columns: columns in WHERE, JOIN, ORDER BY (these are searchable and sorted)
• INCLUDE columns: columns only in SELECT (stored in leaf level, not sorted)
• Key columns count toward the 900-byte / 16-column index key limit; INCLUDE columns do not

Filtered Indexes

Cover only a subset of rows — smaller, faster to maintain, ideal for high-selectivity queries.

-- Only 5% of orders are Active, but 90% of queries filter on Active
CREATE NONCLUSTERED INDEX IX_Order_Active
    ON dbo.[Order] (CustomerId, CreatedAt)
    INCLUDE (Title, Status)
    WHERE Status = 'Active';

Requirements and limitations:

• Filter predicate must use simple comparisons (=, <>, <, >, IS NULL, IS NOT NULL)
• Cannot use BETWEEN, IN, OR, LIKE, or computed columns
• Query predicate must match or be more restrictive than the filter for the optimizer to use it
• Parameterized queries may not match: use OPTION (RECOMPILE) or literal values

Composite Index Column Ordering

Column order in composite indexes matters enormously.

The rule: Most selective column first → range/inequality column last → included columns in INCLUDE.

-- Query: WHERE Status = 'Active' AND CreatedAt >= @StartDate ORDER BY CreatedAt
-- Status has 5 distinct values (high selectivity for equality)
-- CreatedAt is a range predicate

-- ✅ Correct order: equality predicate first, range predicate last
CREATE NONCLUSTERED INDEX IX_Order_Status_CreatedAt
    ON dbo.[Order] (Status, CreatedAt)
    INCLUDE (Title, CustomerId);

-- ❌ Wrong order: range predicate first limits usefulness for Status filter
CREATE NONCLUSTERED INDEX IX_Order_CreatedAt_Status
    ON dbo.[Order] (CreatedAt, Status)
    INCLUDE (Title, CustomerId);

Monitoring with DMVs

Provide these essential DMV queries:

Missing indexes (what SQL Server wishes it had):

SELECT
    OBJECT_NAME(mid.object_id) AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact,
    migs.user_seeks,
    migs.last_user_seek
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact * migs.user_seeks DESC;

Unused indexes (wasting write overhead):

SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates  -- writes to maintain this index
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 AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type_desc = 'NONCLUSTERED'
    AND ISNULL(ius.user_seeks, 0) + ISNULL(ius.user_scans, 0) + ISNULL(ius.user_lookups, 0) = 0
ORDER BY ius.user_updates DESC;

Duplicate / overlapping indexes:

;WITH IndexColumns AS (
    SELECT
        i.object_id, i.index_id, i.name,
        STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.is_included_column = 0
    GROUP BY i.object_id, i.index_id, i.name
)
SELECT
    OBJECT_NAME(a.object_id) AS TableName,
    a.name AS Index1, a.KeyColumns,
    b.name AS Index2, b.KeyColumns AS OverlappingKeys
FROM IndexColumns a
JOIN IndexColumns b ON a.object_id = b.object_id AND a.index_id < b.index_id
WHERE a.KeyColumns = b.KeyColumns OR b.KeyColumns LIKE a.KeyColumns + ',%';

Index Maintenance

| Fragmentation % | Action | Command | |---|---|---| | < 5% | Do nothing | — | | 5–30% | Reorganize (online, lightweight) | ALTER INDEX IX_Name ON dbo.Table REORGANIZE; | | > 30% | Rebuild (heavier, can be online in Enterprise) | ALTER INDEX IX_Name ON dbo.Table REBUILD WITH (ONLINE = ON); |

• Update statistics after rebuild: UPDATE STATISTICS dbo.Table;
• Schedule maintenance during low-activity windows
• Use Ola Hallengren's maintenance solution for automated, threshold-based maintenance
• Monitor with sys.dm_db_index_physical_stats (use LIMITED mode for quick checks)

Index Anti-Patterns

| Anti-Pattern | Impact | Fix | |---|---|---| | GUID (NEWID()) as clustered key | Massive page splits, fragmentation, wide key copied to every NC index | Use INT IDENTITY; GUID as NC unique | | Indexing every column individually | Write amplification, wasted storage, optimizer confusion | Design indexes for query patterns, not columns | | Wide clustered key (>16 bytes) | Every non-clustered index carries a copy of the clustered key | Keep clustered key narrow (4–8 bytes) | | Missing INCLUDE columns | Key lookups on every row — negates index benefit | Add SELECT-list columns as INCLUDE | | Overlapping/duplicate indexes | Double write cost, wasted storage, no benefit | Audit with DMV query above; consolidate | | Never rebuilding or reorganizing | Fragmentation degrades read performance over time | Schedule threshold-based maintenance | | Filtered index on parameterized query | Optimizer cannot match parameter to filter predicate | Use OPTION (RECOMPILE) or literal values | | Index on low-selectivity column alone | Full scan is faster than bookmark lookups on most rows | Combine with selective column or use filtered index |

When to Use

• Designing indexes for new tables before production deployment
• Diagnosing slow queries (check execution plan for scans, key lookups, missing index hints)
• Reviewing existing index strategy for waste and gaps
• Planning index maintenance schedules
• Evaluating trade-offs between read performance and write overhead

When NOT to Use

• EF Core LINQ query optimization (see ef-query-optimization skill — EF side)
• T-SQL coding patterns inside procedures (see sql-coding-standards skill)
• Schema design decisions like normalization or data types (see database-schema-design skill)
• Application-level caching to reduce database load (see caching-strategy skill)

Gotchas

1. Missing index DMVs reset on restart: The sys.dm_db_missing_index_* views are cleared when SQL Server restarts or the plan cache is cleared. Collect data over weeks, not hours.
2. DMV suggestions are per-query, not per-workload: A DMV may suggest 5 overlapping indexes for 5 queries — consolidate them manually into 1–2 well-designed indexes.
3. Online index operations require Enterprise Edition: Standard Edition must take the index offline during rebuild. Plan accordingly or use REORGANIZE (always online).
4. Statistics lag: After large data loads, auto-update statistics may not trigger immediately. Run UPDATE STATISTICS manually after bulk inserts to prevent plan regressions.
5. Covering indexes vs index bloat: Adding INCLUDE columns is cheap for reads but increases index size and write cost. Only include columns from frequently-executed queries — not every SELECT in the codebase.

Related Skills

• SQL Coding Standards (write efficient queries that leverage your indexes)
• Database Schema Design (data types and constraints affect index efficiency)
• EF Core Query Optimization (ORM-side query patterns that generate SQL hitting these indexes)
• Bulk Operation Batching (index impact during bulk load operations)