I was recently having a discussion with a friend of mine about SQL Server indexes. As someone who takes great pleasure in optimizing the heck out of previously-sluggish queries, I love indexes. At least for an OLTP environment, the initial performance hit caused by indexes during a record’s creation/modification rarely seems noticeable, but the benefit you can get later on when pulling data can be quite impressive.

In this post, I’m included a script I’ve gotten quite a bit of mileage out of. Hopefully it’ll help others as well.

 

 

 
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + 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 1 DESC

 

 

The field on the left, called “improvement_measure”, is largely just a hand-wavy value without a unit of measure. The cost and impact used in its calculation are numbers from the optimizer. The important thing to remember is that the larger the number, the more helpful that index would have been.

Some basic common sense needs to be applied with the results, though. On one of the systems I’ve used it on, it constantly suggests an index that I know for a fact already exists. Also, there might be indexes suggested that — once created — remove the need for some of the other indexes.

Still, though, it’s a very handy script to speed up the identification of indexes needing created. If you have suggestions for tweaks to this code, please let me know in the comments.