[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Max degree of parallelisme

Posted on 2015-01-05
13
Medium Priority
?
226 Views
Last Modified: 2015-01-18
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
0
Comment
Question by:jskfan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40532508
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
 
LVL 29

Assisted Solution

by:QPR
QPR earned 500 total points
ID: 40532510
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
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 1000 total points
ID: 40532512
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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 29

Expert Comment

by:QPR
ID: 40532519
Apologies I got my 1 and my 0 the wrong way around in my sharepoint scenario
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40532532
>> 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
 

Author Comment

by:jskfan
ID: 40532687
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40532887
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40533045
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
 

Author Comment

by:jskfan
ID: 40533555
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 40535018
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
 

Author Comment

by:jskfan
ID: 40550864
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
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 1000 total points
ID: 40551100
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
 

Author Closing Comment

by:jskfan
ID: 40556879
Thank you Guys!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question