Sql-server – Optimising MSSQL 2008 server inside VMWare ESXi

sql servervirtualizationvmware-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?

Best Answer

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.

Related Topic