Why Missing-Index Recommendations Mislead

SQL Server tells you about indexes it wishes it had, in the green text on a plan and in the missing index DMVs. The information is genuine and useful. The CREATE INDEX statement it hands you is not a prescription, and applying those statements as-written is one of the most common ways a database ends up with forty indexes on a table and slower writes than it started with.

Here is precisely what the feature does and does not do.

What it actually is

During optimization, when the engine considers a plan and finds no index supporting a predicate, it records "an index on these columns would have helped this query." That is all. It is a per-query, per-compilation note.

Why the suggestion misleads

It ignores the indexes you already have. The recommendation is generated in isolation. If you have an index on (StatusId, RegionId, OrderDate) and this query wants (RegionId, StatusId, OrderDate), you will be told to create the second one. You now have two near-identical indexes, both maintained on every write, when reordering one would have served both queries.

It gets the column order wrong. The DMV separates columns into "equality" and "inequality" groups, but within each group the order is the order the columns appear in the table, not by selectivity, and it has no view of your other queries. Column order is the thing that determines whether an index is useful (Lesson 2), and the recommendation is essentially guessing at it.

It over-includes. The suggestion lists every column the query selected as an INCLUDE. Run it against a SELECT * report and you get an index that duplicates the table.

It only ever adds. It never says "you have six indexes on this table, three unused." The cost side is invisible to it.

Impact is a relative estimate. "Impact: 97%" means the optimizer estimated this query's cost would drop 97% — using the same cost model and the same possibly-wrong cardinality estimates that produced the bad plan. It is not a measurement.

The counters reset on restart and are lost when plans are evicted, so a quiet-looking table might just mean the instance restarted last night.

Reading the DMVs properly

Do not read one recommendation. Read them aggregated, sorted by a measure that includes how often the query actually runs:

SELECT TOP (25)
       DB_NAME(mid.database_id)                  AS database_name,
       OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
       OBJECT_NAME(mid.object_id, mid.database_id)        AS table_name,
       migs.user_seeks + migs.user_scans         AS times_wanted,
       migs.last_user_seek,
       migs.avg_total_user_cost,
       migs.avg_user_impact,
       -- a rough "worth it" score: cost x impact x frequency
       CAST(migs.avg_total_user_cost
            * (migs.avg_user_impact / 100.0)
            * (migs.user_seeks + migs.user_scans) AS decimal(18,2)) AS improvement_measure,
       mid.equality_columns,
       mid.inequality_columns,
       mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups      AS mig  ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details     AS mid  ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

Two more checks before acting on anything at the top of that list:

-- 1. How long have these counters been accumulating?
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

-- 2. What indexes does the table already have?
--    (run the inventory query from Lesson 1 against the table named above)

If the instance restarted four hours ago, the list is a sample of half a morning.

The workflow that works

1. Aggregate the recommendations and take the top few by improvement_measure, not the ones with the biggest impact percentage.
2. Inventory the existing indexes on that table.
3. Ask whether an existing index can be reordered or extended to serve the query. Extending an existing index with an INCLUDE column is almost always better than adding another index.
4. If a new index really is needed, design the key order yourself using the equality-then-range rule, and trim the INCLUDE list to what the query genuinely returns.
5. Name it descriptively, not <Name of Missing Index, sysname,>.
6. Measure before and after (Module 1 Lesson 5), and check dm_db_index_usage_stats a week later to confirm it is being used.

Duplicates you may already have

Worth running once on any database that has been in production a few years:

-- Indexes whose leading key column matches another index on the same table:
-- candidates for consolidation, not automatic deletions.
WITH ix AS (
    SELECT i.object_id, i.index_id, i.name,
           c.name AS leading_column,
           (SELECT COUNT(*) FROM sys.index_columns ic2
             WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id
               AND ic2.is_included_column = 0) AS key_col_count
    FROM sys.indexes AS i
    JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal = 1
    JOIN sys.columns AS c
         ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE i.type_desc = 'NONCLUSTERED'
      AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
)
SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id)        AS table_name,
       leading_column,
       COUNT(*)                      AS index_count,
       STRING_AGG(name, ', ')        AS index_names
FROM ix
GROUP BY object_id, leading_column
HAVING COUNT(*) > 1
ORDER BY index_count DESC;

Two indexes on the same leading column are frequently one index with two names. Confirm with the full inventory and the usage stats before dropping anything.

In practice

Read missing-index recommendations as "the optimizer wanted to seek on these columns and could not", which is real information. Do not run the CREATE statement it gives you — it is generated without knowledge of the indexes that already exist, its column order is not chosen for selectivity, and it includes every column in the SELECT list. Aggregate the recommendations by cost times impact times frequency, check what is already on the table, and you will usually end up extending an existing index instead of adding one.