SA0056 : Index has exact duplicate or overlapping index
The topic describes the SA0056 analysis rule.
Index has exact duplicate or overlapping index
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.
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.
Review the duplicating or overlapping index and take the following considerations before dropping the index:
– 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.
The rule has a ContextOnly scope and is applied only on current server and database schema.
The parameter determines when to ignore partially duplicating indexes when they have too much different columns.
The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Design Rules, Bugs