cpu provisioning for sql server

[ankitrtr]

Hello Guys,

I want to deploy a SQL server VM and I am a bit confuse while allocating CPU. I have 8 Cpu's. Which approach is the best ? and why?

a. 8 Virtual sockets X 1 number of cores per socket

b. 4 Virtual sockets X 2 number of cores per socket

c. 2 Virtual sockets X 4 number of cores per socket

I want to know the factors behind this.

Thank you

[daphnissov]

Answer is A because of NUMA and vNUMA. There is an entire book dedicated to virtualizing SQL server plus untold number of blogs and articles about why this is a preferred, default configuration unless you have specific overriding design requirements.

[ankitrtr]

Could you share some resources about this topic.? Thank you

[daphnissov]

https://www.amazon.com/Virtualizing-SQL-Server-VMware-Technology/dp/0321927753/

[ankitrtr]

Hello,

I came across various NUMA and vNUMA articles which suggested the less number of sockets and more number of cores for improving performance. May I know the reason you said more number of socket and less number of cores. I am asking you this because the earlier engineer at my organization had kept 8 sockets X 1 core per socket configuration.

Please let me know if you have any information on it

Thank you

[Bab]

As i read in many articles their sugget or best practice for optimize Numa is use less sockects and just increase cpu cores and next step if you want increase the cpu socket just select according your physical socket for instance if our server has 4 socket select 4 socket and not select more thatn 4

BR

Babak

[daphnissov]

I came across various NUMA and vNUMA articles which suggested the less number of sockets and more number of cores for improving performance.

Can you provide a link to this evidence? Because, in most cases, that is not true. Also, you say you weren't able to find anything. You must not have searched well enough:

https://itnext.io/vmware-vsphere-why-checking-numa-configuration-is-so-important-9764c16a7e73

Virtual Machine vCPU and vNUMA Rightsizing - Rules of Thumb - VMware VROOM! Blog - VMware Blogs

Does corespersocket Affect Performance? - VMware vSphere Blog

[Bab]

please read this article

Virtual Machine vCPU and vNUMA Rightsizing - Rules of Thumb - VMware VROOM! Blog - VMware Blogs

in all of document said fix cpu socket and just change cpu core per socket untill you nedd a vm with more than core per socket for example need a vm with 24vcpu in this mode select 2socket and change core per sockect to 12 .

[daphnissov]

This implies that you know exactly how your NUMA nodes are outlined and that nothing changes, also that you remember all this. And that can work. But by having only sockets and cores = 1, you always let ESXi place that VM into the most optimal configuration. ESXi 6.5 made some behavioral changes in the way cores per sockets work, so you should read up on that.

[Stuart Hardman]

always start small and work up

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pd…