I have three different DMVs for Day 7. The first one is sys.dm_db_missing_index_group_stats, which is described by BOL as:

Returns summary information about groups of missing indexes, excluding spatial indexes. Information returned by sys.dm_db_missing_index_group_stats is updated by every query execution, not by every query compilation or recompilation. Usage statistics are not persisted and are kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep the usage statistics after server recycling.

The second one is sys.dm_db_missing_index_groups, which BOL describes as:

Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes.

It is basically a join table between sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_details. The third one is sys.dm_db_missing_index_details, which BOL describes like this:

Returns detailed information about missing indexes, excluding spatial indexes.

By joining these three together, you get a very useful missing index query. This query works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC;

Even though I really like this query, and I have gotten some amazing results with it over the last several years, it does have some limitations. First, it does not always specify the best column order for an index. If there are multiple columns listed under “equality_columns” or “inequality_columns”, you will want to look at the selectivity of each of those columns within the equality and inequality results to determine the best column order for the prospective new index. Second, it does not consider filtered indexes (which are new for SQL Server 2008). It is also overly eager to suggest included columns, and to suggest new indexes in general. Finally, if you make an index change for a particular table, all of the missing index statistics for that table are cleared out (but are repopulated over time). Don’t let this fool you into thinking there are no more missing indexes for a particular table…

You should never just blindly add every index that it suggests (especially if you have an OLTP workload). Instead, you need to examine the results of the query carefully to manually filter out results that are not part of your regular workload. I always look at the last_user_seek column to start with. If the last_user_seek time is from a few days or weeks ago, then the queries that caused SQL Server to want that index are probably from a random ad-hoc query or part of a infrequently run report query. On the other hand, if the last_user_seek time is from a few seconds or a few minutes ago, it is probably part of your regular workload, and you should consider that possible index more carefully. Indexes with a higher index_advantage would have a higher impact on your workload because of the cost of the missing index and/or how often SQL Server thinks it wants that index.

Regardless of what this query recommends, I always look at the existing indexes on a table, including their usage statistics, before I consider making any changes. Don’t forget, the system stored procedure sp_helpindex does not show included column information. Remember, a more volatile table should generally have fewer indexes that a more static table. I generally start to get very hesitant to add a new index to a table (for an OLTP workload) if the table already has more than about five or six effective indexes.