The following script displays a list of missing SQL indexes – and the potential impact of creating them. The code to generate the index in also shown.

/****************************************************************/
/* Copyright pfp Solutions Ltd 2010                             */
/* https://pfp-solutions.com/                                    */
/* Can be reproduced intact                                     */
/*                                                              */
/* Produces a list of missing indexes and the potential impact  */
/****************************************************************/

/* Gives weight for missing indexes - 5,000 to 10,000 ?. >10,000 use */

select *,
'CREATE INDEX IXPA' +
replace(replace(replace(replace(isnull(equality_columns,'') + isnull(inequality_columns,''),',',''),'[','_'),']',''),' ','')
+ ' ON ' + Statement +
'(' + isnull(equality_columns,'') +
case when equality_columns is not null then ',' else '' end
+ isnull(inequality_columns,'') + ')'
from
(select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage,
migs.* from sys.dm_db_missing_index_group_stats migs) as migs_adv
inner join sys.dm_db_missing_index_groups as mig on migs_adv.group_handle=
mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid on mig.index_handle=mid.index_handle
where statement like '%' + db_name() + '%'
and index_advantage>10000
order by migs_adv.index_advantage DESC