How to Find Missing and Inefficient Indexes in SQL Server

An index seek on a large table takes milliseconds. A table scan on the same table might take seconds or minutes, depending on size. The difference between these two outcomes is often the presence or absence of a suitable index. For most SQL Server applications, index problems are among the first things worth investigating when performance is poor.

SQL Server provides several DMVs (Dynamic Management Views) that make finding missing and inefficient indexes systematic rather than guesswork.

Why Indexes Matter

Without an index on a column used in a WHERE clause, SQL Server reads every row in the table to find the matching ones. For a table with a few thousand rows, this is fast enough to be invisible. For a table with millions of rows, the difference is dramatic — and the problem grows as the table grows, meaning indexes that weren't needed in year one become critical by year three.

Beyond read performance, indexes also affect locking behaviour. A table scan holds shared locks on more pages for longer than an index seek. In concurrent environments, that locking difference translates directly into blocking and timeouts.

The Missing Index DMVs

SQL Server tracks information about potential missing indexes in three DMVs: sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats.

These views are populated by the query optimiser when it identifies that an index would have improved the execution plan for a query. Join them together to produce a list of index candidates with impact scores:

• sys.dm_db_missing_index_details contains the database, table, and column information for each suggested index — the equality columns, inequality columns, and included columns
• sys.dm_db_missing_index_group_stats contains usage statistics: how many times the suggestion has been generated (user_seeks), how many user scans the missing index would have improved, and the estimated improvement in cost
• The avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) calculation produces a composite impact score that prioritises which suggestions have the most potential value

Sort the results by impact score descending and review the top entries. These are the indexes that SQL Server has most frequently determined would have improved query performance.

An Important Caveat About Missing Index Suggestions

The missing index DMVs provide starting points, not instructions. They should be treated as hypotheses to evaluate, not as a list to implement automatically.

Several common problems arise from blindly adding every suggested index:

• SQL Server suggests indexes independently for each query. If ten different queries would each benefit from a slightly different index on the same table, the DMVs will suggest ten indexes. The right answer is often one well-designed covering index that satisfies most of them.
• Each index added to a table increases the overhead of every INSERT, UPDATE, and DELETE on that table. On a table with high write volume, adding indexes carelessly can degrade write performance significantly.
• The impact score is calculated from the queries SQL Server has optimised since the last restart. It reflects the queries that ran, not necessarily the queries that matter most to the business.

Test candidate indexes in a non-production environment before applying them to production. Confirm the improvement in the queries that matter, and check that write performance on the affected tables has not degraded unacceptably.

Identifying Unused and Redundant Indexes

sys.dm_db_index_usage_stats tracks how often each index has been used in seeks, scans, and lookups, as well as how often it has been updated (by writes to the underlying table). This view is populated per database and resets on each SQL Server restart.

Indexes where user_seeks, user_scans, and user_lookups are all zero but user_updates is high are indexes that are being maintained by write operations but never being used by read operations. These are candidates for removal — they are adding write overhead with no read benefit.

Before removing any index, verify the following: Has the server been running long enough for all query patterns to have occurred? An index used only by a monthly report will show zero reads if the report hasn't run since the last restart. Has the index been excluded from other diagnostic tools that might use it? Some monitoring tools access system tables in ways that don't register in the usage stats.

Also look for redundant indexes: cases where one index covers a superset of another's columns. An index on (CustomerID) and another on (CustomerID, OrderDate) — the first is redundant because the second can satisfy any query the first can, often more efficiently.

The Key Lookup Problem

A key lookup occurs when SQL Server uses a non-clustered index to find rows but then has to visit the clustered index (the base table) to retrieve additional columns that the query needs but the non-clustered index doesn't contain.

Key lookups are visible in execution plans as a Key Lookup operator connected to a Nested Loops join. For queries returning a small number of rows, this is usually acceptable. For queries returning many rows, the key lookups multiply — one per row found — and become expensive.

The fix is to include the additional columns in the non-clustered index using the INCLUDE clause. A non-clustered index that contains all the columns a query needs — both in the key and in the included columns — is a covering index for that query. The query can be satisfied entirely from the index without visiting the base table.

When adding included columns, include only what is needed. Including every column in every index negates the benefit of the index structure.

Index Fragmentation

sys.dm_db_index_physical_stats returns fragmentation information for each index. The key column is avg_fragmentation_in_percent. High fragmentation means that the logical order of pages in the index does not match their physical order on disk, which increases sequential read costs.

The conventional guidance: for fragmentation below 10%, do nothing. Between 10% and 30%, consider ALTER INDEX ... REORGANIZE. Above 30%, ALTER INDEX ... REBUILD. These thresholds are starting points — adjust based on your workload and maintenance window.

Fragmentation matters less for small indexes (SQL Server may read them entirely into memory regardless) and for indexes accessed randomly rather than sequentially. Focus fragmentation maintenance on large, frequently accessed indexes where sequential reads are common.

Over-Indexing

Too many indexes on a table is its own problem. Every INSERT into a table must add an entry to every index on that table. Every UPDATE that modifies an indexed column must update that index. Every DELETE must remove the entry from every index. On a table with 15 indexes, a single row insert involves 15 index maintenance operations.

This is not a theoretical concern. On tables with high write volume, excessive indexing causes measurable write slowdowns and increases locking during write operations. An index audit — using sys.dm_db_index_usage_stats to identify unused indexes — is as valuable as finding missing ones.

What to Do

1. Query the missing index DMVs ordered by impact score — focus on the top few, not the full list
2. Review suggested indexes critically: can multiple suggestions be consolidated into one well-designed index?
3. Test candidate indexes in non-production and measure the actual improvement
4. Review sys.dm_db_index_usage_stats for unused and redundant indexes
5. For specific slow queries, retrieve the execution plan and look for key lookups — resolve with INCLUDE columns
6. Check fragmentation on large, frequently accessed indexes and schedule maintenance accordingly