Your Indexes Are Lying to You: What Missing Index DMVs Won't Tell You

If you're a SQL Server DBA, you've probably got a script that queries sys.dm_db_missing_index_details. Maybe you run it weekly. Maybe you've got it on a schedule. And when it tells you to create an index, you probably create it.

I did the same thing for years. Then I started paying attention to what happened after I created those indexes, and I realized something uncomfortable: the missing index DMVs are frequently wrong. Not wrong in the sense that they're buggy, but wrong in the sense that they give you a dangerously incomplete picture of what your server actually needs.

How the Missing Index DMVs Actually Work

When the query optimizer builds an execution plan and notices that an index could have helped, it records that suggestion in memory. That's all the missing index DMVs are: a log of times the optimizer wished an index existed. It records the table, the equality columns, inequality columns, and included columns it wanted.

Here's the problem: each suggestion is generated in isolation by a single query compilation. The optimizer doesn't look at your existing indexes. It doesn't consider whether two suggestions could be satisfied by one index. It doesn't think about the write cost of maintaining a new index on a table that gets 50,000 inserts per second.

It just says "I wanted this" and moves on.

The Overlapping Index Trap

This is the most common way missing index DMVs lead you astray. Say you see three suggestions for the same table:

-- Suggestion 1: equality on [Status], include [CustomerName, OrderDate]
-- Suggestion 2: equality on [Status, Region], include [CustomerName]
-- Suggestion 3: equality on [Status], include [CustomerName, OrderDate, Total]

If you create all three, you've got three indexes that are mostly redundant. Suggestion 3 is a superset of Suggestion 1. Suggestion 2 could potentially be covered by a single composite index that handles all three patterns.

But the DMV doesn't tell you that. It presents each one as an independent recommendation with its own improvement measure. I've seen servers where DBAs faithfully created every suggested index and ended up with 15 indexes on a single table, five of which were essentially duplicates with slightly different included columns.

The write overhead on that table was catastrophic. Every insert had to maintain 15 separate B-tree structures. The net effect was that the server got slower, not faster.

The Improvement Measure is Misleading

The avg_user_impact column in sys.dm_db_missing_index_group_stats shows the estimated percentage improvement for queries that would benefit from the index. Sounds great, right? Except it's an average across all compilations that triggered the suggestion, and it doesn't account for how often those queries actually run.

An index might show 95% improvement, but if the query that needs it runs once a day at 2am during a batch job, that improvement is almost worthless compared to an index showing 30% improvement for a query that runs 10,000 times per hour.

To get the real picture you need to combine the DMV data with actual execution frequency:

SELECT
    mid.statement AS table_name,
    migs.avg_user_impact,
    migs.user_seeks + migs.user_scans AS total_potential_uses,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS overall_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
ORDER BY overall_impact DESC;

That overall_impact calculation gives you a much more realistic ranking. But even that doesn't account for the write cost you're about to introduce.

The Write Cost Blind Spot

This is the big one. Missing index DMVs only think about reads. They have absolutely no concept of how much write overhead a new index will add. For read-heavy reporting databases, that's fine. For OLTP systems doing thousands of inserts and updates per second, it can be a disaster.

Every index you add has a cost:

• INSERT operations must add a row to every non-clustered index on the table
• UPDATE operations on indexed columns must update the corresponding index entries
• DELETE operations must remove entries from every index
• Page splits happen when index pages fill up, causing IO spikes and fragmentation

Before creating any suggested index, you should check the write activity on that table:

SELECT
    OBJECT_NAME(ios.object_id) AS table_name,
    SUM(ios.leaf_insert_count) AS total_inserts,
    SUM(ios.leaf_update_count) AS total_updates,
    SUM(ios.leaf_delete_count) AS total_deletes,
    SUM(ios.leaf_insert_count + ios.leaf_update_count +
        ios.leaf_delete_count) AS total_writes
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
GROUP BY ios.object_id
ORDER BY total_writes DESC;

If a table has millions of writes per day, think very carefully before adding another index. The read improvement might not outweigh the write penalty.

Missing Indexes Reset on Restart

Here's a subtle one that bites people more often than you'd think. The missing index DMVs are stored in memory. They reset when the SQL Server service restarts, when the database is detached/reattached, or when an index is created or dropped on the table.

That means if you had a server restart last Tuesday, all your missing index data only reflects the workload since Tuesday. If your heaviest workload runs on month-end processing and you're looking at mid-month data, you're getting an incomplete picture.

Always check sqlserver_start_time in sys.dm_os_sys_info before drawing conclusions from missing index data. If the server restarted recently, the data may not be representative.

What to Do Instead

I'm not saying ignore the missing index DMVs. They're a useful starting point. But treat them as suggestions, not instructions. Here's the process I use:

1. Consolidate overlapping suggestions. Look at all suggestions for the same table together. Can two or three suggestions be satisfied by a single, well-designed index? Usually yes.

2. Weight by actual impact. Multiply improvement by frequency. A small improvement on a hot query is worth more than a big improvement on a rare query.

3. Check write activity. If the table is write-heavy, the bar for adding a new index should be much higher. The read benefit needs to clearly outweigh the write cost.

4. Cross-reference with Query Store. If you have Query Store enabled (and you should), look at the actual query plans that are triggering the suggestions. Sometimes a query rewrite or a statistics update fixes the problem without needing a new index at all.

5. Test and measure. Create the index, monitor for a week, and check whether it's actually being used. If sys.dm_db_index_usage_stats shows zero seeks and scans after a reasonable period, drop it.

The missing index DMVs are a tool, not an oracle. The difference between a junior DBA and a senior one is knowing when to trust the tool and when to override it. That nuance, the ability to look at a suggestion in the context of the whole workload and make a judgement call, is what separates good index management from index hoarding.

If you want to see how your current indexes stack up, including unused ones that are costing you write performance for nothing, run a diagnostic scan. The overlap between what's suggested and what's actually needed is usually smaller than you think.