Contact us

Madeira Data Solutions

Your Data, Our Solutions

Planning to Increase Cost Threshold for Parallelism – Like a Smart Person!

Written By: Eitan Blumin 27/02/2018

Planning to Increase Cost Threshold for Parallelism

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.
 
Luckily, we can use a DMV such as sys.dm_exec_cached_plans to help us.
This view can give us the information we need, which is:
 
  • Which execution plans currently use parallelism.
  • What are their sub-tree costs.
  • How often these plans are used (i.e. “usecount”).
 
And most importantly, using that information, we could figure out what would happen if the plan was not allowed to use parallelism.
“What would happen if” is a very smart question to start with. It’s like an hypothesis. It’s science!
 
Anyways, for this purpose, I’ve prepared a special SQL script.
See below:
 

 

 
There are 3 ‘parameters’ for that script:
  • @MinUseCount – Determines the minimum usecount of execution plans. Use this parameter to filter out “noise” from rarely-used, or one-time plans.
  • @CurrentCostThreshold – Gets the current configuration value, serves as minimum value (5 by default).
  • @MaxSubTreeCost – Serves as maximum value for sub-tree costs. We would want to use this parameter to filter out actually-heavy execution plans that we really want to be using parallelism. You should put here the new cost threshold that you consider on using (or something a little higher).
 
The data returned by the script would be a list of execution plans, their respective SQL statements, the Sub-Tree cost of the statements, and their usecounts.
 
Using this script, you will be able to identify execution plans that use parallelism, which may stop using parallelism if you change “cost threshold for parallelism” to a value higher than their respective sub-tree cost.
 
Now comes the difficult part: You’ll need to investigate each of these plans, and think about what would happen if they were not allowed to be run with parallelism.
For example, if the plan performs a 
table scan, then the negative impact of it no longer using parallelism may be catastrophic!
 
In most cases, you will need to embark on a magnificient adventure of performance tuning for these execution plans, to make sure that nothing too bad would happen if they were no longer able to run with parallelism.
 
Maybe that horrible table scan can be avoided altogether if you just add the right covering index? Or write the query a bit differently? Or maybe there’s no way to improve the query so you’re just better off setting the configuration to a value lower than the sub-tree cost of that plan? Or maybe it’ll be just fine and dandy without parallelism after all??
 
To test all of those questions, you can take the original text of those queries, and run them using the query hint OPTION(MAXDOP 1) to force them to run without parallelism.
Observe the results, and decide whether the impact of the query is acceptable without parallelism.
 
Hypothesize… Collect data… Experiment… Observe… Draw conclusions! That’s science!
 
Good luck and enjoy your adventure, you beautiful scientist, you!
 
 
 

8 responses to “Planning to Increase Cost Threshold for Parallelism – Like a Smart Person!”

  1. Kev says:

    Really interesting article, many thanks for sharing

  2. Hiren says:

    This is so awesome! putting science in performance tuning. Thank you so much for the information.

  3. DBA100 says:

    “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.”

    But MAXDOP should be the minimum sub tree cost ,why 1 will force them to run without parallelism ?

    • Eitan Blumin says:

      MAXDOP tells the SQL Server what’s the maximum allowed “degree of parallelism”.
      In other words: How many threads are allowed to run in parallel for a given query.

      If MAXDOP is 1, that means only one thread is allowed to run at any given time.
      In other words: It’s not parallelism.

  4. DBA100 says:

    “To test all of those questions, you can take the original text of those queries, and run them using the query hint OPTION(MAXDOP 1) to force them to run without parallelism”

    Actually MAXDOP 1 is good enough for a lot of situation. our previous company use it but on server level option, not per query level.

    • Eitan Blumin says:

      That means your SQL Server doesn’t use parallelism at all.
      This may be a good fit for very specific situations, but generally the parallelism feature is a very cool and useful feature, especially when dealing with very large quantities of data.

  5. Shimon Gibraltar says:

    Great read. Thanks

  6. Alex Friedman says:

    Cool! Thanks for sharing

Leave a Reply

Your email address will not be published. Required fields are marked *