When to make changes to the cost threshold for parallelism

Question

While examining a performance issue , I have seen an influx on CXPACKETS suggesting I might need to look at the cost threshold for parallelism and perhaps the MAXDOP.

Before making any drastic changes to the MAXDOP I have following the advice of many others including that of @mrdenny in the answer to CXPACKET Waits performance tune for SQL Server 2008 and @aron-Bertrand 's answer from Dealing with CXPACKET waits - setting cost threshold for parallelism. I have added to the maintenance to update the statistics fully on a nightly basis. This feels like a sensible move.

However, making modifications to the cost threshold is still something which niggles me.

At what point should the cost threshold for parallelism be altered? Does any one have an example of where (after examining the cost of their queries and workload) they made change to this cost?

Apologizes if this is something which which has been answered in a previous question.

Thanks!

Answer 1

Using MAXDOP = 1 can be a help, but it is a big gun. It may be that the actual problem is the usefulness of the indexes. Perhaps a new or a different index would resolve the problem.

Following Mr Denny and Aaron Bertrand's comments, did you discover what other waits in that connection were likely the cause of the CXPACKET waits?

Jonathan Kehayias suggested a query that might help you assess you parallelism experience and make a more thoughtful decision. But you should also read the conversation between Jonathan and Paul White.

https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/

Answer 2

I would suggest you to first look into MAXDOP settings as the default setting of 0 (use all available threads) might be dangerous as a runaway query consuming all the available threads will lead to thread starvation.

Refer to my answer here for how to calculate MAXDOP setting for your server instance.

Cost threshold of parallelism refers to what the minimum query cost has to be before Parallelism is considered by the optimizer.

Remenber that CXPACKET waits are just symptoms due to something being wrong related to query - outdated statistics or missing index resulting in a bad or different plan.

You can use sys.dm_exec_cached_plans and sys.dm_exec_query_plan DMV's to mine information from the plan cache as described in Tuning ‘cost threshold for parallelism’ from the Plan Cache by Jonathan and Cost Threshold for Parallelism.

I would suggest to keep the cost threshold for parallelism as default unless you have exhausted resources tuning queries, doing maintenance of Indexes and statistics as well as checked if you are not having any missing indexes that your query might get benefit.

Note: Maxdop setting can be also applied at the query level using OPTION (MAXDOP n) which will override the server wide setting.