SA0056 : Index has exact duplicate or overlapping index

Introduction

Redundant or overlapping indexes in SQL Server databases can lead to inefficient use of resources.

Description

Redundant indexes and overlapping indexes can significantly affect the performance of SQL Server databases by unnecessarily using disk space and memory, and potentially slowing down data modification operations. Identifying these indexes is crucial for database optimization.

For example:

-- Example of problematic indexes
CREATE INDEX Index1 ON TableName (Column1, Column2, Column3);
CREATE INDEX Index2 ON TableName (Column1, Column2);

Here, Index2 shares the same leading columns as Index1 . Keeping both indexes may be redundant if Index1 fully covers the potential query usage scenarios. On the other hand, a narrower index like Index2 might be beneficial for specific queries needing optimized performance.

Redundant indexes waste storage and can slow down write operations due to unnecessary maintenance.
Overlapping indexes might not always be beneficial and could lead to increased overhead without providing significant performance benefits.

How to fix

Identify and remove redundant or overlapping indexes to optimize SQL Server performance and resource utilization.

Follow these steps to address the issue:

1.Analyze current indexes to identify redundant or overlapping indexes. Use sys.indexes or SQL Server Management Studio (SSMS) to view index details.

2.Check for usage of index hints in code that may reference the index you plan to drop. Ensure that removing the index won’t break any existing functionality.

3.Compare the width of overlapping indexes. Avoid dropping partial duplicates where the indexes differ significantly in size, unless justified by query patterns.

4.Set the appropriate value for MaximumNonOverlappingKeyColumns to filter out indexes with small differences in the number of indexed columns.

5.Drop the redundant index using the DROP INDEX command once you have confirmed its redundancy.

For example:

-- Example of index review and drop process
-- Identify potential redundant indexes
SELECT name, object_id, index_id FROM sys.indexes WHERE object_id = OBJECT_ID('TableName');

-- Drop redundant index after confirming redundancy
DROP INDEX Index2 ON TableName;

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Name	Description	Default Value
MaximumNonOverlappingKeyColumns	The parameter determines when to ignore partially duplicating indexes when they have too much different columns.	5

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

1 hour per issue.

Additional Information

How can you tell if an index is REALLY a duplicate?

Analysis Rules