Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MAXDOP - What happens when CTP is < ? Expand / Collapse
Author
Message
Posted Friday, February 28, 2014 10:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959

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
Post #1546475
Posted Friday, February 28, 2014 12:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,194, Visits: 2,216
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


Post #1546526
Posted Friday, February 28, 2014 1:18 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959
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 ?
Post #1546553
Posted Friday, February 28, 2014 1:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,194, Visits: 2,216
Yes ..

--
SQLBuddy
Post #1546554
Posted Friday, February 28, 2014 1:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959
sqlbuddy123 (2/28/2014)
Yes ..

--
SQLBuddy


If query cost is less than CTP it will always use only 1 processor?
Post #1546555
Posted Friday, February 28, 2014 1:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 1,194, Visits: 2,216
More Appropriate answer would be, If Query Cost < CTP , SQL Server won't convert the query into Parallel Query.

--
SQLBuddy
Post #1546558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse