Posted by Erland Sommarskog on 12/15/06 12:38

paul (nomail@nomail.com) writes:
> 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/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]