Earlier I had posted a script about Unused Indexes and I thought it would be good idea to write on duplicate indexes as well.

SQL Server allows indexes to the created on same columns with different index names. There are great chances that developers working in different modules of the application code tend to create indexes on same KEY COLUMNS but with different index names.

When the query is executed the query plan selects the index based on the latest metadata of the index. Hence a newly created index gets selected against a similar previously created index. Hence the old index is an unused and is a storage and maintenance overhead.

Below script identifies the duplicate indexes based on Key columns.

About Script:
* This script is written to execute on all the databases of the instance. But if you need to execute on
any particular database, then pick the code in the block assigned to the variable @sql.
* It will look for KEY COLUMNS which have exact match or columns contained in another index.
* Included Columns are listed but not used in the search criteria.
* Copy them over to a spreadsheet and take best decision depending on your environment.

/****Finding Duplicate Indexes ***/
--Create Temp Table
IF OBJECT_ID('Tempdb.dbo.Index_Duplicates') IS NOT NULL
DROP TABLE TempDB.dbo.Index_Duplicates
CREATE TABLE TempDB.dbo.Index_Duplicates (DBName VARCHAR(200), Schemaname VARCHAR(100), 
             TableName VARCHAR(200), Indexname VARCHAR(300), Index_Type VARCHAR(100), 
             KeyCols VARCHAR(500), InlcudeCols VARCHAR(600))
--Build the query (Use the below code block to find duplicate indexes for a single database)
DECLARE @sql VARCHAR(max)
SET @sql =
'USE [?];
IF DB_ID(''?'')>4
    SELECT ''?'' AS DBName, Sch.name as SchemaName, Tab.[name] AS TableName,
           Ind.[name] AS IndexName, Ind.[type_desc] AS IndexType,
           SUBSTRING(( SELECT '', '' + AC.name FROM sys.[tables] AS T
			INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
			INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id] 
			INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
			WHERE Ind.[object_id] = I.[object_id]
			AND Ind.index_id = I.index_id AND IC.is_included_column = 0
			ORDER BY IC.key_ordinal
			FOR XML PATH('''') ), 2, 8000) AS KeyCols,
		SUBSTRING(( SELECT '', '' + AC.name FROM sys.[tables] AS T
			INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
			INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                           AND I.[index_id] = IC.[index_id]
			INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
                           AND IC.[column_id] = AC.[column_id]
			WHERE Ind.[object_id] = I.[object_id]
                           AND Ind.index_id = I.index_id AND IC.is_included_column = 1
			ORDER BY IC.key_ordinal
			FOR XML PATH('''') ), 2, 8000) AS IncludeCols
    FROM sys.[indexes] Ind
    INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
    INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
    WHERE Ind.[name] IS NOT NULL
    ORDER BY TableName'
--Insert values for all databases
INSERT INTO TempDB.dbo.Index_Duplicates
EXEC sp_MSforeachdb @sql
--Find the duplicate indexes having exactly similar key columns.
SELECT * from TempDB.dbo.Index_Duplicates id1
WHERE EXISTS
(SELECT * FROM TempDB.dbo.Index_Duplicates id2
WHERE id1.DBName = id2.DBName
	AND id1.schemaname = id2.schemaname
	AND id1.tablename = id2.tablename
--and id1.KeyCols = id2.KeyCols
	AND (id1.KeyCols LIKE LEFT(id2.KeyCols, LEN(id1.KeyCols)) OR id2.KeyCols LIKE LEFT(id1.KeyCols, LEN(id2.KeyCols)))
	AND id1.indexname <> id2.indexname)

Leave a comment if it has helped you or if you need help with it.

Note: As with any scripts you find online, test it in your test environments and confirm it work for you before deploying in production.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.