1

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

2

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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