Why does my query use MAXDOP = 0?

Question

Why does my query use MAXDOP = 0?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
March 7, 2014 at 2:49 pm

#290738

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.

Viewing 15 posts - 1 through 15 (of 17 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 (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

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

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.

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

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.

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

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

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

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

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

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

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

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

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.

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

sqlbuddy123 (3/11/2014)
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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

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

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