Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MAXDOP - What happens when CTP is < ?


MAXDOP - What happens when CTP is < ?

Author
Message
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
I have read so many articles, may be i am just missing the point. My understanding is you set your CTP and MAXDOP, if the cost of a given query is greater than set CTP then SQL Server MIGHT use upto number of processors specified in MAXDOP.

MAXDOP = 4 , CTP = 20 ( Server has 24 logical cores, right click -> task manager )

All queries who cost is > 20, each operator might use upto 4 cores --- Is there a possibility it will use less than or more than 4?


What happens when cost is less than 20, queries might use all 24 cores? I couldn't find a article which talks about the impact when cost is less than set CTP value.

Appreciate if someone can clarify and point me to a good article. Thanks
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
curious_sqldba (2/28/2014)

I have read so many articles, may be i am just missing the point. My understanding is you set your CTP and MAXDOP, if the cost of a given query is greater than set CTP then SQL Server MIGHT use upto number of processors specified in MAXDOP.

MAXDOP = 4 , CTP = 20 ( Server has 24 logical cores, right click -> task manager )

All queries who cost is > 20, each operator might use upto 4 cores --- Is there a possibility it will use less than or more than 4?


What happens when cost is less than 20, queries might use all 24 cores? I couldn't find a article which talks about the impact when cost is less than set CTP value.

Appreciate if someone can clarify and point me to a good article. Thanks


If Query Cost > CTP, Then SQL Server may use up to the # of logical processors specified in MAXDOP for the parallel query.
# can be <= MAXDOP. Not More (>Wink.

If Query Cost < CTP, Query is not Parallelized. It will run in a serial fashion.

--
SQLBuddy
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
sqlbuddy123 (2/28/2014)
curious_sqldba (2/28/2014)

I have read so many articles, may be i am just missing the point. My understanding is you set your CTP and MAXDOP, if the cost of a given query is greater than set CTP then SQL Server MIGHT use upto number of processors specified in MAXDOP.

MAXDOP = 4 , CTP = 20 ( Server has 24 logical cores, right click -> task manager )

All queries who cost is > 20, each operator might use upto 4 cores --- Is there a possibility it will use less than or more than 4?


What happens when cost is less than 20, queries might use all 24 cores? I couldn't find a article which talks about the impact when cost is less than set CTP value.

Appreciate if someone can clarify and point me to a good article. Thanks


If Query Cost > CTP, Then SQL Server may use up to the # of logical processors specified in MAXDOP for the parallel query.
# can be <= MAXDOP. Not More (>Wink.

If Query Cost < CTP, Query is not Parallelized. It will run in a serial fashion.

--
SQLBuddy




"If Query Cost < CTP, Query is not Parallelized. It will run in a serial fashion."

Do you mean it will use ONLY 1 processor or will use < 4 ?
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
Yes ..

--
SQLBuddy
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
sqlbuddy123 (2/28/2014)
Yes ..

--
SQLBuddy


If query cost is less than CTP it will always use only 1 processor?
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
More Appropriate answer would be, If Query Cost < CTP , SQL Server won't convert the query into Parallel Query.

--
SQLBuddy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search