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
- Restore test database from a backup in virtual SQL Server environment
- Connect to database locally
- Run script - this step takes 9 seconds
The same procedure on my desktop ran step 3 in less than 1 second.
Test Case #2
- Restore test database from a backup in physical SQL Server environment
- Connect to database locally
- Run Script - this step takes less than 1 second
But running the script in a transaction goes quickly
Test Case #3
- Restore test database from a backup in virtual SQL Server environment
- Connect to database locally
- Add "BEGIN TRAN" at the beginning of the script
- Add "COMMIT TRAN" at the end of the script
- 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
- Restore test database from a backup in virtual SQL Server environment
- Connect to database locally
- Add "BEGIN TRAN" at the beginning of the script
- Add "ROLLBACK TRAN" at the end of the script
- Run script - this step takes less than 1 second
- 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.