I have migrated my SQL server into Vmware and it is a bit disappointing. Queries I ran on my old server with lowered specs ran almost twice as fast. I am wondering what I can do to help improve performance?
Host info: Dell R510. 24GB 1333 memory. Dual Intel E5649. (6 cores ea @ 2.53Ghz, 12M cache). 2 raid 5 datastores each with 4 15K rpm 600Gb drives. ESXi 4.1
VM info: 4 vCPU. 8 GB memory. 5 VMDK's on the same datastore but no other VM's are using this datastore. OS, Files, Database, TransactionLogs, TempDB have their own respective VMDK. All resources set to high & unlimited. OS is Server 2008 R2 SP1 64-bit, running SQL Server 2008 R2 SP1 64-bit.
When I watch system resources it seems that the CPU's are spiking 100% on large queries. I thought I could add some more vCPU's but I am limited at 4 with my license. Traditionally the biggest issue with SQL is disk IO but I don't seem to be spiking it out so I don't think it is disk performance related. I've thought about adding some more disks to create another datastore to seperate out the VMDK's but since the IO is not bad I don't think necessary at this point. Instead of just throwing more resources at it I was wondering if anyone else has found how to maximize SQL on ESXi.
You are writing about no other VM on *that* datastore. Would you please tell about other vm on that host?
You assigned 4 vCPU out of 12. What kind of configurations are used by the other vm on that host?
That RAID5 with just 4 spindles may not be the fastest solution. But you are using a RAID-Controller with a battery backed cache?
I've got 5 other VM's on the host. 3 of them have 1 vCPU, 2 of them have 2. So I'm using a total of 11 of my 12 vCPU.
I have yet to allocate all of my memory out between the VM's, so there is no memory sharing concerns.
I understand the raid concern, but if I'm not even using 40% of the IO during the slow performance times I don't think raid5 is my bottleneck.
I'm looking at getting 2 SSD's for raid 1+0 to squash the IO concern for SQL, I just don't know if I can justify it to my boss yet.
Ever thought about upgrading to vSphere 5?
Your RAID controller does support write back through a battery backup cache? And it is turned on?
What were the specs of the old machine that was running faster?
Ever tried to *lower* the number of vCPU? Just to try?
To answer your question about the raid controller it is a Dell Perc H700 integrated. As far as how it is setup I am unsure. The VM environment was setup before I came to work here and it has production data running on it so I've never taken it down to go into the raid menu. Unless there is a way to check that through vmware?
Yes I've thought about upgrading to vSphere 5 but haven't been able to because there were no devices that could support vCenter. My ESXi host is running off a USB drive and it does not have a cd-rom so I didn't have a way to upgrade the software. I just purchased a new server last week that I'm still setting up so I can run vCenter off it and send updates/upgrades to the host with.
I originally had the server setup with 2 vCPU and it ran much slower.
The old server that ran better was a Dell PE2850. It has 1 CPU Intel 80546K (4 cores @ 3 Ghz 1M cache) with 4 GB RAM. (2) 73 GB drives @ 10K rpm, raid 1. Server 2003 OS.
The information I found on the Web are showing that the RAID controller does have a builtin BBU. If not deliberately wrong configured, it should cache writes.
I am still a bit suspicious about NUMA performance. Just an idea, didn't really read through.
After midnight here, need some sleep. God luck.
Hello again, I found another article about that RAID controller saying the BBU is optional.
Please check your papers about the purchase and - if possible - check for battery and its health through vCenter/VI-Client (Hardware-Overview) or BIOS or SNMP.
Upgraded to vsphere 5 today. I checked the raid config and write back is enabled.
I don't think your problem is related RAID controller. It is probably a combination of number of vCPUs, allocated VM memory and thick or thin vmdk.
Actually the number of allocated vCPU is inversely proportional to the VM's performance. So the less you use the better your VMs will perform.
MS SQL servers by design gabble up as much memory as they can get leaving very little for OS and other programs. They release the gabbled up memory as other programs need it. I am not sure how efficient is the memory releasing algorithm but I have heard people complaining about the SQL server performance even with the physical servers. You can set the maximum limit for the memory usage in the SQL server though. So my recommendations are reduce the number of vCPU, set the upper limit for the SQL server's memory usage and make sure your VMDKs are thick disks, at least the one where transaction logs are stored. Keep an eye on the ballooning too.
All disks are set to thick.
SQL has 4 GB allocated to it (out of 8 given to VM)
I guess I'll reset the vcpu to 1 and run my reports and stopwatch them.
pie8ter wrote:
Actually the number of allocated vCPU is inversely proportional to the VM's performance. So the less you use the better your VMs will perform.
From what source did you come to this conclusion?
Of course there are situation, where more vCPU are not leading to more performance.
But in this case we got 12 physical cores. As long as there are no NUMA problems, more vCPU will scale as MS SQL server does use multithreading and uses multiple cores.
The only difference belonging to CPU that I am seeing are the GHz. The old physical server did use 4*3.1 GHz, the VM host serves 4*2.53 GHz.
Just to be sure we are looking on the right place, check this article: http://kb.vmware.com/kb/1008205
Check if you are having high disk latency on the datastore this VM resides (like more than 20ms sustained). After that, you will be able to start troubleshooting it better.
