One question constantly comes up: "What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to?"
What Do Your Plans Cost?
I have a question right back at you. What do your plans currently cost? Let’s say, for argument’s sake, that all your plans have an estimated cost value (and all plan costs are estimates, let’s please keep that in mind, even on actual plans) of 3 or less. Do you need to adjust the cost threshold in this case? Probably not. But the key is, how do you look at the costs for your plans? Unfortunately, there isn’t a property in a DMV that shows this value. Instead, we have to query the XML:
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;
You can add other fields if you need to, but this query will return all the costs from the execution plans currently in cache.
Does Query Store make this any easier? Not really. There isn’t a property that just shows the cost of the plans, so once again, we’re forced to query the XML:
WITH XMLNAMESPACES ( DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ) , QueryStore AS (SELECT CAST(qsp.query_plan AS XML) AS QueryPlan FROM sys.query_store_plan AS qsp ), QueryPlans AS (SELECT RelOp.pln.value(N'@EstimatedTotalSubtreeCost', N'float') AS EstimatedCost, RelOp.pln.value(N'@NodeId', N'integer') AS NodeId, qs.QueryPlan FROM QueryStore AS qs CROSS APPLY qs.queryplan.nodes(N'//RelOp')RelOp(pln) ) SELECT qp.EstimatedCost FROM QueryPlans AS qp WHERE qp.NodeId = 0;
With these queries, you can get the values for the costs on your plans. This information can be used to determine how high you make your Cost Threshold for Parallelism setting based on actual knowledge. Just be cautious, this query can be quite expensive. The next step is to take these results and derive an average and a standard deviation. That’s where we’re going with the next blog post, using R to determine Cost Threshold for Parallelism.