Sometimes perhaps the things I care about will really leave people wondering. :-)
In this case, it's a SQL Server graphical query plan oddity.
I don't think this behavior is necessarily related to a performance problem.
But I think this behavior can make it *harder* to troubleshoot SQL Server graphical query plans for performance. This is a case where a TOP operator shows up in a graphical plan unexpectedly - and seemingly against the expressed intent of the USE HINT ('DISABLE_OPTIMIZER_ROWGOAL') hint.
Here are some sources for additional background about the general idea.
KB2667211 - A query may take a long time to run if the query optimizer uses the Top operator in SQL Server 2008 R2 or in SQL Server 2012
KB4051361 - Optimizer row goal information in query execution plan added in SQL Server 2014, 2016 and 2017
Inside the Optimizer: Row Goals In Depth
2010 August 18
All right. Game on.
A little bit of info about the system.
Let's do some setup (I'll append all of the T-SQL at the end of the blog post for those that want to follow along at home).
OK, let's see what our folly hath wrought. Good, good. Let the A9 flow...
I know this query seems almost non-sensical. But I assure you it was lovingly distilled from a much more complex case I ran into in the field. Sure, the query could be simplified. But that's not the point of this exercise :-) Just grabbing the estimated plan here, because that's all I care about in this repro.
(If t1 and t0 were actually different heaps this would be about as good SQL Server could do without any indexes.)
OK, it's a funky query... but nothing too unusual yet in the graphical plan.
So we've got a query that probably no *person* would write and the graphical plan isn't really too objectionable. Why don't we add a hint that no *person* would add to this query, for good measure? One that would be expected to have no effect? If there isn't an apparent row goal already, what change could come about if we added the DISABLE_OPTIMIZER_ROWGOAL hint?
Oh. That's weird. A TOP operator magically appeared in the plan.
The attribute EstimateRowsWithoutRowGoal (discussed in the second KB article linked above) doesn't appear in the plan XML. So... I guess this is "row goal lite" or something?
Martin Smith recommended testing the a hint to disable a rule: QUERYRULEOFF GbAggToConstScanOrTop.
The surprise TOP operator still shows up in the estimated plan.
And... I didn't realize this until after I initially published this blog post.
The sum of the costs of the operators in the graphical plans with the surprise TOP operator... is 106%.
Here's the T-SQL for those that want to have their own fun :-)