Find SQL Server Missing Indexes with DMVs

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:

• sys.dm_db_missing_index_details – Returns detailed information about a missing index
• sys.dm_db_missing_index_group_stats  – Returns summary information about missing index groups
• sys.dm_db_missing_index_groups – Returns information about a specific group of missing indexes
• sys.dm_db_missing_index_columns(index_handle) – Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.

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. 

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

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

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)

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; 

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