Usage of three index for the same table

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?

avatar image By Thomas Franz 16 asked Nov 27, 2017 at 03:46 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Nov 27, 2017 at 03:46 PM

Seen: 50 times

Last Updated: Nov 27, 2017 at 03:46 PM