Friday, September 19, 2008

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,
INNER JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]
WHERE indid > 0
AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0


SELECT l1.tablename,
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,
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,'')


DROP VIEW Vw_index_list


Ronald Voets said...

Also interesting is not 100% duplicate indexes but indexes that largely share the same frst set of columns. A query to retrieve those can be found on i.e.

Anonymous said...

I really like when people are expressing their opinion and thought. So I like the way you are writing