Find Missing, Unused, Duplicate Indexes

--------------- find missing indexes-------------------------------------------------------------------------------------------------------------

SELECT top 100
[Impact] = (avg_total_user_cost * (avg_user_impact/100.00)) * (user_seeks + user_scans),
[Table] = [statement], --sys.objects.name,
[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX UIX_'
  --+ 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
  + ';',
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

-----------------------find unused indexes---------------------------------------------------------------------------------------------------------

SELECT
o.name
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
--AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads, o.name

----------------------------------find full duplicate indexes-------------------------------------------------------------------------------------------------
set transaction isolation level read uncommitted;
set lock_timeout 20000;
set implicit_transactions off;
if @@trancount > 0 commit transaction;
set language us_english;
set cursor_close_on_commit off;
set query_governor_cost_limit 0;
set numeric_roundabort off;
set deadlock_priority low;
set nocount on;
declare @dbid int;
select @dbid = db_id();

--------------------------------------- return full duplicate index information-----------------------------------------------
;with indexcols as
(
select
  object_id,
  index_id,
  name,
  is_unique,
  (select cast(c.column_id as varchar) + case c.is_descending_key when 1 then 'D' else 'A' end as [data()] -- pull key columns
   from sys.index_columns as c
   where c.object_id = i.object_id
   and c.index_id = i.index_id
            and c.is_included_column = 0
   order by c.key_ordinal, c.column_id
   for xml path('')) as cols,
  isnull((select c.column_id as [data()] -- pull included columns
   from sys.index_columns as c
   where c.object_id = i.object_id
   and c.index_id = i.index_id
            and c.is_included_column = 1
   order by c.column_id
   for xml path('')), '') as inc
  from sys.indexes as i
   where is_disabled = 0
   and is_hypothetical = 0
   and i.type < 3 -- do not include xml/spatial indexes
   and objectproperty(i.object_id,'IsMSShipped') = 0
   and objectproperty(i.object_id,'IsUserTable') = 1
   and (indexproperty(i.object_id, i.name, 'IsFulltextKey') <> 1)
)
select 'full',
db_name() AS DatabaseName,
c1.object_id,
object_name(c1.object_id) AS TableName,
c1.name AS IndexName,
c1.index_id,
c1.is_unique,
(select user_seeks+user_scans+user_lookups from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c1.object_id and index_id = c1.index_id) AS IndexUsage,
(select user_updates from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c1.object_id and index_id = c1.index_id) AS IndexUpdates,
(select count(*) from sys.foreign_keys fk where fk.referenced_object_id = c1.object_id and fk.key_index_id = c1.index_id) AS IndexForeignKeys,
c2.name AS DupIndexName,
c2.index_id AS DupIndexId,
c2.is_unique As DupIndexUnique,
(select user_seeks+user_scans+user_lookups from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c2.object_id and index_id = c2.index_id) AS DupIndexUsage,
(select user_updates from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c2.object_id and index_id = c2.index_id) AS DupIndexUpdates,
(select count(*) from sys.foreign_keys fk where fk.referenced_object_id = c2.object_id and fk.key_index_id = c2.index_id) AS DupIndexForeignKeys
from indexcols as c1
join indexcols as c2
  on c1.object_id = c2.object_id
  and c1.index_id < c2.index_id
  and c1.cols = c2.cols and c1.inc = c2.inc;

----------------------------------find partial duplicate indexes-------------------------------------------------------------------------------------------------

set transaction isolation level read uncommitted;
set lock_timeout 20000;
set implicit_transactions off;
if @@trancount > 0 commit transaction;
set language us_english;
set cursor_close_on_commit off;
set query_governor_cost_limit 0;
set numeric_roundabort off;
set deadlock_priority low;
set nocount on;
declare @dbid int;
select @dbid = db_id();

------------------------------------ return partial duplicate index information---------------------------------------------------------------
;with indexcols as
(
select
  object_id,
  index_id,
  name,
  is_unique,
  (select cast(c.column_id as varchar) + case c.is_descending_key when 1 then 'D' else 'A' end as [data()] -- pull key columns
   from sys.index_columns as c
   where c.object_id = i.object_id
   and c.index_id = i.index_id
            and c.is_included_column = 0
   order by c.key_ordinal, c.column_id
   for xml path('')) as cols
  from sys.indexes as i
   where is_disabled = 0
   and is_hypothetical = 0
   and i.type < 3 -- do not include xml/spatial indexes
   and objectproperty(i.object_id,'IsMSShipped') = 0
   and objectproperty(i.object_id,'IsUserTable') = 1
   and (indexproperty(i.object_id, i.name, 'IsFulltextKey') <> 1)
)
select
'partial',
db_name() AS DatabaseName,
c1.object_id,
object_name(c1.object_id) AS TableName,
c1.name AS IndexName,
c1.index_id,
(select user_seeks+user_scans+user_lookups from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c1.object_id and index_id = c1.index_id) AS IndexUsage,
(select user_updates from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c1.object_id and index_id = c1.index_id) AS IndexUpdates,
LEN(c1.cols) AS IndexKeySize,
c1.is_unique AS IndexUnique,
    (select count(*) from sys.foreign_keys fk where fk.referenced_object_id = c1.object_id and fk.key_index_id = c1.index_id) AS IndexForeignKeys,
c2.name AS DupIndexName,
(select user_seeks+user_scans+user_lookups from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c2.object_id and index_id = c2.index_id) AS DupIndexUsage,
(select user_updates from sys.dm_db_index_usage_stats where database_id = @dbid and object_id = c2.object_id and index_id = c2.index_id) AS DupIndexUpdates,
c2.index_id AS DupIndexId,
c2.is_unique AS DupIndexUnique,
LEN(c2.cols) AS DupIndexKeySize,
(select count(*) from sys.foreign_keys fk where fk.referenced_object_id = c2.object_id and fk.key_index_id = c2.index_id) AS DupIndexForeignKeys
from indexcols as c1
join indexcols as c2
  on c1.object_id = c2.object_id
  and c1.is_unique = c2.is_unique
  and c1.index_id < c2.index_id
        and (c1.cols <> c2.cols)
  and ((c1.cols like c2.cols + '%' and SUBSTRING(c1.cols,LEN(c2.cols)+1,1) = ' ')
  or (c2.cols like c1.cols + '%' and SUBSTRING(c2.cols,LEN(c1.cols)+1,1) = ' '))
  ORDER BY [DatabaseName] DESC, [TableName] DESC