VMware Cloud Community
nachogonzalez
Commander
Commander
Jump to solution

Performance Issues MS SQL

Hi, I'm having performance issues with a MS SQL VM.
We have recently migrated from a legacy infrastructure to a brand new vSAN ready installation.
On our previous infra it took about 1 minute to complete a batch job, not it takes up to 10 minutes.

I have cheked for resource contention but VM is sitting alone on the ESXi host and has all resources reserved.
Also Host 2 40GBe interfaces and a 40GBe upstream switch


CAn you give some advice?

Warm regards

1 Solution

Accepted Solutions
lucasbernadsky
Hot Shot
Hot Shot
Jump to solution

Hi nachogonzalez.

VMware has a complete guide to install MS SQL on VSAN. As this apps usually needs intensive IOs, i would suggest to take a look to the following things:

EDIT:

  • Please take a look if you are using encryption. I wouldn't be so bad but just take that in adds CPU overhead.
  • Take a look a physical disk format and their compatibility VMware Knowledge Base

As for the OS:

Operating system settings:

  • Set the power plan to high performance in the guest OS.
  • Enable SQL Server to use large pages by enabling the Lock pages in the memory user right assignment for the account that runs the SQL Server in Group Policy.

SQL Server startup parameters:

  • -x: Disable SQL Server performance monitor counters.
  • -T661: Disable the ghost record removal process.

Please keep me updated

View solution in original post

2 Replies
lucasbernadsky
Hot Shot
Hot Shot
Jump to solution

Hi nachogonzalez.

VMware has a complete guide to install MS SQL on VSAN. As this apps usually needs intensive IOs, i would suggest to take a look to the following things:

EDIT:

  • Please take a look if you are using encryption. I wouldn't be so bad but just take that in adds CPU overhead.
  • Take a look a physical disk format and their compatibility VMware Knowledge Base

As for the OS:

Operating system settings:

  • Set the power plan to high performance in the guest OS.
  • Enable SQL Server to use large pages by enabling the Lock pages in the memory user right assignment for the account that runs the SQL Server in Group Policy.

SQL Server startup parameters:

  • -x: Disable SQL Server performance monitor counters.
  • -T661: Disable the ghost record removal process.

Please keep me updated

nachogonzalez
Commander
Commander
Jump to solution

Thanks for the quick reply, let me reply to your marks.

  • vSAN Backend and performance graphics- This will give you some useful metrics to quickly identify the issue. This seems to be fine
  • Take a look at overall vSAN health and make sure every requirement is in good health. (Network is key when talking about vSAN, check dropped packets and MTU settings of your switch ports. Some need to be configured at 9124) This is Okay
  • Storage Policies - If you are running a hybrid configuration (HDD + SSD) I would suggest to take a look at the IOPS requirements of your app and if the disks meet them. Also, you can add more stripes to the VM (About vSAN Policies ). If you are running all flash, I would suggest you to use RAID 1, since Erasure coding (RAID 5/6) takes an impact on performance, and add stripes to your policy. We are using RAID 5
  • VM Tools updated and running on your OS and VMXNET3 vNIC This is Okay
  • Make sure you are using paravirtual scsi as your vdisk controllers. You can find it here SQL Server Virtual Machine Configuration | SQL Server 2016 Database Performance and Improvements on ... and here VMware Knowledge Base​  We are using paravirtual SCSI
  • Make sure you are using different VMK for vMotion, management and vSAN servicies. vSAN services VMK are separated.
  • In the NIOC of your DVS, make sure to prioritize vSAN and VMs traffic over the rest. NIOC Configuration Example | VMware® vSAN™ Network Design | VMware 
  • Will take this into consideration
    Make sure your cache disks are at least 10% of the capacity disks of each disk group. We are using all flash.
  • Make sure your cluster passes the proactive tests Proactive Tests​ all clusters pass Proactive tests
  • if none of the options above worked, I would suggest to see vSAN Observer and vROps analysis.
  • Again, if none of the options above worked, open a SR please

As for the OS:Operating system settings:

  • Set the power plan to high performance in the guest OS. DOne
  • Enable SQL Server to use large pages by enabling the Lock pages in the memory user right assignment for the account that runs the SQL Server in Group Policy.

SQL Server startup parameters: This is already done

  • -x: Disable SQL Server performance monitor counters.
  • -T661: Disable the ghost record removal process.

Please keep me updated

Reply
0 Kudos