SQL Scripts: How To Find Missing Indexes

One of the really cool things that SQL Server does is keep up with index statistics behind the scenes.  It knows how an index has been used, how often it is used, or if it is even being used at all.  But SQL Server doesn’t just monitor the indexes that you currently have, it even keeps up with statistics for indexes that it wishes you had!

What Are Missing Indexes?

When SQL Server is processing a query, it will sometimes make a suggestion for an index that it believes will help that query run faster.  These are known as Missing Indexes, or as I like to refer to them, Missing Index Suggestions.  The reason that I stress “suggestions” is that while SQL Server is very good at determining when an index is needed, it sometimes isn’t so good at recommending what that index should be.

What Is Wrong With The Missing Index Recommendations?

When SQL Server recommends a missing index, it does so based on the execution of that query at that specific point in time.  It doesn’t take into consideration any other factors such as current server load, or any existing indexes, so you should consider these index recommendations as just that, recommendations.  Do not just blindly create whatever indexes SQL Server is recommending.

How Do I Find Missing Index Recommendations?

The below query will show missing index suggestions for the specified database.  It pulls information from the sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups,  and sys.dm_db_missing_index_details DMVs.  You can also narrow it down to a specified table by un-commenting the AND statement and specifying the table name.  This script also generates a CREATE INDEX script for each record, so that you can take a better look at the index and decide if you want to use it.  I don’t remember where I initially got this script, I think it may be based off of Glenn Berry’s Diagnostic Script.

It is not uncommon to see many similar recommendations, usually with the same columns that vary slightly in order or between an include statement.  This goes back to what I said earlier about SQL Server only considering indexes for a query at that specific point in time.  Seeing multiple recommendations for the same index that only vary slightly is a good indicator that multiple queries could benefit from an index.

It’s important to mention that these Missing Index statistics get reset every time SQL Server is restarted, so if you’ve recently rebooted the server you may not have many index stats accumulated yet.

Also, if you’re interested in code that will Persist And Aggregate Index Statistics for your server, take a look at my SQL Server Metrics Pack project that is available on GitHub.

EDIT – 8/27/2019I have also added a new post on how you can Find The Queries That Generated The Missing Index Requests.

(Visited 37,347 times, 88 visits today)

9 thoughts on “SQL Scripts: How To Find Missing Indexes

  1. George Reply

    I’ve got very large tables and somehow the proposed index column is truncated after 4000 chars. Is there a way to fix that?

  2. Eric Cobb Post authorReply

    Hey George! All that does is create an index statement based off of the values returned in “EqualityColumns”, “InEqualityColumns” and “IncludedColumns”, so you should be able to take those values and create your own statement.

    However, I would point out that this sounds like a really big index, and I would be cautious about creating something like that. It’s really going to depend on your specific table design, but it sounds like you either have some really long column names, or SQL Server is suggesting sticking everything and the kitchen sink into this index. Remember, SQL Server is very good at determining when an index is needed, but it sometimes isn’t so good at recommending what that index should be.

    Hope this helps!

  3. Sean Reply

    Hi Eric

     

    Just used your code

    Ran perfectly and I have a rake of suggestions.

    I am new to SQL and we are getting very little help from our vendor with there ERP software so its pretty much down to me.

    I was wondering if you could give some general advice on how to determine from the suggested indexes script which ones should be looked at

    Which of the columns should I be looking at , user seeks,the average total user cost , the average user impact or the index advantage?

    In particular one suggestion has an index advantage of 317011.7788 and has an average user impacts of 99.84 and a user seek of 43002  – would this general be a useful index to have ?

     

    • Eric Cobb Post authorReply

      Thanks Sean, I’m glad my scripts helped! As for deciphering the results, that’s going to depend a lot on your specific system. I typically take notice of any results that have a “high” number for user seeks, user impact, or index advantage. For example, an index advantage of 317011 is certainly one that I would look into. However, everyone has a different definition of what is determined as “high”, depending on their specific situation. For example, 43000 index seeks could be high, depending on how long the SQL Server has been gathering these statistics (since the last restart). Is that 43000 seeks in the last 24 hours? Last month? 18 months?

      It sounds like you’ve got some good candidates for useful indexes, I would just recommend a little investigation and testing to see. I would also recommend checking out my Index Metrics scripts, to show you whether or not an index is being used after you add it to your database.

  4. Sachin Khanna Reply

    I ran the ‘missing index recommendation‘ script yesterday and took top recommendation and created an index in my DB.

    Now today when I am running the ‘missing index recommendation‘ script on the same DB, it is recommending me the same index [column combinations] to create again on the same DB on which I created that index yesterday.

    When does the system DMVs are expected to be refreshed in order to not recommend the same index again which I already created.

    [sys].[dm_db_missing_index_group_stats]
    [sys].[dm_db_missing_index_groups]
    [sys].[dm_db_missing_index_details]

    • Eric Cobb Post authorReply

      Hi Sachin. The system DMVs are cleared out whenever the SQL Server services are restarted.

  5. Pingback: SQL Scripts: Find Queries That Have Missing Index Requests - SQL Nuggets

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.