Find SQL Server Missing Indexes with DMVs

By:   |   Comments (7)   |   Related: > Dynamic Management Views and Functions


Problem

In a previous tip, Deeper insight into unused indexes for SQL Server, we discussed how to identify indexes that have been created but are not being used or used rarely.  Now that I know which indexes I can drop, what is the process to identify which indexes I need to create. 

Solution

Microsoft includes a few dynamic management views to also assist with identifying possible index candidates based on query history.

The dynamic management views are:

Like most of the statistics that are tracked for the DMVs, these basically work the same where once the instance of SQL Server is restarted the data gets cleared out.  So if you are working in a test environment and restart your instance of SQL Server these views will probably return no data.

To see how to use these, we are going to use examples from books online.

The first queries a table from the AdventureWorks database where there is no index on the StateProvinceID as shown below.

USE AdventureWorks;
GO

SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 1;
GO 

Once we have run the above query, data should now be available in our management views.  Let's take a quick look at each of these.

Example using sys.dm_db_missing_index_details

The first query gets data from the sys.dm_db_missing_index_details view.  This is probably the most helpful, since this shows us the object_id and the equality_columns and the inequality_columns. In addition we get some other details about included columns. 

SELECT * FROM sys.dm_db_missing_index_details
query results

So for the query we ran above, we can see the following:

  • equality_columns = "StateProvinceID", this is because this column is used in the WHERE clause with an equals operator.  So SQL Server is telling us this would be a good candidate for an index.
  • inequality_columns = "NULL", this column will have data if you use other operators such as not equal, but since we are using equals there are no columns that could be used here 
  • included_columns = this is additional columns that could be used when the index is created.  Since the query only uses City, StateProvinceID and PostalCode, the StateProvinceID will be handled in the index and the other two columns could be used as included columns when the index is created.  Take a look at this tip for more information about included columns.

Example using sys.dm_db_misssing_index_group_stats

The next query gets data from sys.dm_db_misssing_index_group_stats.  This query gives us additional insight into other stats such as compiles, user seeks, user scans, etc.  So from here we can tell how often this query is being called.  This will help us to determine how much use an index may get if we do create a new index based on this information.

SELECT * FROM sys.dm_db_missing_index_group_stats

Since we only ran this query one time, our unique_compiles = 1 and our user_seeks = 1.  If we run this again, our user_seeks should increment.. 

query results

Example using sys.dm_db_missing_index_groups

The next view, sys.dm_db_missing_index_groups gives us information about the index_group_handle and the index_handle. 

SELECT * FROM sys.dm_db_missing_index_groups
query results

Example using sys.dm_db_missing_index_columns

These output from the above query is basically used to get data from sys.dm_db_missing_index_columns function.  The index_handle value is passed on to the next query as shown below.

SELECT * FROM sys.dm_db_missing_index_columns(1)
query results

To get all of the data displayed in one result set, the following query gives us this data.

SELECT mig.*, statement AS table_name, column_id, column_name, column_usage
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id; 
query results

Summary

  • Based on this one example we can see that we can create a new index on table AdventureWorks.Person.Address on column StateProvinceID and also include columns City and PostalCode.
  • One thing to note is that when you add or drop indexes on a table all stats for missing indexes get cleared for this table.
  • Although this may not be perfect and there are some limitations, this does at least give us some additional insight we never had before with prior versions of SQL Server.
Next Steps
  • This may not be an optimal approach for managing your indexes, but this does give you some additional insight as to what is occurring and what indexes may be helpful.  Take a look at these views to see if you can identify some additional indexes that may be useful.
  • Although it would be nice if this gave you the definitive answer on how and what to create, it still takes time to understand your applications and manage your indexes
  • To view a list of unused indexes take a look at this tip: Deeper insight into unused indexes for SQL Server


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, March 9, 2021 - 10:32:48 PM - Marios Philippopoulos Back To Top (88369)
Thank you for the article.
When are columnstore indexes going to be included in these DMVs?

Tuesday, July 10, 2018 - 7:46:23 AM - Dave Bunch Back To Top (76599)

 Great article! Helped me in 2018!


Friday, December 14, 2012 - 1:36:45 AM - subahan munthamadugu Back To Top (20953)

Hi  Sir,

       This is subahan .m  and i read this article ,it was so nice  but i have a doubt '' WHAT ARE THE RESONS FOR MISSING INDEX"

         i want to know  resouns for  this

thanks


Wednesday, January 6, 2010 - 11:12:32 AM - admin Back To Top (4659)

The tip has been updated to fix the wrong query.

[quote user="Stone"]

Thank you for the post- it's definitely pushed me in the right direction.  A little late to the party, I suppose, but one minor correction... The second query listed in the article is the same as the first:

SELECT * FROM sys.dm_db_missing_index_details

But I think it should be:
SELECT * FROM sys.dm_db_missing_index_group_stats 
[/quote]

Wednesday, January 6, 2010 - 8:50:31 AM - Ozzie Back To Top (4656)

Steve,

Interesting add to the article.  So a quick once over would seem to say create the indexes with the higher avg_user_impact?

Is there a way to do this to show indexes that you have which are not being used and the cost of their existence?

Thanx,

Doug 

 


Monday, May 18, 2009 - 6:44:09 AM - Stone Back To Top (3386)

Thank you for the post- it's definitely pushed me in the right direction.  A little late to the party, I suppose, but one minor correction... The second query listed in the article is the same as the first:

SELECT * FROM sys.dm_db_missing_index_details

But I think it should be:
SELECT * FROM sys.dm_db_missing_index_group_stats 

Monday, December 8, 2008 - 11:01:47 AM - StevenHanley Back To Top (2363)

Good summary of what these views do, however I think the query that pulls all of this information together needs to provide cost statistics. Also, on systems with more than one database it helps to add databasename into the resultset:

select 

sysd.name

,sysddmig.*

,sysddmid.statement as table_name

,sysddmic.column_id

,sysddmic.column_name

,sysddmic.column_usage

,sysddmigs.user_seeks

,sysddmigs.avg_total_user_cost

,sysddmigs.avg_user_impact

from

sys.dm_db_missing_index_details as sysddmid

cross apply sys.dm_db_missing_index_columns (sysddmid.index_handle) sysddmic

inner join sys.dm_db_missing_index_groups as sysddmig on sysddmig.index_handle = sysddmid.index_handle

inner join sys.dm_db_missing_index_group_stats as sysddmigs on sysddmig.index_group_handle = sysddmigs.group_handle

inner join sys.databases as sysd on sysd.database_id = sysddmid.database_id

order by

sysddmigs.avg_user_impact desc

,sysddmig.index_group_handle

,sysddmig.index_handle

,sysddmic.column_id;

 















get free sql tips
agree to terms