Cost Threshold Paralllelism -

Question

Post reply

Cost Threshold Paralllelism -

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
April 21, 2012 at 4:14 pm

#254618

All this time i was thinking that value for CTP is unitless. But based on this 'http://technet.microsoft.com/en-us/library/ms188603%28v=sql.90%29.aspx' looks like it is in seconds? Can someone please confirm this. I have gone through few different posts here and i couldn't find the. exact answer

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Cost threshold is in secs based on what the optimiser thinks a query wy take in secs

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

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

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

A query's cost is not the number of seconds that SQL thinks it will take to run. Yes, the documentation says it is, the documentation here is wrong. Costs (and hence cost threshold) is a unitless number that gives a rough idea of how expensive a query is.

The costs were time at one point, back when SQL Server 7 was being developed a particular piece of hardware in the MS lab was used as a guide for calculating query costs. On that machine (well over 15 years ago), if a query took one second, it would be given a cost of one.

Best just to to think about the cost as a unitless number that you can use to compare one query's estimated cost to another query's estimated cost.

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

GilaMonster (4/22/2012)
A query's cost is not the number of seconds that SQL thinks it will take to run. Yes, the documentation says it is, the documentation here is wrong. Costs (and hence cost threshold) is a unitless number that gives a rough idea of how expensive a query is.
The costs were time at one point, back when SQL Server 7 was being developed a particular piece of hardware in the MS lab was used as a guide for calculating query costs. On that machine (well over 15 years ago), if a query took one second, it would be given a cost of one second.
Best just to to think about the cost as a unitless number that you can use to compare one query's estimated cost to another query's estimated cost.

That's exactly what i thought. So if i have CTP =5 and MAXDOP =1, does it mean that any query who's CTP is more than 5 will use more than 1 processor?

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

sqldba_newbie (4/22/2012)
That's exactly what i thought. So if i have CTP =5 and MAXDOP =1, does it mean that any query who's CTP is more than 5 will use more than 1 processor?

No. If MAXDOP is 1, queries can only ever use one processor (unless overruled by a maxdop hint). That's what maxdop means, maximum degree of parallelism

If you change maxdop to be something other than 1, then any query who's estimated cost is > cost threshold for parallelism may be considered for a parallel plan. Doesn't mean it will run in parallel, just that it may be considered for 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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

GilaMonster (4/22/2012)
sqldba_newbie (4/22/2012)
That's exactly what i thought. So if i have CTP =5 and MAXDOP =1, does it mean that any query who's CTP is more than 5 will use more than 1 processor?
No. If MAXDOP is 1, queries can only ever use one processor (unless overruled by a maxdop hint). That's what maxdop means, maximum degree of parallelism
If you change maxdop to be something other than 1, then any query who's estimated cost is > cost threshold for parallelism may be considered for a parallel plan. Doesn't mean it will run in parallel, just that it may be considered for a parallel plan.

Thanks. So when MAXDOP is set to 1 no matter what CTP value is set to query would ever use ONLY 1 processor?

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

sqldba_newbie (4/22/2012)
So when MAXDOP is set to 1 no matter what CTP value is set to query would ever use ONLY 1 processor?

CTP will have no bearing when maxdop is set to 1. You can as already stated override the server level maxdop setting using a query hint.

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

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

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

GilaMonster (4/22/2012)
A query's cost is not the number of seconds that SQL thinks it will take to run. Yes, the documentation says it is, the documentation here is wrong. Costs (and hence cost threshold) is a unitless number that gives a rough idea of how expensive a query is.
The costs were time at one point, back when SQL Server 7 was being developed a particular piece of hardware in the MS lab was used as a guide

Thanks, I was under the impression that although in secs it was not real secs just what is perceived by the optimiser. I'll update my docs.

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

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