Tuning ‘cost threshold for parallelism’ from the Plan Cache

A good while ago I was asked if it was possible to consistently produce a parallel plan by Tom LaRock(blog/twitter), and I recalled from reading Grant Fritchey’s(blog/twitter) book on Execution Plans that it was possible to do this by adjusting the ‘cost threshold for parallelism’ sp_configure option from the default of five to a value of one which will make even simple queries run in parallel if you have multiple logical processors. 

This got me think about this option and wondering if the default is still applicable in today’s servers and database environments.  If you think about when the default values for SQL Server were actually created, it was a long time ago and in today’s large commodity hardware servers some of them aren’t the best for SQL any longer.  For example, most people migrating from an x86 SQL Server to a x64 server learn really quick that you have to set Max Server Memory on a 64 bit server, something that you could get away with not setting on older x86 hardware even though it was still a recommended practice. 

Max Degree of Parallelism is another hot one these days with newer Xeon Quad Core and Nahelem six core processors and reduced server costs, it is easy to slam sixteen or twenty-four cores into a relatively cheap server that has 64-128GB of RAM, a configuration that only a few years ago was specialty hardware that was cost prohibitive for most shops.  These kinds of servers have quickly become the answer to performance problems that aren’t necessarily hardware related but instead poor design.  What you get is a person with no idea how SQL Server works, with a really powerful server that is full of performance problems because it is trying to run with the default settings which aren’t appropriate for this size of hardware.

In Bob Ward’s session on wait stats at PASS, one thing he said a couple of times is that the answer to CXPACKET waits, isn’t to reduce the ‘max degree of parallelism’ sp_configure option for the hole server.  I have to admit that I am guilty of recommending this solution hundreds of times before PASS and in most cases I continue to recommend this solution to people who have ho idea what they are doing  because:

A.  Most seem to be to lazy to read the referenced whitepapers provided to them for troubleshooting.
B.  They have no idea what they are doing.
C.  They want a quick fix that gets their manager off their back.

One thing that Tom’s question lead me to thinking about was whether or not adjusting the ‘cost threshold for parallelism’ up from five to a number that still allows large queries that don’t execute frequently to work with parallelism, while minimizing the number of smaller queries that execute more frequently from running in parallel.  To help with determining where this option might be set, I wrote a query to search the plan cache for existing parallel plans and see the cost associations to current plans that executed parallel.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
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,  
     ecp.usecounts, 
     ecp.size_in_bytes 
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 

I look at the high use count plans, and see if there is a missing index associated with those queries that is driving the cost up.  If I can tune the high execution queries to reduce their cost, I have a win either way.  However, if you run this query, you will note that there are some really high cost queries that you may not get below the five value.  If you can fix the high use plans to reduce their cost, and then increase the ‘cost threshold for parallelism’ based on the cost of your larger queries that may benefit from parallelism, having a couple of low use count plans that use parallelism doesn’t have as much of an impact to the server overall, at least based on my own personal experiences.

13 thoughts on “Tuning ‘cost threshold for parallelism’ from the Plan Cache

  1. Hi John,

    it’s a very interesting article as I often wonder how to configure the cost threshold for parallelism value.
    Many thank’s for this precious querie that will be helpful !!!

    by the way, did you know there is traceflag 8649 that allow you to force parallelism usage ? Advantage of this trace is that you can use it as a query option only (and not for the entire instance).

    O.

  2. Thanks for the great article Paul, exactly what i was looking for as i’m investigating MAXDOP/Cost Threshold for my work servers. One question, from the output of your script, say i had a parallel plan with a cost of 150. After checking for missing indexes (say, to resolve an index scan), how can i tell if it still benefits from parallelism or if i should increase the Cost Threshold to > 150 so it would run serially in the future? Off the top of my head, i would think to analyze the stats/io of the query as it is, and then testing it with MAXDOP=1 to simulate serial plan..?

    Thanks in advance again!

  3. Paul just referenced this at the seminar. Good thing to try out. Thanks! This will be interesting to see what I get in my environments.

  4. Hi Jonathan
    I looked at your query and understood it. Thank you very much for the same.

    What I see is the cost for some subtrees is as high as 2500. is it then prudent to change the setting to 2500 or just change the maxdop to 1?

    1. Why would you set instance level max degree of parallelism to 1 because you have cost 2500 queries? That’s like saying I have a train with 2500 cars, so I am going to remove all but one locomotive from the train. Tune the queries, or if you test and prove those specific queries don’t benefit from parallelism then maybe hint the individual queries down to where they perform best, but I wouldn’t ever tell someone to totally disable parallelism unless they had a edge case OLTP workload that was tuned to not use parallelism and designed around that.

  5. Hi, is there a way to also get some informations about the query duration and actual CPU and I/O usages ?

  6. We just implemented a new SQL Server-based application last month and I see a huge amount of CXPACKET waits. My SQL Server still has the default configuration. I just ran your query and found that 4.2% of my queries have a StatementSubTreeCost below 5 and 95.8% with a cost above 5. I think I just found part of my problem.

    My median StatementSubTreeCost is 35.5 so I think I’ll bump up my cost threshold and start tuning the worst offenders. Thanks very much for the post – it’s very useful.

    Ken H

  7. Thank you for this work. I have a question in regards to understanding your query. On my server the CTFP is set at 50, but this query is returning queries where the statementsubtreecost is less than 50. I’m trying to understand why they are being included in the result set, as they shouldn’t have actually executed as a parallel query due to the current CTFP being 50 and their cost is lower.

    Please let me know what I’m missing.

    Thank you in advance

    1. CTFP applies to the Serial execution plan cost, which can be higher than the parallel plan cost. If you looked at serial plan the cost would be higher than CTFP which is why it created the parallel plan.

  8. Ref: https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings (Configure SQL Server and storage settings [Microsoft Dynamics Ax])

    In DBs world we just listened IT DEPENDS; but this is the unique one instance in which regardless of all advancements this document still pointing same value which was once devised in 2008. Kindly any intellectual thoughts on it; especially the alternative discussed to increase the value of the cost threshold?

    Thanks

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.