2

I have a table named [Job] with 2 indexes as below. (Due to confidentiality, I can't post the whole execution plan with the exact name, sorry about that.)

[IX_Job_WillTest] : (ColA ASC, ColB ASC) INCLUDE (ColC, ColD, ColE...(a lot more))

[missing_index_613_612] : (ColA ASC, ColB ASC) INCLUDE (ColC)

When I run the query, the query plan is displayed as the picture:

enter image description here

You can see the 2 index join to each other. However, as mentioned above, the index [IX_Job_WillTest] has all the columns needed for the query. Why does SQL bother to join with the index [missing_index_613_612]. I don't see any benefit for SQL to do that.

Can anyone help to shed some light for me please.

I know the question is not clear because I can't post the whole execution plan (due to confidential information). Can anyone suggest some possibility that why SQL came up with this decision? Thank you a lot.

PS: the database is Azure SQL Database.

Update: Hello everyone, I have simplify the query, hide some confidential detail. This is the query and the plan. The thing is, why SQL choose to join 2 index, while it can just use the index IX_Job_WillTest only ?! Why it do such waste effort ?!

0

2 Answers 2

3

SQL Server (and hence Azure SQL Database) has a cost based query optimiser (QO). This means it generates a number of logically equivalent but physically different query execution plans then chooses the cheapest one according to some internal, proprietary cost calculation model. So, trivially, it chose to do this two-index join because that was cheaper than any alternative plan it considered.

The question then becomes why would this be cheaper than using a single index. For an accurate answer we would need the table definitions, statistics and the query plan. However I'm willing hypothesise.

The two indexes have the same keys (ColA, ColB) but different included columns. This means "missing" is narrower and more rows will be able to fit on a page. So less IO will be required to read a given number of rows from "missing" compared to "WillTest". SQL Server's cost model assumes all data read comes off disk so reading narrower indexes will be preferred if they can fulfil the need at hand. I'd guess that the query has predicates on ColA, ColB and/ or ColC and that using "missing" was the cheapest way to satisfy these.

Why then pull in "WillTest". My guess here is that further columns from this table were required to satisfy the query - either in the predicate or as returned values. The cheapest way to find these is in the INCLUDES of "WillTest". The alternative would be to read the base table which likely is even wider than "WillTest" and even more expensive to read.

You could test my guess using table hints. Note the overall cost of the query as it is now. Then hint it to use only one of the indexes, then to use only the other, noting the cost for each. I'd think the current plan has the least cost. Costs are a black-box however so this may not be compelling.

An alternative explanation is that you have an index intersection.

You call it an "unnecessary" join. Likely it is not. Each index contributes to satisfying the needs of the query. Click on each Index Seek and press F4 (properties). The predicates and defined columns will show what part each plays. Doing it by joining indexes is only one possible implementation. Others are possible. The QO thinks this is the cheapest.

Finally note that it is at least possible that the optimization process errored in some non-terminal way and this was just the least-bad plan it had at hand to run.

1
  • Thank you Michael for the hypothesis, but I'm still not quite convinced. there's query and query plan uploaded above, can you help to check it out for more detail hypothesis. I understand that the sql server will choose the smaller index ([missing] index) to find the right records, and it have to join to the other index ([WillTest] index) to pull out more needed information. But whatever happen, in the end, the [WillTest] index will be put in to use. it's inevitable. So same CPU/Data IO resource would be spent. So I think 1 [WillTest] index is good enoguh for the job.
    – Will
    Commented Jan 13, 2022 at 9:08
1

Are you copy/pasting index suggestions?

You probably don't want that.

Index suggestions aren't as accurate as you think they are.

You should probably drop missing_index_613_612 since they're practically indentical.

As for why the plan is built in this way, you should try to post the whole query, but seeing that index name, that would probably be my last thing to worry about.

3
  • Thank you. But my main concern is SQL should be smart enough to get rid of missing_index_613_612 , and use IX_Job_WillTest only. I have updated the query and query plan. can you help to see. Thank you
    – Will
    Commented Dec 21, 2021 at 4:29
  • @Will usually SQL Server is smarter than us when coming to build a query plan. If SQL chooses to use both indexes and then hash match the result, it's probably for the better. If you want, you could disable one of the index and see what plan SQL Server generates. Then, you could compare logical reads, cpu time, plan shape and take a decision from there. Enable the index back again or drop it completely: brentozar.com/archive/2013/02/disabling-vs-dropping-indexes Commented Dec 21, 2021 at 8:42
  • may be you're right, but I try to understand how SQL make decision. The way it make this decision make me so confuse.
    – Will
    Commented Dec 22, 2021 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.