Did you know that your SQL Server is keeping track of the indexes that it thinks you should create? The “missing index” DMVs in SQL are a really great new feature in SQL Server 2005 that (in my opinion) seem to have been underutilized so far. If you want to see if this feature can spare you the tedium of an afternoon identifying poor performing queries and tuning them, all you have to do is ask:
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
You’ll want to run this after your server has been up and running a normal workload for a while. If this returns no results, that’s good news and indicates that you’re not missing any indexes that are obvious enough for the DMV to detect. If it does return some suggestions, even better: you just improved your server’s perf with almost no work.
While to me this feature is so cool it almost seems magical, it does have a few limitations you should be aware of:
It’s not as smart as the Database Engine Tuning Advisor. If you have identified a query that you know is expensive and needs some help, don’t pass up DTA just because the missing index DMVs didn’t have any suggestions. DTA might still be able to help.
The missing index DMVs don’t take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
The “improvement_measure” column in this query’s output is a rough indicator of the (estimated) improvement that might be seen if the index was created. This is a unitless number, and has meaning only relative the same number for other indexes. (It’s a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
The missing index DMVs don’t make recommendation about whether a proposed index should be clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries. (DTA can do this, however.)
Won’t recommend partitioning.
It’s possible that the DMVs may not recommend the ideal column order for multi-column indexes.
The DMV tracks information on no more than 500 missing indexes.
If you’re a typical SQL user, you may not be using these DMVs yet. If you look around, though, there are a few places where they are in use. One is in the SP2 Performance Dashboard reports. Another is the Perf Stats Script that SQL PSS uses. And if you think the missing index DMVs are useful, check out this set of scripts that builds on the missing index DMVs to simulate an “auto create index” feature. Also, you should be aware there is similar missing index info output in the new XML showplan format in SQL 2005. If you are already focused on a poorly-performing query, I would start with the plan view of missing indexes (followed by DTA) rather than the DMVs.