Run it in the database: indexes that duplicate or are covered by another index, and what they cost in writes. It suggests DROP statements but runs none. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
09_Duplicate_and_Overlapping_Indexes.sql - run IN the database: indexes that duplicate or are covered by another
index, and what they cost in writes. Clive Roberts DBA, 10 October 2026. Read-only (it only suggests DROPs).
SQL Server 2016 or later.
WHY: every index is updated on every INSERT, DELETE and (for its columns) UPDATE. A duplicate or overlapping
index costs that write work, log, backup size and memory, for no read benefit.
HOW TO READ IT
match = DUPLICATE same key columns in the same order (and same filter) as another index - one can go.
= OVERLAPPING the index's keys are the leading keys of a wider index that also has all its included
columns - the wider one can do its job.
writes_since_restart rows inserted + updated + deleted in this index since SQL Server started (the cost).
reads_since_restart seeks + scans + lookups that used it (the benefit). A redundant index with reads > 0
is still redundant: those reads would use the wider index instead.
suggestion a DROP statement, commented out. Primary keys, unique indexes and constraints are
never suggested: drop the other index of the pair, or check what relies on uniqueness.
Before dropping: check index hints in code (WITH (INDEX(...))), plan guides and forced Query Store plans, and
keep the CREATE script (Script Index as > CREATE) so it can be put back.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb..#ix') IS NOT NULL DROP TABLE #ix;
SELECT i.object_id, i.index_id, i.name AS index_name, i.type_desc, i.is_primary_key, i.is_unique, i.is_unique_constraint,
i.has_filter, ISNULL(i.filter_definition, N'') AS filter_definition,
keys = STUFF((SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns AS ic JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 AND ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N''),
includes = ISNULL(STUFF((SELECT N', ' + c.name
FROM sys.index_columns AS ic JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N''), N''),
size_mb = (SELECT SUM(ps.used_page_count) / 128 FROM sys.dm_db_partition_stats AS ps WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id)
INTO #ix
FROM sys.indexes AS i
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND OBJECTPROPERTY(i.object_id, 'IsMsShipped') = 0
AND i.type IN (1, 2) AND i.is_hypothetical = 0 AND i.is_disabled = 0;
;WITH pairs AS (
-- DUPLICATE: same keys in the same order, same filter (keep the clustered/PK/unique one, or the lower index_id)
SELECT a.object_id, a.index_id, b.index_id AS other_index_id, N'DUPLICATE' AS match
FROM #ix AS a JOIN #ix AS b ON b.object_id = a.object_id AND b.index_id <> a.index_id
WHERE a.keys = b.keys AND a.filter_definition = b.filter_definition
AND (b.type_desc = N'CLUSTERED' OR b.is_primary_key = 1 OR (b.is_unique = 1 AND a.is_unique = 0)
OR (a.is_unique = b.is_unique AND a.type_desc = b.type_desc AND b.index_id < a.index_id))
UNION ALL
-- OVERLAPPING: a's keys are the leading keys of b, and b carries all of a's included columns
SELECT a.object_id, a.index_id, b.index_id, N'OVERLAPPING'
FROM #ix AS a JOIN #ix AS b ON b.object_id = a.object_id AND b.index_id <> a.index_id
WHERE a.type_desc = N'NONCLUSTERED' AND a.is_unique = 0 AND a.filter_definition = b.filter_definition
AND LEN(b.keys) > LEN(a.keys) AND LEFT(b.keys, LEN(a.keys) + 1) = a.keys + N','
AND (b.type_desc = N'CLUSTERED' -- a clustered index carries every column
OR NOT EXISTS (SELECT 1 FROM sys.index_columns AS ica -- every included column of a is somewhere in b
WHERE ica.object_id = a.object_id AND ica.index_id = a.index_id AND ica.is_included_column = 1
AND NOT EXISTS (SELECT 1 FROM sys.index_columns AS icb
WHERE icb.object_id = b.object_id AND icb.index_id = b.index_id AND icb.column_id = ica.column_id)))
)
SELECT OBJECT_SCHEMA_NAME(p.object_id) + N'.' + OBJECT_NAME(p.object_id) AS [table], p.match,
a.index_name AS redundant_index, a.keys AS redundant_keys, a.includes AS redundant_includes,
b.index_name AS covered_by, b.keys AS covered_by_keys, b.includes AS covered_by_includes,
a.size_mb,
ISNULL(os.writes, 0) AS writes_since_restart,
ISNULL(us.user_seeks + us.user_scans + us.user_lookups, 0) AS reads_since_restart,
us.last_user_seek, us.last_user_scan,
CASE WHEN a.is_primary_key = 1 OR a.is_unique = 1 OR a.is_unique_constraint = 1 OR a.type_desc = N'CLUSTERED'
THEN N'-- keep: ' + CASE WHEN a.is_primary_key = 1 THEN N'primary key' WHEN a.type_desc = N'CLUSTERED' THEN N'clustered index' ELSE N'unique' END
+ N' - consider dropping ' + b.index_name + N' instead'
ELSE N'-- DROP INDEX ' + QUOTENAME(a.index_name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) + N';' END AS suggestion
FROM pairs AS p
JOIN #ix AS a ON a.object_id = p.object_id AND a.index_id = p.index_id
JOIN #ix AS b ON b.object_id = p.object_id AND b.index_id = p.other_index_id
OUTER APPLY (SELECT SUM(leaf_insert_count + leaf_update_count + leaf_delete_count) AS writes
FROM sys.dm_db_index_operational_stats(DB_ID(), p.object_id, p.index_id, NULL)) AS os
LEFT JOIN sys.dm_db_index_usage_stats AS us ON us.database_id = DB_ID() AND us.object_id = p.object_id AND us.index_id = p.index_id
ORDER BY writes_since_restart DESC, [table];
-- Tables with a lot of indexes: each write maintains every one of them
SELECT TOP (20) OBJECT_SCHEMA_NAME(i.object_id) + N'.' + OBJECT_NAME(i.object_id) AS [table],
COUNT(*) AS nonclustered_indexes,
(SELECT SUM(ps.row_count) FROM sys.dm_db_partition_stats AS ps WHERE ps.object_id = i.object_id AND ps.index_id IN (0, 1)) AS table_rows
FROM sys.indexes AS i
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.type = 2
GROUP BY i.object_id
HAVING COUNT(*) >= 8
ORDER BY nonclustered_indexes DESC;
DROP TABLE #ix;