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.

How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly unit-less? Is the estimate treated as if it were seconds or is there a conversion happening internally based on some hardware metrics?

From MS: cost threshold for parallelism Option

The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration

how-do-i-read-query-cost-and-is-it-always-a-percentage

sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The parallelism threshold is based on the estimated plan cost. If the estimated plan cost goes past the parallelism threshold, the optimizer considers parallel plan options too. There is no other measurement involved.

In the beginnings of the optimizer the plan cost was derived based on actual measurements on a specific computer. Since then throughout many versions a lot has changed with the optimizer, so attaching any time unit to the current "estimated cost" is not appropriate anymore. For example, not only estimated execution time influences that estimated cost in newer versions of SQL Server. The only thing it is telling you is that the optimizer assumes that a query plan with a higher estimated cost for the same query executed under identical circumstances will require more resources.

You cannot even compare the estimated cost of two different queries. It is fairly easy to come up with two queries where the estimated cost of the one is ten times higher, but the execution time of the other is actually significantly higher. So, you should never use the estimated cost of a query plan for performance tuning.

The quote in your question is a left over from those early days when it still made sense. It was at that time also mentioned in the section explaining the estimated query cost.

share|improve this answer
    
So the docs are wrong, there is no longer a tie to reality with either value and the costs cannot be reliably compared inter-query although they are compared to a common threshold. I appreciate your candor. –  crokusek Nov 20 '13 at 2:38
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.