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 ?
Any help will be very much appreciated.
Thanks
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
QPR
Apologies I got my 1 and my 0 the wrong way around in my sharepoint scenario
>> 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.
Oh no, never set MAXDOP higher than the physical number of CPUs. That could cause severe bottlenecks.
ASKER
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 ?
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 ?
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.
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.
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
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
ASKER
Rainer Jeschor
Parallele is when you enter 1 or when you enter 0 ?
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 ?
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
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
ASKER
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 ?
if 0 is the fastest then why it is not by default ?
if 1 is the fastest then why it is not by default ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Guys!