Find Missing indexes

 Review create the script before implementing, this will show indexes needed with improvement percentage

/*
Returns a list of missing indexes that SQL Server believes (if added)
would result in the highest anticipated cumulative improvement
based on query execution since the last restart
*/
SELECT
CAST((migs.avg_total_user_cost * migs.avg_user_impact) *
(migs.user_seeks + migs.user_scans) AS DECIMAL(20,3)) “ImpactFactor”,
statement “Table”,
N’CREATE NONCLUSTERED INDEX ix_’ +
sys.objects.name COLLATE DATABASE_DEFAULT + ” + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”) + ISNULL(mid.inequality_columns,”), ‘[‘, ”), ‘]’,”), ‘, ‘,”) +
‘ ON ‘ + [statement] + ‘ ( ‘ + IsNull(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 + ‘;’ “CreateIndexStatement”,
migs.avg_user_impact “UserImprovement(%)”,
migs.avg_system_impact “SystemImprovement(%)”,
migs.user_scans “UserScans”,
migs.system_scans “SystemScans”,
migs.user_seeks “UserSeeks”,
migs.system_seeks “SystemSeeks”,
mid.equality_columns “EqualityColumns”,
mid.inequality_columns “InequalityColumns”,
mid.included_columns “IncludedColumns”
FROM
sys.dm_db_missing_index_group_stats migs
INNER JOIN
sys.dm_db_missing_index_groups mig
ON
migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid
ON
mig.index_handle = mid.index_handle
INNER JOIN
sys.objects WITH (nolock)
ON
mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (
migs.group_handle IN (
SELECT TOP (500) 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))
AND
OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’) = 1
ORDER BY
ImpactFactor DESC,
CreateIndexStatement DESC;