I have two SQL Server 2016 Always On nodes – (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.
The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.
I need advise on the best way to:
-
Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
-
Perform Write and Read as fast as possible using all the hardware resources.
Best Answer
Ensure that the NUMA configuration of the host is reported accurately by VMWare, and he out-of-the box configuration of SQL Server will do that. Be sure to apply Service Pack 2 to get all the latest fixes. There's some good stuff back-ported from SQL 2017 in there.
The CPUs and RAM are only half the story when it comes to writing.
Your ability to Write will be gated by the performance of the disk containing your database log, and by your network connection to the AlwaysOn Secondary (assuming it's synchronous).
Consider Memory-Optimized Tables which are designed specifically to scale in write-intensive scenarios on big-memory multi-core machines by 1) minimizing logging, 2) eliminating locking and page latching, and 3) enabling native-compilation of TSQL code.
Your ability to Read should be largely unaffected by the disks, unless your frequently-read data is significantly larger than the RAM.