SQL Server Cost Threshold for Parallelism

Hi Friends,

What triggers me to write this blog today is that in one of my recent consulting engagements, my audience had blurred understanding of this server-level property and the concept of Parallelism overall. Here is a simple explanation:

SQL Server Cost Threshold for Parallelism, in simple words, means that SQL Server can use 2 or more processors to execute your query. Which means, the optimizer produces a parallel plan and you can observe the parallel physical operator (yellow circle with black arrows)

1_SQL_Server_Cost_Threshold_of_Parallelism

Processors here could be cores or even hyper-threading. You might have one physical processor with 4 cores and SQL Server can use all the cores. Mind you, affinity mask setting and max degree of parallelism should be set correctly so that you can use more than 1 processor.

The default value of ‘cost threshold of parallelism’ is 5. My audience had a blurred understanding that this value means that if the query takes more than 5 seconds to execute, the optimizer will switch to parallel plans. That is not correct ! There’s a lot more to that ‘cost’ thing. And other folks had other ideas !

SQL Server’s optimizer is a cost based optimizer and it compares the cost of multiple plans before outputting the plan with least cost. And that value of 5 is that cost – BOL says that that value is estimated elapsed time but I have something more to add to it.

So when the optimizer calculates that the cost of a serial plan is more than 5, it switches to a parallel plan. I can demonstrate that with a simple example.

Here is a query that uses parallel plan.

use AdventureWorks2008R2
GO
 
select * from sales.SalesOrderDetail
order by LineTotal DESC
GO

2_SQL_Server_Cost_Threshold_of_Parallelism

   

If you observe the execution plan and hover your mouse over the SELECT operator, you can see Estimated Subtree Cost is 4.37091. Now, this is a parallel plan and the cost is less than 5. But what is the cost of this plan if it was serial? Let us see that using MAXDOP hint:

use AdventureWorks2008R2
GO
 
select * from sales.SalesOrderDetail
order by LineTotal DESC
OPTION (MAXDOP 1)
GO

3_SQL_Server_Cost_Threshold_of_Parallelism

Using the MAXDOP hint, I forced SQL Server to use only one CPU and you can see that the cost is 10.4921. The plan is does not use the parallel physical operator and is a serial plan. This proves that since the cost of serial plan was more than the cost threshold of parallelism value of 5, the optimizer decides to go for a parallel plan.

Does this mean that if we change the value of 5 to 11 (for example), the optimizer will not output parallel plan? Answer is Yes, the optimizer will not output parallel plan. Let us test it.

First change the value:

sp_configure 'cost threshold for parallelism', 11
GO
RECONFIGURE
GO

Run the query again. (Note that there is no hint)

use AdventureWorks2008R2
GO
 
select * from sales.SalesOrderDetail
order by LineTotal DESC
 
GO

4_SQL_Server_Cost_Threshold_of_Parallelism

Does SQL Server produce parallel plan? What is the cost? Is it less than cost threshold of parallelism?

There’s lot more to this subject and I have just given a simple explanation to clarify the meaning of this server-level property.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

4 Comments on “SQL Server Cost Threshold for Parallelism”

  1. As per SQL Server Books Online “Configure the cost threshold for parallelism Server Configuration Option” http://msdn.microsoft.com/en-us/library/ms190949.aspx

    The cost threshold for parallelism option specifies the threshold at which SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

  2. Hi MArc, I knew someone would point that out 🙂 – unfortunately there are many of those one-liners in BOL that I do not understand 🙁 and thats why I explained in simple example using Estimated Subtree cost. However, I have updated my blog now to be a bit lenient on that BOL one-liner 🙂

Leave a Reply

Your email address will not be published.