Click or drag to resize

SA0056 : Index has exact duplicate or overlapping index

The topic describes the SA0056 analysis rule.

Message

Index has exact duplicate or overlapping index

Description

The rule matches exact duplicating or partially duplicating indexes.

The exact duplicating indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination.

The overlapping indexes share the same leading key columns, but the included columns are ignored. These types of indexes are probable dead indexes walking.

Considerations:

- Before dropping the duplicating indexes,check for index hints referencing the particular duplicating index.

- Be careful when dropping a partial duplicate index if the two indexes differ greatly in width.

Consider setting appropriate value to the MaximumNonOverlappingKeyColumns in order the rule to filter only the indexes having small differences in the number of indexed columns.

For example:

If Index1 is a very wide index with 12 columns, and Index2 is a narrow two-column index that shares the first two columns,

you may want to leave Index2 as a faster, compact, narrower index.

Scope

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

Parameters
NameDescriptionDefault 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.

Categories

Design Rules

Additional Information
See Also

Other Resources