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.
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.
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)', 'VARCHAR(4000)') AS StatementText, n.value('(@StatementOptmLevel)', 'VARCHAR(25)') AS StatementOptimizationLevel, n.value('(@StatementSubTreeCost)', '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.