Post reply

Tuning Parallelism

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply