Search code examples
sql-serverperformanceconfiguration

What's the Cost Threshold for Parallelism value I should set for this SQL Server?

Here is the output from Brent Ozar's EXEC [dbo].[sp_BlitzFirst] @SinceStartup = 1; Thank Brent, It gives me the whole picture of the WATIS on this box.

But my question is what's the number of the cost threshold for parallelism should I set instead of the default 5?

So I read through Jonathan's https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/

But I cannot get the results by that query and my SQL Server is SQL 2016 SP2-CU12.

Any thoughts?

Thanks in advance!

enter image description here

Solution

  • Agreed. There is no magic number. It depends on the workload and/or problem you're trying to solve.

    A CTFP of 5 is too low for nearly all cases.

    50 is a good starting point, and adjust up or down from there.

    Parallelism by itself is not an issue. It can be a good thing for trawling through huge datasets. If no one's complaining, though, why bother fixing it?

    If, however, you know queries or procs are going parallel when they definitely shouldn't be, these wait times might point to a problem.

    If you're a fan of Brent Ozar, try running sp_BlitzCache with @SortOrder = 'reads' or @SortOrder = 'cpu'.

    That should return the worst performing queries, in terms of page reads or cpu time. I find the Warnings and Query Plan columns a good launch pad for further investigation.