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 creating 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 the “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 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 a hypothesis. It’s science!
Anyway, for this purpose, I’ve prepared a special SQL script.
See below:
DECLARE
@MinUseCount INT = 50 -- Set minimum usecount to ignore rarely-used plans
, @CurrentCostThreshold FLOAT = 5 -- Serves as minimum sub-tree cost
, @MaxSubTreeCost FLOAT = 30 -- Set the maximum sub-tree cost, plans with higher cost than this wouldn't normally interest usSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @CurrentCostThreshold = CONVERT(FLOAT, value_in_use)
FROM sys.configurations
WHERE [name] = 'cost threshold for parallelism';
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT *
FROM
(
SELECT
ecp.plan_handle,
CompleteQueryPlan = query_plan,
StatementText = n.value('(@StatementText)[1]', 'VARCHAR(4000)'),
StatementSubTreeCost = n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)'),
ParallelSubTreeXML = n.query('.'),
ecp.usecounts,
ecp.size_in_bytes,
RankPerText = ROW_NUMBER() OVER (PARTITION BY n.value('(@StatementText)[1]', 'VARCHAR(4000)') ORDER BY ecp.usecounts DESC)
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1AND ecp.usecounts > @MinUseCount
AND n.value('(@StatementSubTreeCost)[1]', 'float') BETWEEN @CurrentCostThreshold AND @MaxSubTreeCost
) AS Q
WHERE
RankPerText = 1 -- This would filter out duplicate statements, returning only those with the highest usecountORDER BY
usecounts DESCThere 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!
