Swamped server - fixed through maxdop change from 0 to 1

  • Hi

    I'm trying to understand whats happened - we had an 8 CPU server that was maxing out at 100% CPU across all CPUs with a heavy read component to queries.
    What stopped it was changing the maxdop from 0 ( use all CPUs for each query ) to 1 ( use 1 CPU per query ).
    Can anyone explain in plain language why this massive decrease in CPU utilization happened? 
    The only explanation I can come up with is that with the 0 MaxDop setting the SQL Server was "drinking from a fire hose" and was able to process the data as fast as it was sent to it, and as such is basically "drowned" in data at 100% CPU.
    The MaxDop setting of 1 forced SQL Server to use 1 CPU per query, which has now dropped CPU to about 20% across all 8 CPUs.
    Maybe I dont understand SQL as well as I thought - can someone enlighten me as to whats happened please? I hate not understanding....
    In simple terms I now know that I have a "lever" I can pull to throttle throughput  when a server is overwhelmed.

    Thoughts welcome...  🙂

  • Oh, no, that's not a good setting. You've just disabled parallalism server-wide and forced all queries to run single threaded. Having your CPU at 20% is not a good thing, it means that 80% of your server is doing nothing, that's a waste of time and money.

    Maxdop 0 is not always good, especially with default cost threshold settings, but you do usually want SQL processing data as fast as it can.

    I'd recommend maxdop to 4, cost threshold to 50 and don't think that crippling a server is a good thing to do when it's high on CPU. 100 is too high, but should be fixed with query tuning, not by forcing most of your CPUs to do nothing at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply