We have on many occasions looked at query execution plans in the Management Studio. No doubt you have noticed that occasionally a green note appears in the heading of the plan describing a “missing index”. What could be better? SQL Server is telling you what indexes you need! Ah, if it were really only that simple.
SQL Server’s missing index recommendations may be helpful, but they are limited and cannot be considered a substitute for actually thinking. Let’s take a look. We’ll start with a query on the Bigwind sample database where we want all the Employees from Delaware. We’ll also run a related query that only retrieves a subset of the columns from the Employees table.
We see that SQL Server does not make a recommendation for the first query, even though it would benefit from a conventional (i.e. non-covering) index on the Region column. An index is recommended for the second query.
If you take a moment to look at index recommendations that might appear, you will quickly see that the SQL Server query engine essentially makes recommendations for covering indexes and focuses on listing columns in an INCLUDE clause. Other potentially useful indexes are ignored. For example, SQL Server’s missing index feature will not recommend a filtered index.
If we run a query very similar to the one above but include a couple more columns, we get a recommendation for yet another index.
It’s fair to say that after a while SQL Server will have recommended millions of covering indexes (OK, at least a lot); essentially an index for every query that might benefit from a covering index.
You may be aware that when SQL Server makes missing index recommendations it stashes them in its system tables. The saved recommendations can be examined using dynamic management views (DMVs) provided for that purpose. If we look at the listing of index recommendations when SQL Server starts up we will see nothing. SQL Server monitors many aspects of index usage, but this data is not persisted on the hard drive. If you restart the server, you restart with a blank slate as far as index usage information is concerned.
As is often the case with DMVs, we must join two or more to get the information we want in a format the human beings can reasonably interpret. In this case, we join three DMVs, dm_db_missing_index_groups, dm_db_missing_index_group_stats, and dm_db_missing_index_details.
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs
ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
If, just after startup, we run the two queries that generate recommendations, we’ll see two entries in the missing index listing.
You’ll notice that many of the columns that appear in dm_db_missing_index_stats are also to be found in the related DMV dm_db_index_usage_stats. This is very valuable if we would like to seriously examine index recommendations in a production environment. We could, for instance, retrieve the query results in descending order by avg_total_user_cost. Rather than take the time to examine all the recommendations, many of which will be of little value, we can focus on those recommendations associated with queries of higher cost.
SQL Server recommends covering indexes far too often and recommends other types of index often not at all. Missing index recommendations cannot, therefore, be used as a primary method for database tuning. While some recommendations may call our attention to additional possibilities for optimization, it must always be a human administrator who decides which indexes are likely to be worthwhile.