Header_Footer

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.

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:

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:

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

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.

 

 

Register for my SQL Server Performance Tuning Master Class!

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Browse my blog posts on SQLMaestros
FB page | Connect on LinkedIn | Google+ | Know more about me

Create Tail Log Backup when Data files are not available
SQL Server Copy Only Backup