Site icon Eitan Blumin's blog

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

Science FTW!

Science FTW!

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.

What can we do?

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:

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 a hypothesis. It’s science!

The “Laboratory”

Anyways, for this purpose, I’ve prepared a special SQL script.

See below:

The script is available in my GitHub Gists

There are 2 ‘parameters’ for that script:

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 the sub-tree cost of their non-parallel counterpart (i.e. when you specify OPTION(MAXDOP 1)).

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 magnificent 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.

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!

This article was originally published by Eitan Blumin on February, 2018 in www.madeiradata.com

Cross-post on LinkedIn on August, 2018

Cover image by luvqs from Pixabay

Exit mobile version