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.
Hi Heather,
We did make adjustments to this in our environment early last year. I likely read some of the same material that turned up in your research! I took a fairly conservative approach, and I believe we did see some improvement. (At the very least, it caused no issues or deterioration in performance.)
Here are the settings we currently use:
· Cost Threshold for Parallelism = 15
· Max Degree for Parallelism = 4
We are currently running 8 vCPUs. I increased Cost Threshold for Parallelism to ensure queries with lower cost did not use parallelism. This was meant to be an interim step before raising it to something like 50, as you mentioned, but I haven’t had time to circle back to it. I set Max Degree of Parallelism to 4 for similar reasons Nick mentioned: I don’t want any single query to be spread across all of the cores allocated to our database VM. I see this as important as generally speaking, our longest running queries tend to be for reporting. We did change this and use it for at least 2-3 weeks in one of our test environments before making the change in our live database.
We use SolarWinds Database Performance Analyzer, which I believe is a similar product to Spotlight. We see very little SOS_SCHEDULER_YIELD waits at this point – in fact, most days, we have no waits of this type on a typical day. Sadly, I’m not sure what that wait looked like before we made this change. While I doubt changing these parallelism settings would resolve the root issue of that particular wait, it might at least provide some relief if there are some queries that are misbehaving, so to speak, and spread across all CPUs when they run.
I hope this helps – good luck to you!
Thanks,
David
_______________________________________________________ David Frederick Database Applications Analyst Segerstrom Center for the Arts 600 Town Center Drive, Costa Mesa, CA 92626 T (714) 556-2122 x 4067 E DFrederick@SCFTA.org