Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tuning Parallelism Expand / Collapse
Author
Message
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: Monday, September 22, 2014 4:37 AM
Points: 1,379, Visits: 2,688
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
Post #1466078
Posted Sunday, June 23, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 14, 2014 5:41 PM
Points: 10, Visits: 119
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
Post #1466527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse