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.
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.
Tim Peters 
You’re lucky, because even I avoid the dark alleys in Dynamicsville.
It’s totally different for 3rd party apps like Dynamics (and SharePoint) where the workload is designed a certain way. Most 3rd party apps don’t have that luxury; ditto in-house ones.
I’ve read a lot of Dynamics experts (though not necessarily with shared expertise in SQL) say to set MAXDOP to 1 on Dynamics boxes, but then people run into the issue you describe — those rare birds with a high cost run painfully serial. I also get the feeling that reporting of Dynamics data is much more common than SharePoint data.
Anywho.
NICE BLOG, BE A SHAME IF SOMETHING HAPPENED TO IT
::flips coin across knuckles::
LikeLike