When administrating a SQL Server instance with multiple CPU cores and heavy workload, it’s common to see SQL Server creating and using execution plans with parallelism. The instance configuration “cost threshold for parallelism” is what determines for SQL Server the minimum sub-tree cost before it starts considering to create a parallelism plan. The default “out-of-the-box” value of this configuration is 5.
However, in some cases, we would want to increase the default configuration of “cost threshold for parallelism” to something higher than the Microsoft default of 5, thus decreasing the frequency in which SQL Server creates parallelism plans.
This is something we would usually want to do when we see too much CPU workload as a result of parallelism plans, and we would want to reduce it.
But how can we know which new value we should use? Do we just throw a randomly high number such as 50 and see what happens (as is often recommended in most blogs)?
Well, no, we don’t actually have to do that.
We can do it like smart people.
Being smart is a good thing. Continue reading Planning to Increase Cost Threshold for Parallelism – Like a Smart Person!