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.

 

(Visited 722 times, 10 visits today)

Leave a Reply

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