Will Virtual Machines slow down T-SQL queries??

  • Hi All,

    I have this problem killing me! A job that generally takes 1 hour to complete on physical machines is taking more than 2 hours in a VM and still failing!

    Physical Machine - 4 GB RAM

    Virtual Machine - 8 GB RAM

    All other configurations are ditto in both the machines!

    So i have these doubts:

    1. Will VMs slow down a query?

    2. Will their architecture make the SQL Engine to wait for some other background processes to complete?

    3. How do i ascertain that the delay and consequent failure of job is due to the slackness of VM?

    Any pointers/help will help me out immensely in closing the request! Please ask me if you further need any info about the machine

    Cheers!

  • Heh, Mr. Coffee, virtual machines are horribly slower than physical machines.

    There's a virtualization layer between your system and the physical resources: CPU, disks and memory must be accesses through this layer, so everything is slower.

    Virtual machines make administration simpler, but tend to hit heavily on performance.

    -- Gianluca Sartori

  • Thanks Gian for a fast reply! 🙂

    My guess is right then, i was on a debate that this is not a code mistake, but rather the network/hardware limitation. Hmmm..

    I have a scenario, assume that the max memory load my server can take is 3 GB and the query hits that mark, will it wait for OS to free RAM so that the process in-progress can complete?

    Actually the cause of failure for that failed job in VM is due to "Timeout Expired" from the calling application! So i have a gut feeling that SQL Engine waits till some free RAM comes up , but while waiting the time-out occurs and the job fails..You buy this reason??

  • Gianluca Sartori (5/13/2010)


    Heh, Mr. Coffee, virtual machines are horribly slower than physical machines.

    There's a virtualization layer between your system and the physical resources: CPU, disks and memory must be accesses through this layer, so everything is slower.

    Virtual machines make administration simpler, but tend to hit heavily on performance.

    I dont have an issue with VM's, especially if there are setup correctly( to meet requirements). We have a number of production DB servers on VM clusters that perform just as good as the one's on bare metal (if not better).

    I would look at the code that is running the query, whether it be with an SP or direct SQL from an application. The other thing to look at is the disk setup. Granted, if there VM has shared storage with other VM's on that host ,"YES" it will be must slower at it is fighting for IO.

    Thought I would put my 2p's worth .

  • I often hear people saying that VMs are great for databases, but, in my experience, this is a no-go. Probably I don't know enough about VMs to configure everything properly, but I've seen big performance problems in the past.

    ColdCoffee (5/13/2010)


    You buy this reason??

    I don't know enough on your environment to buy anything...

    How are database files stored? Is it a virtual disk?

    Anyway, you should be able to see if your query is IO-intensive or CPU-intensive and focus on that.

    -- Gianluca Sartori

  • sql_lock (5/13/2010)


    Gianluca Sartori (5/13/2010)


    Heh, Mr. Coffee, virtual machines are horribly slower than physical machines.

    There's a virtualization layer between your system and the physical resources: CPU, disks and memory must be accesses through this layer, so everything is slower.

    Virtual machines make administration simpler, but tend to hit heavily on performance.

    I dont have an issue with VM's, especially if there are setup correctly( to meet requirements). We have a number of production DB servers on VM clusters that perform just as good as the one's on bare metal (if not better).

    I would look at the code that is running the query, whether it be with an SP or direct SQL from an application. The other thing to look at is the disk setup. Granted, if there VM has shared storage with other VM's on that host ,"YES" it will be must slower at it is fighting for IO.

    Thought I would put my 2p's worth .

    Thanks for the insights, sql_lock! I am not the DBA/Network Admin, just a junior developer, so i don't know much about the configurations of the machine. Pro'lly i will ask my seniors and let know the configurations!

    Thanks!

  • How are database files stored? Is it a virtual disk?

    No idea, Gian! As i said above, i am just a junior dev, so not much into hardware/network related things! I don't know if its a virtual disk., but the databases in the SSMS are restored using Quest's LiteSpeed tool..

    Anyway, you should be able to see if your query is IO-intensive or CPU-intensive and focus on that.

    Yes, i will concentrate on the IO and CPU of the query, Gian! Thanks for the pointers!

  • How is the VM setup? On what software? That could be an issue, but VMs are machines like other machines. However the thing is that your sizing cannot be directly compared to the size of a physical machine. There is some translation efforts, but the modern hypervisors in Hyper-V and VMWare minimize the translations.

    However you do wait on host resources at times, especially if there are other VMs.

    The biggest issue that has appeared in SQL Servers, from what I've heard, is that IO isn't sized correctly. Dropping your Virtual disk on a local disk and expecting the same performance doesn't necessarily work. You should have a strong IO system, which can impact how long queries take if the IO is slow.

    It's not necessarily RAM, and for you to worry that the VM isn't freeing RAM doesn't help you. You need to diagnose the pressure on your SQL Server just like it was a physical server. Find out of memory, IO, CPU are your bottleneck and work on fixing that issue.

  • Wow Jones himself replying a thread is such a gift, thanks first of all for the reply, Mr.Editor!

    Now for your questions:

    Steve Jones - Editor (5/13/2010)


    How is the VM setup? On what software?

    I don't exactly know about the configurations (i guess Hyper-V), but all i can tell you is that, the machine is set-up by the Microsoft Team itself! So i guess they would have set-up a high quality piece! Hmmm..

    Thanks for details about the RAM, Jones!

  • Ha, me answering a thread isn't that amazing. At least I hope not. 😛

    Dig into the VM and look for pressure in memory or IO and then work on that like you would on another box. Separate out IO, if you have a SAN, get dedicated HBA for the VM, etc. I have lots of friends running production SQL on VMs and it works.

    However, they do not run heavily loaded SQL boxes in VMs. And even the low/medium load SQL boxes that are in VMs have fewer VMs on the physical than other VM servers. The standard at one place is 10VMs:1 Host for most servers. For SQL Servers it's 4:1. Make sure your Windows team isn't considering SQL to be just another box. That's asking for trouble.

  • I read in MVP Deep Dives that Hyper-V seems to be good at performance, but I don't have hands-on experience.

    My latest fight with a database engine on a virtual machine dates back to 6 years ago.

    -- Gianluca Sartori

  • Gianluca, you ought to play with the newer Hyper-V or VMWare systems. The hypervisors are world's beyond 6 years ago. The last 2-3 years have seen incredible performance improvements and a relatively low penalty for being in VM.

  • Steve Jones - Editor (5/13/2010)


    Ha, me answering a thread isn't that amazing. At least I hope not. 😛

    😛

    Dig into the VM and look for pressure in memory or IO and then work on that like you would on another box. Separate out IO, if you have a SAN, get dedicated HBA for the VM, etc. I have lots of friends running production SQL on VMs and it works.

    However, they do not run heavily loaded SQL boxes in VMs. And even the low/medium load SQL boxes that are in VMs have fewer VMs on the physical than other VM servers. The standard at one place is 10VMs:1 Host for most servers. For SQL Servers it's 4:1. Make sure your Windows team isn't considering SQL to be just another box. That's asking for trouble.

    hmmm, i will keep my Senior team informed about this Steve! But one thing i missed out to tell is, the VM machine is a DEV box and not PROD box.. My another team was working on some performance testing, and that's when we hit this problem! I will look into the IO requests and CPU usage and take a final call on why the job failed and it's mitigation plans!!

    Thanks once again for your time, Steve!

  • Gianluca Sartori (5/13/2010)


    I read in MVP Deep Dives that Hyper-V seems to be good at performance, but I don't have hands-on experience.

    My latest fight with a database engine on a virtual machine dates back to 6 years ago.

    My other team did a Deep Dive Analysis and they directed some maintenance works! And after that only we got this VMs..Let me hope that the machine and it's parts are the problem-givers :w00t: and not the code...:-P

    Thanks, Luca!

  • You are welcome, good luck, and let us know if you find something.

    If it's a dev box, I'd be wary of load testing under VMs. You want to load test on close-to-production-sizes.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply