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 12»»

Why does my query use MAXDOP = 0? Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 2:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936

I don't have any query hints being used, at server level i have MAXDOP = 5 and CTP = 1, in the exec plan i see that query is using MAXDOP = 0, btw the cost of query is 1440 . How is that possible? Shouldn't it be using MAXDOP =1 since cost is more than 5????

For the same query when i set MAXDOP =4 and CTP 20 , i see that query is using MAXDOP =4 and query cost is 1300. This makes sense.
Post #1548910
Posted Friday, March 7, 2014 3:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:23 PM
Points: 1,194, Visits: 2,209
curious_sqldba (3/7/2014)

I don't have any query hints being used, at server level i have MAXDOP = 5 and CTP = 1, in the exec plan i see that query is using MAXDOP = 0, btw the cost of query is 1440 . How is that possible? Shouldn't it be using MAXDOP =1 since cost is more than 5????

For the same query when i set MAXDOP =4 and CTP 20 , i see that query is using MAXDOP =4 and query cost is 1300. This makes sense.


When you say cost of the query, what cost are you referring to ? Could you post the execution plan.

Actually query cost has to be compared with CTP, not with the parallelism value.

--
SQLBuddy
Post #1548916
Posted Friday, March 7, 2014 3:34 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
sqlbuddy123 (3/7/2014)
curious_sqldba (3/7/2014)

I don't have any query hints being used, at server level i have MAXDOP = 5 and CTP = 1, in the exec plan i see that query is using MAXDOP = 0, btw the cost of query is 1440 . How is that possible? Shouldn't it be using MAXDOP =1 since cost is more than 5????

For the same query when i set MAXDOP =4 and CTP 20 , i see that query is using MAXDOP =4 and query cost is 1300. This makes sense.


When you say cost of the query, what cost are you referring to ? Could you post the execution plan.

Actually query cost has to be compared with CTP, not with the parallelism value.

--
SQLBuddy


Attached is actual exec plan.


  Post Attachments 
Temp.sqlplan (14 views, 234.98 KB)
Post #1548917
Posted Monday, March 10, 2014 2:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
curious_sqldba (3/7/2014)
sqlbuddy123 (3/7/2014)
curious_sqldba (3/7/2014)

I don't have any query hints being used, at server level i have MAXDOP = 5 and CTP = 1, in the exec plan i see that query is using MAXDOP = 0, btw the cost of query is 1440 . How is that possible? Shouldn't it be using MAXDOP =1 since cost is more than 5????

For the same query when i set MAXDOP =4 and CTP 20 , i see that query is using MAXDOP =4 and query cost is 1300. This makes sense.


When you say cost of the query, what cost are you referring to ? Could you post the execution plan.

Actually query cost has to be compared with CTP, not with the parallelism value.

--
SQLBuddy


Attached is actual exec plan.


Update:

I meant MAXDOP =1 and CTP = 5 in my original post.

Post #1549502
Posted Monday, March 10, 2014 2:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:23 PM
Points: 1,194, Visits: 2,209
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.

That's why I couldn't see that in the execution plan you posted.

--
SQLBuddy
Post #1549506
Posted Tuesday, March 11, 2014 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.

That's why I couldn't see that in the execution plan you posted.

--
SQLBuddy


Exactly, but my plan is using MAXDOP =0 ( 24 cores) in my case. Did you notice this?
Post #1549749
Posted Tuesday, March 11, 2014 8:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.

That's why I couldn't see that in the execution plan you posted.

--
SQLBuddy


Exactly, but my plan is using MAXDOP =0 ( 24 cores) in my case. Did you notice this?


Yes. Does the query have any function calls?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1549769
Posted Tuesday, March 11, 2014 8:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:23 PM
Points: 1,194, Visits: 2,209
[b]curious_sqldba (3/11/2014)

Exactly, but my plan is using MAXDOP =0 ( 24 cores) in my case. Did you notice this?


Where did you find that ? I don't see any Parallelism operator in the plan.

--
SQLBuddy
Post #1549773
Posted Tuesday, March 11, 2014 8:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
sqlbuddy123 (3/10/2014)
MAXDOP =1 means disabling the parallelism. That's why SQL Server doesn't consider parallelizing the query.

That's why I couldn't see that in the execution plan you posted.

--
SQLBuddy


Exactly, but my plan is using MAXDOP =0 ( 24 cores) in my case. Did you notice this?


Yes. Does the query have any function calls?


No function calls.
Post #1549779
Posted Tuesday, March 11, 2014 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 1,274, Visits: 2,936
sqlbuddy123 (3/11/2014)
[b]curious_sqldba (3/11/2014)

Exactly, but my plan is using MAXDOP =0 ( 24 cores) in my case. Did you notice this?


Where did you find that ? I don't see any Parallelism operator in the plan.

--
SQLBuddy


If you hover your mouse to the insert statement you will see it is using DOP =0.
Post #1549780
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse