Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 18 '11 at 11:41

This question came from our site for professional and enthusiast programmers.

    
Can you elaborate a little more on your setup. How much RAM is assigned to the VMs, how many CPUs on host (how many assigned), VMs running of 1 hard disk (same hard disk as host?). What is task manager in the VMs saying? Busy? Or is memory at 100% in VMs? –  AlexDuggleby Sep 16 '08 at 19:03
add comment

5 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

If a database hasn't been used for a while, you can go have a coffee before it starts responding again.

Could it be that you have set the DB option Auto Close set to 'ON'?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.