Q:

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?

A:

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. 

A:

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.

