Beware of CPU bottlenecks on SQL Server on VMWare ESX Server 3.0

Posted by Jason on Wednesday, December 12, 2007 to SQL Server 2005, Virtualization
2403 Views | 0 Comments |

I have worked on two applications that were completely virtualized both on the front end and on the data tier. This post will cover my observations and experience.  Both applications were similar. Both were newly developed ASP.Net 2.0 web applications with a SQL2005 x86 standard edition backend.  They went with a virtual environment to avoid CAPEX. They both had 2 VPU, 2GB of RAM and a good HP EVA SAN. The databases were new so they were relatively small. The first one was tuning during a pre-production automated load test. The second was tuning a production work load.

I treated it as a normal tuning gig. I captured a perfmon log to determine which, if any, hardware subsystems were the bottleneck. They both turned up no disk or memory bottleneck. CPU averaged 70-80%. However, slow response times at the front end were reported. Upon further investigation, there was a CPU queue length, SOS_SCHEDULER_YIELD wait types, and runnable tasks averaging five or higher. We definitely had a CPU bottleneck. The only odd thing is we could not hit 100% CPU.

After some research, it appears that the CPU time counter may not be totally accurate. The full details can be found in this white paper.

“CPU usage data collected within virtual machines is not useful for two reasons. First, this data does not always accurately reflect the overhead of virtualization that is incurred by the ESX Server host. Second, because of the way time is kept within virtual machines, the usage data itself may be inaccurate(for details, see “Timekeeping in VMware Virtual Machines” in “References”). For these reasons, we use CPU usage data collected on the ESX Server host in this study. We used the esxtop tool to collect resource utilization statistics for VMware ESX Server. For further details, see “Appendix 2: Data Collection.”

Well, I went on tuning like a normal CPU bottleneck. We made substantial gains mainly through indexing, parameterization and caching at the web tier.  We did nothing different because of virtualization.

Although I have yet to experience it myself, the white paper shows nice gains by using x64 especially if you need a multiprocessor environment so that is another thing to keep in mind. I would love to hear your experience.