Optimize index structure: How to find duplicate indexes?

This is the fourth post in a serie about optimizing your index structure.

Previous posts in this serie:

1. Which indexes are not used in my database? Index usage statistics and disk space usage.
2. Defragmentation clustered indexes.
3. Online nonclustered index defragmentation.

Every database will have some indexes on a table to retrieve information efficiently. After a long period it can happen that different people have added indexes to tables which already exists. This is not usefull and results in unnecessary overhead, for example disk space usage and performance on updates, inserts and or deletes.
With next script you can retrieve all double indexes in your database.


CREATE VIEW vw_index_list AS
SELECT tbl.[name] AS TableName,
idx.[name] AS IndexName,
INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,
INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,
INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,
INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,
INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,
INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,
INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,
INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,
INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,
INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,
INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,
INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,
dpages,
used,
rowcnt
FROM SYSINDEXES idx
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0

GO

SELECT l1.tablename,
l1.indexname,
l2.indexname AS duplicateIndex,
l1.col1, l1.col2, l1.col3, l1.col4, l1.col5, l1.col6, l1.col7, l1.col8,
l1.col9, l1.col10, l1.col11, l1.col12, l1.col13, l1.col14, l1.col15, l1.col16,
l1.dpages,l1.used,l1.rowcnt
FROM vw_index_list l1
INNER JOIN vw_index_list l2 ON l1.tablename = l2.tablename
AND l1.indexname <> l2.indexname
AND l1.col1 = l2.col1
AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')
AND COALESCE(l1.col6,'') = COALESCE(l2.col6,'')
AND COALESCE(l1.col7,'') = COALESCE(l2.col7,'')
AND COALESCE(l1.col8,'') = COALESCE(l2.col8,'')
AND COALESCE(l1.col9,'') = COALESCE(l2.col9,'')
AND COALESCE(l1.col10,'') = COALESCE(l2.col10,'')
AND COALESCE(l1.col11,'') = COALESCE(l2.col11,'')
AND COALESCE(l1.col12,'') = COALESCE(l2.col12,'')
AND COALESCE(l1.col13,'') = COALESCE(l2.col13,'')
AND COALESCE(l1.col14,'') = COALESCE(l2.col14,'')
AND COALESCE(l1.col15,'') = COALESCE(l2.col15,'')
AND COALESCE(l1.col16,'') = COALESCE(l2.col16,'')
ORDER BY
l1.tablename,
l1.indexname

GO

DROP VIEW Vw_index_list