Missing Indexes

I’m back from vacation.  It was wonderful.  Here’s the code I use to help me get a jump on indexes that may need to be created before I get complaints about system performance.

SELECT 
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 find queries like it all over the internet but not necessarily an explanation of what it’s telling you.  The SQL Server DMVs are based on the same concepts used in query plans and query optimization.

sys.dm_db_Missing_Index_Group_Stats – Updated By Every Query Execution

1. Avg_Total_User_Cost – A number representing the cost of queries for which the index could have been used
2. Avg_User_Impact – Percentage by which the average query cost would drop if index was implemented
3. User_Seeks – Number of seeks caused by queries for which this index could have been used
4. User_Scans – Number of scans caused by queries for which this index could have been used

sys.dm_db_Missing_Index_Details – Updated Every Time Query is Optimized by the Query Optimizer

1. Statement – Table where the index is missing
2. Equality_Columns – Columns used in equality predicates (Column=’a’)
3. Inequality_Columns – Columns used in a predicate that’s anything except equality such as >
4. Included_Columns – Columns need to cover the query
5. Database_ID – Database
6. Object_ID – Table

The higher the improvement_measure the greater the possibility for improvement.  As always with indexes make sure you look at all of the pros and cons for the index.