January 20, 2017 at 9:53 am
I understand that Cost Threshold for Parallelism is the "cost" that the optimizer will use to go to Parallel, but I have one question.
There are four costs in the plan, which are Operator, I/O, Subtree and CPU so is it using all of them? Is it a formula of all of them or Just one of them as the cost? This is where I'm not certain. Appreciate any clarification!
Thanks,
January 20, 2017 at 10:12 am
butcherking13 - Friday, January 20, 2017 9:53 AMI understand that Cost Threshold for Parallelism is the "cost" that the optimizer will use to go to Parallel, but I have one question.There are four costs in the plan, which are Operator, I/O, Subtree and CPU so is it using all of them? Is it a formula of all of them or Just one of them as the cost? This is where I'm not certain. Appreciate any clarification!
Thanks,
This article might answer your question: https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 20, 2017 at 10:20 am
The way I understand it, for a given task in the execution plan, Operator cost is IO cost + CPU cost. Subtree cost is the cost of the task and any other tasks in the same branch of the execution tree that are further to the right or down. Cost Threshold for Parallelism is considering the maximum cost of the entire tree for all possible execution plans. The entire tree cost is the Subtree cost shown in the task (such as the SELECT task) at the upper left root node of the tree.
https://www.brentozar.com/archive/2014/11/sql-server-cost-threshold-for-parallelism/
January 20, 2017 at 10:21 am
Okay, based off this article and what I just read it looks like it is the Subtree cost. Unless someone says differently. Thanks!
February 9, 2017 at 8:38 am
The 2 articles are great references for this setting.
Remember that SQL Server Optimizer is cost based, so the Cost Threshold is for SQL to decide whether it needs to "Consider" a parallel plan, not a guarantee that it will go parallel. If the Cost Threshold is met, then it will generate a Parallel plan and if the Parallel Plan is cheaper then SQL can decide to use it. No guarantees, just consideration.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply