I work at the Museum of Science, Boston as their Senior DBA. I’d like to know if anyone has adjusted these two parallelism settings in your on-premise SQL Server:
We have slowdowns periodically and when we do, I use Spotlight on SQL Server to make sure the production SQL Server isn’t the problem. In doing some of that forensic work, I noticed undue pressure on the CPU. I don't think it's the culprit but it made me review the settings more deeply.
My research is showing that we should set the cost threshold to something other than 5 (most often quoted at 50) and the MAXDOP should be equal to or less than the CPU (8 or less). If we set it to 1, we’ll essentially turn parallelism ‘off’ so the range is 2-8.
We opened a ticket with Tessitura to ask their advice but they won't comment on server settings like this. We run reports directly against production, so I’m wondering if some of the larger/longer ones are eating up the CPU cycles across all CPU’s and not yielding to the more chatty queries that take no time to run.
Has anyone adjusted these settings and had any luck? Our environment is quite chatty - we have butterfly shows every 15 minutes, planetarium shows every hour, etc. We don't sell just one or two performances a day - more like hundreds throughout the day (including exhibit hall admissions). I'm seeing a lot of SOS_SCHEDULER_YIELD wait types showing up which makes me believe we're having this issue. I've been watching it over the past month or two, so it's a cumulative observation, not one isolated to a particularly bad time.
Thanks for the help.
How many cores and NUMA nodes (processors) are you running? I'm relatively new at this, but that seems relevant.
I set cost threshold to 50 based on the recommendation from the folks at brentozar.com -- their story is that the default setting of 5 may have made sense many years ago, but single threaded CPU performance has changed dramatically since then (and SQL Server's scale for query cost hasn't).
Our production server is 4 cores on one NUMA node, and I set MAXDOP to 2.
I'm by no means an expert on this, and I've never had to triage performance issues on our server, but my understanding is that you don't want parallelism to span multiple NUMA nodes (if you have them), and I wouldn't want a single query to take over all of my cores if I'm running an OLTP workload. It sounds like the strategy you need to go for is to let the reporting queries take as long as they need to in order to leave enough cores free to handle all of the transactions. So... maybe start with a lower MAXDOP and work your way up?
And just a theory -- what could be happening on your system is with your cost threshold so low, lots of queries are going parallel that would be fine single-threaded, and so all the extra parallelism overhead is inflating your CPU usage.