I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric.

According to any documentation I could find via Google, the query cost is a percentage figure, and represents the percentage of the whole batch taken up by any one part of it. This already seemed a little odd to me, as I am interested in the absolute merit of a particular query, rather than its merit relative to other queries which happen to appear alongside it.

But then I thought, well, maybe what you are expected to do is place two alternative queries side by side, run them as "a batch", and then whichever one has a cost less than 50% is the winner.

But the discussion of query cost in chapter 6, lesson one of Microsoft's SQL 70-433 Training Kit doesn't seem to bear any relation to this.

Here is an example: They show a query containing two correlated sub-queries, and then improve on it by replacing the sub-queries with an OUTER APPLY. The result: "This query has a cost of roughly 76, while the first query's cost was double that, about 151." They then improve the query even further, and reduce the cost from 76 to 3.6. They do not imply these figures are percentages, whereas they do imply that they are absolute figures which relate to the query as a standalone object, without reference to any other queries. And anyway, how could the first query have a cost of 151%?

Later on in the chapter, they show a screenshot of an execution plan which has three parts. The first says "Cost: 0%", the second says "Cost: 1%" and the last says "Cost: 99%" but the text (of the book itself) below the screenshot "The cost of this query is 0.56". I'm guessing that they mean some other kind of cost, but I can't find reference to it elsewhere.

Can someone help? I'm thoroughly confused.

  • PS Presuming they are referring to something other than the percentages shown in execution plans... how do I get SSMS to show me the figures they are quoting? Do I need something like SET STATISTICS TIME ON, but with some other command that will show me a "cost" figure on the Messages tab?
    – Born Again Geek Girl
    Dec 31, 2011 at 15:18

1 Answer 1


The query cost is reported in execution plans as "estimated subtree cost". This is an absolute figure such as 1.5. Conor Cunningham mentioned in a SQLBits presentation that it originally referred to the number of seconds taken to execute on a particular Microsoft employee's machine ("Nick's Machine") in the SQL Server 7 days

enter image description here

but now should be interpreted as a unitless measure of overall cost.

The execution plan is a tree. Each iterator in the tree is given an estimated CPU cost and an estimated IO cost and these are added together to get the overall cost (the relative weightings can be adjusted with a couple of undocumented DBCC commands). The estimated subtree cost includes the cost for the iterator itself and all its descendants. To see an example of the costing formulas used you can look at this article.

To determine the estimated cost for an entire query in SSMS select the root iterator (e.g. the SELECT iterator) to the left of the graphical plan and look at this metric in the SSMS properties window.

When running multiple queries (whether in the same batch or not) the percentage is calculated by adding up all of these values and calculating the percentage as you would expect.

You should be aware that even in actual execution plans this cost figure is based on estimates and using this to compare the relative merits of two different queries can be woefully wrong in cases where the estimates are inaccurate.

  • Thankyou, that's really helpful. And not at all obvious! I wonder why that figure isn't flagged up more clearly?
    – Born Again Geek Girl
    Dec 31, 2011 at 15:56
  • 4
    I always wondered about the unit of measure, glad to finally find out. I have been explaining it to people (comically) that it is "SQL Dollars". If a query cost is 1.0, that is like paying 1 dollar for a cup of coffee. Not a bad price. Would you pay $300 for a cup? No way!
    – datagod
    Sep 21, 2012 at 13:42
  • It's just a joke that the Query Cost in the actual execution plan is an estimate, had used lots of time trying to figure out why 2 queries taking the same time have 10/90 and not 50/50. Apparently the estimates are just very wrong as you pointed out... Microsoft has always had troubles with naming things (it's hard as usual). How hard could it be to add Estimated word to it?... Jan 14, 2021 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.