Share via

Are you using SQL's Missing Index DMVs?

  • Article

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:

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

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

  • Anonymous
    August 24, 2007
    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.
  • Anonymous
    October 16, 2007
    The comment has been removed
  • Anonymous
    November 27, 2007
    In my "APPLY Operator" post I used the example query below to illustrate the use of CROSS APPLY. I mentioned
  • Anonymous
    January 22, 2008
    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. ...
  • Anonymous
    May 08, 2008
    One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several
  • Anonymous
    July 07, 2008
    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 ServerThanks,Phil
  • Anonymous
    July 07, 2008
    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?
  • Anonymous
    July 08, 2008
    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>Thanks,Phil
  • Anonymous
    July 08, 2008
    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
  • Anonymous
    July 10, 2008
    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,Phil
  • Anonymous
    October 22, 2008
    The comment has been removed
  • Anonymous
    December 03, 2008
    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
  • Anonymous
    March 11, 2009
    Thank you very much. This has improved my query performance a lot :)
  • Anonymous
    April 09, 2009
    Is There a way to find the object query that caused the generation of the missing index entry in the dmv
  • Anonymous
    October 20, 2009
    I´m sorry my english, was by googleI have to disagree on some points. I had a recent experience that the situation could not use DTA and the DMV's were fantasticEach 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 queryof 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 workloadI 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 serverThis is my feedback about the DMV´s.http://www.simple-talk.com/sql/performance/reducing-io-with-the-missing-indexes-dmvs/
  • Anonymous
    December 03, 2009
    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.  
  • Anonymous
    December 03, 2009
    @joetig, > 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 http://sqlblogcasts.com/blogs/leopasta/archive/2008/05/08/which-queries-are-missing-indexes.aspx.  HTH!
  • Anonymous
    January 31, 2010
    I just run the script with SQL 2008 and get no results?Any ideas? Is the script fully compatible with SQL 2008?Thanks
  • Anonymous
    April 27, 2010
    The comment has been removed
  • Anonymous
    July 21, 2010
    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
  • Anonymous
    October 24, 2010
    Bart,This is the sh*t.Two of our indexes came up with improvements of 500+ and 700+.You the man.Thanks!
  • Anonymous
    November 16, 2010
    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_handleFROM sys.[dm_db_missing_index_details] DINNER JOIN
    sys.[dm_db_missing_index_groups] AS G
    ON (G.index_handle = D.index_handle)INNER JOIN
    sys.[dm_db_missing_index_group_stats] GS
    ON (GS.group_handle = G.index_group_handle)
  • Anonymous
    January 24, 2011
    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?Thanks,Tim
  • Anonymous
    January 24, 2011
    The comment has been removed
  • Anonymous
    January 26, 2011
    This is really cool. I just need to do some investigation into the validity of each of these recommended changes.
  • Anonymous
    May 04, 2011
    It was an excellent post. I started using this and many of my performance issues disappears. Thanks Bart.
  • Anonymous
    May 04, 2011
    The comment has been removed
  • Anonymous
    September 08, 2011
    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! :-)
  • Anonymous
    November 03, 2014
    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,Igor