SA0056 : Index has exact duplicate or overlapping index

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:

1-- Example of problematic indexes
2CREATE INDEX Index1 ON TableName (Column1, Column2, Column3);
3CREATE 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.

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:

1-- Example of index review and drop process
2-- Identify potential redundant indexes
3SELECT name, object_id, index_id FROM sys.indexes WHERE object_id = OBJECT_ID('TableName');
4
5-- Drop redundant index after confirming redundancy
6DROP INDEX Index2 ON TableName;

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

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

Design Rules, Bugs

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

Other Resources