master sysmessages: parallelism

Showing posts with label parallelism. Show all posts

Wednesday, March 28, 2012

max degree parallelism

hi,
i've set 'max degree of parallelism' to 1 because some sql request hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

Thanks

Paulpaul (nomail@.nomail.com) writes:

Quote:

Originally Posted by

i've set 'max degree of parallelism' to 1 because some sql request hanged.
Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

There are only places you can control the degrees of parallelism: at
server level and at query level.

If you have a problem with parallelism going awry with a certain query,
the best is to add OPTION (MAXDOP 1) to that query. If you set "max
degree of parallelism" on server level 1, for any query where you want
parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
other number to get a certain number of CPUs.

The most common reason to change the configuration parameter to reflect
the number of physical CPU cores when you have hyper-threaded CPU. That
is, if you have two dual-core CPUs that are hyperthread, set the
parameter to 4.

If you have plenty of CPUs, you may still want to reduce the number, so
that a single query cannot monopolize the CPU power.

Setting the parameter to 1 because of a single query is not a good idea.
Better is to fix the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks for your reply
i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of several
queries) runned with ~220 threads
i don't know why the optimiser choosed this but the fact is the server was
unstable ("and many error like Process ID 85:80 owns resources that are
blocking processes on Scheduler 4.")
the batch takes 3h to complete with max degree = 0 and 3mn with max degree =
1
So i've set the max degree = 1 bacause i don't know if others requets (like
dynamic) can also generate ~200 threads

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989A725ABC0D7Yazorman@.127.0.0.1...

Quote:

Originally Posted by

paul (nomail@.nomail.com) writes:

Quote:

Originally Posted by

i've set 'max degree of parallelism' to 1 because some sql request

hanged.

Quote:

Originally Posted by

Quote:

Originally Posted by

Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

>
There are only places you can control the degrees of parallelism: at
server level and at query level.
>
If you have a problem with parallelism going awry with a certain query,
the best is to add OPTION (MAXDOP 1) to that query. If you set "max
degree of parallelism" on server level 1, for any query where you want
parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
other number to get a certain number of CPUs.
>
The most common reason to change the configuration parameter to reflect
the number of physical CPU cores when you have hyper-threaded CPU. That
is, if you have two dual-core CPUs that are hyperthread, set the
parameter to 4.
>
If you have plenty of CPUs, you may still want to reduce the number, so
that a single query cannot monopolize the CPU power.
>
Setting the parameter to 1 because of a single query is not a good idea.
Better is to fix the query.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||paul (nomail@.nomail.com) writes:

Quote:

Originally Posted by

i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of
several queries) runned with ~220 threads i don't know why the optimiser
choosed this but the fact is the server was unstable ("and many error
like Process ID 85:80 owns resources that are blocking processes on
Scheduler 4.") the batch takes 3h to complete with max degree = 0 and
3mn with max degree = 1 So i've set the max degree = 1 bacause i don't
know if others requets (like dynamic) can also generate ~200 threads

Certainly it would be a good idea to identify the queries in the batch
that gets a wild parallel plan, and OPTION (MAXDOP 1). At least if you
want to be able to use parallelism elsewhere.

As for why the optimizer generates a parallel plan that takes 3h to complete
I don't know, but I've certainly see it happen more than once.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"paul" <nomail@.nomail.comwrote in message
news:elturg$6s3$1@.s1.news.oleane.net...

Quote:

Originally Posted by

thanks for your reply
i've 4 hyperthread cpu
i've effectively set the degree to 1 because one batch (consist of several
queries) runned with ~220 threads
i don't know why the optimiser choosed this but the fact is the server was
unstable ("and many error like Process ID 85:80 owns resources that are
blocking processes on Scheduler 4.")
the batch takes 3h to complete with max degree = 0 and 3mn with max degree
=
1
So i've set the max degree = 1 bacause i don't know if others requets
(like
dynamic) can also generate ~200 threads
>
>
>
>
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989A725ABC0D7Yazorman@.127.0.0.1...

Quote:

Originally Posted by

>paul (nomail@.nomail.com) writes:

Quote:

Originally Posted by

i've set 'max degree of parallelism' to 1 because some sql request

hanged.

Quote:

Originally Posted by

Quote:

Originally Posted by

Now when i connect, how can i set the parallelism to 4 for a session.
Is there a command like this :
'alter session set max degree of parallelism 4' ?

>>
>There are only places you can control the degrees of parallelism: at
>server level and at query level.
>>
>If you have a problem with parallelism going awry with a certain query,
>the best is to add OPTION (MAXDOP 1) to that query. If you set "max
>degree of parallelism" on server level 1, for any query where you want
>parallelism, you need to use OPTION (MAXDOP 0) to get all CPUs, or any
>other number to get a certain number of CPUs.
>>
>The most common reason to change the configuration parameter to reflect
>the number of physical CPU cores when you have hyper-threaded CPU. That
>is, if you have two dual-core CPUs that are hyperthread, set the
>parameter to 4.
>>
>If you have plenty of CPUs, you may still want to reduce the number, so
>that a single query cannot monopolize the CPU power.
>>
>Setting the parameter to 1 because of a single query is not a good idea.
>Better is to fix the query.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

To add on to Erland's response, excessive parallelism can be a symptom
that query and index tuning is needed. The optimizer will sometimes
throw processors at the problem to compensate.

Yeah, but in those cases the query usually don't complete in three seconds
if constraint to one CPU!

Quote:

Originally Posted by

if constraint to one CPU!

True, but I've seen overly aggressive parallel plans that completed
significantly faster without parallelism. In many of those cases, tuning
eliminated parallelism entirely and the single-threaded query ran much
faster too. IMHO, tuning is a often better than fiddling with MAXDOP.

However, in Paul's case, the difference is much more pronounced (3 minutes
vs. 3 hours) and apparently introduced instability. I'm not sure what
service pack he's running but there are optimizer fixes/enhancements in all
the SQL 2000 SPs.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns989AB0A9CE989Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:

Quote:

Originally Posted by

>To add on to Erland's response, excessive parallelism can be a symptom
>that query and index tuning is needed. The optimizer will sometimes
>throw processors at the problem to compensate.

>
Yeah, but in those cases the query usually don't complete in three seconds
if constraint to one CPU!
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
Kay
The only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>
|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:

> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very long
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...
sql

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayThe only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
--
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very long
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...

max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayThe only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:

> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very lo
ng
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but ne
w
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...

max degree of parallelism Option

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.

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

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

Wednesday, March 28, 2012

max degree parallelism

max degree of parallelism Option

max degree of parallelism Option

max degree of parallelism Option

max degree of parallelism Option

max degree of parallelism Option

max degree of parallelism Option

Max Degree of Parallelism - clearification

Max Degree of Parallelism - clearification

Max Degree of Parallelism - clearification

master sysmessages

Blog Archive

About Me