none
Performance upgrade op SQL VM RRS feed

  • Question

  • Hi,
    I did setup a new VM for SQL.  Software vendor installed SQL2017 on it and their application. Clients are complaining about performance and speed of the application.
    Is there a way i can approve the performance of the virtual machine or with other words the performance of the application.  by adding more VCPU ? more Memory ? other settings ??
    Any help would be welcome.

    This is the Host i use :
    HP DL380 G10 - 2 processors XeonSilver 4110 -  RAM 144Gb -  SAS disks 10K
    RAID5 -> Host
    RAID6 -> VM's

    These are the VM settings for the (VM) SQL-server
    1 Virtual CPU
    32Gb RAM (not dynamic)
    Disksize is 1Tb

    Regards,

    Jo
    Wednesday, January 23, 2019 9:59 AM

Answers

  • I don't know if it is your "problem" but your disk IO is extremely slow and is certainly contributing to the slowness. 

    Your IO is significantly slower than the SSD in my laptop, which is several years old.  You should be getting at least 20K+ IOPs on a server.  You are getting 5590.69.  My laptop is 18560.59.

    I would suggest you start there.

    My laptop running the command you posted above

    Total IO
    thread |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
    -----------------------------------------------------------------------------------------------------
         0 |     36370644992 |       554972 |     289.05 |    4624.76 |    6.913 |     6.808 | C:\TestLoad\TestLoad.dat (2048MiB)
         1 |     36531601408 |       557428 |     290.33 |    4645.22 |    6.882 |     6.684 | C:\TestLoad\TestLoad.dat (2048MiB)
         2 |     36665229312 |       559467 |     291.39 |    4662.22 |    6.857 |     6.717 | C:\TestLoad\TestLoad.dat (2048MiB)
         3 |     36399284224 |       555409 |     289.27 |    4628.40 |    6.907 |     6.811 | C:\TestLoad\TestLoad.dat (2048MiB)
    -----------------------------------------------------------------------------------------------------
    total:      145966759936 |      2227276 |    1160.04 |   18560.59 |    6.890 |     6.755


    Monday, February 4, 2019 7:47 PM
    Moderator
  • Adding more resources will definitely help but please note you are not curing the problem but only making server more powerful which may mask the performance problem. You need to find out what is causing issue and below should get you started

    How to Analyze SQL Server Performance


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, January 23, 2019 10:15 AM
    Moderator
  • Hi Ed,

    After my hours and days of searching and optimizing.  The problem was in the software (which was my first tought). Since i was at the end of knowledge. I went into discussion with the software developpers, stating that the problem was not hardware related, and they had to investigate their sql software.

    After a few days they came back with the answer.  Somewhere in SQL they where accessing an "old" database for every input/request which caused the slowness.

    After adjusting their software, the program is working fine and fast now.

    It took me days (for nothing), but i also learned a lot about SQL, IOPS, DISKS, etc...

    Friday, August 9, 2019 7:17 AM

All replies

  • Adding more resources will definitely help but please note you are not curing the problem but only making server more powerful which may mask the performance problem. You need to find out what is causing issue and below should get you started

    How to Analyze SQL Server Performance


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, January 23, 2019 10:15 AM
    Moderator
  • Hi Jo,

     

    I agree with Shashank. In order to completely solve the problem, you'd better to find the root reason.

     

    Could you please tell us the version and edition for your SQL Server.

     

    >>Clients are complaining about performance and speed of the application.

     

    Please share us more details for the problem. For example: which operation is slow and whether there are some error messages displayed.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 24, 2019 8:05 AM
  • Hi Emily,

    It's Microsoft SQL 2017 they are using.

    The "problem" they have is that it is "slow".  For example in the applications,  if they search for a customer it takes from 10 to 20 seconds to display that customer.  When it used to go instantly on the old server.

    Additional info, i have been gathering in the last weeks to solve this problem :

    If i open the resource manager on my SQL machine, i see the following :

    - CPU usage between the 4% and 6%, with now and then a spike to  10% - 15%

    - MEMORY usage constant on 16Gb (from the 32Gb available)

    - DISK usage between 1% and 2%, with spikes up to 15%


    -----------------------------------------------

    You could also monitor the following:

    Memory - Monitor Memory Usage

    Memory: Available Bytes
    Processor (CPU) - Monitor CPU Usage

    Processor: % Privileged Time
    Processor: %User Time
    System: Processor Queue Length
    Disk - Monitor Disk Usage

    Avg. Disk sec/Read
    Avg. Disk sec/Write

    =>  NO PROBLEMS found there

    -------------------------------------------------

    The old server was 6 years old, Xeon processor and had 16Gb TOTAL RAM memory, for everything !  AD, SBS, PRINT, FILE, SQL, etc...  Disks where SAS in RAID5 10K

    The new server is a high-end dual Xeon server, with 144Gb of RAM and 2 RAID5 with SAS DISKS 10K.  On the new server HYPERV is installed.  And the application has a virtual server ONLY to run their application. That virtual machine has 4 virtual processors and 48Gb available and has a disk of 1Tb.
    So, i don't get it ?  The new machine is like 20 times more powerfull, and still they complain the applictations is behaving slow ???

    By behaving slow they mean, if, for example, they search a customer in the app,  and it takes seconds (5 to 15) to display that customer. While is use to go instantly.


    -------------------------------------------------

    1. there was a F-secure Antivirus installed. But i already uninstalled it, so it would not interfere with performance.

    2. Running PerfMon, and  DiskSpd, These are the results.  SQL STRESS TEST

    DiskSpd Results :

    Command Line: diskspd -b64k -d120 -o32 -t4 -h -r -w25 -L -c2G C:\TestLoad\TestLoad.dat

    Input parameters:

        timespan:   1
        -------------
        duration: 120s
        warm up time: 5s
        cool down time: 0s
        measuring latency
        random seed: 0
        path: 'C:\TestLoad\TestLoad.dat'
            think time: 0ms
            burst size: 0
            software cache disabled
            hardware write cache disabled, writethrough on
            performing mix test (read/write ratio: 75/25)
            block size: 65536
            using random I/O (alignment: 65536)
            number of outstanding I/O operations: 32
            thread stride size: 0
            threads per file: 4
            using I/O Completion Ports
            IO priority: normal

    System information:

        computer name: SQL
        start time: 2019/01/29 08:16:15 UTC

    Results for timespan 1:
    *******************************************************************************

    actual test time:    120.00s
    thread count:        4
    proc count:        4

    CPU |  Usage |  User  |  Kernel |  Idle
    -------------------------------------------
       0|   9.32%|   0.72%|    8.61%|  90.68%
       1|   3.41%|   0.66%|    2.75%|  96.59%
       2|   3.52%|   0.76%|    2.76%|  96.48%
       3|   3.71%|   0.73%|    2.98%|  96.29%
    -------------------------------------------
    avg.|   4.99%|   0.72%|    4.27%|  95.01%

    Total IO
    thread |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
    -----------------------------------------------------------------------------------------------------
         0 |     11126374400 |       169775 |      88.42 |    1414.79 |   22.606 |    82.291 | C:\TestLoad\TestLoad.dat (2048MiB)
         1 |     10935074816 |       166856 |      86.90 |    1390.47 |   23.019 |    83.322 | C:\TestLoad\TestLoad.dat (2048MiB)
         2 |     11043274752 |       168507 |      87.76 |    1404.23 |   22.787 |    82.688 | C:\TestLoad\TestLoad.dat (2048MiB)
         3 |     10862198784 |       165744 |      86.33 |    1381.20 |   23.167 |    83.833 | C:\TestLoad\TestLoad.dat (2048MiB)
    -----------------------------------------------------------------------------------------------------
    total:       43966922752 |       670882 |     349.42 |    5590.69 |   22.893 |    83.031

    Read IO
    thread |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
    -----------------------------------------------------------------------------------------------------
         0 |      8343519232 |       127312 |      66.31 |    1060.93 |   29.771 |    93.619 | C:\TestLoad\TestLoad.dat (2048MiB)
         1 |      8202944512 |       125167 |      65.19 |    1043.06 |   30.191 |    94.391 | C:\TestLoad\TestLoad.dat (2048MiB)
         2 |      8287551488 |       126458 |      65.86 |    1053.82 |   29.907 |    93.741 | C:\TestLoad\TestLoad.dat (2048MiB)
         3 |      8152481792 |       124397 |      64.79 |    1036.64 |   30.414 |    95.149 | C:\TestLoad\TestLoad.dat (2048MiB)
    -----------------------------------------------------------------------------------------------------
    total:       32986497024 |       503334 |     262.15 |    4194.45 |   30.068 |    94.222

    Write IO
    thread |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
    -----------------------------------------------------------------------------------------------------
         0 |      2782855168 |        42463 |      22.12 |     353.86 |    1.124 |    13.497 | C:\TestLoad\TestLoad.dat (2048MiB)
         1 |      2732130304 |        41689 |      21.71 |     347.41 |    1.485 |    20.466 | C:\TestLoad\TestLoad.dat (2048MiB)
         2 |      2755723264 |        42049 |      21.90 |     350.41 |    1.375 |    19.022 | C:\TestLoad\TestLoad.dat (2048MiB)
         3 |      2709716992 |        41347 |      21.53 |     344.56 |    1.366 |    17.359 | C:\TestLoad\TestLoad.dat (2048MiB)
    -----------------------------------------------------------------------------------------------------
    total:       10980425728 |       167548 |      87.26 |    1396.23 |    1.336 |    17.765



    total:
      %-ile |  Read (ms) | Write (ms) | Total (ms)
    ----------------------------------------------
        min |      0.066 |      0.083 |      0.066
       25th |      0.147 |      0.150 |      0.148
       50th |      0.182 |      0.173 |      0.179
       75th |      0.392 |      0.239 |      0.320
       90th |     99.222 |      0.385 |     52.176
       95th |    204.227 |      1.134 |    163.224
       99th |    457.885 |     18.511 |    416.340
    3-nines |    929.659 |    204.330 |    884.969
    4-nines |   1395.675 |    904.954 |   1327.566
    5-nines |   1842.018 |   1315.000 |   1797.621
    6-nines |   2612.470 |   1434.551 |   2612.470
    7-nines |   2612.470 |   1434.551 |   2612.470
    8-nines |   2612.470 |   1434.551 |   2612.470
    9-nines |   2612.470 |   1434.551 |   2612.470
        max |   2612.470 |   1434.551 |   2612.470

    Hope you can detect something in the info i have provided.

    Regards,

    Jo

    Monday, February 4, 2019 5:09 PM
  • I don't know if it is your "problem" but your disk IO is extremely slow and is certainly contributing to the slowness. 

    Your IO is significantly slower than the SSD in my laptop, which is several years old.  You should be getting at least 20K+ IOPs on a server.  You are getting 5590.69.  My laptop is 18560.59.

    I would suggest you start there.

    My laptop running the command you posted above

    Total IO
    thread |       bytes     |     I/Os     |    MiB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
    -----------------------------------------------------------------------------------------------------
         0 |     36370644992 |       554972 |     289.05 |    4624.76 |    6.913 |     6.808 | C:\TestLoad\TestLoad.dat (2048MiB)
         1 |     36531601408 |       557428 |     290.33 |    4645.22 |    6.882 |     6.684 | C:\TestLoad\TestLoad.dat (2048MiB)
         2 |     36665229312 |       559467 |     291.39 |    4662.22 |    6.857 |     6.717 | C:\TestLoad\TestLoad.dat (2048MiB)
         3 |     36399284224 |       555409 |     289.27 |    4628.40 |    6.907 |     6.811 | C:\TestLoad\TestLoad.dat (2048MiB)
    -----------------------------------------------------------------------------------------------------
    total:      145966759936 |      2227276 |    1160.04 |   18560.59 |    6.890 |     6.755


    Monday, February 4, 2019 7:47 PM
    Moderator
  • Hi,

    If i do the same test on my Hper-V host i only get  2956 I/O per s.
    If i do the same test in my Virtual machine (SQL) i get 5590 I/O per s.

    The hardware is : 
    HPE Smart Array P408i-a SR Gen10 Controller
    One RAID 5 array consists off 3 x 1.2Tb SAS 10K Disks
    One RAID 6 array consists off 4 x 2.4Tb SAS 10 K Disks
    The seconds array is where my virtual machines are stored.

    So if i follow the logic, it would suggest "the problem" is already on my host system ?

    Correct ?

    Regards,

    Jo

    Tuesday, February 5, 2019 10:30 AM
  • Update :

    Still testing/searching.

    I did recup the old server to do som testing.  I could not run diskspd because the system was Server 2008SP2, and diskspd doesnt work on that OS.  So what i did now is reconfigured/reinstall the server, with Server 2016 to be able to run diskspd.

    Made 2 arrays,  one RAID5 with 4 disks 10K, and one in RAID0 with 4 disks 10K (for speed)

    This gives me on the first array approx. 1100 IOPS, and on the second array approx. 2500 IOPS.  There for i think i can conclude that the old server isn't (wasn't) faster, what disk-io concerns.

    So i still need to keep seraching,  to check or find what i can, if possible, improve.

    Maybe just an odd question, since i start to doubt anything :

    This is the setup i can see, on my host the raid controller driver is installed. On the VM there a microsoft driver installed. I think this is normal, and should not change it. But as said i'm doubting evrything now.

    Should i lead this like that ?

    Saturday, February 9, 2019 10:24 AM
  • Hi,

    Over the weekend i did the conversion.

    I stopped all my VM's

    Stopped all Veaam services

    Stopped "Hype-V Virtual Machine Management"  (because some files where blocked by this service)

    Explorer,  copy all my directories to an external NAS

    Started my Raid management tool, deleted the second volume, and created a new volume, this time RAID10

    Explorer, copy back all my directories from the external NAS

    Start the services that i stopped

    set the permisions back on the folders, as they where

    And started my VM's one by one

    All seems to work as i should.


    IOPS before and after
    On the HOST
    Before    6417
    After    8207
    After+CacheUpdate    10251

    Within the VM
    Before    5590
    After    10429


    So i hope this has impact now on the performance of my SQL VM
    Customers is going to test comming days an give feedback.
    i'll keep you posted.

    Monday, February 18, 2019 3:12 PM
  • Any news, Jo? Thanks!

    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 8, 2019 9:32 PM
    Owner
  • Hi Ed,

    After my hours and days of searching and optimizing.  The problem was in the software (which was my first tought). Since i was at the end of knowledge. I went into discussion with the software developpers, stating that the problem was not hardware related, and they had to investigate their sql software.

    After a few days they came back with the answer.  Somewhere in SQL they where accessing an "old" database for every input/request which caused the slowness.

    After adjusting their software, the program is working fine and fast now.

    It took me days (for nothing), but i also learned a lot about SQL, IOPS, DISKS, etc...

    Friday, August 9, 2019 7:17 AM
  • Hi Jo,

    Appreciate your response. I have marked your response an answer 


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, August 9, 2019 8:54 AM
    Moderator