Cost Threshold For Parallelism – Best Practice and Recommendations

Most DBA’s will make sure they are across the Maximum Degree of Parallelism setting in SQL Server.

A setting often ignored though is Cost Threshold for Parallelism.

I have also been guilty of not paying enough attention to this setting. Until some recent performance tuning I was working on ended up with me taking a deep dive into this area.

CTFP

What is CTFP?

CTFP is the threshold cost value that SQL Server uses to determine whether or not to parallize a query. By default this is set to 5 however this is generally considered too low.

The threshold value is not an arbitrary number it is loosely related to the cost value in terms of the estimated time in seconds it would take to run the query serially. So if the estimate is “6 seconds” to run serially and your threshold is set to 5 it will parallize it in an attempt to maximize execution time.

ctfp_subtree-cost

Performance problems can arise when it actually takes longer to do it in parallel than what it would take to do it serially.

Best Practice for CTFP

As with many settings there is not a magic number that will provide the best performance for all environments. The general consensus appears to fall between 15-50 depending on the environment. So the recommendation is to set and test as much as possible.

By doing some smart analysis of your query plans you should be able to identify which queries will be affected by changing the setting.

For SQL Server running on VMWare the general recommendation is to set this value to 50. You really need to be careful changing this setting though, as it may slow down important queries in production.

Helpful Query for CTFP Planning

This query is written by Jonathan Kehayias and scans the cache for all the queries that are using a parallell query.

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

What about your experiences with CTFP?

How have you used this setting in your environment? Please share your experience.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

Leave a Reply