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.
Best Answer
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 daysbut 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
SELECTiterator) 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.