2

I am requesting a new server from our Server team. Which is better for SQL Server performance: 2 CPUs with 8 cores per CPU or 1 CPU with 16 cores?

  • 8
    You mean, 2 CPUs with 8 cores each? – mustaccio Nov 12 at 16:52
  • @mustaccio Ditto for 1 cpu with 16 cores. I would love to see the motherboard with 16 cpu slots that only supports single core cpus; it probably advises you to download more ram on page 1 of the manual. – MonkeyZeus Nov 13 at 14:07
5

This question is very broad, and the answer depends very much on the nature of your workloads and your planned SQL Server setup. However, assuming your future hardware supports NUMA (why wouldn't it?) and given that

With SQL Server 2016 (13.x), whenever the SQL Server Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. [...] the SQL Server Database Engine creates soft-NUMA nodes that ideally contain eight cores

one might reason that the vendor thinks 8 cores per socket is the optimal configuration in most cases.

You might also find this old article useful.

5

If you are talking about physical CPUs then you would have to study the differences between the chips in those configurations, better still see if you can run some benchmarks using your own code to see if there is any useful difference. There could be a wide range of other differences: how much L2/L3 cache is present available in total, to each chip, and to each core?, is there any difference in memory bus standards?, ...

If you are talking about virtual hardware, then it is going to depend upon which virtualisation system you are using and how it shares loads around the CPUs resources it has. There may effectively be no difference because in either case your 16 virtual cores will get their workloads distributed around the real hardware in exactly the same way, or there might be some sort of mirror of real hardware (so in the 2x8 case each set of 8 cores may be more "local" to each other than they are to the other set).

In either case there may be a licensing difference: if you buy licenses per socket (i.e. per CPU) then the 1x16 option may be cheaper as you only have one to pay for. If you pay per machine/node then there will be no difference there.

2

The differences are very small and depend on locality of the threads. If the CPU threads are isolated and have large datasets the additional L2 cache on two physical CPUs will offer slightly better performance. But if the threads are poorly isolated and spend all their time accessing the same memory regions then having everything in a single L2 cache will reduce memory fetches.

I believe the current generation of SQL Server is NUMA aware and will try to keep related processes on a single NUMA node. So running two CPUs might offer a very small advantage. But, from a practical perspective this is not significant.

My recommendation would be to buy whichever is cheaper and spend the difference on more RAM and SSD drives.

  • Two socket board, one 16 core NUMA capable CPU. The price difference for the board is minimal, and it leaves the option to buy another 16 core CPU later and just add it. – Simon Richter Nov 13 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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