Max Degree of Parallelism - clearification

I am somewhat confused on how this works. I have searched MS and many other
forums with no answer. BOL states "Use the max degree of parallelism option
to limit the number of processors (a maximum of 32) to use in parallel plan
execution.". So when this is set to 2 and the physical server has 4 cpu's
will MS SQL only use 2 processors? Or does this limit the number of threads
on each processor to 2 and still use all 4 CPUs? If it only uses 2, how
does it know which 2 to use?
JJaski wrote:
> I am somewhat confused on how this works. I have searched MS and many other
> forums with no answer. BOL states "Use the max degree of parallelism option
> to limit the number of processors (a maximum of 32) to use in parallel plan
> execution.". So when this is set to 2 and the physical server has 4 cpu's
> will MS SQL only use 2 processors? Or does this limit the number of threads
> on each processor to 2 and still use all 4 CPUs? If it only uses 2, how
> does it know which 2 to use?
> J
>
Setting it to 2 will restrict any given query to using 2 processors
simultaneously. SQL itself will still use all of the processors, but
one query alone will use a maximum of 2 at one time.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy you stated, "SQL itself will still use all of the processors, but one
query alone will use a maximum of 2 at one time." When the query runs I
understand it's limited to only utilizing 2 CPUs simultaneously, how do
threads play a role in this? Is it one thread per CPU? Or can multiple
threads run on a CPU?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45782C05.8090107@.realsqlguy.com...
> Jaski wrote:
>> I am somewhat confused on how this works. I have searched MS and many
>> other forums with no answer. BOL states "Use the max degree of
>> parallelism option to limit the number of processors (a maximum of 32) to
>> use in parallel plan execution.". So when this is set to 2 and the
>> physical server has 4 cpu's will MS SQL only use 2 processors? Or does
>> this limit the number of threads on each processor to 2 and still use all
>> 4 CPUs? If it only uses 2, how does it know which 2 to use?
>> J
> Setting it to 2 will restrict any given query to using 2 processors
> simultaneously. SQL itself will still use all of the processors, but one
> query alone will use a maximum of 2 at one time.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||On Thu, 07 Dec 2006 20:25:18 GMT, "Jaski" <Jaski@.nospam.com> wrote:
>Tracy you stated, "SQL itself will still use all of the processors, but one
>query alone will use a maximum of 2 at one time." When the query runs I
>understand it's limited to only utilizing 2 CPUs simultaneously, how do
>threads play a role in this? Is it one thread per CPU? Or can multiple
>threads run on a CPU?
Multiple threads can run on a CPU, that's for sure.
It is very confusing, but I think the MAXDOP SQL parameter may control
that, but not the number of physical processors. Or, not.
J.|||A query executing in parallel will not use several threads on the same processor. That would
decrease performance. SQL Server has one scheduler per processor (core, or logical if HT), and it
will make sure that the query will use threads over different schedulers (i.e., processors).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:0r6in2liu4hi8cragc4dcrde4qmjvmi3nb@.4ax.com...
> On Thu, 07 Dec 2006 20:25:18 GMT, "Jaski" <Jaski@.nospam.com> wrote:
>>Tracy you stated, "SQL itself will still use all of the processors, but one
>>query alone will use a maximum of 2 at one time." When the query runs I
>>understand it's limited to only utilizing 2 CPUs simultaneously, how do
>>threads play a role in this? Is it one thread per CPU? Or can multiple
>>threads run on a CPU?
> Multiple threads can run on a CPU, that's for sure.
> It is very confusing, but I think the MAXDOP SQL parameter may control
> that, but not the number of physical processors. Or, not.
> J.
>|||On Fri, 8 Dec 2006 09:34:35 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>A query executing in parallel will not use several threads on the same processor. That would
>decrease performance. SQL Server has one scheduler per processor (core, or logical if HT), and it
>will make sure that the query will use threads over different schedulers (i.e., processors).
I was commonly seeing seven and more SPIDs on a two-processor,
single-core, non-hyper'd system, SQL2K Enterprise.
So, SPID is not quite the same as thread, is it?
I was asking about this back in April and May:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/d961ed7576f6a6f9/8336914737a3e20a?lnk=st&q=group%3Amicrosoft.public.sqlserver.*+author%3Ajxstern&rnum=16&hl=en#8336914737a3e20a
J.|||Hmm, I was pretty certain that SQL Server would not use several threads per SPID and processor
(i.e., one spid would get maximum one thread per processor(=scheduler)). Did you see more rows then
the number of processors (from sp_who or similar) with the same spid number? If so, then you can
ignore my prior post (as I apparently then have misunderstood things).
> So, SPID is not quite the same as thread, is it?
No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whenever a SPID need to do
something, it uses one or more threads from the thread pool. I might be slightly off here with the
details, so you might want to check out Slava Oks' blog for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:5h0jn2lcvlj6i2f3r64vivuf4vlvl1qg4j@.4ax.com...
> On Fri, 8 Dec 2006 09:34:35 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>A query executing in parallel will not use several threads on the same processor. That would
>>decrease performance. SQL Server has one scheduler per processor (core, or logical if HT), and it
>>will make sure that the query will use threads over different schedulers (i.e., processors).
> I was commonly seeing seven and more SPIDs on a two-processor,
> single-core, non-hyper'd system, SQL2K Enterprise.
> So, SPID is not quite the same as thread, is it?
> I was asking about this back in April and May:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/d961ed7576f6a6f9/8336914737a3e20a?lnk=st&q=group%3Amicrosoft.public.sqlserver.*+author%3Ajxstern&rnum=16&hl=en#8336914737a3e20a
>
> J.
>|||On Fri, 8 Dec 2006 21:20:36 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Hmm, I was pretty certain that SQL Server would not use several threads per SPID and processor
>(i.e., one spid would get maximum one thread per processor(=scheduler)). Did you see more rows then
>the number of processors (from sp_who or similar) with the same spid number?
Very definitely.
Certainly surprised me!!
>If so, then you can
>ignore my prior post (as I apparently then have misunderstood things).
OK.
>> So, SPID is not quite the same as thread, is it?
>No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whenever a SPID need to do
>something, it uses one or more threads from the thread pool. I might be slightly off here with the
>details, so you might want to check out Slava Oks' blog for details.
http://blogs.msdn.com/slavao/
?
Looks good, thanks.
J.|||> http://blogs.msdn.com/slavao/
> ?
Yes that was the one I was thinking of. I browsed the articles but didn't find anything that by the
article name seemed to fit this topic.
Another possible source would be Ken Henderson's "The Guru's Guide to SQL Server Architecture and
Internals" book (which is a 2000 book). I'm on the road this week, so I can't say for sure, though.
But a large portion of the book is the OS primitives (mem, sch etc) and how SQL Server uses them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:fgbkn2t2puedgb6en0a4or8fuqi1hojs53@.4ax.com...
> On Fri, 8 Dec 2006 21:20:36 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Hmm, I was pretty certain that SQL Server would not use several threads per SPID and processor
>>(i.e., one spid would get maximum one thread per processor(=scheduler)). Did you see more rows
>>then
>>the number of processors (from sp_who or similar) with the same spid number?
> Very definitely.
> Certainly surprised me!!
>>If so, then you can
>>ignore my prior post (as I apparently then have misunderstood things).
> OK.
>> So, SPID is not quite the same as thread, is it?
>>No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whenever a SPID need to do
>>something, it uses one or more threads from the thread pool. I might be slightly off here with the
>>details, so you might want to check out Slava Oks' blog for details.
> http://blogs.msdn.com/slavao/
> ?
> Looks good, thanks.
> J.
>