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 oth
er
> forums with no answer. BOL states "Use the max degree of parallelism opti
on
> to limit the number of processors (a maximum of 32) to use in parallel pla
n
> 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 threa
ds
> 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:
> 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 proce
ssor. That would
decrease performance. SQL Server has one scheduler per processor (core, or l
ogical 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:
>
> 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 proc
essor. 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., pro
cessors).
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/micr...336914737a3e20a
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. When
ever a SPID need to do
something, it uses one or more threads from the thread pool. I might be slig
htly 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:
>
> 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/micr...336914737a3e20a
>
> 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)). Di
d you see more rows then
>the number of processors (from sp_who or similar) with the same spid number?[/vbcol
]
Very definitely.
Certainly surprised me!!
[vbcol=seagreen]
>If so, then you can
>ignore my prior post (as I apparently then have misunderstood things).
OK.

>No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whe
never a SPID need to do
>something, it uses one or more threads from the thread pool. I might be sli
ghtly 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 fi
nd 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 Se
rver 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:
>
> Very definitely.
> Certainly surprised me!!
>
> OK.
>
> http://blogs.msdn.com/slavao/
> ?
> Looks good, thanks.
> J.
>