October 11, 2018 at 10:14 pm
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... 🙂
October 12, 2018 at 1:28 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply