Andrew J. Kelly
3/25/2007 5:02:00 PM
Josh,
The number of SPIDs you see indicate how many threads SQL Server spawned and
have no direct correlation to MAXDOP. MAXDOP specifies how many processors
in total any one piece of a plan can use at one time. The number of threads
may and often is different than the number of procs used. For instance you
may see ona 4 way box 13 threads which may break down to 3 per processor
with 1 as sort of a manager thread. They obviously can't all do work at
exactly the same time since there is only 4 procs. But that is how threads
work in general with most OS's. Each thread gets assigned a unit of work to
do and they will be swapped on and off the proc as appropriate. Changing the
MAXDOP may have an effect on how many are generated due to the availability
of the procs but that does not govern the thread count directly. Setting the
MAXDOP to 1 on a 4 proc box does not preclude SQL Server from using all 4
procs. It simply can't use them all at the same time for any one task. You
can still have 4 independent threads from 4 separate tasks all working at
the same time on all 4 procs. This allows for more concurrency. The correct
setting is what works best for your situation. What works for you may not
work for the server next to it as there are a lot of things that play a part
in how it is all used. Also keep in mind that SQL Server monitors the env at
the time each plan is run to see if it should even think about using more
than 1 proc or not. There are about 7 different factors that it takes into
consideration in deciding how many procs to use fora single task. One of
them is how busy the system is. So if you have a busy system to begin with
it may rarely use more than 1 proc regardless of the MAXDOP setting. YMMV
and the bottom line is you need to test it under real life conditions to
determine which is the best setting for your server and app.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:oa1b03pbs3bfe3ppqtv294dthdbeih4mgk@4ax.com...
> On Fri, 23 Mar 2007 19:47:53 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@shadhawk.com> wrote:
>
>>While I will agree that using parallel plans in a heavy OLTP env can have
>>relatively little benefit for queries and such you have to be careful
>>about
>>simply setting MAXDOP to 1 at the server level, especially on Enterprise
>>Edition. That also restricts things such as Index rebuilds & creation,
>>DBCC's, Backup & Restores etc. And I can assure you that parallelism for
>>these operations can go a long way. I see you briefly hinted at something
>>like this but I see too many people set it to 1 and not realize all the
>>potential affects.
>
> Andrew,
>
> I continue to be mystified when sp_who2 shows 20+ lines for a single
> SPID on something like a four*singlecore processor box, or my
> 1*dualcore processor workstation. Are those tasks awaiting a
> processor, or what?
>
> If MAXDOP=1 is too strict, what would you say to MAXDOP=2? Since time
> immemorial SQLServer has been *much* happier on at least a
> two-processor box, but it was never entirely clear to me if that left
> the OS more power, or if it was SQLServer internals that benefited
> most directly.
>
> Josh
>