Optimising MSSQL 2008 server inside VMWare ESXi

I normally run my MSSQL servers on dedicated servers, but we've taken on a client who is running their SQL Server inside a VMWare ESXi 4 host, and quite frankly it's running like crap.

We have identified that it is not the database itself that is the issue. We deployed the same database onto a dedicated server with lower specs than the VMWare guest and it ran at least 5x faster.

The server is:

Dual Quad Core Xeon 2Ghz (I don't know the exact model)
24Gb RAM
4x 300Gb SAS (RAID 10)
ESXi 4

The host is:

4x vCPUs
3Gb RAM
80Gb disk space
Server 2008 Standard
SQL Server 2008 Workgroup

The other VM's on the host are very low traffic. A 2nd DC (almost unused), a low-traffic web server and a low-volume terminal server (~5 users at any given time) and a few other misc guests.

I read an article a long time ago about setting a whole bunch of Paging settings in Windows and the SQL server to optimise it, but I can't find it any more :(

Are there any tips or tricks anyone can offer to increase the SQL performance?

Ok, here's some tips, in no particular performance-benefitting order;

• Make sure the VMs is running on model 7 virtual hardware (it says in the VM summary)
• Make sure all VM disks are fat not thin
• Upgrade to version 4 update 1
• Make sure all VMs have the latest vmtools installed
• For W2K8 VMs use the 'LSI SAS' disk controller and vmxnet3 NICs
• Make sure you have swapping switched on in the OS
• Disable screen-savers
• Set the video frame buffer to 4MB or less
• Remove any unnecessary virtual hardware from all VMs such as floppies, serial, parallel etc.
• As Zypher says ESX won't give a VM any vCPU time until all of it's allocated vCPUs are available - try reducing the number from 4 to 3 or 2 (don't be afraid to give a VM 3, 5 or 7 vCPUs, it feels odd but works just fine), also look at your other VMs, reduce their vCPUs if they don't need them - this machine doesn't have that many cores really
• Ensure hardware virtualisation is enabled in the BIOS and all power management options are tuned for performance
• Look at your VM's 'shares', consider increasing and/or lowering your various share values/priorities based on their importance and current behaviour - also seriously consider your reservation options - these options can make a huge difference in a contended box.
• Then obviously look at the SQL VM's performance data, particularly available memory, %RDY and disk queue lengths/wait
• Consider adding disks to the array and/or providing dedicated vmdk's/datastores/disks to this VM

I'll add more if I think of any ok, best of luck.

Go get the vSphere remote CLI (go to drivers and tools -> automation tools and SDKs)

After you have that setup log into the box and use esx top to see where your bottleneck is (informative linky)

Since you are running 4 vCPU's pay attention to the %RDY column of the processor screen in esxtop. this is how long the machine is waiting to gain access to 4 cpu's at once. Depending on which VMWare SE you talk to anything above 2-5 is BAD.

Also you can look at the graphs under "performance" tab of the VIC to see what is going on, look especially closely at the memory graphs, watch for a high memory balloon.