Understanding and Cleaning Up Overlapping Indexes in SQL Server

Indexes are critical to SQL Server performance. A well-designed index can make a query fly. A bad or redundant one can quietly drag down write performance and waste space — and unlike a slow query, overlapping indexes rarely announce themselves. They just accumulate over time as teams add “just one more index” to fix an immediate problem without checking what is already there.

This article walks through how indexes work under the hood, why overlapping indexes are a problem, and gives you a full set of scripts to detect, analyze, and safely remove or consolidate them.

B-Tree Fundamentals: Clustered vs Nonclustered

Every traditional SQL Server rowstore index uses a B-tree (balanced tree) structure. Understanding the difference between the two index types is foundational to understanding why overlaps form and why they matter.

• A clustered index is the table itself. It stores the actual data rows in key order. Every table can have exactly one clustered index.
• A nonclustered index is a separate B-tree structure that stores its own key columns plus a pointer back to the base data — either the clustered key or a row identifier (RID) for heap tables.

When a query executes, the optimizer navigates from the root of the B-tree down to the leaf level to find matching rows. How efficiently this works depends entirely on key column order and how selective the key columns are relative to the query’s predicates.

Equality vs Inequality and Key Order

The order of columns in a composite index is not optional — it is everything. SQL Server can only perform efficient seeks starting from the leftmost key column. The general rule is:

• Equality predicates first — columns used in WHERE col = value
• Inequality or range predicates second — columns used in WHERE col >= value, BETWEEN, LIKE
• INCLUDE for covering — columns needed in the SELECT but not in filters or joins go in INCLUDE, not in the key

-- Well-designed composite index following equality-first rule
CREATE INDEX IX_Orders_Status_WarehouseId_OrderDate
ON dbo.Orders (Status, WarehouseId, OrderDate)
INCLUDE (CustomerId, TotalAmount);
-- Status and WarehouseId are equality predicates (seekable)
-- OrderDate is the range predicate (scan after seek)
-- CustomerId and TotalAmount avoid key lookups at the leaf level

Putting a range column before equality columns is a common mistake that turns what should be a seek into a scan. Getting this order right is the first step toward having fewer, better indexes.

What Overlapping Indexes Are

Overlapping indexes are two or more indexes on the same table that share the same leading key columns and have similar or identical included columns. One index is effectively a subset of another — it covers no query that the broader index cannot also cover.

-- Index A
CREATE INDEX IX_Sales_CustDate
ON dbo.Sales (CustomerId, OrderDate)
INCLUDE (TotalAmount, SalesRepId);

-- Index B (superset of A)
CREATE INDEX IX_Sales_CustDate_Rep
ON dbo.Sales (CustomerId, OrderDate)
INCLUDE (SalesRepId, TotalAmount,
         ShipMethod);

-- One index replaces both
-- Covers every query either
-- original index could serve
CREATE INDEX IX_Sales_CustDate_Covering
ON dbo.Sales (CustomerId, OrderDate)
INCLUDE (SalesRepId, TotalAmount,
         ShipMethod);

Why Overlapping Indexes Hurt Performance

Workshop: Demo Table Setup

Create this table with 50,000 rows to run all four scripts against in a development environment:

DROP TABLE IF EXISTS dbo.Orders;

CREATE TABLE dbo.Orders
(
    OrderId      BIGINT        IDENTITY PRIMARY KEY,
    CustomerId   INT           NOT NULL,
    Region       VARCHAR(10)   NOT NULL,
    Status       VARCHAR(12)   NOT NULL,
    OrderDate    DATETIME2     NOT NULL,
    TotalAmount  MONEY         NOT NULL,
    SalesRepId   INT           NULL,
    CreatedBy    SYSNAME       NULL,
    ApprovedBy   SYSNAME       NULL
);

INSERT dbo.Orders
    (CustomerId, Region, Status, OrderDate, TotalAmount, SalesRepId, CreatedBy, ApprovedBy)
SELECT TOP (50000)
    ABS(CHECKSUM(NEWID())) % 5000,
    CASE ABS(CHECKSUM(NEWID())) % 4
        WHEN 0 THEN 'West' WHEN 1 THEN 'East'
        WHEN 2 THEN 'North' ELSE 'South' END,
    CASE ABS(CHECKSUM(NEWID())) % 3
        WHEN 0 THEN 'Open' WHEN 1 THEN 'Closed' ELSE 'Pending' END,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, SYSUTCDATETIME()),
    ABS(CHECKSUM(NEWID())) % 100000 / 100.0,
    ABS(CHECKSUM(NEWID())) % 100,
    SUSER_SNAME(), NULL
FROM sys.all_objects;

Now create a good index and two overlapping ones to demonstrate the problem:

-- Good composite index: equality predicates first, range second, covering includes
CREATE INDEX IX_Orders_Region_Status_OrderDate
ON dbo.Orders (Region, Status, OrderDate)
INCLUDE (CustomerId, TotalAmount);

-- Overlapping index 1: subset of the good index — no queries need this
CREATE INDEX IX_Orders_Region_Status
ON dbo.Orders (Region, Status)
INCLUDE (CustomerId, TotalAmount);

-- Overlapping index 2: same as the good index with identical includes
CREATE INDEX IX_Orders_Region_Status_OrderDate2
ON dbo.Orders (Region, Status, OrderDate)
INCLUDE (CustomerId, TotalAmount);

Script 1 — Index Inventory

Before looking for overlaps, get a full picture of every index on every table — key columns, included columns, whether it is filtered, and how much space it consumes.

WITH cols AS
(
    SELECT
        i.object_id, i.index_id, i.name AS index_name,
        i.is_unique, i.is_primary_key,
        i.has_filter, i.filter_definition,
        ic.is_included_column, ic.key_ordinal,
        c.name AS column_name, c.column_id
    FROM sys.indexes       i
    JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    JOIN sys.columns       c  ON c.object_id  = ic.object_id AND c.column_id = ic.column_id
    WHERE i.is_hypothetical = 0 AND i.index_id > 0
),
shape AS
(
    SELECT
        SCHEMA_NAME(o.schema_id)    AS schema_name,
        o.name                      AS table_name,
        c.object_id, c.index_id,
        MAX(CASE WHEN c.is_primary_key = 1 THEN 1 ELSE 0 END) AS is_pk,
        MAX(CASE WHEN c.is_unique      = 1 THEN 1 ELSE 0 END) AS is_unique,
        MAX(CASE WHEN c.has_filter     = 1 THEN 1 ELSE 0 END) AS is_filtered,
        MAX(c.filter_definition)    AS filter_definition,
        STRING_AGG(CASE WHEN c.is_included_column = 0 THEN c.column_name END, ', ')
            WITHIN GROUP (ORDER BY c.key_ordinal)   AS key_cols,
        STRING_AGG(CASE WHEN c.is_included_column = 1 THEN c.column_name END, ', ')
            WITHIN GROUP (ORDER BY c.column_id)     AS include_cols
    FROM cols c
    JOIN sys.objects o ON o.object_id = c.object_id AND o.type = 'U'
    GROUP BY SCHEMA_NAME(o.schema_id), o.name, c.object_id, c.index_id
),
sz AS
(
    SELECT object_id, index_id, SUM(used_page_count) * 8 AS size_kb
    FROM sys.dm_db_partition_stats
    GROUP BY object_id, index_id
)
SELECT
    s.schema_name, s.table_name, s.index_id,
    s.is_pk, s.is_unique, s.is_filtered,
    s.filter_definition,
    s.key_cols, s.include_cols,
    z.size_kb
FROM shape s
LEFT JOIN sz z ON z.object_id = s.object_id AND z.index_id = s.index_id
ORDER BY s.schema_name, s.table_name, s.index_id;

Script 2 — Detect Overlapping Indexes

This query identifies indexes where one index’s entire column set (keys and includes) is a subset of another’s leading key columns. These are the candidates for removal or consolidation. Unique indexes and filtered indexes are excluded automatically — they cannot be safely consolidated the same way.

WITH idx AS
(
    SELECT i.object_id, i.index_id, i.name,
           i.is_unique, i.is_primary_key, i.has_filter, i.filter_definition
    FROM sys.indexes i
    WHERE i.is_hypothetical = 0 AND i.index_id > 0
),
ic AS
(
    SELECT object_id, index_id, column_id, key_ordinal,
           CONVERT(BIT, CASE WHEN is_included_column = 1 THEN 1 ELSE 0 END) AS is_included_column
    FROM sys.index_columns
),
a_keys    AS (SELECT object_id, index_id, key_ordinal, column_id FROM ic WHERE is_included_column = 0),
b_keys    AS (SELECT object_id, index_id, key_ordinal, column_id FROM ic WHERE is_included_column = 0),
a_allcols AS (SELECT object_id, index_id, column_id FROM ic),
b_allcols AS (SELECT object_id, index_id, column_id FROM ic),
cand AS
(
    -- All pairs of non-unique, non-filtered indexes on the same table
    SELECT ia.object_id, ia.index_id AS a_index_id, ib.index_id AS b_index_id
    FROM idx ia
    JOIN idx ib ON ib.object_id = ia.object_id
               AND ib.index_id <> ia.index_id
    WHERE ia.is_primary_key = 0 AND ia.has_filter = 0
),
same_leading_key AS
(
    -- Index A's key columns are a leading subset of Index B's key columns
    SELECT c.object_id, c.a_index_id, c.b_index_id
    FROM cand c
    WHERE NOT EXISTS
    (
        SELECT 1 FROM a_keys ak
        WHERE ak.object_id  = c.object_id
        AND   ak.index_id   = c.a_index_id
        AND   NOT EXISTS
        (
            SELECT 1 FROM b_keys bk
            WHERE bk.object_id  = c.object_id
            AND   bk.index_id   = c.b_index_id
            AND   bk.key_ordinal = ak.key_ordinal
            AND   bk.column_id  = ak.column_id
        )
    )
),
a_cols_subset_of_b AS
(
    -- Every column in Index A (keys + includes) also exists in Index B
    SELECT l.object_id, l.a_index_id, l.b_index_id
    FROM same_leading_key l
    WHERE NOT EXISTS
    (
        SELECT 1 FROM a_allcols aa
        WHERE aa.object_id = l.object_id
        AND   aa.index_id  = l.a_index_id
        AND   NOT EXISTS
        (
            SELECT 1 FROM b_allcols ba
            WHERE ba.object_id = l.object_id
            AND   ba.index_id  = l.b_index_id
            AND   ba.column_id = aa.column_id
        )
    )
),
u AS
(
    SELECT object_id, index_id,
           user_seeks, user_scans, user_lookups, user_updates
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID()
)
SELECT
    QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) AS table_name,
    ia.name                                                         AS redundant_index,
    COALESCE(ua.user_seeks,0) + COALESCE(ua.user_scans,0)
        + COALESCE(ua.user_lookups,0)                              AS redundant_reads,
    COALESCE(ua.user_updates,0)                                     AS redundant_writes,
    ib.name                                                         AS covering_index,
    COALESCE(ub.user_seeks,0) + COALESCE(ub.user_scans,0)
        + COALESCE(ub.user_lookups,0)                              AS covering_reads,
    COALESCE(ub.user_updates,0)                                     AS covering_writes
FROM a_cols_subset_of_b x
JOIN idx         ia ON ia.object_id = x.object_id AND ia.index_id = x.a_index_id
JOIN idx         ib ON ib.object_id = x.object_id AND ib.index_id = x.b_index_id
JOIN sys.objects o  ON o.object_id  = x.object_id
LEFT JOIN u      ua ON ua.object_id = x.object_id AND ua.index_id = x.a_index_id
LEFT JOIN u      ub ON ub.object_id = x.object_id AND ub.index_id = x.b_index_id
ORDER BY table_name, redundant_reads;

Script 3 — See Which Queries Use an Index (Query Store)

If Query Store is enabled, this script shows which queries have used a specific index in their execution plans. Run this before removing any index that shows non-zero usage in Script 2’s results.

-- Requires Query Store enabled on the database
-- Replace the variable values with your actual schema, table, and index names

DECLARE @Schema SYSNAME = N'dbo';
DECLARE @Table  SYSNAME = N'Orders';
DECLARE @Index  SYSNAME = N'IX_Orders_Region_Status';

SELECT
    qsqt.query_sql_text,
    qsp.last_execution_time,
    qsrs.avg_duration          AS avg_duration_us,
    qsrs.avg_cpu_time          AS avg_cpu_us,
    qsrs.avg_logical_io_reads  AS avg_logical_reads,
    qsrs.count_executions
FROM sys.query_store_plan                  qsp
JOIN sys.query_store_query                 qsq
    ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text            qsqt
    ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats         qsrs
    ON qsrs.plan_id       = qsp.plan_id
WHERE TRY_CONVERT(XML, qsp.query_plan).exist(
    N'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
      //p:IndexScan[@Index[contains(.,sql:variable("@Index"))]]
     | //p:IndexScan[@Object[contains(.,sql:variable("@Index"))]]'
) = 1
ORDER BY qsrs.avg_cpu_time DESC;

Script 4 — Disable and Drop Safely

Never drop an overlapping index without disabling it first. Disabling takes effect immediately and is fully reversible — the index definition stays in the catalog but the data pages are deallocated. Let it sit for at least one full week and preferably through a month-end or other peak business cycle. If no complaints and no regressions, then drop.

-- Populate this table with indexes identified by Script 2
DECLARE @Drops TABLE
(
    schema_name SYSNAME,
    table_name  SYSNAME,
    index_name  SYSNAME
);

-- Add your candidates here -- one row per index to remove
INSERT @Drops VALUES (N'dbo', N'Orders', N'IX_Orders_Region_Status');
-- INSERT @Drops VALUES (N'dbo', N'Orders', N'IX_Orders_Region_Status_OrderDate2');

-- Generate DISABLE and DROP statements -- review before executing
SELECT
    'ALTER INDEX ' + QUOTENAME(d.index_name)
        + ' ON ' + QUOTENAME(d.schema_name) + '.' + QUOTENAME(d.table_name)
        + ' DISABLE;'                                              AS disable_stmt,
    'DROP INDEX '  + QUOTENAME(d.index_name)
        + ' ON ' + QUOTENAME(d.schema_name) + '.' + QUOTENAME(d.table_name)
        + ' WITH (ONLINE = ON);'                                   AS drop_stmt
FROM @Drops d;

To re-enable quickly if something breaks after disabling:

-- Re-enable a disabled index without dropping the definition
ALTER INDEX [IX_Orders_Region_Status]
ON dbo.Orders
REBUILD WITH (ONLINE = ON);

Quick Option: dbatools

If your team already uses dbatools, the Find-DbaDbDuplicateIndex command handles duplicate and overlapping index detection across all databases on an instance in one call — no T-SQL required:

# Find duplicate and overlapping indexes across all databases on a server
Find-DbaDbDuplicateIndex -SqlInstance YourServer

# Limit to specific databases
Find-DbaDbDuplicateIndex -SqlInstance YourServer -Database YourDatabase

# Include overlapping (not just exact duplicates)
Find-DbaDbDuplicateIndex -SqlInstance YourServer -IncludeOverlapping

The command returns one object per duplicate or overlapping index found with compression type, column structure, and filter information. Useful for a quick inventory across a large server fleet before diving into the detailed T-SQL analysis above.

Recommended Cleanup Workflow

Final Thoughts

Overlapping indexes are a quiet performance killer. They accumulate over time through missing-index suggestions, quick developer fixes, and well-intentioned patches — each one adding write overhead, consuming memory, and making the optimizer’s job slightly harder. The cumulative effect on a busy OLTP table can be significant.

The scripts in this article give you everything you need to find, evaluate, and safely remove them. The workflow is straightforward: inventory, detect, validate usage, consolidate where needed, disable before dropping, and document the change.

References

• Microsoft Docs – Clustered and Nonclustered Indexes
• Microsoft Docs – Index Architecture and Design Guide
• Microsoft Docs – Create Indexes with Included Columns
• Microsoft Docs – sys.dm_db_missing_index_details
• Brent Ozar – Duplicate Indexes (sp_BlitzIndex)
• dbatools – Find-DbaDbDuplicateIndex
• Microsoft Docs – sys.dm_db_index_usage_stats