Are you using SQL's Missing Index DMVs?

Are you using SQL's Missing Index DMVs?

Rate This
  • Comments 28

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. 

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • 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.

  • 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: http://blogs.msdn.com/biztalk_core_engine/archive/2007/01/04/what-you-can-and-can-t-do-with-the-messagebox-database-server.aspx, "(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!

    Thanks,

    Duncan

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

  • 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. ...

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

  • 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

    Thanks,

    Phil

  • 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?

  • 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

  • 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

  • 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

  • Everyone,

    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.

    HTH

  • 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

  • Thank you very much. This has improved my query performance a lot :)

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

  • 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.

    http://www.simple-talk.com/sql/performance/reducing-io-with-the-missing-indexes-dmvs/

Page 1 of 2 (28 items) 12