SQL - Virtualized - Cores

If you're managing the VM platform SQL Server is running on, you must read this whitepaper from VMWare. I don't know if Microsoft has one for Hyper-V, but a lot of the advice in the VMWare document should apply to Hyper-V as well.

The number of CPU cores (virtual or otherwise) will have an impact on performance. SQL Server will split a single expensive query into multiple parallel threads if it can, but only if you have multiple CPU cores available. This can be the difference between a query running in 9 seconds and 90 seconds.

If the workload is primarily single-threaded, you may be able to get away with less. But you have to be careful there and may find yourself adding vCPUs anyway. And if you're doing things beyond the SQL Server engine itself (SSRS, SSIS, etc.) you'll have higher CPU requirements as well because everyone is fighting for the same resources.

I have one DBA insisting that SQL runs optimally on 4 cores because, 1 core is exclusively used for this, and 1 core is used exclusively for that, and the 3rd core is used exclusively for this other thing, leaving 1 core for Misc.

I don't think that's true, but they're not wrong that a production SQL Server can't skate by on a single CPU. I don't think I've ever run a production or even dev/test instance on fewer than 4 CPUs, but for a very light workload you may be able to get by on two.

Your DBA is wrong. Queries and other processes will run on any available CPU. SQL decides which scheduler to assign a query to based on load.

The actual number of Cores required will depend entirely on workload. There are a lot of configuratio s that can change the behavior of SQL.

You can benchmark SQL query latency, I would suggest you do so on 2 vs 4 core builds and show them that latency is lower on 2 core VM's. The reality is that most application queries are hot garbage, and a lower latency pipeline is often the best way to address them.

Doesn't even pass the sniff test. A core exclusively used for anything is a wasted core.

I don't give any VMs 1 core... they end up running something (virus scan, windows updates, etc...) and make the VM unresponsive.

​

For your situation 2 cores for the template seems fair and if the SQL Server is doing OLTP or SSAS or anything else CPU intensive additional cores can be added. If the SQL databases are designed properly, the CPU usage shouldn't be high... proper indexes and RAM allocation should limit disk reads and CPU usage... but there are some workloads that are going to be CPU intensive regardless.