MAXDOP - What happens when CTP is < ?

Question

MAXDOP - What happens when CTP is < ?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
February 28, 2014 at 10:28 am

#396350

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

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 (>).

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

--

SQLBuddy

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

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 (>).
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 One Orange Chip Points: 28294 More actions · Answer 3

SQLBuddy123

One Orange Chip

Points: 28294

February 28, 2014 at 1:21 pm

#1693252

Yes ..

--

SQLBuddy

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

sqlbuddy123 (2/28/2014)
Yes ..
--
SQLBuddy

If query cost is less than CTP it will always use only 1 processor?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

More Appropriate answer would be, If Query Cost < CTP , SQL Server won't convert the query into Parallel Query.

--

SQLBuddy