none
Hyper-V SQL vs Physical SQL vs Vmware SQL

    Question

  • Hi Hyper-V team:

    I discovered a SQL issue within Hyper-V today, hope you can try to reproduce the issue in your lab and have answer for it.

    i run a simple SQL insert query,

    it inserts values 10000 times into a test table , loops through 300 times

    drop table test
    set nocount on 
    DECLARE @i int  
    DECLARE @starttime datetime 
    create table test (a tinyint, b smallint, c int, d float, e bit, f char(50), g varchar(50)) 
    select @i = 1 
    select @starttime = current_timestamp 
    while (@i <= 10000) 
    	begin 
    	insert into test (a, b, c, d, e, f, g) values (1, 1, @i, 1, 1, 'value', 'value') 
    	select @i = @i + 1 
    	end 
    select datediff(ms, @starttime, current_timestamp) 
    go 300

    what I found is that:

    1)

    if I run this query under SQL 2014 on Windows 2012 R2 OS on a physical machine

    the perfmon (or task manager) shows  disk write bytes /sec is around 11M bytes per second throughput  

    2)

    if I run this query under SQL 2014 on Windows 2012 R2 OS on a VMware ESXi 6.5a VM machine, Thin provision disk

    the perfmon (or task manager) shows disk write bytes /sec is around 11M bytes per second throughput  

    3) 

    if I run this query under SQL 2014 on Windows 2012 R2 OS on a Hyper-V 2012 R2 VM machine, Dynamic or Fixed VHDX 

    the perfmon (or task manager) shows  disk write bytes /sec is around 40-50M bytes per second throughput

    I performed 3 tests on the same hardware, a Dell Optilex 9020 with one 2TB Samsung 850 SSD

    each time, I trashed the system and rebuild it. 

    I have done twice rebuild, ie. 6 tests total, the issue seems to be very consistently reproducible. 

    I tried fixed or dynamic VHDX with block size of 1MB,2MB,4MB,8MB,16MB,32MB(default VHDX setting)

    the result is similar with 1 or 2MB block yielded slightly lower throughput of 30-35MBytes/sec, but still 3x more than a physical machine or a VM under VMware 

    so it seems to me that the Hyper-V has some kind of amplified disk write under SQL

    I am looking forward your help

    thank you

    Friday, April 07, 2017 1:16 PM

All replies

  • Hi Sir,

    Have you compared with the total time of physical disk busyness ?

    Unfortunately , I didn't find the mechanism of storing data to VHD .

    I'd suggest you to open a case with Microsoft to get further assistance :

    http://support.microsoft.com/contactus/?ln=en-au

    Best Regards,

    Elton


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, April 10, 2017 2:10 AM
    Moderator
  • BTW, I derived a newer, easier test to show it is MS bug,

    run a simple insert 50K row query across 3 platform, and record the IO write byte column in the task manager

    For my case, for the same insert query, I have roughly 350MB IO write on a VMware VM or Physical against 2.1GB IO write on a Hyper-V VM

    through perfmon data, I can see the "disk write/sec" is similar across 3 platforms, but "disk write byte/sec" is about x7 times amplified 

    and a simple file copy command across 3 platforms show NO disk amplified write

    So there is fundamental issue here in your Hyper-V +SQL product

    I’ve logged call MS and Microsoft support has reproduced the issue under their Hyper-V,

    I wonder how long they can reach a conclusion that this is a major MS bug and a  ETA for a hot fix





    • Edited by Jay5.5 Tuesday, April 18, 2017 1:47 AM
    Tuesday, April 18, 2017 1:32 AM
  • Hi Sir,

    Have you tired to put the Database on a VHD which was attached on SCSI controller ?

    Or, using GEN2 VM for test ?

    Best Regards,

    Elton


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Sunday, April 23, 2017 9:04 AM
    Moderator