June 15, 2008 at 9:04 am
Our new company strategy is to move from multiple physical machines onto a set of VMWare virtual machines. This makes perfect sense for us in terms of our application servers which tend not to be fully loaded. However, I do have concerns about moving our main SQL Server machine.
There seems to be a few posts on the internet saying don't do it, and some quote a 10% drop in performance. Others have said that SQL Server doesn't always come up with correct query plan as it does really know what the underlying cpu/memory is.
Has anyone had any real life experience? and are the claims about the drop in performance true? If so, should we just add 10% more resources to the server?
Thanks in advance
David
June 15, 2008 at 5:15 pm
Virtual Machine is software that looks like hardware. Do you think software will ever be as performant as hardware? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 16, 2008 at 2:19 am
Lots of businesses are consolidating to virtual hosts, to save power and management overheads. VM is really impressive, it's been around a while, and I comfortably run SQL2K in a virtual PC on my own desktop machine. But that is for my db development and for SEM across the WAN - any VM overhead accessing remote servers is lost in the WAN overhead.
I am very hesitant about using VM for Production, when data access and transfer have got to be optimal. I've no technical justification, it just seems like a bad idea to add another layer of complexity and contention.
We use VM for terminal servers, and at a new site we're about to use ESX to host virtual servers for file storage. But I'm getting a physical box for SQL Server.
June 16, 2008 at 2:52 am
Last year at the SQLPass2007 conference there has been a pannel discussion regarding os virtualistion.
The conclusion in rough lines was:
- don't virtualise our db servers in a production environment
- virtualise only development instances(sql2005) or QA instances that have no performance role.
Another downside of virtualisation is cpu licensing:
If you license an instance on a physical server, you license per cpu socket.
With virtual servers, you license per available cpu (i.e. cpu-core !)
If you plan on virtualisation for high availability, there are other options:
- clustering
- db mirroring
- replication options
AFAIK a virtual machine can only use 4Gb RAM, that too may cause issues.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 16, 2008 at 5:56 am
Make sure you look closely at the support offered for VMWare and SQL Server. It is only partially supported and with particulare versions of VMWare.
I have had good and bad experiences with it.
It will certainly be a headache if you do have any problems that may or may not be hardware (or virtual hardware) related as it becomes another layer of things to troubleshoot and MS is not above pointing the finger at the virtualization layer right away.
June 16, 2008 at 10:42 am
I used and it seemed not to have too many issues. I setup a clustered environment to do some testing and it seemed to run ok without any problems.
However, from all the articles i read they recommend not using virtualized servers with SQL in production since performance issue arised in multiple tests. The performance hit grew as they tested with larger databases.
June 17, 2008 at 8:40 am
My 2 cents is that if you have 5 SQL Servers, each ticking over at 10% utilisation, then they are a good case for virtualising. You won't hit any problems, unless your SQL Servers are doing something very unusual (eg. atypical usage of DTC and distributed queries).
Note this is only advised if the SQL Servers are ticking over, handling the odd query. If you have an SQL Server which is being hammered, say above 60% utilisation, then don't bother virtualising it. You will lose performance and gain nothing.
An alternative is to consolidate multiple SQL Instances onto the same, non-virtualised hardware. Does not help with redundancy, but does reduce the quantity of physical machines.
Andy
June 17, 2008 at 8:46 am
What? No growth expected on those 5 servers?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2008 at 8:48 am
I like to plan for databases shrinking.
June 17, 2008 at 9:00 am
Heh... yeaup... I like to plan for it, too... but the users never let it happen. They always want everything available even 10 years back... they never summarize... they never want to see "offline storeage". It's been a real pain for me and the DBA's.
We also been trying to retire a program and the related server that isn't even used anymore... management won't let it happen because they "might need it someday". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2008 at 9:05 am
I can beat that: a system with an OS no-one understands any more, with a failed System Disk, using a LAN protocol we don't support any more... and they still won't switch it off (yes, we migrated the data)
June 17, 2008 at 9:12 am
In my environment, although we have a SAN, our net admins have decided to use local storage for the ESX VM's we have. I think they're just a set of mirrored disks. I constantly remind them how important disk config is for DB performance, but they dismiss it. I feel like Rodney Dangerfield.
We have a 30gb database that had a test instance on VM. Ultimately the "test" instance weasled its way into production. Now the app runs poorly, has frequent hangs or timeout errors. They say it's just a poorly written app (which it is), but the main problem I believe, is Disk IO. I'm not even sure if perfmon returns anything meaningful in a VM environment to demonstrate the bottleneck.
The other problem we have is that our admins have stacked too many VM's on a host machine. Stack 'em till it hurts boys.... and it does. We have other systems that have taken a step backward performance-wise when they were moved to VM.
As others have said, I think SQL on VM is OK with smaller systems, but not for larger systems that need performance. Also, if you're not the VM admin, then you may lose control or awareness of some key factors affecting db performance.
June 17, 2008 at 9:25 am
Some things do not seem to report all that well, but wait stats report correctly. You may have an argument if you show your team wait stats that are way outside of normal bounds.
This seems to happen pretty easily on virtualized systems because you end up having to wait on disk access if your are disk, memory, or processor bound because of the virtualization layer.
June 17, 2008 at 9:39 am
What? No growth expected on those 5 servers?
With VMware VirtualInfrastructure, you can add more CPUs and more RAM to a VM, as and when needed. With some provisos, this may not even require a reboot of the VM.
Anyway, I barely have the budget to manage my Servers for today's needs, never mind in 3 years time :w00t:
June 17, 2008 at 4:01 pm
The only clear cut case that I can think of to support 'virtualization' in production. The case has a number of prerequisites:
-small to medium databases - usually less than 50 Gb
-a small number of users per database - usually less than 20-30
-the applications behind the databases are NOT mission critical
-the applications behind the database are NOT 24x7
-there are no more than 30-40 databases per virtual instance
-there are no more than 500-600 users per virtual instance
... and so ends my 2 cents ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply