Sample Header Ad - 728x90

SQL Server-- Calculating Optimal Number of CPU Cores

3 votes
1 answer
4107 views
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.
Asked by Eluros (75 rep)
Mar 25, 2022, 02:19 PM
Last activity: Jun 11, 2025, 12:06 PM