We're soon going to rebuild the SQL Server running our production ERP. Our SAN Admin issued me the following challenge:
Assume I could give you as many Intel Xeon Gold 6240 CPU @ 2.6 GHz cores as you need for optimal SQL Server performance, as long as the ROI is reasonable. We don't want to waste money, but are willing to splurge a bit as long as you're getting tangible performance improvements. How many cores do you want?
On our current production box, we think we have MaxDOP and CTP set effectively, and expensive queries are going parallel, but we still hit very high numbers quite regularly. We're regularly getting SOS_SCHEDULER_YIELD and CXPACKET/CXCONSUMER as top wait stats. I'm pretty confident that we're under CPU pressure, and I'd love the new server to work better.
After doing a bunch of reading, I've found quite a few articles (including by Glenn Berry) talking about which CPUs to select. What I've not had success finding are articles talking about how to calculate the optimal number of cores to allocate.
Assuming cost matters but is secondary to tangible performance, what kind of metrics can I take from my production ERP SQL Server, and how can I compare them to a specific known processor, to determine how many cores to allocate for the best ROI in terms of performance:cost?
EDIT-- Since someone may ask-- we're on SQL Server Enterprise Edition. The production instance is SQL Server 2017 but we'll likely be upgrading to 2019 on the new server/instance.
SOS_SCHEDULER_YIELD
, but it is possible. You should monitor the CPU usage of your existing server as a benchmark at least & perhaps as a high level way to tell if you're really dealing with CPU contention. Performance issues aren't often solved with hardware.