|
|
Posted by paul on 12/15/06 10:53
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]
|