Max Degree of Parallelism - clearification

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.
|||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:
[url]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#833691 4737a3e20a[/url]
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.

>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.