What's an Estimated Subtree Cost?

An execution plan

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!

About Rob

I've been interested in computing since the day my Dad purchased his first business PC (an Amstrad PC 1640 for anyone interested) which introduced me to MS-DOS batch programming and BASIC.

My skillset has matured somewhat since then, which you'll probably see from the posts here. You can read a bit more about me on the about page of the site, or check out some of the other posts on my areas of interest.

No Comments

Add a Comment