• Eitan Blumin

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

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:

  1. Which execution plans currently use parallelism.

  2. What are their sub-tree costs.

  3. 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:

Use this script to investigate parallelism plans from the cache

Transact-SQL

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 us SET 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"]') = 1 AND 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 usecount ORDER BY usecounts DESC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

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 us

SET 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"]') = 1

AND 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 usecount

ORDER BY

usecounts DESC

There are 3 ‘parameters’ for that script:

  1. @MinUseCount – Determines the minimum usecount of execution plans. Use this parameter to filter out “noise” from rarely-used, or one-time plans.

  2. @CurrentCostThreshold – Gets the current configuration value, serves as minimum value (5 by default).

  3. @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!

#cpu #performance #Parallelism

CONTACT US

 +972-9-7400101

 3 Rapaport St. Kfar Saba, Israel

JOIN OUR MAILING LIST

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle