SQL server hardware: memory frequency or count of cores?

Question

I have SQL Server 2019 installed on a server with an Intel® Xeon® W-2145 (8-core; 3,70 GHz) processor. Actually this SQL Server has been working properly for the latest 2 years.

To have the most effective hardware for SQL Server, I have tried to replace the current server machine with a new one with same amount of RAM, but with an AMD EPYC 7502p (32-core; 2,5 GHz) processor. But this server with AMD processor is working worse in the period of peak loading on server.

Some SQL requests were failing with timeout but such problem had not occurred on the old 8-core server. Is memory frequency so important for SQL Server?

And the next question below. Currently I am thinking to try to use the server with processor Intel® Xeon® W-2295(18-core; 3,0 GHz). But now I am not sure what is more important for SQL: memory frequency or count of cores? Based on my previous experience it looks like frequency is crucial. So it will be better use current Intel processor with 3,70 GHz frequency instead of new one with 3,00 GHz frequency but with 18-cores. Is it right?

Answer 1

First of all, timeouts is a client-side setting. Most client APIs have a default timeout of 30 seconds, so if SQL Server does not complete in this time frame, they raise the error Timeout expired.

If you are seeing lots of timeouts now, and you had none before, I would say this is because the query plans have changed. Query plans can change for many reasons, updated statistics, different parameter value sniffed etc. And they can also change because of the hardware. The memory frequency is not likely to affect the plans, but adding more cores certainly do.

If you already had Query Store enabled before the hardware change, you have a wealth of material to dig into, and you can compare old and new plans.

Else, enabling Query Store is always a good start, so that you can track down exactly which queries that are timing out, and then investigate how you can tune them.

Answer 2

Hi @Andrei Sidar

for high-loading sql server installation, what is the better to choose: 1). 32 cores, 2.5 GHz 2). 16 cores, 3.0 Ghz 3). 8 cores, 3.7 GHz

In my opinion, more cores would be better than more cpu speed. Because higher clockrate != shorter pipeline, and there are too many things to consider there, but with more cores, the OS can have more room to do side processes, and the engines are usually smart enough to be able to spread themselves over more cores.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.