Solved

SQL Max degree of parallelisme

Posted on 2015-01-05
13
188 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 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 125 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 250 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 69

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 69

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 250 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

735 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