May 24, 2017 2:00:34 PM by Denny Cherry
This sort of question comes up a lot. And there’s a lot of it-depends built into the answer, so I’ll try and break this down a little bit for you. Be warned, we’re going to be talking about NUMA and other hardware-y things here for the most part.
You wants 1×6 (one socket, 6 cores) because standard edition will only use the first 4 sockets in a server (up to 16 cores combined). There’s no getting around that.
From a NUMA perspective as long a vNUMA at the Hypervisor is disabled then it doesn’t matter as SQL Server standard edition isn’t NUMA aware (NUMA awareness is an Enterprise Edition feature).
This is where things get more complicated if vNUMA is enabled in VMware or Hyper-V.
If vNUMA is enabled then you want one multiple vSockets and multiple vNUMA nodes so that SQL Server is aware of how the CPUs and memory are laid out within the hardware so that SQL can made good decisions on how the processes are being laid out against the hardware.
Now that said, you probably don’t want 6 vSockets. You probably want 2 vSockets with three cores each so that you get multiple cores per vSocket. But a lot of that will depend on if you can control how many vSockets there are per vNUMA node.
If vNUMA is disabled then you care less because SQL thinks that everything is in a single NUMA node so it’s going to make decisions based on that.
If the amount of RAM configured for your VM is larger than a physical NUMA node, then you need to turn on vNUMA for the VM (no matter how many cores you have) and configure the VM to the cores equally across the NUMA nodes that you present to the VM.
Yes, this is all very hardware-y, and requires some understanding of how pNUMA, vNUMA, vSockets, etc. all work together. For most DBAs you’ll want to just kick this over to the VMware / Hyper-V admin and have them do some tweaking.
Tags: SQL Server
Written by Denny Cherry
I am a Senior SQL Server DBA at CDW with 10 years of IT experience, mostly as a software developer building web and windows based applications (VB, VB.NET, C#, C++ and a smidge of Java). I have always found database design and set based logic interesting, so 3 years ago I took the plunge and became a DBA, soon after I discovered people would tell anyone who would listen all about the SQL Server internals. I was hooked. I have not looked back since. The things I say represent my opinion and in no way represent the views or opinions of my employer or coworkers.