SQL Server Index Strategy: Deciding What to Index Based on Real Query Patterns

Knowing index syntax isn’t the hard part — deciding what deserves an index is. This is strategy, not mechanics.

Let SQL Server Tell You What It’s Missing

SELECT
    d.statement AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns,
    s.user_seeks, s.avg_total_user_cost, s.avg_user_impact
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact * s.user_seeks DESC;

This DMV logs every time the optimizer wished an index existed. It’s a starting hypothesis, not an automatic answer — verify against the Evidence-First workflow before creating anything.

The Cost-Benefit That Actually Matters

This is a direct application of Ch.8’s covering-index lesson turned into a decision framework, not just a syntax choice: every key/INCLUDE column you add helps reads but also widens what every write has to maintain.

An index that helps a report run once a week but slows down a table receiving thousands of writes per second is very likely a bad trade — measure both sides, not just the read win.

Finding Indexes That Aren’t Earning Their Keep

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
    s.user_seeks + s.user_scans + s.user_lookups AS total_reads, s.user_updates AS total_writes
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() AND s.user_updates > 0
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC;

Sort ascending by reads with nonzero writes — the indexes at the top are pure cost, no benefit. Strong drop candidates, pending one more check: confirm they’re not enforcing a UNIQUE constraint or primary key first.

Key Takeaways

• Missing index DMVs are hypotheses to verify, not automatic instructions
• Every index decision is a trade: read benefit vs. write cost, weighted by actual frequency
• Regularly audit for unused indexes — they cost on every write with zero return