cost threshold for parallelism

Martin Garcia 0 Reputation points
2025-01-17T03:40:19.15+00:00

Good day,

I am confused by reading 2 different information in Microsoft Documents and making some testing analyzing the execution plans regarding the cost threshold for parallelism

What is the estimated cost which the threshold refers to?

In one source is stated
"The cost refers to an estimated elapsed time in seconds required to run the serial plan..."

https://learn.microsoft.com/en-us/previous-versions/sql/**sql-server-2008-r2**/ms188603(v=sql.105)?redirectedfrom=MSDN

In another source

"The cost refers to an estimated cost required to run the serial plan on a specific hardware configuration, and isn't a unit of time."

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=**sql-server-ver16**

Also, I have found the statement

"In SQL Server, the "Cost Threshold for Parallelism" setting generally remains the same between versions like 2008 R2 and 2016, with the default value usually being "5", but the key change is that in newer versions like 2016, it is often recommended to increase this value to a higher number..."

Any advice would be very appreciated.

Thank you

Martin

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,481 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 117.1K Reputation points MVP
    2025-01-17T22:12:41.88+00:00

    The first link you found is for SQL Server 2008 R2, which is a very old version. And the text was inaccurate already at the time it was published, presumably a holdover from even older versions of SQL Server.

    Legend has it that these "costs" that the optimizer works with relates to metrics on a some developer's desktop in the late nineties. By time, these values has lost all contact with the real world, and today they are just abstract metrics with no real-word meaning. The later URL points this out.

    It is also true that the original default is considered way too low with modern computers. The default remains 5, since Microsoft are not keen on changing the defaults for backwards-compatibility reasons. But the recommendation in general is to set this parameter to 30 or 50.

    What would be the right value for your site, is something that only can be determined by experimenting.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.