Hyper-V Best Practices for SQL Performance

Question

Hi,

I am installing SQL 2019 Standard Edition on a Windows 2019 Virtual Server. There are also other application servers on the physical server which will be connected to this SQL Server. I am having the below network and virtual switch settings on the Hyper-V Server. Are these settings sufficient enough to have maximum performance or we can increase the performance by changing settings? For example, creating internal virtual switches for the communication among servers on the same physical hyper-v server.

1. NIC Team1 with 2 NICs on the Physical Server as the Management Interface
2. NIC Team2 with 2 NICs on the physical Server configured as a Virtual Switch under Hyper-V and is being used for all virtual servers including SQL.

Thanks.

Answer 1

Well, my first Hyper-V performance advice would be not to run anything on the parent partition other than Hyper-V itself.

That said, you're only talking about NICs so obviously you're expecting the network to be the bottleneck here. It will, of course, depend on the speed of those NICs. It's very hard for a single VM to saturate a single 10G NIC, let alone a team.

If you're expecting a significant load on your SQL VM, you should be looking at CPU cores, NUMA, RAM allocation and the likes. Not forgetting the underlying storage subsystem.

I think your best chance of getting usable guidance here lies in specifying your precise requirements.