Link to home
Create AccountLog in
Avatar of publicvoid
publicvoidFlag for United States of America

asked on

sql server 2000 max degree of parallelism

When we run the execution plan for a 7 table join, it states that parallelism is taking 70% of the time. The query takes 0.3 sec. Rebuilding Indexes, updating stats does not help. max degree of parallelism is set to 0, which is meant to use all processors right?

The server has 2 quad-core processors.

If i set max degree of parallelism to 1, then the query takes 0.001 secs.

If I set max degree to anything other than 1, the query takes 0.3 secs again.

Can someone explain why this is?


Avatar of arnold
Flag of United States of America image
The plan in this case might be not as efficient, but under other circumstance other plans fair better.

Use sql profiler to see whether an additional index here or there may help.
Avatar of GSGDBA

Can you please provide the execution plan.
We've run into just a few of these where one might conclude the query optimizer isn't perfect.  It is underestimating the overhead relative to the benefit of parallelizing the query.  Adding an index might give it the info it needs to make the right decision, but just leaving max_dop = 1 on that query isn't a terrible solution.
TempDB bottle neck maybe?  I beleive the MS recommendation is 1 TempDB file per core.
Avatar of b_levitt

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account