max degree of parallelism Option

Dear all,
What is the ideal value for "max degree of parallelism" if we have very long
running queries which eat up lots of resources in peak hours.
We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
DBA suggests it to set it to 1.
Need your expert opinion.
KayThe only way to see what is best is to test each of them. It depends a lot
on what you are doing, schemas etc. It could be any of them.
--
Andrew J. Kelly SQL MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OPcIup92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very
> long running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but
> new DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
>|||On Fri, 28 Oct 2005 21:12:47 +0500, Kay wrote:
> Dear all,
> What is the ideal value for "max degree of parallelism" if we have very long
> running queries which eat up lots of resources in peak hours.
> We have 4 processors Dell machine. Our last DBA recommended it to 4 but new
> DBA suggests it to set it to 1.
> Need your expert opinion.
> Kay
Hello,
Just to add a few things.
If you have hyperthreading procs, MS recommends to set the max degree of p.
to the number of physical procs. Ref here :
http://support.microsoft.com/default.aspx/kb/322385
Look also at the evolution over time of CXPACKET wait types ( DBCC SQLPERF
(waitstats) ), indicating if parallelized queries wait for their threads to
finish.
Look also, on the CPUs, the percentage of Kernel Time. That could give you
a hint if the parallelism is detrimental. E.g. if decreasing your maxdop
show a drop of the kernel time, that could be a good thing.
But as it was said, a good answer is, try not to have very long running
queries at peak hours...