SQL Server 2008 R2 Express and VMware

Question

We have used SQL Server Express - various versions without issue. However we have an install on a VMware machine and there are severe performance issues. I have contacted VMware and Microsoft - Microsoft don't explicitly support Express as its a free version. The problem is that as the database get larger, the performance gap between a physical machine and VMware gets much larger. VM is currently about 5 times slower than a physical machine of similar spec with a 4GB SQL Server 2008 R2 Express install (limit 10GB). There is a huge VMware service taking over 1GB of the 3.5 GB Memeory available on the VM. I'm looking for resources to try to find if performace can be improved. Links, comments much appreciated.

what type of storage subsystems, fc,iscsi and would assume you have the vmtools installed also but I thought I'd ask anyway — tony roth, Sep 18 '11 at 18:06
The disc subsystem is EMC disc using fibre channel. VMware tools is installed - sorry fro the delay I have to ask somone — Ian P, Sep 20 '11 at 13:34

score 0 · Answer 1 · answered Sep 23 '11 at 13:46

This is not very surprising. By many traditional measures the database operating system is a guest operating system running on top of Windows in this case. When it starts it immediately grabs a chunk of RAM and a chunk of disk that the SQL server wants to manage on its own for performance reasons. As soon as you ask the host to become a guest in VMWARE you are introducing another layer of arbitration in access to disk and memory associated with the performance of SQL Server. Another feature of guest operating systems is that they tend to have their own natural consolidation layer to maintain performance. In the case of SQL Server, although not necessarily SQL Express, you can coalesce multiple database instances on a single piece of hardware thereby maintaining performance and access to resources for the database engine and at the same time hitting many of the cost items which are driving virtualization from upper management.

With your note that the performance gets worse as more and more data is added this would imply that you have ever greater disk access to pull information. Each one of those requests must be arbitrated by the hypervisor before it actually hits the disk and the greater the disk access the greater the cumulative toll of all of those microseconds on delay in arbitration. The best you can do in this case is to examine your queries. Make sure that they are hyper-efficient in production, that they make use of indexes when and wherever possible to minimize table scanning activity (which is usually the case when response times are directly related to data table size). The fewer requests you have to make to the disk subsystem the fewer opportunities the hypervisor will have to arbitrate the requests and the faster your system becomes.

If your data is growing at a pretty good clip then you will want to look at other options moving forward since you are likely to run out of gas on SQL Express' ability to hit larger and larger data sets sooner rather than later....and usually this comes at an awkward time when you have to get stuff out the door. I think the limit on SQL Express 2088 R2 is 10GB. Not an insubstantial amount of data but one that seems to be easily exceeded these days.

I've actually tried limiting the memory SQL express asks for - It had been previously set at 3GB - I thought SQL2008R2 was limited to 1GB - options said it was using upto 3GB on a 3.5 GB system with vmWareService.exe using 1.051,372. The long duration 100% on one CPU spikes seem to have reduced. Its taken 5 years to get to 4GB, so the 10GB limit seems a reasonable limit for the next two years- provide we can get it to behave with the VMware environment. I've also added the some of indexes that SQL requested and removed some that were narrower. — Ian P, Sep 23 '11 at 14:42

score 0 · Answer 2 · answered Sep 23 '11 at 14:13

0

The Hypervisor layer for disk access will not cause this much of an issue. What is the VM doing on the back end? It sounds like it is probably ballooning memory and fighting for RAM or CPU contention with other resources on your VMware server. What version of VMware are you running? How over-committed are your memory and CPU resources? I run about 400 VMs with SQL servers on them and they do not experience issues. But I'm not over committed on RAM.

answered Sep 23 '11 at 14:13

rob

11
1

Thanks Rob, I'll ask the VM guy for figuers (it will take a day or two) – Ian P Sep 23 '11 at 14:24
Up the top of the post I expressed concern about the 1.3GB that vmWareService was running this article was relevant http://ict-freak.nl/2010/10/17/vmware-tools-vmwareservice-exe-or-vmware-guestd-high-memory-usage/ – Ian P Sep 27 '11 at 12:56

score 0 · Answer 3 · answered Oct 09 '11 at 12:36

Ok so the vmWare should not have been using 1G3- there is a documented memory leak due to some sort of reentrant ip thing. Rebooting the machine (if in doubt switch it off and switch it on again) cleared that out. On the other hand SQL express should not grab 3GB. This is supposed to be a 1GB limit according to MS. The SQL express Memory was (manuall) pegged at 786MB - small? increments up or down 256M both seemed worse, but the changes were not dramatic, so we went for the median. vmWare is slowly increasing - its up to 89MB - it started around 10MB. Note with SQL 2008 you can change the memory without restarting the (SQL) service.

score 0 · Accepted Answer · answered Oct 09 '11 at 13:04

It is quite possible that the "huge vmware service" you are seeing is due to memory ballooning which would occur in the case of physical memory shortage on your ESX host. The observed periods of full system utilization while it is not doing anything are in turn probably wait times for swap I/O to occur on the ESX host side. While ESX is swapping pages in or out (again, due to memory shortage) it would halt the guest, thus leaving only a few cycles for actual processing.

SQL Server 2008 R2 Express and VMware

4 Answers4