Finding Missing Indexes in SQL Server Using DMVs

SQL Server's dynamic management views give you a fast, low-overhead way to identify missing indexes without running a full Profiler trace. The sys.dm_db_missing_index_details family of DMVs tracks every instance where SQL Server's query optimiser determined that an index would have improved query performance, and surfaces that information as actionable CREATE INDEX statements you can review and test.

For DBAs managing busy production systems, this is one of the most practical diagnostic tools available. A single query can surface index gaps that have been quietly costing you CPU cycles and I/O for months.

Why Missing Indexes Matter

Poor indexing is one of the most common causes of SQL Server performance problems we see in the field. When the query optimiser can't find a suitable index, it falls back to a table scan or a clustered index scan, reading far more data than necessary. On small tables this is barely noticeable. On tables with millions of rows, it translates directly into slow queries, high CPU, excessive I/O wait, and frustrated end users.

The challenge is that these problems are often invisible until something breaks. A query that runs acceptably under normal load can grind to a halt when data volumes grow or concurrent users increase. Identifying missing indexes proactively, before they cause an incident, is exactly the kind of work that separates reactive database management from a properly maintained environment.

How SQL Server Tracks Missing Indexes

Every time SQL Server compiles a query plan, the optimiser evaluates whether an index would have helped. When it determines one would have, it records that information in three DMVs:

  • sys.dm_db_missing_index_details - stores the specific columns involved (equality columns, inequality columns, and included columns) along with the table and database
  • sys.dm_db_missing_index_groups - groups related missing index entries together
  • sys.dm_db_missing_index_group_stats - tracks usage statistics including how many seeks and scans would have benefited, and the estimated cost impact

These three views work together. Joining them gives you both the structural definition of the proposed index and a quantified measure of how much impact it would have had.

One critical limitation to understand: these DMVs only retain data since the last SQL Server service restart. If you've recently patched and rebooted the server, the data will be sparse or empty. For meaningful results, you want at least several days of representative workload captured, ideally a full business cycle.

The Missing Index DMV Query

The query below joins all three DMVs and generates a ready-to-review CREATE INDEX statement for each missing index. It also calculates an improvement measure score that lets you prioritise which indexes to investigate first.

SELECT
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle)
        + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
        + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL(mid.inequality_columns, '')
        + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Understanding the Output

improvement_measure is the key column for prioritisation. It's calculated by multiplying the average query cost, the estimated percentage improvement, and the total number of seeks and scans that would have benefited. A higher score means SQL Server has seen this missing index situation frequently and estimates a significant cost reduction. Focus your attention on the highest scores first.

The create_index_statement column gives you a syntactically valid CREATE INDEX statement you can copy, review, and test. Don't execute these statements directly in production without review. Treat them as a starting point for analysis, not a deployment script.

The WHERE clause filters out low-impact suggestions (anything scoring 10 or below). You can adjust this threshold depending on how much signal versus noise you want. On a very active system, raising it to 50 or 100 helps cut through the clutter.

The results are ordered by impact descending, so the most significant missing indexes appear at the top of the result set.

What to Do With the Results

Finding missing indexes is the easy part. Deciding which ones to actually create requires judgement. Here's a practical approach:

  1. Review the top candidates by improvement_measure score. Anything with a score in the thousands is worth immediate attention. Scores in the tens or low hundreds may not justify the overhead of an additional index.

  2. Check for duplicates and overlaps. SQL Server sometimes recommends multiple similar indexes for the same table. Before creating anything, check existing indexes with sys.indexes and sys.index_columns to see whether a minor modification to an existing index would cover the gap.

  3. Evaluate the write cost. Every index you add slows down INSERT, UPDATE, and DELETE operations on that table. On write-heavy tables, the read performance gain from a new index can be partially or fully offset by increased write overhead. This trade-off needs to be assessed for each candidate.

  4. Test in a non-production environment first. Apply the index in a test or staging environment, run representative workloads, and compare execution plans and query durations before and after.

  5. Monitor after deployment. Use sys.dm_db_index_usage_stats to confirm the new index is actually being used after it goes to production. Unused indexes carry write overhead with no read benefit.

What These DMVs Won't Tell You

The missing index DMVs are useful but not comprehensive. They don't account for parameter sniffing issues, statistics staleness, or query plan regressions. They also can't tell you whether a proposed index column order is optimal for your specific query patterns.

For that level of analysis, you need execution plan review, workload capture via Extended Events, and in some cases Database Engine Tuning Advisor run against a representative workload trace. Think of the missing index DMVs as a quick triage tool, not a complete index strategy.

Over-indexing is a real problem. We've seen production databases with 20 or 30 indexes on a single table, most of them created in response to DMV suggestions without proper review. The result was INSERT and UPDATE performance so degraded that the "improvements" made things worse overall. The DMV output is a recommendation, not an instruction.

Key Takeaways

  • The sys.dm_db_missing_index_details DMV family identifies indexes SQL Server's query optimiser determined would have improved query performance, ranked by estimated impact.
  • DMV data resets on every SQL Server service restart, so you need a representative period of uptime before the results are meaningful. Aim for at least several days of normal workload.
  • The improvement_measure score lets you prioritise candidates. Focus on high scores first and ignore anything in the low single digits.
  • Never blindly execute the generated CREATE INDEX statements. Review each one for duplicates, write overhead trade-offs, and relevance to your actual workload before deploying.
  • Missing index DMVs are a useful first step in index analysis, not a replacement for proper execution plan review and workload-based tuning.

If you're finding missing indexes across multiple databases or want a structured review of your SQL Server indexing strategy, DBA Services offers SQL Server health checks that cover index analysis, query performance, and configuration review. It's a practical way to get an expert set of eyes on your environment without committing to a long-term engagement.