We have observed a few cases now where sql server has started performing slow after changing the Cost Threshold Of Parallelism server option of the sql server.
Ironically in such cases we observe that sql server is performing slow even when there is no high CPU utilization and no high I/O operations on the server. Increasing the Cost Threshold Of Parallelism decreasing your CPU utilization as it reduces the Parallelism in the execution plan of some of the queries which are fired on the database and in turn slows their response time and hence the performance of sql server deprecates.
As defined in the BOL,
Cost threshold for parallelism option is used to specify the threshold at which Microsoft 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. Only set cost threshold for parallelism on symmetric multiprocessors.
The Default Value of the Cost Threshold Of Parallelism is 5 which means when Optimizer creates and estimates the cost of the query if the cost exceeds 5, optimizer considers this query as a candidate for the parallel execution plan and hence it selects the Parallel plan for this query. However the Degree of Parallelism depends on the Number of Processors in the System and the MAX DOP setting of the sql server. Based on these values optimizer selects a parallel execution plan which consumes higher CPU but with much better response time of the queries and hence the performance of sql server increases.
Cost Threshold of parallelism can also be used to reduce the parallelism in the sql server if the parallel queries consume excessive CPU so we can increase the Cost Degree of Parallelism and change the behavior of sql server to use serial execution plans.
Usually it is always a good option to go with default value for the Cost Threshold Of Parallelism and avoid manipulating the behavior of optimizer unless it is tested on your environment with the same workload as production.
It’s good to know that it’s best to go with the default value for the Cost Threshold Of Parallelism and avoid manipulating the behavior of optimizer. That might be why I’ve been having problems. I’ll have to do some tests and see how the optimizer responds in our production environment. Thanks