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?
By Thomas Franz 16 asked Nov 27, 2017 at 03:46 PM