Solved

SQL Max degree of parallelisme

Posted on 2015-01-05
13
173 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:ScottPletcher
ScottPletcher 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
 
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:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Expert Comment

by:ScottPletcher
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now