Improving SQL Server performance on VMware under Linux

Question

I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has SQL Server 2000 on it and one has SQL Server 2005, in each case running on Win2k. Each of the SQL Servers has a couple of small-but-important databases.

As near as I can tell, the Linux server spends its life utterly bored. It has 2GB of memory, but even with the VMs it never uses more than about 512MB, with the rest being allocated to cache.

But the VM performance stinks! The databases are slower than before I moved them from dedicated (but ancient) machines. If a database hasn't been used for a while, you can go have a coffee before it starts responding again.

What can I do to improve the performance?

Answer 1

Give the virtual machines more memory (configured in VMWare). SQL Server 2005 has a minimum requirement of 512M, with 1024M recommended; it does not sound like you've given them that much.

Answer 2

Does it seem like the performace problem is CPU, disk or network bound? i.e. is the disk thrashing or the CPU maxed during a single expensive slow query, or it is it slow for remote queries even if they're small?

How are the virtual disks allocated? Have you tried defragmenting them?

How much memory is dedicated to the VMs? Are the guest Win2ks having a low memory problem?

Is there enough RAM for the SQL server to cache the entire database, and would it be practical to allocate it enough to allow it to do so? Disk access can be very slow from inside a VM.

Would it be practical to move both SQL server installs to the same VM? This would reduce resource contention between them.

Answer 3

I had a similar problem. Virtual disks kill performance. Try to get as much data into memory as possible. I eventually put the disk intensive VM's onto its own RAID stack.

Most people in OIT do not recommend putting disk intensive applications into a VM because of this reason. You also didn't specify which VMWare product you are using. ESXi was released for free not to long ago, if you are not using that you may want to try it. It may help you get that last bit of performance you need.

Answer 4

SQL Server is an IO machine period.

SQL Server uses memory as cache so the more it can store in memory the better as far as read performance goes. I would also look into a hyper visor based VM solution to get you as close as possible to the physical machine.

Segregating the vdisk on to different sets of spindles will also help.

Answer 5

You should focus on disk IO. Is you VM installation set to autogrow? If it is, this is the source of your problem. Assuming that SQL is set to grow automagically and VM will autogrow it's disk allocation, you are on the path slowville.

We have run a payroll system that serves as the backoffice for HR and produces a bi-weekly payroll for 900+ people on VMWare with no issues. To do this we pre-allocated drive space and defrag both the VM slice of and the VM Host every quarter. Keeping the disks tuned will go a long way to maintaining good performance.