Duplicate Index Detection & Optimization

Detects duplicate, redundant, and combinable nonclustered indexes across all databases on a server. Collects live index metadata via DMV queries (one fast query per database), analyzes column overlap, and generates ready-to-execute DROP and CREATE scripts with usage statistics and size estimates.

Why This Matters

Duplicate and redundant indexes are one of the most common performance drains in SQL Server, yet they're rarely cleaned up because they're hard to spot manually. Every unnecessary index:

• Increases write overhead — every INSERT, UPDATE, and DELETE must maintain every index on the table. A table with 15 indexes does 15x the index write work of a table with one. On high-write tables, this directly impacts transaction throughput.
• Wastes storage — a redundant 2 GB index on 50 servers is 100 GB of wasted disk across the fleet, plus backup time and restore time.
• Confuses the optimizer — SQL Server's query optimizer may choose a suboptimal index when multiple overlapping indexes exist, especially when statistics differ between them.
• Slows maintenance — every extra index extends REBUILD and REORGANIZE times during maintenance windows.

This tool automates what would take hours of manual sys.indexes comparison into a sub-minute analysis with actionable output.

Detection Types

The tool classifies every finding into one of four action types, each with different risk profiles and output scripts:

DUPLICATE

-- Index A (DROP): Keys: [customer_id, order_date], Includes: (none)
-- Index B (KEEP): Keys: [customer_id, order_date], Includes: [total_amount, status]
-- Index B covers everything Index A does, plus more.

DUPLICATE of PK

-- Clustered PK:     Keys: [order_id]
-- NC Index (DROP):  Keys: [order_id]
-- The NC index is a mirror of what the PK already provides.

REDUNDANT

-- Index A (DROP): Keys: [customer_id],                Includes: [name]
-- Index B (KEEP): Keys: [customer_id, order_date],    Includes: [name, total]
-- A seek on [customer_id] can use Index B (leftmost prefix).
-- Index A's include [name] is already in Index B.

COMBINE

-- Index A: Keys: [customer_id],              Includes: [email, phone]
-- Index B: Keys: [customer_id, order_date],  Includes: [total_amount]
-- Neither covers the other.
-- Combined: Keys: [customer_id, order_date], Includes: [email, phone, total_amount]
-- One index replaces two, saving write overhead.

Analysis Engine

The core analysis is performed by indexing.IndexOptimization_Insert, a self-contained procedure that collects live data and analyzes in one pass:

Step 1: Live Index Collection

Loops through target databases. For each database, runs a single DMV query joining sys.indexes, sys.index_columns, sys.columns, and sys.data_spaces into a #ServerIndexes temp table. Sub-second per database. Handles collation differences with COLLATE DATABASE_DEFAULT to prevent cross-database comparison errors.

Step 2: Duplicate & Redundant Detection

Compares all active, non-filtered, non-disabled nonclustered indexes on each table. Uses column-set comparison to find:

• Exact key matches with superset includes (DUPLICATE)
• Key matches against the clustered index (DUPLICATE of PK)
• Leading prefix key matches with subset includes (REDUNDANT)

Deduplicates across categories so a single index only appears in one finding. Priority: DUPLICATE of PK > DUPLICATE > REDUNDANT.

Step 3: Combination Detection

For indexes not already flagged in step 2, finds pairs where one index's keys are a leading prefix of the other's. Merges their include columns (deduped, excluding columns already in the longer key set or the clustered key). Generates:

• A new index name following IX_TableName_Keys[_INCLUDES] convention, with _NEW appended on name collisions
• CREATE and DROP statements
• Handles DESC columns in keys ((-) replaced with DESC in generated SQL)

Step 4: Size Estimation

After COMBINE inserts, calls sp_GetHybridIndexSizeEstimate per row to estimate the new combined index size. The hybrid estimator first tries structural similarity to existing indexes, then falls back to actual data sampling via AVG(DATALENGTH()). Result stored in EstimatedSizeGB, displayed in MB.

Step 5: Usage & Size Stats

After all inserts, loops through distinct databases in the results and queries sys.dm_db_partition_stats (sizes) and sys.dm_db_index_usage_stats (seeks, scans) for only the indexes in the results table. This populates the Index1/Index2 size and usage columns shown in the output comments.

Script Generation

The recommendation proc (sp_DuplicateIndexOptimizationRecommendations) generates a single ScriptText organized by database and table:

DUPLICATE / REDUNDANT / DUPLICATE of PK Output

/*****************************************************/
-- Table: [dbo].[orders]
/*****************************************************/

-- REDUNDANT
--   DROP:  IX_orders_customer_id (Id: 5)
--     Key Columns:     [customer_id]
--     Include Columns: (none)
--     Size: 45.2 MB  |  Seeks: 1,200  |  Scans: 50
--   KEEP:  IX_orders_customer_id_order_date (Id: 8)
--     Key Columns:     [customer_id], [order_date]
--     Include Columns: [total_amount]
--     Size: 78.9 MB  |  Seeks: 8,500  |  Scans: 200

-- EXEC sp_helpIndexUsage 'dbo.orders';

DROP INDEX IF EXISTS [IX_orders_customer_id] ON [dbo].[orders];

UPDATE DBA_Utilities.indexing.IndexOptimizationResults
SET ResolvedDate = GETDATE() WHERE IndexOptimizationId = 42;

Key details in the comments: both indexes' keys, includes, size, and DMV usage stats (seeks + scans). The sp_helpIndexUsage call lets you inspect column-level usage before committing.

COMBINE Output

-- COMBINE
--   Index 1: IX_orders_customer_id_INCLUDES (Id: 5)
--     Key Columns:     [customer_id]
--     Include Columns: [email], [phone]
--     Size: 120.5 MB  |  Seeks: 5,000  |  Scans: 100
--   Index 2: IX_orders_customer_id_order_date (Id: 8)
--     Key Columns:     [customer_id], [order_date]
--     Include Columns: [total_amount]
--     Size: 78.9 MB  |  Seeks: 8,500  |  Scans: 200
--   New Index: IX_orders_customer_id_order_date_INCLUDES
--     Key Columns:     [customer_id], [order_date]
--     Include Columns: [email], [phone], [total_amount]
--   Estimated Size: ~210.3 MB

CREATE NONCLUSTERED INDEX [IX_orders_customer_id_order_date_INCLUDES]
ON [dbo].[orders] ([customer_id], [order_date])
INCLUDE ([email], [phone], [total_amount]);

DROP INDEX IF EXISTS [IX_orders_customer_id_INCLUDES] ON [dbo].[orders];
DROP INDEX IF EXISTS [IX_orders_customer_id_order_date] ON [dbo].[orders];

Enterprise Features

ONLINE = ON

On Enterprise and Developer editions, COMBINE operations automatically add WITH (ONLINE = ON) to the CREATE INDEX statement. This allows the index to be built without blocking concurrent queries — critical for 24/7 production databases.

@RunnableUnder

Controls auto-commenting of large COMBINE operations. Accepts the edition-aware syntax:

• NULL — everything uncommented (default)
• '2' — COMBINE operations with estimated size ≥ 2 GB are wrapped in /* */
• '1,5e' — 1 GB threshold for Standard, 5 GB for Enterprise

Important: DROP-only operations (DUPLICATE, REDUNDANT, DUPLICATE of PK) are never commented out regardless of @RunnableUnder. Dropping an index is a metadata operation — it doesn't build anything, doesn't lock the table, and reclaims space immediately.

Filtered Index Protection

The analysis engine excludes all filtered indexes from comparison. A filtered index with a WHERE clause serves a specific purpose that column-set comparison cannot assess. The tool will never recommend dropping, combining, or modifying a filtered index.

Parameters

Results Table

indexing.IndexOptimizationResults — one row per index pair finding.

Records are never deleted. The analysis engine only clears unresolved records before re-analyzing, preserving the full resolved history for audit and trending.

Usage Examples

-- Full analysis with live data, all databases
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @RunAnalysis = 1;

-- Single database with edition-aware thresholds
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyDatabase',
    @RunnableUnder = '1,5e';

-- Wildcard: all app databases
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%';

-- View existing results without re-analyzing
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations;

-- Only COMBINE recommendations
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @ActionType = 'COMBINE';

-- Only DUPLICATE/REDUNDANT (safe drops, no new indexes)
EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @ActionType = 'DUPLICATE';

EXEC indexing.sp_DuplicateIndexOptimizationRecommendations
    @ActionType = 'REDUNDANT';

-- Query the results table directly for a fleet-wide view
SELECT ServerName, DatabaseName, TableName, ActionType,
       Index1Name, Index2Name, Index1SizeKB/1024.0 AS Index1SizeMB
FROM indexing.IndexOptimizationResults
WHERE ResolvedDate IS NULL
ORDER BY Index1SizeKB DESC;