Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SQL Server DMVs to Identify Missing Indexes


By:   |   Read Comments (5)   |   Related Tips: More > Dynamic Management Views and Functions

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.  I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?

Solution
As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more 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 get started we are going to use an example from SQL Server 2005 Books Online, that 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.

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

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.

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


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

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)


To get all of the data displayed in one result set, the following query from SQL Server 2005 Books Online 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;


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
  • For a list of limitations for these new views, take a look at this article
  • To view a list of unused indexes take a look at this tip: Deeper insight into unused indexes for SQL Server


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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 06, 2010 - 11:12:32 AM - admin Back To Top

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 06, 2010 - 8:50:31 AM - Ozzie Back To Top

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

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 08, 2008 - 11:01:47 AM - StevenHanley Back To Top

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;

 


Learn more about SQL Server tools