Identifying and Removing Duplicate Indexes in SQL Server

In an earlier post, I mentioned I was looking for ways to simplify my performance tuning. One of the approaches I’ve taken is finding exact duplicate indexes. Duplicate indexes are a problem because any time you insert, update, or delete a row in that table, SQL Server has to update the same index more than once. I don’t like doing the same task twice, so I don’t want SQL Server to have to do it either.

Why Duplicate Indexes Are Problematic

For this post, a duplicate is an index with the same key columns, in the same order and sort direction, and the same included columns as another index on the same table. That’s the definition the query below uses.

Issues caused by duplicate indexes include:

• Storage: Each index takes disk space, so a duplicate is space you don’t need.
• Slower writes: Every INSERT, UPDATE, and DELETE has to maintain both copies.
• Longer maintenance: Index rebuilds and statistics updates have one more index to work through.

The Script

This is the query I use to find exact duplicate indexes in a database. Running it is straightforward, and I’ve broken it into parts below for anyone who wants to see how it works.

;WITH CTE_IndexData
AS (
	SELECT s.name AS SchemaName
		,t.name AS TableName
		,i.name AS IndexName
		-- Key columns of the index
		,STUFF((
				SELECT ', ' + c.name + ' ' + CASE 
						WHEN ic.is_descending_key = 1
							THEN 'DESC'
						ELSE 'ASC'
						END
				FROM sys.index_columns ic
				INNER JOIN sys.columns c ON ic.object_id = c.object_id
					AND ic.column_id = c.column_id
				WHERE ic.object_id = i.object_id
					AND ic.index_id = i.index_id
					AND ic.is_included_column = 0
				ORDER BY ic.key_ordinal
				FOR XML PATH('')
					,TYPE
				).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS KeyColumnList
		-- Included columns of the index
		,STUFF((
				SELECT ', ' + c.name
				FROM sys.index_columns ic
				INNER JOIN sys.columns c ON ic.object_id = c.object_id
					AND ic.column_id = c.column_id
				WHERE ic.object_id = i.object_id
					AND ic.index_id = i.index_id
					AND ic.is_included_column = 1
				ORDER BY ic.key_ordinal
				FOR XML PATH('')
					,TYPE
				).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IncludeColumnList
		,i.is_disabled
		-- Generate the DROP INDEX statement
		,'DROP INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + ']' AS DropIndexStatement
	FROM sys.indexes i
	INNER JOIN sys.tables t ON i.object_id = t.object_id
	INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
	WHERE t.is_ms_shipped = 0
		AND i.type_desc IN (
			'NONCLUSTERED'
			,'CLUSTERED'
			)
	)
SELECT d1.SchemaName
	,d1.TableName
	,d1.IndexName
	,d1.KeyColumnList
	,d1.IncludeColumnList
	,d1.is_disabled
	,d1.DropIndexStatement
FROM CTE_IndexData d1
WHERE EXISTS (
		SELECT 1
		FROM CTE_IndexData d2
		WHERE d1.SchemaName = d2.SchemaName
			AND d1.TableName = d2.TableName
			AND d1.KeyColumnList = d2.KeyColumnList
			AND ISNULL(d1.IncludeColumnList, '') = ISNULL(d2.IncludeColumnList, '')
			AND d1.IndexName <> d2.IndexName
		)
ORDER BY d1.SchemaName
	,d1.TableName
	,d1.KeyColumnList;

How It Works

Gathering Basic Index Information

First, I use a Common Table Expression (CTE) called CTE_IndexData to gather some basic information about the indexes that already exist:

SELECT s.name AS SchemaName
    ,t.name AS TableName
    ,i.name AS IndexNam

Retrieve Key Columns

Next, I create a comma-separated list of the key columns for each index, including the sort order (ASC/DESC). A subquery concatenates the column names and sort orders into a single string using the FOR XML PATH('') trick, and STUFF removes the leading comma and space.

,STUFF((
    SELECT ', ' + c.name + ' ' + CASE 
            WHEN ic.is_descending_key = 1
                THEN 'DESC'
            ELSE 'ASC'
            END
    FROM sys.index_columns ic
    INNER JOIN sys.columns c ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.is_included_column = 0
    ORDER BY ic.key_ordinal
    FOR XML PATH('')
        ,TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS KeyColumnList

Retrieve Included Columns

This creates a comma-separated list of included columns for each index. This works similarly to the key columns retrieval but filters on is_included_column = 1.

,STUFF((
    SELECT ', ' + c.name
    FROM sys.index_columns ic
    INNER JOIN sys.columns c ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.is_included_column = 1
    ORDER BY ic.key_ordinal
    FOR XML PATH('')
        ,TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IncludeColumnList

Drop Index Statement

This part is just to script out the DROP INDEX statement. It has nothing to do with finding duplicates. It was just a “nice to have.”

,i.is_disabled
,'DROP INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + ']' AS DropIndexStatement

Find the Duplicates in my Results

Lastly, the main query finds the duplicates by comparing key and included columns. It joins the CTE_IndexData to itself, as d1 and d2, so we can compare indexes within the same table.

Here’s how it works:

• Different Index Names: d1.IndexName <> d2.IndexName stops an index from matching itself.
• Same Schema and Table: Both sides have to be on the same table.
• Same Key Columns: d1.KeyColumnList = d2.KeyColumnList. The list includes sort order, so an ASC and a DESC index on the same column don’t match.
• Same Included Columns: ISNULL(d1.IncludeColumnList, '') = ISNULL(d2.IncludeColumnList, ''), so two indexes with no included columns compare as equal instead of as NULL against NULL.

SELECT d1.SchemaName
    ,d1.TableName
    ,d1.IndexName
    ,d1.KeyColumnList
    ,d1.IncludeColumnList
    ,d1.is_disabled
    ,d1.DropIndexStatement
FROM CTE_IndexData d1
WHERE EXISTS (
    SELECT 1
    FROM CTE_IndexData d2
    WHERE d1.SchemaName = d2.SchemaName
        AND d1.TableName = d2.TableName
        AND d1.KeyColumnList = d2.KeyColumnList
        AND ISNULL(d1.IncludeColumnList, '') = ISNULL(d2.IncludeColumnList, '')
        AND d1.IndexName <> d2.IndexName
    )
ORDER BY d1.SchemaName
    ,d1.TableName
    ,d1.KeyColumnList;

Limitations

There are a number of things this query doesn’t check for, so take precautions when dropping any index. When deciding which of the pair to drop, it’s best to drop the one with fewer reads, which this query shows:

SELECT o.name AS TableName
    ,i.name AS IndexName
    ,i.type_desc AS IndexType
    ,u.user_seeks AS UserSeeks
    ,u.user_scans AS UserScans
    ,u.user_lookups AS UserLookups
    ,u.user_updates AS UserUpdates
    ,u.last_user_seek AS LastUserSeek
    ,u.last_user_scan AS LastUserScan
    ,u.last_user_lookup AS LastUserLookup
    ,u.last_user_update AS LastUserUpdate
FROM sys.dm_db_index_usage_stats AS u
INNER JOIN sys.indexes AS i ON u.object_id = i.object_id
    AND u.index_id = i.index_id
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
WHERE u.database_id = DB_ID()
    --AND o.name = 'Badges' -- Filter by table name
ORDER BY o.name
     ,i.name;

Before you trust those numbers, check how long the server has been up. The sys.dm_db_index_usage_stats documentation says “the counters are initialized to empty whenever the database engine is started,” so an index that looks unused might be one nobody has needed since the last reboot. SELECT sqlserver_start_time FROM sys.dm_os_sys_info tells you the window you’re actually looking at. A monthly report that runs on the first is invisible in three days of data, and if you drop its index because the counters say zero, you’ll find out about the report when it next runs.

A restart isn’t the only thing that clears them. The same page says “whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.” So on a database quiet enough for AUTO_CLOSE to keep closing it, the counters can be empty no matter how long the instance has been up, and the uptime check won’t show it.

The query also doesn’t check whether either index is unique, or compare compression or fill factor, just to name a few, so check those yourself. A unique index needs the most care, since it enforces a constraint as well as serving reads, and dropping it changes what the table allows rather than just how it performs.

As always, test changes in a non-production environment before making them in production. In other words, don’t ruin your Friday.

Before dropping an index, script out its CREATE INDEX statement and keep it somewhere, so putting it back doesn’t depend on remembering the definition.