SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why does my query use MAXDOP = 0?


Why does my query use MAXDOP = 0?

Author
Message
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43253 Visits: 20015
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".

“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
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 3696
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".


Right, MAXDOP means the maximum it would go to based on Cost, in my case it should't go pass DOP =1 when cost is greater than 5, so cost is greater than 5 but it is using DOP = 0?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43253 Visits: 20015
curious_sqldba (3/11/2014)
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".


Right, MAXDOP means the maximum it would go to based on Cost, in my case it should't go pass DOP =1 when cost is greater than 5, so cost is greater than 5 but it is using DOP = 0?


It's a serial plan, yes. It's an expensive plan so you would expect it to be a parallel plan. I'd guess you've used MAXDOP = 1 as a query hint.

“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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43253 Visits: 20015
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.


Have you checked the plan before and after obfuscation?

“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
sqlbuddy123
sqlbuddy123
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6530 Visits: 2243
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".


True. Degree of parallelism (Server Level ) and MAXDOP query hint are different than this Degree of Parallelism (Query Plan).

Also MAXDOP = 0 (Server Level or Query Hint) doesn't necessarily mean all Cores. It means that we are leaving it to SQL Server to decide the number of cores to be used for Parallel plans.

In Query Plan it means serial plan.

--
SQLBuddy
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 3696
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".


Right, MAXDOP means the maximum it would go to based on Cost, in my case it should't go pass DOP =1 when cost is greater than 5, so cost is greater than 5 but it is using DOP = 0?


It's a serial plan, yes. It's an expensive plan so you would expect it to be a parallel plan. I'd guess you've used MAXDOP = 1 as a query hint.


No hints used .
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43253 Visits: 20015
curious_sqldba (3/11/2014)
ChrisM@Work (3/11/2014)
curious_sqldba (3/11/2014)
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?


MAXDOP is not the same as "Degree of Parallelism" on the properties sheet of the INSERT operator. If you're still unsure, inspect the properties sheet of any of the index/table scans/seeks, which show "Parallel = False".


Right, MAXDOP means the maximum it would go to based on Cost, in my case it should't go pass DOP =1 when cost is greater than 5, so cost is greater than 5 but it is using DOP = 0?


It's a serial plan, yes. It's an expensive plan so you would expect it to be a parallel plan. I'd guess you've used MAXDOP = 1 as a query hint.


No hints used .


"Degree of Parallelism" on a plan operator typically shows 1 for a serial plan, hence asking if you checked the plan before obfuscation.
Not having the un-obfuscated plan or even the query is unhelpful. I'm bored with guessing now.

“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
sqlbuddy123
sqlbuddy123
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6530 Visits: 2243
curious_sqldba (3/11/2014)
[quote]

No hints used .


Update:

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


This means Parallelism has been disabled at server level and SQL Server generates serial plans only.

For serial (non -parallel) plans, it shows as 0 in the query plan meaning no=parallelism. For a parallel plan you should see parallel operator in the query plan and should be able to see no of threads in it's properties.


Check this http://thesmilingdba.blogspot.com/2012/06/maxdop-and-cost-threshold-for.html
--
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