Index bloat is one of the most common and most ignored performance problems in SQL Server environments. Not missing indexes — those make noise and get noticed. Duplicate and unused indexes sit quietly in the background, costing you write performance and storage every single day, while contributing nothing to read performance.

Most production SQL Servers have them. Most DBAs know they probably have them. The problem is proving it to a manager or a change advisory board well enough to justify touching a production index.

This post gives you the queries, the reasoning, and the decision framework to find duplicate indexes, validate they are actually duplicates and not doing something subtle, confirm usage, and remove them safely.

Why Index Bloat Happens

SQL Server does not remove indexes automatically. Developers add indexes to fix slow queries. DBAs add indexes in response to missing index recommendations from the query optimiser. Migrations bring in indexes from old schemas that no longer reflect the current workload. Over years, indexes accumulate.

The typical pattern: a new developer runs sp_BlitzIndex or looks at the missing index DMVs, sees a recommendation, creates the index without checking whether something similar already exists. Three months later, a different developer does the same thing. Now you have three indexes on the same table covering nearly the same columns, slightly differently ordered.

What Duplicate Indexes Actually Cost

The cost is concrete:

Write overhead. Every INSERT, UPDATE, and DELETE on a table must update every non-disabled index on that table. An extra duplicate index on a heavily-written table is not free — it adds I/O, log writes, and lock contention on every write operation. On tables with thousands of inserts per minute, this adds up.

Storage. An index is a copy of (some of) the table’s data in a specific order. A large duplicate index on a 200GB table can consume tens of gigabytes of unnecessary storage.

Query optimiser confusion. More indexes means the optimiser has more choices to evaluate. In practice, this rarely causes serious problems — the optimiser is good at its job. But duplicate indexes with slightly different column orders can lead to unexpected plan choices.

Maintenance overhead. Index rebuilds and reorganisations run against every index. Maintaining duplicate indexes means more maintenance time, more I/O during maintenance windows, and more fragmentation to manage.

Finding Duplicate Indexes

Two indexes are truly duplicate if they have the same key columns in the same order and the same included columns. This query finds them:

-- Find indexes with identical key columns and included columns
WITH KeyCols AS (
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(
            c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END,
            ', '
        ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
    WHERE ic.is_included_column = 0
    GROUP BY ic.object_id, ic.index_id
),
IncludeCols AS (
    SELECT
        ic.object_id,
        ic.index_id,
        STRING_AGG(c.name, ', ')
            WITHIN GROUP (ORDER BY ic.index_column_id) AS included_columns
    FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id
        AND ic.column_id = c.column_id
    WHERE ic.is_included_column = 1
    GROUP BY ic.object_id, ic.index_id
),
IndexColumns AS (
    SELECT
        i.object_id,
        i.index_id,
        i.name AS index_name,
        i.type_desc,
        i.is_unique,
        i.is_primary_key,
        k.key_columns,
        inc.included_columns
    FROM sys.indexes i
    LEFT JOIN KeyCols k ON i.object_id = k.object_id AND i.index_id = k.index_id
    LEFT JOIN IncludeCols inc ON i.object_id = inc.object_id AND i.index_id = inc.index_id
    WHERE i.type > 0           -- exclude heaps
      AND i.is_disabled = 0
)
SELECT
    OBJECT_SCHEMA_NAME(a.object_id) AS schema_name,
    OBJECT_NAME(a.object_id) AS table_name,
    a.index_name AS index_1,
    b.index_name AS index_2,
    a.is_unique AS idx1_unique,
    b.is_unique AS idx2_unique,
    a.is_primary_key AS idx1_pk,
    b.is_primary_key AS idx2_pk,
    a.key_columns,
    a.included_columns
FROM IndexColumns a
JOIN IndexColumns b ON a.object_id = b.object_id
    AND a.index_id < b.index_id
    AND a.key_columns = b.key_columns
    AND ISNULL(a.included_columns, '') = ISNULL(b.included_columns, '')
ORDER BY OBJECT_NAME(a.object_id), a.key_columns;

Note: STRING_AGG requires SQL Server 2017+. For SQL Server 2014/2016, use the FOR XML PATH('') equivalent. The logic is identical — you are concatenating column names in ordinal order and comparing the resulting strings.

Note: This query does not catch filtered indexes — an index with a WHERE clause filters to a subset of rows, so it cannot replace a non-filtered index with the same columns even if the key and include columns match. If your environment uses filtered indexes, cross-check results manually.

Distinguishing True Duplicates from Covering Indexes

Not everything that looks like a duplicate is one. Before you touch anything, understand the difference between:

True duplicates: Index A and Index B have identical key columns in identical order, with identical included columns. One can be removed with no functional loss.

Partial duplicates (one is a superset): Index A has key columns (CustomerID, OrderDate) with no includes. Index B has key columns (CustomerID, OrderDate) with INCLUDE (Amount, Status). Index B is a superset — it satisfies everything Index A can, plus more. In this case, Index A is the candidate for removal, not Index B.

Covering indexes with different leading columns: Index A has key columns (CustomerID, OrderDate). Index B has key columns (OrderDate, CustomerID). These are not duplicates — the leading column determines which queries can use the index for a seek. An equality predicate on CustomerID alone can use Index A but not Index B for a seek.

This query helps identify the superset cases:

-- Find cases where one index is a subset of another (same leading keys, one has additional includes)
WITH IndexKeys AS (
    SELECT
        i.object_id,
        i.index_id,
        i.name AS index_name,
        i.is_primary_key,
        i.is_unique,
        STRING_AGG(
            CASE WHEN ic.is_included_column = 0
                 THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
                 ELSE NULL END,
            ', '
        ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns,
        COUNT(CASE WHEN ic.is_included_column = 0 THEN 1 END) AS key_col_count,
        COUNT(CASE WHEN ic.is_included_column = 1 THEN 1 END) AS include_col_count
    FROM sys.indexes i
    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE i.type > 0 AND i.is_disabled = 0
    GROUP BY i.object_id, i.index_id, i.name, i.is_primary_key, i.is_unique
)
SELECT
    OBJECT_NAME(a.object_id) AS table_name,
    a.index_name AS narrow_index,
    b.index_name AS wider_index,
    a.key_columns,
    a.include_col_count AS narrow_includes,
    b.include_col_count AS wider_includes
FROM IndexKeys a
JOIN IndexKeys b ON a.object_id = b.object_id
    AND a.index_id <> b.index_id
    AND a.key_columns = b.key_columns
    AND a.include_col_count < b.include_col_count
ORDER BY OBJECT_NAME(a.object_id), a.key_columns;

Checking Actual Usage

SQL Server tracks how many seeks, scans, lookups, and updates each index has received since the last service restart via sys.dm_db_index_usage_stats. This is your usage evidence.

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    ISNULL(us.user_seeks, 0) AS user_seeks,
    ISNULL(us.user_scans, 0) AS user_scans,
    ISNULL(us.user_lookups, 0) AS user_lookups,
    ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0) + ISNULL(us.user_lookups, 0) AS total_reads,
    ISNULL(us.user_updates, 0) AS user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_update,
    p.rows AS table_rows,
    SUM(a.total_pages) * 8 / 1024 AS index_size_mb
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us ON i.object_id = us.object_id
    AND i.index_id = us.index_id
    AND us.database_id = DB_ID()
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.object_id > 100  -- exclude system objects
  AND i.type > 0         -- exclude heaps
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
GROUP BY
    i.object_id, i.name, i.type_desc, i.index_id,
    us.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
    us.last_user_seek, us.last_user_scan, us.last_user_update,
    p.rows
ORDER BY total_reads ASC, user_updates DESC;

Critical caveat: sys.dm_db_index_usage_stats resets every time SQL Server restarts. If you have a server that was recently rebooted, patched, or failed over, the usage data is partial. An index with zero seeks since the last restart is not necessarily unused — it may have had heavy usage before the restart.

Before concluding an index is unused, confirm:

How long has the server been running since the last restart? (SELECT sqlserver_start_time FROM sys.dm_os_sys_info)
Does the usage period cover all typical workload cycles — end of month, end of quarter, annual batch processes?

The Missing Index DMV Trap

SQL Server’s missing index recommendations (sys.dm_db_missing_index_details) are useful but dangerous in the wrong hands. The optimiser generates these recommendations based on individual query plans — it does not know about other indexes on the table, other queries using those indexes, or the cost of maintaining the new index.

The trap: a DBA or developer looks at missing index recommendations, creates the suggested indexes without cross-referencing existing ones, and ends up with more duplicates than they started with. This is extremely common.

Before acting on a missing index recommendation:

Check whether an existing index already covers or nearly covers the recommendation.
If the recommendation has different included columns from an existing index with the same keys, consider whether you can extend the existing index rather than creating a new one.
Never create a missing index recommendation verbatim in production without checking the impact on write workloads.

-- Current missing index recommendations
SELECT
    OBJECT_NAME(mid.object_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.unique_compiles,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact / 100 AS estimated_improvement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

How to Remove an Index Safely

Once you have identified a genuine duplicate, confirmed it has low or zero usage, and checked it is not a constraint (primary key, unique constraint), the removal process is:

Step 1: Script the index before dropping it.

-- Script an index for rollback (example — generate from SSMS or manually)
-- Replace table/index names with your actual values
-- This is your safety net
CREATE NONCLUSTERED INDEX [IX_YourIndex]
ON [dbo].[YourTable] ([Column1] ASC, [Column2] ASC)
INCLUDE ([Column3], [Column4])
WITH (FILLFACTOR = 80, ONLINE = ON);

Save this script somewhere you can run it immediately if something breaks after the drop.

Step 2: Disable before dropping (optional but recommended for unfamiliar indexes).

ALTER INDEX [IX_YourIndex] ON [dbo].[YourTable] DISABLE;

A disabled non-clustered index retains its definition in the catalog but its pages are deallocated — SQL Server will not use it for reads, and DML operations will not maintain it. This removes the write overhead immediately, which you can confirm by monitoring the workload. However, recovering from a disable is not trivial: to re-enable the index you must rebuild it with ALTER INDEX ... REBUILD, which can take significant time on large tables. Disabling is less a “quick undo” safety net and more a way to verify that nothing relies on the index before you commit to the drop.

Step 3: Drop the index.

DROP INDEX [IX_YourIndex] ON [dbo].[YourTable];

Step 4: Monitor after the drop. Check query performance for 48 hours. Look for any sudden increases in scan operations or blocking. Check sys.dm_exec_query_stats for queries with rising logical read counts — a sign that something that was using the dropped index is now doing more work.

Decision Framework: Drop or Consolidate?

Situation	Recommendation
True duplicate — identical keys and includes	Drop the one with lower usage stats (or the one without the friendlier name)
One index is a superset of another	Drop the subset (the narrower one) — the superset covers all the same queries
Same key columns, one has additional includes	Consider extending the narrower one to include the extra columns, then drop the wider one — or just drop the narrower one if usage is low
Low-use index on a high-write table	Strong candidate for drop — write overhead is certain, read benefit is marginal
Zero usage but server restarted recently	Do not drop yet — wait for a full workload cycle before concluding it is unused
Index referenced in code, stored procedures, or deployment scripts	Audit code before dropping — a drop can cause a procedure to fail if it uses an index hint

The Practical Takeaway

Index bloat rarely causes acute failures. It is a slow tax on write performance and storage that compounds over years. The reason most DBAs do not address it is not ignorance — it is that justifying a production index change requires evidence, and gathering that evidence takes method.

The queries in this post give you that evidence. Run the duplicate detection query, cross-reference with usage stats, apply the superset check, and you will have a defensible list within an hour. Script your drops, disable before dropping on anything you are uncertain about, and monitor for 48 hours after.

AIDA Diagnostics includes index analysis as part of its 150+ check assessment — finding duplicate indexes, unused indexes, missing index recommendations, and fragmentation patterns across your environment. $495 per server instance. Try AIDA Recon free for a no-cost baseline of your index health.

Finding and Removing Duplicate Indexes in SQL Server