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.
--
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|||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.
--
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!
--
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|||Yeah, but in those cases the query usually don't complete in three seconds
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
No comments:
Post a Comment