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 Tuesday, March 11, 2014 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Post #1549782
Posted Tuesday, March 11, 2014 8:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 1,299, Visits: 3,003
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?
Post #1549785
Posted Tuesday, March 11, 2014 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Post #1549790
Posted Tuesday, March 11, 2014 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Post #1549800
Posted Tuesday, March 11, 2014 9:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1549802
Posted Tuesday, March 11, 2014 9:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:01 AM
Points: 1,299, Visits: 3,003
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 .
Post #1549808
Posted Tuesday, March 11, 2014 9:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
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
Post #1549813
Posted Tuesday, March 11, 2014 9:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1549816
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse