Configuring vmware for SQL Server 2005 performance

[MNTNBKBRD]

I have a dell 6850 which is configured with 4 3.0ghz hyperhtreading processors and 16 gb of ram. The ESX OS is installed on a single raid five utilizing 5 146gb 15k scsi disks. I have one guest os running apache MSSQL 2005 and ColdFUsion and the database for this server is 13gb. We ahve about 1500 users hitting this one server and it routinely gets it's ass kicked by a dell 2850 with two 3.2ghz processor's and 8 gb of ram running the same application and database with no vmware. I need to know if the hardware configuration on the 6850 is causing this. I have a dell 220s raid cage with 14 146gb disks at my disposal as well as an extra 6850 and 5 15k 146gb drives. I need to build a replacement for the server in question. I want to put two VM's on the new server each running the app and the database for failover. Will I see a performance inprovement by running each guest C: drive on a raid one volume (2 total) and then putting each database on it's own raid 5? I have seen posts by some people saying to just make one big raid five and slice it up using vmware. However, this is basically what I have and performance sucks. I have also seen posts saying disable hyperthreading if you have a read intensive application. I guess my biggest problem is that while I realize that the answers to these questions depend alot on being able to run tests I don't have any way of generating the load on the machine that it will see in production so I am hoping that someone can say definitively whether or not the LUN holding the DB should be on it's own raid array from a performance perspective. Help is greatly appreciated.

Andy

[Dave_Mishchenko]

Hi Andy, I'd lean towards a single RAID array and preferably RAID 10 given that the db seems not to be that large and total storage space may not be concern. In that way, with a single array, all your drives will contribute to the I/O for data rather than dedicating a few drives to the OS which will likely be under utilized.

A few questions about your setup.

1) What performance monitoring have you done within the VM itself? For example, how many processors show up in task manager, how much memory does the VM see and how much memory is in use? If you start performance monitor, what's the disk performance like and specifically the average disk queue length?

2) Is the VM W2k3 Enterprise?

3) Have you tried running IOmeter within the VM with SQL/apache/coldfusion shut down to see what sort of disk speed you get from the VM?

4) Do you have battery backup on the RAID controller and what is the write policy set to?

5) Can you stage your spare 6850 with Windows and a similar disk config and then run IO meter to see what sort of results you can get?

[MNTNBKBRD]

Thanks for taking the time to reply.

1) I have only been able monitor using perf monitor. I am only seeing 1 processor in windows task manager. The box itself has 4 processors which vmware VIClient sees as 8 processors and they are 3.0ghz zeons. The VM (guest OS) is using 9.83gb of page file, physical memory comes in at 14.2gb and available is 3.4gb. Kernel memory is 16gb and 14gb of that is paged. The cpu usage fluctuates between 40 and 100% and the average disk queu is on avergae about .15 and when a sql query hits it spikes up to 100%

2) Yes

3) I have not as It is a production server. I may be able to in the future. But I have to schedule an ASI. The max bytes a sec is 1884215 when the apps are running and average is 60000 bytes/sec

3) I have a dell 220s hardware raid and it doesn't have battery backup. The write policy is set to writeback and the read policy is adaptive. Cache policy is direct IO.

4) Yes I can.

I appreciate the help.

[Dave_Mishchenko]

Has the VM been configured to have more than one CPU? If not, I would consider making that change first. You may want first go to 2 CPU, check the performance and then to 4 within the VM. You would have to reboot for this change of course and you may need to manually upgrade the HAL in the VM to see the additional virtual CPUs.

1) I have only been able monitor using perf monitor.

I am only seeing 1 processor in windows task manager.

The box itself has 4 processors which vmware VIClient

sees as 8 processors and they are 3.0ghz zeons. The

VM (guest OS) is using 9.83gb of page file, physical

memory comes in at 14.2gb and available is 3.4gb.

Kernel memory is 16gb and 14gb of that is paged. The

cpu usage fluctuates between 40 and 100% and the

average disk queu is on avergae about .15 and when a

sql query hits it spikes up to 100%

[MNTNBKBRD]

Sorry man. I guess I wasn't clear...In windows itself the guest os windows sees one cpu. In VIclient there are 8 virtual cpu's snd I can allocate a max of 4 per machine. We have a vmware license for a 4 cpu physical machine. I have a tech support contract with vmware and I think I willl hit them up. Thanks for your help.

[Dave_Mishchenko]

You were clear on that. What I was trying to say was that you can configure the virtual machine to have more than one virtual CPU and thus Windows would see either 2 or 4 processors depending on how you configure the VM. You would then likely see a performance improvement from that given that the CPU is hitting 100% in the VM at times.

If you select Edit Setting for the VM, you can change the CPUs that the VM has on the hardware tab.

[MNTNBKBRD]

Yah...I get it but that has already been done from the start. I am starting to do some IO testing now on the stuff I have set up and I should be able to run to ground where the choke point is. Thanks again for the help.

Andy

[actixsupport]

The thing with SQL is that it's more about the disks than the RAM and processors (although it sure as hell loves it RAM).

The main thing with database's for performance is to split the logs and the databases. The logs are write only (except when restoring a backup) and sequential. Databases are (generally) lots of read activity.

Try this if you can, put the 220 on the 6850, then create an array big enough for you DB. Then move the DB's to the 220 and leave the logs on the OS and see how it performs.

You should see performance go up, you can bet better performance again moving the logs to their own disks but depending on your hardware you may not see a big improvment. I'm guessing the 220 you have doesn't have a split backplane?

[moneill]

Andy,

just to confirm, you are running ESX3.0.01 with the latest patches?

I have always installed my ESX OS on either a raid1 or raid10 array and that has worked for me.

I would look at adding the 220 and at least setting it up as a raid 10 array for your vmfs datastores.

Hyperthreading or not is trial and error - in my experience it works on older xeons but not

on current multi-cores.

If you're on ESX3.0.1 and you have vsmp you should look at trying 2vcpu.

I would pin the vcpus away from socket 0 and ideally each one on a different socket.

I don't know the 6850 architecture, but the above only applies if you can get both vcpus

addressing the same memory dimms.

Build your guest with separate vmdks for C: D:(database) L:(logs) T:(tempdb).

In sql2005 segregate the processor functions, one for IO one for query processing.

Mike