Missing Indexes

SQL Server can be fantastic on occasions and provide golden nuggets of information just waiting to be mined. One such great source of performance tuning gold dust is around missing indexes.

If I run a query such as

Missing index

You can see the missing index hint in green above the actual execution plan. Not only is this information shown here, but SQL Server is continually tracking these missing indexes. This is fantastic, you can now inherit a database you have no prior knowledge of, and still have an historical reference of missing indexes. Unfortunately this information does not persist over a server restart. Don’t be so lazy, nothing worthwhile is ever easy, you would have to persist this information yourself on a regular basis.

This can be queried like so

SELECT migs.unique_compiles ,
migs.unique_compiles ,
migs.user_seeks ,
migs.user_scans ,
migs.last_user_seek ,
migs.last_user_scan ,
migs.avg_total_user_cost ,
migs.avg_user_impact ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mid.index_handle = mig.index_handle
ORDER BY mid.[statement];

This will display

  • Number of seeks and scans
  • Cost information – concentrate on the highest cost first
  • Equality, inequality and included columns
    • Equality typically used in a where clause equality predicate (x=y)
    • Inequality typically used in a where clause inequality predicate (x<>y)
    • Included columns are not used in the where clause, but are referenced elsewhere in the query. Typically these columns are in the select list.
  • The statement i.e. the object affected.

Some points to consider

  • Only non-clustered indexes are suggested and no advanced features such as filtered indexes.
  • Do not just blindly create all suggested indexes. There will be repetition and overlap between suggestions. You will have to use your judgement to decide which indexes to create.
  • Carefully consider the order of the columns you specify when creating the index. Which column is the most selective? Which column is used in the most queries? Which column is used in the most expensive queries?

SQL Server is constantly collecting information on missing indexes. Know how to extract this information and use it to your full advantage.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s