Cost threshold for parallelism - Subtree Cost

Question

Cost threshold for parallelism - Subtree Cost

david.alcock

SSCertifiable

Points: 6021
More actions
July 3, 2014 at 7:36 am

#309740

Hi all, wonder if somebody would be so kind and clear something up!?
Firstly am I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
Apologies if I have the wrong end of the stick(s)!
As always, many thanks!
D
'Only he who wanders finds new paths'

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Neither cost threshold nor subtree costs are measured in minutes. They're a unit-less number that only makes sense when compared to another cost.

A query of cost 14 should be faster and use less resources than a query of cost 45. That's it.

If the total query cost is higher than the cost threshold, the optimiser will consider a parallel plan. That doesn't mean the query will run in parallel, just that the optimiser will consider trying a parallel plan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david.alcock SSCertifiable Points: 6021 More actions · Answer 2

GilaMonster (7/3/2014)
Neither cost threshold nor subtree costs are measured in minutes. They're a unit-less number that only makes sense when compared to another cost.
A query of cost 14 should be faster and use less resources than a query of cost 45. That's it.
If the total query cost is higher than the cost threshold, the optimiser will consider a parallel plan. That doesn't mean the query will run in parallel, just that the optimiser will consider trying a parallel plan

Thanks Gail. That is what I always understood however in the accidental dba book I read this which I read to say that the cost is based in seconds hence my confusion as to how the subtree cost and threshold can be compared:

The "cost" is the estimated amount of time in seconds that it would take to execute

the query serially with a given execution plan. The default value is five, meaning that

a parallel plan will only be generated and used by queries that are estimated to take

longer than five seconds to execute serially on the given system.

D

'Only he who wanders finds new paths'

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

It did start out as a figurative unit of time. Now it's just a figurative unit. It's never been meant as an actual measure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If that's in the Accidental DBA book, I'll have to have words with the technical editor (oh wait, that was me). That's an error

No, they're not. The costs are not times, they haven't been remotely associated with times since the early development stages of SQL Server 7.

Cost threshold for parallelism is a unit-less number.

Subtree cost is a unit-less number

If a query's subtree cost is greater than the cost threshold, the optimiser will consider a parallel plan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

david.alcock SSCertifiable Points: 6021 More actions · Answer 5

Thanks Gail/Grant - I have to admit it was the cause of some debate in the office as my colleague spotted the chapter in the book and I was adamant it wasn't the case (time based) but then found quite a few bits on the web etc to suggest it is.

As always, thank you ever so much for providing the answers and taking the time to do so. Appreciated.

D

'Only he who wanders finds new paths'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Just to put things in perspective and explain where that myth comes from...

During the development of SQL Server 7, a particular piece of reference hardware in the MS lab was used to calibrate the costing model. If, on that specific piece of hardware, a query ran 2 seconds, then it was assigned a cost of 2 and the costing algorithms were adjusted to produce 2 as the cost of that query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lempster SSCoach Points: 15651 More actions · Answer 7

Where do you get these nuggets of information from Gail? 🙂

david.alcock SSCertifiable Points: 6021 More actions · Answer 8

GilaMonster (7/3/2014)
Just to put things in perspective and explain where that myth comes from...
During the development of SQL Server 7, a particular piece of reference hardware in the MS lab was used to calibrate the costing model. If, on that specific piece of hardware, a query ran 2 seconds, then it was assigned a cost of 2 and the costing algorithms were adjusted to produce 2 as the cost of that query.

Right I see, wow...

Thanks Gail!

'Only he who wanders finds new paths'

Perry Whittle SSC Guru Points: 233821 More actions · Answer 9

Lempster (7/3/2014)
Where do you get these nuggets of information from Gail? 🙂

Paul Randal 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉