Consider the following query:
select top 50 p.value, p.pointid from point p where exists (select c.collectionid from collection c where c.parentid = 1 and c.somebool = 0 and c.someotherbool = 1 and p.collectionid = c.collectionid) order by p.value desc
In a given schema where the collection table has points and a parent is referenced from the collection table (part of this query).
Something about this query and the indexes I have on the more complex version of this results in Sql Server thinking its a good idea to execute the "order by" FIRST on a table of ~500k rows instead of executing the where and working on 4k rows instead.
This results in a spill (btw would a spill kill the cache?) which just destroys performance seemingly across the board.
I created this a smaller example of this but couldn't reproduce the problem making me believe there is some sort of issue with another index or materialized view or something that is confusing the execution plan.
On the more complex version of this I can get the desired result by forcing it to use an index that is on the equivalent of point.collectionid. Sadly I'm constrained by an ORM that doesn't output nice SQL otherwise I'd use the hint or temp table.
Amusingly enough top(3250) causes the 500k sort but top(3500) doesn't.
Any ideas about the rationale? Could this be a bug in Sql Server?
EDIT: An image showing the original execution plan and my attempt to recreate the problem.
We can see from this that in the "madness" it begins the query off the PK clustered of the 500k row table, in the recreation (there are a million rows in that one) it correctly executes the WHERE part of the clause first starting with the PK of the other table to reduce its subset to 4k as opposed to sorting the whole thing.
Sorry I can't post the lot but there is a large amount of noise in this db and I don't want to post internals.