0

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:

  1. Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.

  2. Perform Write and Read as fast as possible using all the hardware resources.

  • 1
    I recommend that you post your 3rd question (read-only routing) as another question as it is not related to the broader performance-related question. Also, your questions about performance--books have been written, so you might want to do some reading and then ask specific questions. Or, ask a more general question of "what do I need to learn about in order to know how to best configure..." – Tony Hinkle May 4 '18 at 12:35
  • I agree with @TonyHinkle that your questions may be too broad and there are a lot of books written on those topics. I do have a link that can help: vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/… – Shaulinator May 4 '18 at 14:00
3

Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.

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.

Perform Write and Read as fast as possible using all the hardware resources.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.