major performance problem with SQL Server on Win 2008 R2 geu

[Bullitt]

Hi guys!

We have been testing Virtualbox with a new installation of SQL Server 2008 R2

We have a specific script that takes about 32 seconds to run in the virtual machine, but it only takes about 2 seconds on the old physical server with 4GB and core 2 duo CPU.

Before the mem settings of the VM was increased from 2GB to 5GB, it took a whooping 47 seconds to run.


Any input and suggestions will be much appreciated, as 32 seconds is WAY to much


Guest:
Windows 2008 R2 Foundation
5GB RAM
using 4 core
1 TB dynamic disk setup
MS SQL Server 2008 R2

host system:
Dell Server
8GB RAM
2,26GHZ XEON QUAD
Windows 2008 R2 Foundation

[popej]

Can you test your task with only 2 core in guest?

[Bullitt]

ok done, only shaved 2 seconds off the first run-though the script.

On second run, it runs in 1 second.

so there must be a big issues when it comes for SQL Server to load data into RAM...

[vbox4me2]

Remember you are sharing disk caching from the Host, if IO gets used up by the Host the Guest will suffer, there are tuning tools for such items. See what happens to all Hosts caching when the Guest runs a query with tools like processexplorer.

[Bullitt]

Will having a host running a RAID (striped) disk system help performance for the virtual machines???

[vbox4me2]

raid1 will help yes, or put the vdi's (sql)storage on iscsi.

[Bullitt]

Do you have any idea of just much more performance can be gained from RAID1? how does this iscsi work???

[vbox4me2]

It depends how much money you trow at a raid system, or how big the cache is, for iscsi see Openfiler

[Bullitt]

Today we just upgraded the host from 8GB RAM to 16GB - just to find out that windows server 2008 Foundation R2 only supports up to 8GB.... bummer.

now in the coming days, we will make a complete re-install using Windows Server 2008 R2 Enterprise.

The Dell server actually has a PERC H200 RAID controller, but was for some reason never configured by Dell before delivery.

Now I am thinking as we have to re-install the OS anyway - we should set it up as a stripe to improve performance.

The big question is - how much will it improve performance? We will have 3 VMs running - with typical 2-3 users per VM. All VMs run an instance of SQL Server 2008 R2, but most of the day there is not much activity. The VMs are just idling.

[vbox4me2]

Go for a LSI's SAS 6gb raid controller ea. 9260-4i and sas 6gb disks in raid 1, as a Host consider xp64, cheap, stable.

[scottgus1]

set it up as a stripe to improve performance

If you set up your machine with stripe raid (RAID0) you'll lose redundancy for the sake of performance. One drive in the array goes, the whole thing is sold down the river. RAID 5 gives striping wth some redundancy, but the intense disk activity to rebuild the array after one disk goes can kill another disk in the process. Again, sold down the river.

RAID 10 might get better performance with redundancy, by combining stripe and mirror raid styles, but I don't know for sure. Maybe try some tests with a basic install before setting up the complete installation.

[Bullitt]

First of all, thanks for all the input.


Can anyone elaborate to me how the CPU/core assignment work? I can see that if you assign 4GB to a VM, it "reserves" it from the system, but apartment running 3 VMs with 2 cores each on a quad core host cpu seem to work just fine....

[vbox4me2]

running 3 VMs with 2 cores each on a quad core host cpu seem to work just fine....

You will get problems here, 3x2=6, with only 4 cores and a Host that needs power too this is waiting to go south. Always leave 1 core for the Host.

[Bullitt]

so there is no kind of load-balancing? One of the machines might just be idle, while another is being used.

[Martin]

running 3 VMs with 2 cores each on a quad core host cpu seem to work just fine....

You will get problems here, 3x2=6, with only 4 cores and a Host that needs power too this is waiting to go south. Always leave 1 core for the Host.

Multiple 2 vCPU guests on a 4 core host is no problem. The scheduling will take care to only run one guest at the same time with the host tasks.
You just don't want to run a 4 vCPU guest on a 4 core host because here the scheduler will need four free cores at the same time to be able to give the guest some CPU time.