Create Missing Indexes

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.

[sql]

/****************************************************************/
/* 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

[/sql]