3

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.

Improve this question
3
  • 1
    You mention expensive queries going parallel.... is that a good thing? What happens if you run the query with MAXDOP 1? If it performs at least as well then buying more cores won't fix that. See blog.quest.com/…. for a better explanation. Similarly SOS_SCHEDULER_YIELD is usually benign.
    – Jonathan Fite
    Mar 25, 2022 at 14:34
  • 1
    Besides KASQLDBA's advice, you should consider working with your ERP vendor to understand what they recommend to run their software most efficiently, especially in your specific case. There's always a wait type, it's not possible for there not to be. The wait types you described don't necessarily mean you have a problem, especially 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.
    – J.D.
    Mar 25, 2022 at 19:12
  • Can you post output from query mentioned in This Link. Lets see what top waits are there apart from CXPACKET.
    – Shanky
    Mar 28, 2022 at 14:06

1 Answer 1

Reset to default
0

I believe you would need to collect CPU Usage on that server over a period of time to analyze the different intervals of usage. Depending upon if sql is hosted on windows or linux platform you can collect either via system health XE or dmvs to caculate the usage.

In addition to this we at our end are always collecting the data on max worker threads available vs used over time to make sure we are not running out of worker threads. Generally speaking this counter very much relate with cores as available worker thread is calculated based on number of cores available.

Few other things i would monitor is queries or TOP CPU consuming sql queries. sp_blitzcache is something I recommend since i use it quite often. Lastly you also would need to measure how much blocking/deadlocks are there which may be impacting this.

Final note- i believe having more cores is equally important as faster CPU family processor. If my load is more of serial i would go for faster CPU family considering performance is my priority. However if its least, i might go with more cores lesser speed to avoid running out of worker threads

Improve this answer
3
  • Thanks for your response! I'm not sure how to apply it, though. We're capturing blitzcache data every 15 minutes and dashboarding/analyzing it. How would I apply that data, though, to determine an ideal number of cores when spinning up a new VM/instance?
    – Eluros
    Mar 25, 2022 at 15:01
  • There is no easy way to say i want exact cores which will suit my requirements! There are chances many a times cores added can be sitting idle while on contrast if added less can still spike the CPU. You would also need to do some load analysis and see whats avg consumption right now. If you can add all those details may be we can add more to answer
    – KASQLDBA
    Mar 25, 2022 at 18:46
  • 1
    Have you seen this answer about core count vs speed?
    – Hannah Vernon ♦
    Aug 6, 2022 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.