SQL Max degree of parallelisme

Can someone explain,  in plain English, what is the Max Degree of Parallelisme?
Let 's say I have 2 CPUs, if I set the value MDOP to 0 then each query will use both CPUs, if I set it to 1 then each query will use 1 CPU at a time.  I hope I am correct.
So where the advantage/disadvantage of using 0 or 1 ?

Any help will be very much appreciated.

Thanks
jskfanAsked:
Who is Participating?
 
Rainer JeschorConnect With a Mentor Commented:
Hi,
this depends a lot on the executed query. Parallelism has surely an overhead because it has to sync the threads/processes - and this can take more time than executing a query in a single thread.
So depending on your application / SQL Server usage the optimum can be 0 or 1 or 2 or 4 ...
Afaik there is no general rule of thumb.
HTH
Rainer
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
If you have only 2, it's easy, set MAXDOP to 1 :-).  You can't afford to have all your CPUs used by every query that has a parallel component.

It's the number of CPUs to use, by default, if SQL decides to use parallel execution on the query plan.  In that sense, "max" is a bit of a misnomer -- if SQL decides to parallel, it will use that number of CPUs ... unless you've explicitly specified "MAXDOP = <some_other_number>" on the query/command itself, in which case that overrides the default.

If you use more than 1 for MAXDOP, you should also review the "cost threshold for parallelism", as by default it's usually way too low for best overall throughput on the system.
0
 
QPRConnect With a Mentor Commented:
It is to do with threading so will also work on a single cpu. A query can be divided by SQL server across more than 1 thread in order to "share" the workload. This is advantageous in certain queries/applications. It can lead to SQL "waits" (which might be fine) while thread a waits for thread b to finish. In certain cases e.g SharePoint a maxdop of 0 is recommended. 1 means users many as you like SQL
Does that help?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
Rainer JeschorConnect With a Mentor Commented:
Hi,
sorry, but afaik you have no choice when it comes to SharePoint. SharePoint requires SQL Server Max degree of parallelism to be set to 1 and even sets it in the background in some situations
http://www.pythian.com/blog/thou-shalt-configure-maxdop-for-sharepoint-2013-is-no-longer-needed/

Some explanations:
http://www.brentozar.com/archive/2013/12/q-can-high-maxdop-make-a-query-slower/
http://blogs.technet.com/b/lukeb/archive/2011/09/08/sharepoint-maxdop-1.aspx


HTH
Rainer
0
 
QPRCommented:
Apologies I got my 1 and my 0 the wrong way around in my sharepoint scenario
0
 
Scott PletcherSenior DBACommented:
>> It is to do with threading so will also work on a single cpu. <<

Oh no, never set MAXDOP higher than the physical number of CPUs.  That could cause severe bottlenecks.
0
 
jskfanAuthor Commented:
OK if you setting it to 0,
or you telling the system to use both CPUs to share the query load ?

if you set it to 1,
you are telling the system use one CPU to process the query load and leave the other CPU alone ?
0
 
Scott PletcherSenior DBACommented:
Yes and yes:

if you set it to 0, the computer determines the max degree of parallelism available;

if you set it to 1, you are turning off parallelism, and no plans generated will contain parallel tasks.
0
 
Rainer JeschorCommented:
Hi,

afaik no, it will use both CPUs as far as you have the SQL Server configured to use both CPUs. But one query will only run with one thread hence when you run two queries in parallel they might run on different threads / CPUs.

HTH
Rainer
0
 
jskfanAuthor Commented:
Rainer Jeschor
when you run two queries in parallel they might run on different threads / CPUs.

Parallele is when you enter 1 or when you enter 0 ?
0
 
Rainer JeschorCommented:
Hi,
this is independent - I meant having two users executing queries (e.g. two different user browse your SharePoint site).
So maxdop has just effects how one query internally gets parallelized - not how parallel / multi-threaded the server itself runs.

HTH
Rainer
0
 
jskfanAuthor Commented:
Which one Is the fastest ? 0 or 1?

if 0 is the fastest then why it is not by default ?
if 1 is the fastest then why it is not by default ?
0
 
jskfanAuthor Commented:
Thank you Guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.