Does SQLIO lie when run from a Hyper-V guest on a VHD?

Question

SQLIO seems like a useful tool. I thought it would be interesting to try to measure the speed difference between a physical disk and a VHD. So I ran SQLIO on the Hyper-V host on the physical drive. Results seemed reasonable. Then I ran it from the guest to test the vhd (on the same physical disk). I expected it to be a bit slower. But instead it was way faster - like 0ms average latency.

So I'm trying to learn something here. It seems like hyper-v is fooling SQLIO somehow but I don't understand it well enough to figure it out.

It's a dynamic vhd, no snapshots or anything, and the vhd is the only file on the disk. The physical disk is actually a two SAS drive RAID 1.

Answer 1

I know I'm late to the party here but I've found that using SQLIO on Dynamic disks does give false results. The reason behind this is that the tesfile.dat is just a zeroed out file, so the dynamic disk is clever enough to compress this file down behind the scenes, which in turn means it'll fit in your disk cache. So your SQLIO test is essentially just testing how fast your cache is.

I'm sure this is well resolved, but I had trouble finding the answer when I was testing, so I thought it might be useful to write it down for future reference :)

Stephen

Answer 2

Yes, Hyper-V is "fooling" sqlio with transaction bunching. A Hyper-V Server's SQL VM should not store the DB or Logs in a VHD. You should use a passthrough disk of some kind (iSCSI included).

Microsoft has a list of guidelines for SQL on Hyper-V and some details on configuring passthrough disks.

Answer 3

What size test file are you using? By increasing the size of the test file (to several gigabytes perhaps) you should be able to minimize the caching effect.

Answer 4

More diectly, time passes at variable rates in a virtual machine. In a physical machine there is a crystal and a clock thumping out precision cycles at a constant rate. The operating system uses this to tell time. But on a virtual machine the clock can slow down and speed up. The hypervisor decides how fast or slow the virtual clock beats based on load.