Using SQL Server DMVs to Identify Missing Indexes
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?
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:
- 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.