Dodgy T-SQL Query Execution driving me crazy
is there any way to get that TOP N to not be so heavy?


I think there's a little bit of a misunderstanding here about how execution plans work.

That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be.  It's not updated at runtime, so even if the operators used very little resources, the estimated costs are still displayed as they were when the plan was created.

Looking at the runtime plan you provided, Sentry One Plan Explorer makes it a little easier to see that some of those "expensive" operators didn't run at all (they are "greyed out"):




  I am having a heck of a time understanding why this query is taking 0.02 seconds to execute


I'm not sure how you're measuring the time here, but the execution plan indicates that the entire query took less than one milliseond to run, and took 9 milliseconds to compile.  See the excerpts from the plan XML:

&lt;QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736"&gt;
...
&lt;QueryTimeStats CpuTime="0" ElapsedTime="0" /&gt;


Max has some good suggestions on general improvements to the query, definitely check those out.  But I thought it was worth pointing this out as well.

You might be able to eliminate the TopN Sort with an indexed view, but I'm not sure it's worth the overhead at this point.