Why can SQL Server not perform a TOP N SORT between an Index Seek and the Key Lookup?

Question

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.

Answer 1

self-love

You can probably get the plan shape you're after by doing a self-join, which is far more easily done in Entity Framework than all the CTE stuff. It's also a more reliable option because CTEs can be flaky.

If we have this index:

CREATE INDEX 
    whatever 
ON dbo.Users 
    (Reputation, Age, CreationDate)
WITH(SORT_IN_TEMPDB = ON);

And this query:

SELECT TOP (1000) 
    u2.*
FROM dbo.Users AS u
JOIN dbo.Users AS u2
    ON u.Id = u2.Id
WHERE u.Reputation = 2
ORDER BY 
    u.CreationDate DESC;

The resulting plan looks like this:

the details

What you want to do is have one instance of the self-join (in this case the u alias) take care of the where clause and order by, and the other reference (in this case u2) take care of the select list.

You can see in the query plan that the Sort meets the 1000 row goal before going into the join.

In the case of a parallel execution plan (like the one in your question), you may see a slightly higher number go into the sort before being discarded by the TOP.

There is some additional detail about why this is here:

• Parallel Execution Plans Suck

Answer 2

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.

There are three main reasons:

1. A lookup is tightly bound to its parent operator.

   The logical operation is a GET to return attributes from a relation. The physical implementation of that logical operation can take many forms:

   • Scan a table, index, or matching indexed view
   • Seek into an index, one or many times
   • Scan or seek(s) plus lookup
   • Residual predicate on the scan, seek, or lookup

   Whichever physical option is chosen, SQL Server has to honour the original intent and semantics of the GET, including locking lifetime and other consistency guarantees and internal invariants.

   As a result, there are very few operators allowed between a scan or seek and any related lookup. These include a sort on the clustering key(s) introduced to optimize for sequential I/O at the lookup, and an eager spool for Halloween protection.

2. A "Top" is not a relational operator. Most of the optimizer is built on relational principles and equivalences. Some specific support has been added (or deliberately omitted) for Top over time, but these are still a minority.

   Consequently, the optimizer doesn't consider alternative Top operator placements very much.

3. A "Top N Sort" is a post-optimization rewrite to potentially perform a replacement selection sort rather than use the general algorithm.

   As a post-optimization rewrite, it is not subject to cost control and doesn't form any part of the optimizer's reasoning.

   The rewrite is limited to cases where the physical Top operator ends up immediately after a Sort operator in the optimizer's chosen execution plan.

   Given that the optimizer doesn't explore moving Top around the plan tree very much, it is easy to end up with the Top separated from the Sort (which can move around a lot more, but not as much as true relational operators).

It not doing this seems a glaring weakness in the platform.

Well, it is. People have been writing about ways to express queries to get better results for a decade or more. It's not the only weakness, either.

On the other hand, the SQL Server optimizer has a goal of finding an apparently reasonable execution plan quickly. It does not have the same goals as an optimizing compiler found in programming languages, which have much more freedom and time to find and apply their tricks.

I am using Entity Framework for this query and simply playing around with the query isn't as easy as that.

Sorry to hear that.