Usage of three index for the same table

Thomas Franz 2017-11-27 15:46:09

the attached plan contains two version of the same (complex) statement (used to fill a data warehouse table). The only difference is the WITH(INDEX(t_werbetraeger_werbeflaechen_uq5)) hint for the table pld.t_werbetraeger_werbeflaechen specified only in the first run.

The index is matching perfect (index column is [id_wt], the other fields are contained, filter for ([aktuellster_stand]=(1) AND [id_wt] IS NOT NULL) which is specified in the WHERE condition too).

But when I do not pass the index hint (second version), it uses three different indexes (including the _uq5) for this table and hash joins them. All three used indexes are filtered for at least ([aktuellster_stand]=(1)) and have one or more output columns as index column.

Any idea, why SQL Server uses 3 indexes instead of just one?