5

We recently set up a new machine with 8 dual-core CPUs, 20 GB RAM, and 3 1-TB drives set up in a RAID of some sort resulting in 2 1-TB drives we actually get to use (I'm not the hardware guy here). It is set up as an ESXi host and we have a number of test environments set up within it. The current tests are running on Windows 2003 64-bit with SQL Server 2005 Standard 64-bit SP3. From all reports, this system should host environments that perform better than our previous setup, and yet certain tasks are performing much worse. I have found one specific SQL script that reliably runs very slowly under certain conditions, which I can't understand. The SQL script is a simple series of 1700+ UPDATE statements that starts out like this:

UPDATE SrfItem SET fkSrfItem = 5 WHERE id = 4
UPDATE SrfItem SET fkSrfItem = 8 WHERE id = 7
UPDATE SrfItem SET fkSrfItem = 10 WHERE id = 9

I have found that if I follow the following procedure in one of these virtual environments, running the script takes 9-12 seconds:

Test Case #1

  1. Restore test database from a backup in virtual SQL Server environment
  2. Connect to database locally
  3. Run script - this step takes 9 seconds

The same procedure on my desktop ran step 3 in less than 1 second.

Test Case #2

  1. Restore test database from a backup in physical SQL Server environment
  2. Connect to database locally
  3. Run Script - this step takes less than 1 second

But running the script in a transaction goes quickly

Test Case #3

  1. Restore test database from a backup in virtual SQL Server environment
  2. Connect to database locally
  3. Add "BEGIN TRAN" at the beginning of the script
  4. Add "COMMIT TRAN" at the end of the script
  5. Run script - this step takes less than 1 second

What I find interesting is that it still runs slowly even after I execute it in transaction once and roll it back

Test Case #4

  1. Restore test database from a backup in virtual SQL Server environment
  2. Connect to database locally
  3. Add "BEGIN TRAN" at the beginning of the script
  4. Add "ROLLBACK TRAN" at the end of the script
  5. Run script - this step takes less than 1 second
  6. Execute only the portion of the script that does not include the transaction - this step takes 9 seconds.

I have run tests on a virtual system with Windows 2003 32-bit and SQL 2005 32-bit and on and a virtual system with Windows 2008 64-bit and SQL 2008 64-bit. I have run tests on a physical system with Windows 2003 and SQL 2005 and on a physical system with Windows 7 64-bit and SQL 2008 R2 64-bit. All the virtual systems I have tried exhibit this slowness and are hosted on the new ESXi environment. All the physical systems do not exhibit this slowness.

Can anyone help me understand what's going on here? I fear that similar performance issues are affecting other areas and we should reconfigure something on the host or guest environments. The only thing we can think of so far is turning off hyperthreading in the BIOS of the host machine to match the configuration of another virtual environment and its host where we were not able to see the slow behavior (I didn't observe the test on the other virtual environment&host where it wasn't slow). Could that create such a large performance difference?

Edit: After some review of my question and the first answer, I agree that what I managed to demonstrate is probably a difference in performance of I/O latency between our physical and virtual environments. I also realize that I should have provided some other details: these images are using thin provisioning and have two or three snapshots under them. Could this affect that statistic so significantly? The question now becomes, is it normal for this statistic to be so drastically different between virtual environments and physical environments? Should I be able to optimize that in the environment or in the SQL configuration, or is it up to the software itself to be written more optimally for virtual systems with extreme I/O latency?

vSphere client reports that the write latency on the virtual disk is 11 to 40 ms with an average of 21 ms. Is that a useful statistic? Is that extreme?

Edit: It appears that our hardware (DL380 G6) has performance problems as described at http://laez.nl/vmware-bad-performance-on-hp-proliant-dl380-g6-with-esxi-3-5-u4/ and we just need to do some reconfiguration to get the performance up. I'll accept the answer that led us in the right direction of seeing that disk I/O latency was the issue.

14
  • This can be due to slow disk performance - please run a speed test - like sourceforge.net/projects/iometer. Sep 18, 2010 at 15:42
  • Run it on what? Is it even possible to run things on the ESXi host? Wouldn't we have to remove ESXi and install an OS?
    – BlueMonkMN
    Sep 18, 2010 at 15:53
  • 1
    What are the results of going into unsupported mode and typing smartctl --all /dev/sda smartctl --all /dev/sdb?
    – zetavolt
    Sep 18, 2010 at 16:04
  • Is that something that would be run on the host machine? If so, it will have to wait a while because I have never seen the hardware myself and don't have access to it (will need assistance). Is it safe to do that while the host and guest environments are running?
    – BlueMonkMN
    Sep 18, 2010 at 16:28
  • 1
    You're running RAID5 across 1TB disks. It's not exactly the recommended configuration for SQL log storage..
    – pauska
    Sep 19, 2010 at 11:59

3 Answers 3

5

To sum up:

  • on your real server you can make 1700 table updates + 1700 commits in less than a second,
  • on your virtual server you can make 1700 table updates + 1700 commits in 9 seconds,
  • on your virtual server you can make 1700 table updates + 1 commit in less than a second.

So it seems to me that your problem can be redefined as "on a real server I can make 1700 commits in less than a second, but the performance drops tenfold on my virtual server".

What is the difference between 1700 table updates and 1700 commits? The table updates are fully cached and do not depend on disk I/O at all. With commits this is quite different. Per the very nature of transactional databases, the database engine has to be damn sure that the commit has been actually saved to disk (saved to a log file), before it even starts to commit the next transaction. So for every of those 1700 commits, it has to wait the entire I/O round-trip. To sum up, in your scenario the latency of I/O plays a very important role, and should be analyzed (do not mistake the latency with the I/O rate or throughput in bytes; these three are all totally different animals; they are always tuned separately).

It's a good plan to test your storage with IOMeter. It hangs at startup because it tries to fill your entire disk with its test file. Just wait until the file grows to considerable amount and re-start IOMeter, it will work properly with the "incomplete" test file.

5
  • 1
    +1 for the summary; probably worth pointing out that removing the BEGIN TRAN/COMMIT essentially adds an implied BEGIN TRAN before each UPDATE, and a COMMIT afterwards, making 1700 commits, and is much more dependent on speed of the disks, physical or virtual.
    – SqlACID
    Sep 19, 2010 at 14:23
  • I suspected something like this after reviewing my results. So I guess the question becomes "is it normal for virtual environments to have such a huge difference in I/O latency, or should I be able to optimize these environments?" I'll update my question with this and other details.
    – BlueMonkMN
    Sep 19, 2010 at 15:47
  • @blue - It's very common for virtual environment storage to be located on a SAN rather than local disks in the host servers. It's also common for those disks to be shared amongst lots of VMs. I would look closely at potential improvements from enabling paravirtual SCSI, dedicating spindles/LUNs to your VM, adding additional disk caches, network/fiber improvements and SSDs and/or local storage. In approximately that order. Sep 20, 2010 at 2:40
  • The thing that had us so puzzled is that we are using local storage, and it's performing worse than the virtual systems that are on a SAN. I think we need to look at the hardware statistics of the drives in use, and perhaps the RAID configuration. Hopefully now that we have a work day, we'll be able to review some of that.
    – BlueMonkMN
    Sep 20, 2010 at 10:50
  • As it turns out, the reason for the poor performance is specific to our server model as I described in the latest edit to the question. Thanks for leading us down the right path.
    – BlueMonkMN
    Sep 20, 2010 at 13:48
3

Your clarifications shed some light on the issue.

A 3 drive SATA RAID 5 pack isn't an optimal disk configuration for write performance. Each write IO incurs [up to] 4 disk IO's (read current block, read current parity, write new block, write new parity). In effect this turns your three 7200 rpm disks into a disk that performs more like a single 5400rpm drive, assuming your base drives are 7200 rpm.

Secondly you say that you have a number of active snapshots on the SQL VM's. VMware ESXi Snapshots incur an overhead that is not trivial - depending on what you are doing there will be a 50-100% IO overhead when you have active snapshots. This affects both reads and writes.

Thirdly you say you are using thin provisioning - that has an impact on IO performance but it's not as significant as the other two.

Finally you don't say if there are any other VM's running on the ESXi host - if there are they will obviously impact overall performance especially with that RAID5 x 1TB SATA disk setup.

0

I don't think your testing is really that robust to determine that there is a problem with the virtualised system. A one second test does not give enough time to stress the system to show up any real bottlenecks.

There are many moving parts in a virtualised world, and within SQL Server. I think disk IO is a major player here, but also RAM. ESX can give and take RAM from the guest on demand and it sometimes take a few seconds for ESX to react, producing short pauses. If a server is under a certain constant load then ESX stablises the RAM, but if the test is short and burst then it may take time to ramp up.

Before you start throwing the baby out with the bath water, run longer tests and monitor with ESX and perfmon the RAM usage, disk IO latencies, CPU queue lengths etc. A good test would take 30 to 60 seconds to run on the physical machine, and I would expect the virtual machine to be within 150% of that.

1
  • We have been searching for the source of this performance problem for weeks and have already looked at RAM, and a number of other factors. This test, while not very robust, has led us to what I feel is the source of the problem -- the I/O latency. It appears to be an order of magnitude higher than it should be, which seems to fit all the observations. Sure, more testing would be a good idea too, but I couldn't figure out what the test I was already running was pointing at. Now I see.
    – BlueMonkMN
    Sep 20, 2010 at 10:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .