Posted by Erland Sommarskog on 12/15/06 10:14

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]