Why does my query use MAXDOP = 0?

Question

Why does my query use MAXDOP = 0?

Viewing 3 posts - 16 through 17 (of 17 total)

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

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

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 SSC Guru Points: 186127 More actions · Answer 2

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

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

curious_sqldba (3/11/2014)

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