Are you using SQL’s Missing Index DMVs?

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create?  The "missing index" DMVs in SQL are a really great new feature in SQL Server 2005 that (in my opinion) seem to have been underutilized so far.  If you want to see if this feature can spare you the tedium of an afternoon identifying poor performing queries and tuning them, all you have to do is ask:


  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

You'll want to run this after your server has been up and running a normal workload for a while.  If this returns no results, that's good news and indicates that you're not missing any indexes that are obvious enough for the DMV to detect.  If it does return some suggestions, even better: you just improved your server's perf with almost no work.

While to me this feature is so cool it almost seems magical, it does have a few limitations you should be aware of:

  • It's not as smart as the Database Engine Tuning Advisor.  If you have identified a query that you know is expensive and needs some help, don't pass up DTA just because the missing index DMVs didn't have any suggestions.  DTA might still be able to help. 
  • The missing index DMVs don't take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
  • The "improvement_measure" column in this query's output is a rough indicator of the (estimated) improvement that might be seen if the index was created.  This is a unitless number, and has meaning only relative the same number for other indexes.  (It's a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
  • The missing index DMVs don't make recommendation about whether a proposed index should be clustered or nonclustered.  This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries.  (DTA can do this, however.)
  • Won't recommend partitioning.
  • It's possible that the DMVs may not recommend the ideal column order for multi-column indexes.
  • The DMV tracks information on no more than 500 missing indexes.

If you're a typical SQL user, you may not be using these DMVs yet.  If you look around, though, there are a few places where they are in use. One is in the SP2 Performance Dashboard reports.  Another is the Perf Stats Script that SQL PSS uses.  And if you think the missing index DMVs are useful, check out this set of scripts that builds on the missing index DMVs to simulate an "auto create index" feature.  Also, you should be aware there is similar missing index info output in the new XML showplan format in SQL 2005.  If you are already focused on a poorly-performing query, I would start with the plan view of missing indexes (followed by DTA) rather than the DMVs. 

Comments (29)
  1. Anonymous says:

    Agree with the cool & magical of this feature (imagine avoiding errors in a group of people creating indexes @ a large datawarehouse). Thank You for the reminder about this.

  2. Anonymous says:

    Hi Bart, thanks for such a great heads-up.

    Just a slight note of caution to BizTalk users: I just ran this query on a box here and found lots of recommendations for adding indices in the BizTalk databases.

    People must not follow these recommendations on BizTalk! The BizTalk database is very carefully tuned by hand; any changes to these databases will potentially cause serious problems and PSS will most likely insist on step 1 of any troubleshooting being to set BizTalk database installations back to the out of the box install.

    As they say at:, "(don’t add indexes, columns, triggers, … If you do you will hear silence when you call for help)"

    Hope this little bit of info is useful!



  3. Anonymous says:

    In my "APPLY Operator" post I used the example query below to illustrate the use of CROSS APPLY. I mentioned

  4. Anonymous says:

    SQL Server 2005 has some DMVs that will help you tune the system. The missing index DMVs track recent queries that could have benefited from an index that didn’t exist. …

  5. Anonymous says:

    One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several

  6. Anonymous says:

    I’ve been using your excellent "missing index" DMV for some time and just recently it has started throwing an error on one of my servers:

    "Arithmetic overflow error converting float to data type numeric."

    Any idea what might be causing this and how I can possibly work around it by altering the script?

    SQL2005 Ent Ed, SP2, Windows 2003 Server



  7. bartduncan says:

    I haven’t run into that problem myself, but think I see what is causing it.  I’ve made a couple of minor changes to the query that should fix it; can you give it a try and let me know what you find?

  8. Anonymous says:

    Hi Bart:

    I recopied the script and ran it again but am still getting the same error. Only happens on one of my servers though. <weird>



  9. bartduncan says:

    Phil, I’ve removed the attempted conversion of the improvement measure to decimal (it was just there for formatting, anyway).  Can you try once more?  You should no longer get the error, but if you do, ping me offline at bartd at micro soft dot com and we’ll figure it out.  Thanks, Bart

  10. Anonymous says:

    Hi Bart:

    That seemed to fix the problem. The script runs successfully now and returns expected results.

    However, the highest ‘improvement_measure’ is now displaying ‘9.84239188405347E+36’ so, I guess this is where the formatting gets lost. I’m not quite sure how to interpret that number now, but it’s obviously a big one.

    Thanks again,


  11. Anonymous says:


    If you want to import the results into Excel for analysis, you need to change Line 10 from this:

    • ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,

    to this:

    • ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)”’, ‘”’) AS create_index_statement,

    Otherwise, the SQL statement jacks with the columns in the comma-separated file.


  12. Anonymous says:

    Thanks for the script for missing indexes, I used that a few times. And I found it very useful. Since it gathers all those statistics by using the activity on the database.

    Thanks again

  13. Anonymous says:

    Thank you very much. This has improved my query performance a lot 🙂

  14. Anonymous says:

    Is There a way to find the object query that caused the generation of the missing index entry in the dmv

  15. Anonymous says:

    I´m sorry my english, was by google

    I have to disagree on some points. I had a recent experience that the situation could not use DTA and the DMV’s were fantastic

    Each situation has its application, both DTA and DVM’s.

    "The missing index DMVs don’t take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however. "

    I belive they not been made for that. In this case we have the DTA and this type of type of analysis  I prefer to do this without automatic recommendations.

    "The "improvement_measure" column in this query’s output is a rough indicator of the (estimated) improvement that might be seen if the index was created.  This is a unitless number, and has meaning only relative the same number for other indexes.  (It’s a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.) "

    The Avg_user Impact is near to close. Very near with the Real

    (I did a lot of tests with a 4TB database )

    "The missing index DMVs don’t make recommendation about whether a proposed index should be clustered or nonclustered.  This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries.  (DTA can do this, however.)"

    Like I said , I belive they not been made for that too. For this we have another DMV´s and DTA like you said.

    And particularly for me, the definition of the index cluster is very focused on their specific business rule as discussing when the normalization is good or not. Is good when you answer your business.

    Won’t recommend partitioning.

    Yes, this is a problem really.

    "It’s possible that the DMVs may not recommend the ideal column order for multi-column indexes."

    No, By ton of tests I Did ALWAYS the columns suggested in multi-column indexed was correctly, by your selectivity.

    A very interesting point to see is that the DMV’s return a run  statistics and cost per query

    of not benefited from this index as the actual load in your environment. The DTA does not.

    If you have a third-party software that makes many queries dynamically by the application,

    you have to get the queries by profiler and run  in the DTA.

    And to know which query is being accessed more (number) is complicated by the profiler.

    For DMV use user_seeks.And it is a REAL number to your environment workload

    I believe that we can not go out creating indexes with the DMV’s, but also by the DTA.

    It’s all a question of the situation that you will use one or the other.

    But surely they were a goal of time sql server

    This is my feedback about the DMV´s.

  16. bartduncan says:

    Thanks for the comment, Laerte.  You make good points, and for the most part I agree with you.  It wasn’t my intent to imply that the DMVs are "bad" and DTA is "good".  I just wanted to make sure that people knew the relative strengths and weaknesses of each tool so that they can deploy each in the proper circumstances.  

  17. bartduncan says:


    > Is There a way to find the object query that caused the generation of the missing index entry in the dmv <<

    Not directly, but there’s an approximate technique for doing this that was shared by Leo Pasta at  


  18. Anonymous says:

    I just run the script with SQL 2008 and get no results?

    Any ideas? Is the script fully compatible with SQL 2008?


  19. Anonymous says:

    Yes, I just ran this script against a suppliers database as their software kept hanging and I noticed the SQL was reporting over 15 seconds running time on queries at random intervals.

    Already knew about DMV’s so ran the script against my 2008 database and suddenly we (touch wood) have not experienced any more hangings.


  20. Anonymous says:

    Is There a way to find the  query that caused the generation of the missing index entry in the sys.dm_db_missing_index_details

  21. nhwilly says:


    This is the sh*t.

    Two of our indexes came up with improvements of 500+ and 700+.

    You the man.


  22. Anonymous says:

    Do you have any idea why they limited the number of missing indexes that this functionality can report on?  I have a server on which this stops working after the service has been running for awhile.  This query returns nothing now, even though it did a few weeks ago.  I haven't addressed many index recommendations.  It appears that the DMVs are out-of-sync because of the 600 missing index limitation.

    SELECT database_id,[statement]AS table_name,equality_columns,inequality_columns,included_columns,D.index_handle

    FROM sys.[dm_db_missing_index_details] D


    sys.[dm_db_missing_index_groups] AS G

    ON (G.index_handle = D.index_handle)


    sys.[dm_db_missing_index_group_stats] GS

    ON (GS.group_handle = G.index_group_handle)

  23. Anonymous says:

    A funny thing I noticed was that running this on a newly restored database (development from production) doesn't return any results. Looking as the base view, sys.dm_db_missing_index_groups, on the production server returns 429 rows. After restoring a backup on the development server, the same DMV has zero rows. Where is the base data for this DMV stored?



  24. bartduncan says:


    The data behind this DMV is not persisted to disk; it's stored in-memory and has a transient lifetime. That's why you don't see any recommendations after moving the database to a different server.  You'd also get no recommendations if you queried the DMV on the production server immediately after bouncing the prod SQL service.  


  25. Anonymous says:

    This is really cool. I just need to do some investigation into the validity of each of these recommended changes.

  26. Sanki says:

    It was an excellent post. I started using this and many of my performance issues disappears. Thanks Bart.

  27. bartduncan says:

    Someone contacted me off-blog with the question "[The missing index DMVs] show some queries with high user_seeks, but user_scans, last_user_scan, system_seeks, system_scans are all zero. Is this something we should investigate further?"

    Including the reply here, since this is a good question:

        You mean, will there be any benefit if a proposed index has some non-zero value for a *_seek column but zero for all *_scans columns?  Yes.  (Assuming that the query optimizer’s estimates are accurate, that is… All of these DMVs are based on the QO’s plan cost estimates. If an index shows up in this list it means that the QO thinks the index would improve performance, and the QO would generally use the index if it existed. Of course, the QO is a guessing engine, and occasionally its estimated costs are wrong.)  

        Why would a query that is already doing an index seek benefit from a new index? Because not all seeks are created equal.  In this case I’d guess that the current plans are doing a less selective index seek than would be possible with a different index.  For example, suppose you have the query “SELECT col1, col2 FROM t WHERE t.col1=X AND t.col2=Y”. Suppose there is an index on col1; the QO will definitely seek on that index to find all rows where col1=X.  But an index on (col1, col2) that allowed a more selective seek on both of the predicates would generally touch fewer pages and do less I/O.

  28. Excellent post, thank you! Currently trawling through a 3rd party database that's been developed by people who clearly don't really understand indexes (for instance a 4.4million row table with no clustered index), and this has really helped bring to light where I should focus my efforts in improving the performance issues. Only downside is that it's identified quite a few potential issues, so that's me busy for a while! 🙂

  29. Igor Micev says:

    Hi Bart,

    I use your query and decide on my own whether I'm creating a proposed index or not.

    I want to know what is the usual threshold value for the "improvement_measure" you use to decide whether you're going with the proposed index or not.

    Thanks in advance,


Comments are closed.

Skip to main content