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!
Best Answer
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/