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
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 , mid.[statement] 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.