Hungry DBA .com for DBAs who like food

The following query can be used to find candidates for creating new required indexes to optimise performance within a SQL Server database.

SELECT
DISTINCT
sys.objects.name, sys.partitions.rows, migs.user_seeks,migs.avg_total_user_cost,migs.avg_user_impact,
'CREATE NONCLUSTERED INDEX <NewNameHere> ON ' + sys.objects.name + ' ( ' + mid.equality_columns +
CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ' with (online =ON, maxdop = 2, sort_in_tempdb = ON ) on IndexFileGroup ;'
COLLATE DATABASE_DEFAULT AS CreateIndexStatement , 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 migs.group_handle = mig.index_group_handle INNER JOIN
sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle INNER JOIN
sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id INNER JOIN
sys.partitions on sys.objects.object_id = sys.partitions.object_id
WHERE
migs.group_handle IN
(SELECT TOP (10) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC )
order by
--migs.user_seeks desc
--migs.avg_total_user_cost desc
migs.avg_user_impact desc

The query uses the dynamic view dm_db_missing_index_group_stats to calculate which of the index recommendations produced in the view dm_db_missing_index_details will have the greater impact on performance.

Feel free to change the commented out ORDER by clauses to assess the impact by different criteria.

More information regarding the relevant columns in dm_db_missing_index_group_stats is available from here

Remember that the art of creating new indexes to optimise performance is never an exact science.  The index recommendations produced by this script are just candidates for new indexes and you should make your own judgement and perform your own tests to see how performance will actually be impacted by the new indexes.