🧭 Understanding and Cleaning Up Overlapping Indexes in SQL Server

Introduction

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 performance and waste space.

One common culprit is overlapping indexes—multiple indexes that are nearly identical and cover the same workload. This often happens when teams add “just one more index” to fix an immediate problem without checking what’s already there.

In this post, we’ll break this down step by step:

1. How clustered and nonclustered indexes work under the hood (B-trees).
2. Why equality vs. inequality matters when designing indexes.
3. What overlapping indexes are and why they hurt performance.
4. How to detect, analyze, and safely remove or consolidate overlaps.
5. A hands-on workshop with full T-SQL scripts.

---

1. B-Tree Fundamentals: Clustered vs. Nonclustered

Every traditional SQL Server rowstore index uses a B-tree structure.

• A clustered index is the table. It stores rows in key order, one per table.
• A nonclustered index is a separate B-tree that stores its own key columns plus a pointer back to the base data (clustered key or RID on a heap).

When a query runs, the optimizer uses the index to navigate from root to leaf to find matches quickly. How well this works depends entirely on key column order and selectivity.

📚 Microsoft Docs – Clustered and Nonclustered Indexes

---

2. Equality vs. Inequality and Key Order

The order of columns in a composite index matters a lot. SQL Server can only do efficient seeks starting from the leftmost key column.

• Put equality predicates first (e.g., WHERE Status = 'Open').
• Put inequality or range predicates next (e.g., WHERE OrderDate >= '2025-01-01').
• Use INCLUDE to cover extra columns needed in the SELECT but not used in filters or joins.

CREATE INDEX IX_Orders_Status_Warehouse_OrderDate
ON dbo.Orders (Status, WarehouseId, OrderDate)
INCLUDE (CustomerId, TotalAmount);

This lets SQL Server seek on Status and WarehouseId, scan the range on OrderDate, and avoid key lookups by pulling CustomerId and TotalAmount from the leaf level.

📚 Microsoft Docs – Index Architecture and Design Guide

---

3. 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 includes.

Example:

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

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

Both serve nearly the same queries. One well-designed index could replace both:

CREATE INDEX IX_Sales_CustDate_Rep_Ship
ON dbo.Sales (CustomerId, OrderDate)
INCLUDE (SalesRepId, TotalAmount, ShipMethod);

Why this matters:

• Extra indexes increase write overhead (every insert, update, delete).
• They consume memory and disk space.
• They confuse the optimizer with too many similar choices.

📚 Microsoft Docs – Index Design Best Practices

---

4. Hands-On Workshop: Demo Table and Equality/Inequality Example

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;

Good composite index design:

CREATE INDEX IX_Orders_Region_Status_OrderDate
ON dbo.Orders (Region, Status, OrderDate)
INCLUDE (CustomerId, TotalAmount);

Bad / overlapping example:

CREATE INDEX IX_Orders_Region_Status
    ON dbo.Orders (Region, Status) INCLUDE (CustomerId, TotalAmount);

CREATE INDEX IX_Orders_Region_Status_OrderDate2
    ON dbo.Orders (Region, Status, OrderDate) INCLUDE (CustomerId, TotalAmount);

The second one typically supersedes the first.

---

5. Script 1: Index Inventory

This script lists your indexes with key columns, includes, and size.

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 o.name AS table_name,
         SCHEMA_NAME(o.schema_id) AS schema_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 o.name, SCHEMA_NAME(o.schema_id), 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;

6. Script 2: Detect Overlapping Indexes

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,
         is_included_column = CONVERT(bit, CASE WHEN is_included_column=1 THEN 1 ELSE 0 END)
  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 (
  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 (
  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 (
  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;

Interpretation

• If redundant_reads is low and covering_reads is high, the redundant index can probably be dropped or merged.
• Unique and filtered indexes are excluded automatically.

---

7. Script 3: See Which Queries Use an Index (Optional)

If Query Store is enabled, check what queries actually touch an index before removing it.

DECLARE @Schema sysname = N'dbo';
DECLARE @Table  sysname = N'Orders';
DECLARE @Index  sysname = N'IX_Orders_Region_Status_OrderDate';

SELECT
  qsqt.query_sql_text,
  qsp.last_execution_time,
  qsp.avg_duration, qsp.avg_cpu_time, qsp.avg_logical_io_reads
FROM sys.query_store_plan AS qsp
JOIN sys.query_store_query AS qsq
  ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text AS qsqt
  ON qsqt.query_text_id = qsq.query_text_id
CROSS APPLY (SELECT TRY_CONVERT(xml, qsp.query_plan) AS p) AS x
WHERE x.p.exist(
  N'//RelOp/IndexScan[@Index="@idx"] | //RelOp/IndexSeek[@Index="@idx"]'
    .replace('@idx', @Index)
) = 1
AND OBJECT_SCHEMA_NAME(qsq.object_id) = @Schema
AND OBJECT_NAME(qsq.object_id) = @Table;

8. Script 4: Disable and Drop Safely

DECLARE @Drops TABLE (schema_name sysname, table_name sysname, index_name sysname);

-- Example: copy from overlap results
-- INSERT @Drops VALUES (N'dbo', N'Orders', N'IX_Orders_Region_Status');

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;

Disable first, let it sit for a week or a few business cycles, then drop once you’re sure no queries rely on it.

To re-enable quickly if needed:

ALTER INDEX [YourIndex] ON [schema].[table] REBUILD WITH (ONLINE = ON);

9. Recommended Cleanup Workflow

1. Inventory your indexes.
2. Run the overlap detector and export results.
3. Check usage with sys.dm_db_index_usage_stats.
4. Confirm no critical queries use the redundant index (Query Store).
5. Create or adjust a consolidated index where needed.
6. Disable the redundant index first.
7. Drop the index if no regressions occur.
8. Rebuild stats and document the change.

---

10. Final Thoughts

Overlapping indexes are a quiet performance killer. They creep in over time through missing-index suggestions, quick fixes, and developer patches. Cleaning them up will:

• Reduce write overhead
• Save space
• Simplify your index landscape
• Make the optimizer’s job easier

Think equality first, inequality second, cover only what’s needed, and keep your index set lean.

---

References

• Microsoft Docs – Clustered and Nonclustered Indexes
• Microsoft Docs – Index Design Guide
• Microsoft Docs – Included Columns
• Microsoft Docs – Missing Index DMV
• Community: Duplicate and Overlapping Index Analysis

---

✅ Workshop Summary

• Understand B-tree structures and key order.
• Detect overlaps with the provided scripts.
• Review usage before disabling.
• Merge or remove redundant indexes safely.
• Keep a lean, targeted index strategy.