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 ?

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.

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?

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: 2015-01-06

Rainer Jeschor

when you run two queries in parallel they might run on different threads / CPUs.

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: 2015-01-15

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 ?

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