In production environment found that the default value of 5 is there for Cost Threshold for parallelism and 0 for Max Degree of Parallelism. From various resources I understand that it is not the optimal value and has to be changed. Now the question I am trying to figure out is what might be the optimal value for my environment.
With the help of sys.dm_os_sys_info found that the environment has:
- Physical CPU = 1 & Logical CPUs = 4
So does it mean I can set Max Degree of Parallelism as 4? If not please help me understand what to look for and decide the value for it.
Made use of Jonathan Kehayias below script:
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
and this is the summary I found:
- StatementSubTree cost: Mean is 36.5, Mode is 12.78 and Median is 9.38
- Totally ~850 entries out of which ~750 entries subtree cost is less than 50. And ~25 is above 100 & out of which 15 is above 500.
So wondering should I change the cost threshold value as nearest to median or mean. If that is not the way to do please guide me the steps to be followed.