Posted by Dan Guzman on 12/15/06 13:12

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.com> wrote in message
news:elturg$6s3$1@s1.news.oleane.net...
> 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.se> wrote in message
> news:Xns989A725ABC0D7Yazorman@127.0.0.1...
>> paul (nomail@nomail.com) writes:
>> > 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/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

[Back to original message]