Tuning Parallelism

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  June 21, 2013 at 1:34 am

  #274272

  Hi All

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

  Using the query below, taken from http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/. 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?

  Query:

  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)[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

  Thanks

• Matan_Yungman

  SSC Eights!

  Points: 906

  More actions

  June 23, 2013 at 8:36 am

  #1626608

  Hi,

  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.

  Matan