Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 31 '11 at 17:25

This question came from our site for professional and enthusiast programmers.

    
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 '11 at 15:18

1 Answer 1

up vote 14 down vote accepted

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 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.

share|improve this answer
    
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 '11 at 15:56
1  
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 '12 at 13:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.