I am looking into a minor performance issue, where an optimizer tool is basically saying, "hey just go ahead and include all the columns on this table in this index" which is a horrible solution in my opinion.
My thought process brought me to think "why don't I go ahead and give it everything in this existing index to do its sort and TOP(N) operation and cut out this massive 100k key lookup operation, surely SQL Server can do that and change from 100k key lookups to N."
That was not what I saw, what I saw was that nothing changed at all, it still did all the key lookups and sort after that. As below
Very simply removing the select of the other columns not in the index changes it to not need a key lookup at all of course.
I have seen many workarounds using CTE to get around this, but I am using Entity Framework for this query and simply playing around with the query isn't as easy as that.
I would like the primary purpose of this question to be WHY does this happen? Seems like a trivial operation thing to do the sort and top clause prior to the key lookup loop if you are able. It not doing this seems a glaring weakness in the platform.
I am asking why this behavior exists, not how can I improve this query's performance.