|
Home > Archive > MS SQL Server > October 2005 > max degree of parallelism Option
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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.
Kay
| |
| Andrew J. Kelly 2005-10-28, 1:23 pm |
| The 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
>
>
| |
| Rudi Bruchez 2005-10-31, 3:23 am |
| 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...
|
|
|
|
|