vSAN1

 View Only

  • 1.  Performance Issues MS SQL

    Posted Jun 10, 2020 07:35 PM

    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



  • 2.  RE: Performance Issues MS SQL
    Best Answer

    Posted Jun 10, 2020 07:49 PM

    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



  • 3.  RE: Performance Issues MS SQL

    Posted Jun 10, 2020 07:55 PM

    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 vSAN 6.7™ | VMware 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