/*1)Below query is to Findout the Missing Indexes on the tables.

This will generate the Create Index script based on the queries that are executed on the server by checking the columns

used in equal/inequal /where clause. */

SELECT statement, migroupsstats.avg_total_user_cost as AvgTotalUserCostThatCouldbeReduced, migroupsstats.avg_user_impact as AvgPercentageBenefit,

'CREATE INDEX missing_IX_' + CONVERT (varchar, object_name(mid.object_id))

+ CASE WHEN mid.equality_columns IS NOT NULL THEN '_' ELSE '' END

+ ISNULL (replace(replace(replace(mid.equality_columns,', ','_'),']',''),'[',''),'')

+ CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END

+ ISNULL (replace(replace(replace(mid.inequality_columns,', ','_'),']',''),'[',''), '')

+ ' 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, '')+ ')'

+ CASE WHEN mid.included_columns IS NOT NULL THEN + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') ELSE

'' END AS create_index_statement

FROM sys.dm_db_missing_index_groups migroups

INNER JOIN sys.dm_db_missing_index_group_stats migroupsstats ON migroupsstats.group_handle = migroups.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON migroups.index_handle = mid.index_handle

order by 1

Srihari Nandamuri