Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Virtualize or not ?


Virtualize or not ?

Author
Message
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
Rich Mechaber
Rich Mechaber
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1087 Visits: 3661
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
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18084
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 :-D

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?
Gagne
Gagne
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 361
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.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6976 Visits: 8839
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 w00t

- 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" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Gagne
Gagne
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 361
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.
ellabell24
ellabell24
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
Sqlchicken
Sqlchicken
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 669
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
frankcastle509
frankcastle509
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search