What's an Estimated Subtree Cost?
When you're looking at the execution plan for a query (you do that, right?) one of the data-points that's shown is "Estimated Subtree Cost". Looking at this is one of the things I review when I've got a new query I want to ensure is as performant as possible or when I'm working with a query which is problematic. The lower the number, the better like in "TIL: Queries against linked servers... Be careful how you structure them!" where changing the way the query against the linked server was executed reduced the Subtree cost from 3.23267 to 0.0103333 a reduction of 99.7%!
The Subtree Cost is a bit of an arbitrary number, or certainly seems that way until you find a story like this:
the story goes that when the new query optimizer was developed for SQL server 7.0 in the Query Optimizer team there was a programmer called nick (I am sorry but I do not know his last name) ,he was responsible for calculating query costs (among other things…) ,and he had to decide how to generate the cost number for a query ,so he decided that if query runs for 1 second on his own pc the cost will be ….. 1 ,so we can finally answer the question what is “estimated subtree cost = 1” means,it means ladies and gentleman that it runs for 1 second on nick’s machine
So, there you go - that's what Subtree cost is measuring, according to that guy!