Hyper-V SQL vs Physical SQL vs Vmware SQL
-
Hi:
I am new here and looking for some heIp
I discovered a SQL issue within Hyper-V today, hope you can easily reproduce it too.
i run a simple SQL insert query,
it inserts values 10000 times into a test table , loops through 300 timesdrop 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 300what 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 30-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 -
virtualisation has an overhead, average 10-15%, what ever way you dress it up. There is a software layer interracting with the hardware, the most sensitive and usually the worst performing is the virtual hard disks. They're sequential files and do not like random I\O.
What is different between your tests?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
This is a very interesting test. I am sorry, I do not have any insight to offer. But I wanted to know if you also collected any data related to time taken by each query in the various environments? How do the times compare amongst the various environments?
Thanks,
Ashish - ash304 - Monday, April 10, 2017 8:39 PM
this is kinda of disk latency benchmark
and the physical machine as wins, there is no surprise, but it is the disk write that generated by the same query suprised me by factor of x5
Physical around 530ms, VMware about 650ms and Hyper-V about 850ms
if you consider Hyper-V guest VM actually writes (if) x5 time more data, it is probably performed better than VMware in this aspect.
Physical Machine
Hyper-V Guest and HOST task manager , so the HOST actually is consistent with the Guest VM stats - fiveandhalf - Tuesday, April 11, 2017 2:03 AMash304 - Monday, April 10, 2017 8:39 PM
this is kinda of disk latency benchmark
and the physical machine as wins, there is no surprise, but it is the disk write that generated by the same query suprised me by factor of x5
Physical around 530ms, VMware about 650ms and Hyper-V about 850ms
if you consider Hyper-V guest VM actually writes (if) x5 time more data, it is probably performed better than VMware in this aspect.
Physical Machine
Hyper-V Guest and HOST task manager , so the HOST actually is consistent with the Guest VM statsHow many guest machines are on the HyperV host and the VMWare host
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply