Posted by Dan Guzman on 12/16/06 14:47

> Yeah, but in those cases the query usually don't complete in three seconds
> 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.se> wrote in message
news:Xns989AB0A9CE989Yazorman@127.0.0.1...
> Dan Guzman (guzmanda@nospam-online.sbcglobal.net) writes:
>> 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/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]