«

»

Apr 26

Determining a setting for “Cost Threshold for Parallelism”

I was asked to share this after a discussion at SQLSaturday Chicago by a presenter, David Klee. So, thank you David for getting me to finally post something!

A while ago, I was tasked with researching Max Degree of Parallelism and Cost Threshold for Parallelism settings for my company’s SQL Servers. While trying to find recommendations, I found that the Cost Threshold for Parallelism was mostly done by picking a number and testing. I could not find any way of even finding a starting number in some sort of objective way. I couldn’t just settle for “start at 25 or 30 and play with it to see how your server responds.” It was just too random for me, and I knew there had to be a better way.

My boss had given me a script from Jonathan Kehayias, some time before this, that returned a list of parallel query plans and their subtree costs that I thought could be used to gather some data for me.

;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
	query_plan AS CompleteQueryPlan
	, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText
	, n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel
	, n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
	, n.query('.') AS ParallelSubTreeXML
	, cp.usecounts
	, cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

I then took this script and modified it to simplify it for what I needed. All I really cared about were the costs. So I removed all other columns. I also thought it would be much easier to work with these numbers in a temp table, so I did that. The script below is now what I use to help me decide what to set my Cost Threshold for Parallelism. It gathers all of the costs, inserts them into a temp table, retrieves the data (not always needed and sometime too large to do anything with, so comment out if not needed), and calculates 3 important statistics; Mean, Median, and Mode.

CREATE TABLE #SubtreeCost(StatementSubtreeCost DECIMAL(18,2));

;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO #SubtreeCost
SELECT
	CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS DECIMAL(18,2))
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

SELECT StatementSubtreeCost
FROM #SubtreeCost
ORDER BY 1;

SELECT AVG(StatementSubtreeCost) AS AverageSubtreeCost
FROM #SubtreeCost;

SELECT
	((SELECT TOP 1 StatementSubtreeCost
	FROM
		(
		SELECT TOP 50 PERCENT StatementSubtreeCost
		FROM #SubtreeCost
		ORDER BY StatementSubtreeCost ASC
		) AS A
	ORDER BY StatementSubtreeCost DESC
	)
	+
	(SELECT TOP 1 StatementSubtreeCost
	FROM
		(
		SELECT TOP 50 PERCENT StatementSubtreeCost
		FROM #SubtreeCost
		ORDER BY StatementSubtreeCost DESC
		) AS A
	ORDER BY StatementSubtreeCost ASC))
	/2 AS MEDIAN;

SELECT TOP 1 StatementSubtreeCost AS MODE
FROM   #SubtreeCost
GROUP  BY StatementSubtreeCost
ORDER  BY COUNT(1) DESC;

DROP TABLE #SubtreeCost;

So what do I do with these numbers? In my case, I am trying to get “about” 50% of the queries below the threshold and 50% above. This way, I split the amount of queries using parallelism. This is not going to guarantee me the best performance, but I figured it was the best objective way to come up with a good starting point.

If all of my statistics are very close, I just set the Cost Threshold for Parallelism equal to about what that number is. An average of the 3 and round will work. In many of my cases, this was between 25 and 30. If the numbers are different, i.e. a few very large costs skew the average up but the median and mode are close, then I will use something between the median and mode.

Although this method does not spit out 1 number that will magically solve all of your problems, nor is it completely objective… It is the most objective way I have found to come up with a starting point. If you have any thoughts on this or would like to share your own experiences, please feel free to send me an email and I will respond as soon as I can.

15 comments

1 ping

Skip to comment form

  1. tonnypoulsen

    Thanks for the post. Very userful. One questio: What is the purpose of MODE? I can see that it is the Top 1 post from the subcosttable but unclear of the purpose.

    /Tonny

    1. Jared Karney

      MODE is just another statistics to help determine a good choice for cost threshold. It is the most frequently occurring value. If it is very different from my MEAN, it would indicate skew in the distribution and I may move my number closer to that from the MEAN.

  2. Avinash Kakarla

    AverageSubtreeCost
    980.126153

    MEDIAN
    259.730000

    MODE
    8.73

    Could you please suggest me how to decide cost of threshold based on above figures?

    1. Jared Karney

      It is hard to say, but I am guessing you have only a couple queries that are very high that are driving your average up. If you take some of those out of the equation, what do the calculations come out to? (Use Excel for that) Personally, I would set it somewhere between the MODE and the Median, and test. With numbers so far apart for those statistics, though, I would really put the data in Excel and look at how the raw numbers distribute.

      1. Avinash Kakarla

        I got total 100 values ,out of which 30 are below 100 and remaining values are above 100.
        Which set of values I need to consider for calculation 1) below 100 or above 100 .
        If I exclude them .. then the figures are like below
        Median 848.68
        Mode 347.26
        Average 1713.596471

        1. Jared Karney

          I wouldn’t exclude anything unless it is an outlier; i.e. a subtree cost of 2300000 or something. This is just a guide, so I would start with setting at just above your Mode and watching. Let me ask you this, though, what is your current setting?

          1. Avinash Kakarla

            We have 5

  3. Gaby Abed

    Love this. Found one more option you might consider, a weighted average:

    declare @totalusecounts int

    select @totalusecounts = sum(usecounts) from #statementcosts

    ;with Weighted AS
    (
    select (StatementSubTreeCost * usecounts)/@totalusecounts [Wgt]
    from #statementcosts
    )
    select sum(Wgt) [WeightedAverage] from Weighted

    1. Gaby Abed

      forgot to mention, in my case, the value is right in between MODE and Median., but thought it would be an interesting metric.

  4. llinares

    I’m confused about the section for MEDIAN. It looks like you are retrieving the highest value from the bottom half of the StatementSubtreeCost values and the lowest value from the top half of the StatementSubtreeCost values and then adding that value from the bottom half to that value from the top half which has been divided by 2 (i.e. Bottom + Top / 2)

    Wouldn’t the median be equal to adding the 2 values together first and then divide the result by 2 (i.e. (Bottom + Top) / 2 )?

    Thanks for the code. I had used Jonathan’s but yours takes out the guesswork..

    1. Jared Karney

      You are absolutely correct. I have updated the code above to reflect the correct sequence to account for order of operations. Thanks!

  5. Kiran

    Hello Jared,

    Really good post.

    Used your query and got below result on one of my Production Server(Mix Workload),

    Average Subtree Cost
    30892.34

    MEDIAN
    865.4

    MODE
    234.9

    Current setting :-
    2 Processors
    2 NUMA NODE
    Logical Processore 32
    RAM 512( Two instances, each instances 50+ Databases)
    MAXDOP :- 1 and Cost Thershold = 50

    I did not set this :). Some previous employee set this and this server has lot of performance issues

    What is your recommendation base on the above result?

    Thanks in Advance.

    -Kiran

    1. Jared Karney

      Personally, I’d immediately set the maxdop to 8. I’d monitor closely. There’s rarely a reason for maxdop to be set at 1.

      1. kiran_sqldba

        Thanks a lot for response. one more questions,what should be cost threshold between MODE :- 234.9 and MEDIAN = 865.4( 350)?

        Moreover, I executed your script on two more instances(SQL Server 2008 R2 Data Center Edition) and got blank result, didn’t understand what’s wrong with that. Any idea?

        1. Jared Karney

          Again, I’d have to see more data to determine what a good value is. Those are both pretty high, so I’d look at removing any outliers and then going somewhere where half is above and half is below. As far as a blank result set, I’m not really sure except your MAXDOP may be set to 1 or nothing is going parallel.

Leave a Reply