Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Tuning Parallelism Expand / Collapse
Posted Friday, June 21, 2013 1:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 13, 2016 1:22 AM
Points: 1,416, Visits: 2,931
Hi All

I've been trying to tune Cost Threshold for Parallelism on a test system

Using the query below, taken from I have isolated a query that has a subtree cost of 93. I changed my Cost Threshold for Parallelism to 95 but the query still uses parallelism.

Am I missing something here?

SELECT query_plan AS CompleteQueryPlan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
n.query('.') AS ParallelSubTreeXML, ecp.usecounts, ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
ROSS 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

Post #1466078
Posted Sunday, June 23, 2013 8:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 27, 2016 8:02 AM
Points: 29, Visits: 221
Basically, the parallel plan is chosen if its cost is lower than the cost of the serial plan.
The thing is the parallel plan's cost is calculated a little different, so most likely the serial plan's cost is higher than 95.

Here's two options:
1. Run the query with option(maxdop 1). That way you will know the serial plan's cost.
2. Raise the cost threshold for parallelism to a very high number and start lowering it until the parallel plan returns, and it will give you an idea of the serial plan's cost.

Hope it helps.
Post #1466527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse