Solved

SQL Max degree of parallelisme

Posted on 2015-01-05
13
182 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

856 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