Using the DMV sys.dm_db_missing_index_details to identify and create “missing” indexes could be doing you more harm than good. If you read Microsoft books online carefully for information on this DMV, you’ll notice that under the remarks it states that data in the DMV is “updated when a query is optimized by the query optimizer”. Ok, big deal, who cares? You should! Why? Because every single query is passed through the query optimizer, and if it believes an index would be useful whilst optimizing the query, a record will be placed in sys.dm_db_missing_index_details.
What this means is that someone could have run a simple one-time query, with no intention of ever running it again, but a record will be placed into the DMV.
With that in mind, you should never, ever, blindly create an index suggested by this DMV. In doing so, you run the risk of solving a problem that isn’t really there. Having the extra, unnecessary, index will consume valuable overhead whilst performing maintenance tasks and slow the insertion of new data.
So, what is this DMV used for? It’s still a great little tool in helping identify useful indexes. If you’re working on a query and believe an index could improve your performance, it may be worth checking in here to see if many other queries could have been helped by the same index.
One of the most useful things you can do with dm_db_missing_index_details, is combine it with dm_db_missing_index_columns, allowing you to apply aggregate functions with ease and see exactly which columns are being utilized heavily and may require an index. I’ve written the below script which does exactly that:
SELECT TableName, ColumnName, INCLUDE, EQUALITY, INEQUALITY FROM ( SELECT D.statement [TableName], C.column_name [ColumnName], C.column_name, C.column_usage FROM sys.dm_db_missing_index_details D CROSS APPLY sys.dm_db_missing_index_columns(D.index_handle) C ) Cols PIVOT ( COUNT(column_name) FOR column_usage IN ([INCLUDE], [EQUALITY], [INEQUALITY]) ) PVT ORDER BY EQUALITY DESC
Running the above query will give you every single column in the instance you run it against which has, in one way or another, been identified by the query optimizer as requiring an index. The more useful part of the query is the count within the pivot, this is allows you to see how many times the optimizer has deemed an index was required, and for what purpose.
It’s important to remember that data is cleared down from this DMV every time the instance is restarted, so it has next to no use for a short while after a restart. This again plays a part in making this DMV a dangerous indicator of missing indexes.