Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Virtualize or not ? Expand / Collapse
Author
Message
Posted Thursday, December 23, 2010 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 6,841, Visits: 13,361
rmechaber (12/23/2010)
...it honestly sounds like you'd likely get more performance bang for your buck by doing that than you will by virtualizing. ...

Just to make sure there is no misinterpretation: there will be no such thing like performance improvement due to virtualization itself.
As long as you use the same hardware there won't be any benefit performance wise. You might benefit from reducing the number of servers required. But at the best you'll get the same performance. It's a different story when bigger hardware is used to run multiple virtual machines on it. In such a case you benefit from the hardware, not from virtualization. Take away the virtual machine and run your app on the bigger hardware and you'll see the same improvement.

Other than that I second Rich's comment. Especially the part using experienced VMWare consultants for the setup. That's definitely a key factor for success.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1038851
Posted Thursday, December 23, 2010 9:49 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 705, Visits: 3,019
Just to make sure there is no misinterpretation: there will be no such thing like performance improvement due to virtualization itself.

I should have been clearer. I didn't quite catch the idea that you were planning to take existing hardware, use it for a virtual server, and then put SQL (and other servers?) on top of that.

Lutz is quite correct, your performance will likely suffer. How can you expect it to improve or even remain the same if you are running additional software on the same hardware? The HyperV or VMWare software incurs overhead. Hardware abstraction is not free.

One thing our consultant was clear with us about was to buy and build sufficient resources (ESX servers, RAM and SAN) to be larger than our typical load requirements would suggest. That way we can take down one of the ESX servers and all of our host servers will first migrate seamlessly to the other ESX servers. Users don't even know it happened. It's remarkable.

I would be very wary of putting an 800-pound gorilla app. such as you described onto a virtual setup, especially a poorly behaved gorilla. I'm a little confused. Your SQL server you describe as "our SQL Server which is quite powerful, more than needed actually." But you describe the primary SQL application as "Needless to say, the whole thing looks like a bad joke and is slow as hell." If throwing more hardware at your current application hasn't enabled it to function properly, it won't get any better as a virtual server. This is a classic example of an application in need of re-coding.

We've been very successful with ESX, and it has exceeded our expectations. We have linux, Windows, and Netware servers all running side by side. We deploy test servers from a template in 15 minutes, run them for as long as we need, then simply delete them. But we don't put big, resource-hungry things like email on it.

Rich
Post #1038868
Posted Thursday, December 23, 2010 9:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:59 PM
Points: 7,105, Visits: 15,445
LutzM (12/22/2010)
Gagne (12/22/2010)
...
Lutz, that sounds like you're saying that generally speaking, virtualizing a SQL Server will impact performance and that it's all a question of whether it will slow it down enough to be noticeable or not. Did I interpret this correctly ?
...
On a 30GB database I had a 15GB transaction log and it wouldn't go a day without expanding when I was backing it up every 12 hours. I think that falls well into the high I/O category

Virtualization basically means to share hardware resources, or to allow other "processes" (= virtual machines) utilize free resources on a given machine.
There are basically two scenarios:
1) your (formerly dedicated SQL) server will be used as a host for virtual machines. Then you'd have to share resources. Example: As long as your current system never exceeds 90% CPU utilization and you don't have anI/O issue at all, you'd hardly recognize a VM utilizing 5% of the CPU with low I/O impact. It won't impact the SQL Server performance in general.
2) Your server will be moved to a (more powerful) hardware, but will run in a virtual machine. Assuming you'd have to share the hardware with other systems consuming much less CPU and I/O and your system had hardware driven performance issues before, you could even benefit from going virtual.

It all depends. Therefore, there's no "generally speaking". Unfortunately.

Regarding your transaction log: you might want to perform a transaction log more frequently (the setting on our production system is 15 minutes...). But that's a different story. More or less (because of the file size issue).


Here's another aspect to consider: just because you viritualize you server doesn't necessarily mean you HAVE to share the underlying hardware. We've virtualized a LOT of our servers, simply because it becomes much easier to swap out the "underlying plumbing" without huge conversion routines/server moves/etc....

I am not sure of all of the magic our intrastructure guys pull to make this happen, but they've done a really good job of just that. A week ago, the hardware our big primary DB ran on started showing signs that some of the hardware was going, so they "moved" it from one physical box to another while the server was up. Sp the virtual instance stayed up even after the physical hardware was taken out.

So the question might turn into - how do they plan on making the virtualization work? can they make sure you odn't lose the performance edge you need, etc....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1038874
Posted Thursday, December 23, 2010 11:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 2, 2014 11:42 AM
Points: 99, Visits: 343
I think I haven't been clear or maybe my original post was too long and this got lost in it.

We are not looking into this because we want to virtualize our server. We need a second hyper-v server and with the economy being what it has been for 2 years we'd rather not spend 8000$ on it........at least not right now.

Since our actual physical SQL Server is quite powerful, it can be turned into a Hyper-V server but if we do that I need to move that SQL server somewhere and I'd rather no run it alongside anything else which is why we're looking into virtualizing it.

I'm not really worried about CPU and memory, I am really much more concerned about physical disk I/O and whether I should virtualize the disks or give the VM direct access to the disks (physical i/o path).

One thing I feel needs to be closely looked at is the possibility of a power failure. If, for any reason, our UPS doesn't do its job and doesn't properly shutdown the server there's a risk of damaging the transaction log and end up being unable to restart the database which would for me to restore the most current backup.

Post #1038902
Posted Thursday, December 23, 2010 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 6,841, Visits: 13,361
Ok, so at least you have a budgetary number to compete against...
Get a quote from your vendor how much it would cost to replace the "heaviest" c.u.r.s.o.r.s.
Once that's done, your I/O wont suffer that much anymore and you might be able to use the hardware as a VM host.

On the other side you should really consider a backup strategy for your server. So, a second system used as a VM host but being able to be used as a backup system for your app might be the way to go.
A UPS will protect you from a power failure. But how about hardware defects?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1038915
Posted Thursday, December 23, 2010 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,731, Visits: 8,483
Brent Ozar did a very good job highlighting the goals:

http://www.theinfoboom.com/articles/virtualizing-databases-too-big-to-fail/?utm_source=dlvr.it&utm_medium=twitter&utm_campaign=0810#

First make sure, your goal matches that of the software users managers.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1038938
Posted Saturday, December 25, 2010 8:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 2, 2014 11:42 AM
Points: 99, Visits: 343
I've been doing my homework the last 2 days and got some information over a 48 hours period from Perfmon. Some numbers are encouraging while others raise some concerns.

Available MBytes: 8934, Our server has 32 Gigs and we set SQL Server to use a maximum of 20. Since I always have over 8 Gigs available I can only assume that my SQL Server uses all the 20 Gigs I assigned to it, which is normal, and that other apps and the OS use about 4 Gigs which means I could give more memory to SQL.

Pagefiles % Usage. Now that is interesting because for some reason our tech people created 2 pagefiles on 2 separate partitions that are on the same drive. The interesting thing is that one of them shows an average usage of 4.46% while the other is as 0.25%. How can the pagefiles be used while I have almost 9 GB of RAM available ? Should I conclude from this that the 20 Gigs I gave SQL Server isn't enough and that it has to use the pagefiles ?

Cache hit ratio: 99.9%, looks good there.

As for I/O's, I don't know yet how to interpret this, I'm gonna have to learn about that in the next few days but the numbers are very low so I think it's good.

On the database disk.
Avg Disk Sec/Read: 0.0014
Avg Disk Sec/Write: 0.0050
Disk reads/Sec: 7.49
Disk writes/Sec: 4.51

On the logs disk
Avg Disk Sec/Read: 0.0010
Avg Disk Sec/Write: 0.0044
Disk reads/Sec: 0.9
Disk writes/Sec: 6.8

% processor time.
799.35% for 8 CPU's. So it looks like my CPU's never go idle and at first I thought I had a big bottleneck here but my average processor queue length is at 0.04 with a max of 13. I'm not sure I interpret this correctly but it seems to me that hardly anything ever waits for the CPU although it used 100% of the time. I'll have to run a check on this but I think the antivirus on the server is responsible for that, it probably uses the CPU all the time but releasing it to other processes when required.

If I'm right on the processor time and if I can reduce the amount of pagefile usage by increasing memory available to SQL it looks to me like I wouldn't have any problems running this in a virtual machine.
Post #1039203
Posted Monday, March 28, 2011 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 28, 2011 5:11 AM
Points: 2, Visits: 3
I am also thinking of adopting the virtualization technology. Thanks for sharing knowledge. I am new to it and want to know more about it. Thanks

Microsoft Office Visio
Post #1084840
Posted Thursday, June 16, 2011 1:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:30 AM
Points: 137, Visits: 648
I put a form of this answer here, but I can add a bit here as well. As much as I hate to say it, this is a giant "it depends" answer. If your current SQL Server box is a beefy 128GB memory, 4x12 core proc box that is constantly running at 80% utilization it's probably not going to be the greatest candidate for virtualization. However if you have departmental SQL Server databases/instances that are sitting on 4GB machines running a dual-core processor and 99% idle, I'd say that's a good candidate for consolidation and/or virtualization.

Notice I threw consolidation on there? That's definitely another option if you want to still make use of some of your hardware without needing to virtualize. Also there's licensing concerns. If you consolidate you pay for one OS license as opposed to new one for each VM you spin up. There's lots of variables at play, just need to find what works best for you.


=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Post #1126907
Posted Wednesday, December 28, 2011 3:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 10:52 PM
Points: 27, Visits: 53
you are probably looking to completely redesign your system and I think you should hire an expert for all your queries because you got lot of things to deal with and a minor mistake may affect your system very badly. you need a experienced MCSE AND MCSA Certified person along with any of the technical support team from vmware or hyper-v.

resume writing | examples of resumes
Post #1227259
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse