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

  • 2
    Where are you bottlenecking-- IO, CPU, RAM? If you haven't, throw Perfmon at it and see what the bottleneck looks like. You should be getting pretty decent IOPS out of those disks in that configuration. I'm assuming you've got one datastore, so all your IO from all the guests is smearing into a mass of random IO. It's Windows Server 2008, so your partitions will be aligned in the virtual disks. Hopefully your VMFS datastore partition is aligned. Hmm... I'm not a VMware expert, so tht about taps me out for ideas. – Evan Anderson Jan 7 '10 at 2:52
  • Thats ok, better than where I'm at at the moment. I'm running some traces in perfmon at the moment and will analyse them later this afternoon. – Mark Henderson Jan 7 '10 at 2:57
  • Perfmon won't give you reliable information inside a VM. – Zypher Jan 7 '10 at 2:59
  • @Evan Anderson: Since VMware 3.0 U2 (I think maybe 3.5 U2 ... i know the U2 part is right) when you create a new VMFS partition is auto aligns w/ the storage :) – Zypher Jan 7 '10 at 3:11
  • Hmm. So I should ignore these traces then? – Mark Henderson Jan 7 '10 at 3:20

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.

  • For W2K8 the Paravirtualized SCSI Controller will lower CPU overhead\increase performance even more than the LSI SAS but can only be used for data volumes. If this is a P2V then consider a clean build; P2V migrated partitions may not be aligned, and legacy hardware\drivers brought across can significantly degrade performance. I've heard (but cannot verify) that for DB VM's it is a good idea to assign Virtual SCSI adapters to the VM before you add virtual networking. – Helvick Jan 7 '10 at 12:11
  • Thanks Helvic. This is a pure Virtual installation, not a P2V, but I didn't even think to check if paravirtualisation is enabled... – Mark Henderson Jan 7 '10 at 20:55

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.

  • +1 I'd be suspicious about having 4 vCPUs too in that config, but anything without measurements are just guesswork ^^ – Oskar Duveborn Jan 7 '10 at 3:20
  • Thanks. I'll give that a go and find out where it's stalling. – Mark Henderson Jan 7 '10 at 3:21
  • We dropped the vCPU's to 1 overnight, and noticed no increase in performance, so we increased it to 2 (thinking it might be a NEW bottleneck) but still no go. Must be being throttled elsewhere. Thanks for the advice though, very useful for the future. – Mark Henderson Jan 7 '10 at 21:01

Maybe the virtualization is just a sign of an already poor performing database. While database performance has always been particularly troublesome when virtualized, if it isn't very high traffic there is no reason why the DB can't perform reasonably by keeping the hot data in RAM. I would check the SQL Performance in general and see if it is using the 3GB of RAM allocated (can you give it more?), as well as checking things like indexes, fragmented database files, specifically bad queries that can benefit from being rewritten or use additional indexes, etc.

  • Hi Goyuix, I can safely say that the database is designed and indexed appropriately. It's a production database that we run on about half-a-dozen other servers for different clients and it runs great on all of them (but they're not virtualised). Thanks for the tips about utilisation though. – Mark Henderson Jan 7 '10 at 3:19

As others have said, 4vCPUs on a box with only 8 total cores is a lot. You're probably hitting a lot of processor scheduling contention. I'd drop it down to one or two and see if performance improves and go from there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.