how to find out missing index & duplicate index?

hi,

how find out missing index & duplicate index? want prepare report of both this. please me.

hi maur,

have @ script http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

select top 25  dm_mid.database_id databaseid,  dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) avg_estimated_impact,  dm_migs.last_user_seek last_user_seek,  object_name(dm_mid.object_id,dm_mid.database_id) [tablename],  'create index [ix_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'  + replace(replace(replace(isnull(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +  case  when dm_mid.equality_columns not null , dm_mid.inequality_columns not null '_'  else ''  end  + replace(replace(replace(isnull(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')  + ']'  + ' on ' + dm_mid.statement  + ' (' + isnull (dm_mid.equality_columns,'')  + case when dm_mid.equality_columns not null , dm_mid.inequality_columns not null ',' else  '' end  + isnull (dm_mid.inequality_columns, '')  + ')'  + isnull (' include (' + dm_mid.included_columns + ')', '') create_statement  sys.dm_db_missing_index_groups dm_mig  inner join sys.dm_db_missing_index_group_stats dm_migs  on dm_migs.group_handle = dm_mig.index_group_handle  inner join sys.dm_db_missing_index_details dm_mid  on dm_mig.index_handle = dm_mid.index_handle  dm_mid.database_id = db_id()  order avg_estimated_impact desc  go

unused indexes same post

-- unused index script -- original author: pinal dave (c) 2011 -- http://blog.sqlauthority.com select top 25 o.name objectname , i.name indexname , i.index_id indexid   , dm_ius.user_seeks userseek , dm_ius.user_scans userscans , dm_ius.user_lookups userlookups , dm_ius.user_updates userupdates , p.tablerows , 'drop index ' + quotename(i.name)  + ' on ' + quotename(s.name) + '.' + quotename(object_name(dm_ius.object_id)) 'drop statement' sys.dm_db_index_usage_stats dm_ius   inner join sys.indexes on i.index_id = dm_ius.index_id , dm_ius.object_id = i.object_id    inner join sys.objects o on dm_ius.object_id = o.object_id inner join sys.schemas s on o.schema_id = s.schema_id inner join (select sum(p.rows) tablerows, p.index_id, p.object_id  				from sys.partitions p group p.index_id, p.object_id) p  		on p.index_id = dm_ius.index_id , dm_ius.object_id = p.object_id objectproperty(dm_ius.object_id,'isusertable') = 1 , dm_ius.database_id = db_id()    , i.type_desc = 'nonclustered' , i.is_primary_key = 0 , i.is_unique_constraint = 0 order (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) asc go

note:- not directly drop indexes, if indexes unique can used indirectly , if index rebuild happens row removed usage stats dmv

---

- chintak (my blog)

SQL Server  >  SQL Server Database Engine