Is a Cost Threshold For Parallelism of 5 right…for me?

It has been said, “that the default value of 5 is pretty universally denigrated” and “Cost Threshold For Parallelism Shouldn’t Be Set To 5“. Using Mr. Scary’s query and letting it run for 3 hours (without completing) I returned the cost for over 9,800 plans.

WITH XMLNAMESPACES (
DEFAULT N’http://schemas.microsoft.com/sqlserver/2004/07/showplan’
)
, TextPlans
AS (SELECT CAST(detqp.query_plan AS XML) AS QueryPlan,
detqp.dbid
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_text_query_plan(
deqs.plan_handle,
deqs.statement_start_offset,
deqs.statement_end_offset
) AS detqp
),
QueryPlans
AS (SELECT RelOp.pln.value(N’@EstimatedTotalSubtreeCost’, N’float’) AS EstimatedCost,
RelOp.pln.value(N’@NodeId’, N’integer’) AS NodeId,
tp.dbid,
tp.QueryPlan
FROM TextPlans AS tp
CROSS APPLY tp.queryplan.nodes(N’//RelOp’)RelOp(pln)
)
SELECT qp.EstimatedCost
FROM QueryPlans AS qp
WHERE qp.NodeId = 0;

Rounding to zero decimals and subtotaling the costs leaves me with a less than normal distribution. 93%, or over 9,100, plans have a rounded cost of 0 and 95% of the plans are 1 or less query bucks, to use the popular term for cost. Leaving the Cost Threshold For Parallelism at 5 covers 97% of the plans I returned.

20170317-NotANormalDistribution

These plans are from a SQL Server 2008 R2 SP3 instance that runs a version of Microsoft Dynamics. The MS Dynamics team knows their product issues a bunch of really small queries and recommend a Max Degree of Parallelism of 1. In my case there is one query plan with a cost of 34,402 query bucks that a MAXDOP = 1 can’t afford. Increasing the MAXDOP from 1 while keeping the Cost Threshold at 5 will keep all of my little queries running in their single threaded paradise while allowing some infrequent biguns to spread their lovely wings across a few more threads. My Cost Threshold For Parallelism is set to 5 and I hope to never have to defend my settings in a dark alley with Erik Darling.

Advertisements