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 Tuesday, December 21, 2010 6:58 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:07 PM
Points: 102, Visits: 347
Hi all,

I apologize in advance for this is gonna be a long post but I'd rather give too much info than not enough.

Our tech departments wants to reduce our hardware and re-use some for other tasks. One of the machines they'd like to take is our SQL Server which is quite powerful, more than needed actually.

So I have to decide between sticking to that physical machine or virtualizing our SQL Server and frankly, I don't know if I like the idea or not. I'm the first one to advocate virtualization when it comes to terminal servers or application servers but I don't know if it's such a good idea with servers doing lots of disk I/O like SQL Server.

This is about SQL 2005 Standard Edition SP3 currently running on Windows 2003, both 64 bit.

The current machine is a SuperMicro server with two quad-core CPU's, 32 Gigs of RAM and a RAID controller. We have a 450 GB SAS Drive where we keep the databases and a 1 TB WD Caviar Black Sata II drive for the Transaction Logs. Both drives are mirrored.

We use Red Gate's SQL Backup with two full backups a day, one differential backup at noon and transaction log backups every 2 hours. These backups are stored on the 1 TB Drive (same as our T-Logs) and also copied to a another drive with Red Gate's network copy feature.

We run all four SQL Services (SSDS, SSIS, SSRS, SSAS) on that machine and use them all.

SSDS: We have 30 databases ranging from a few hundred KB's to over 28 GB's. The 28 GB one supports our MRP systems and all the operations associated with running our company.

SSIS/SSAS: We really don't use it much, I have just a couple of packages which run twice a day to generate/update an OLAP cube that is accessed by about 15 people a few times a day each.

SSRS: We don't have many reports on the server but they are linked to our live databases and they are critical to our normal operations. They give our people the information they need to do their job when they need it. I'd say it's running reports around a 100 times a day.

Now back to our databases. Our MRP system comes from a 3rd party, it's programmed in C# .NET and relies heavily on stored procedures, user functions and database triggers. The problem is that it is all very badly coded and uses cursors like you wouldn't believe. It has cursors in SP's calling SP's that have cursors calling SP's that also have cursors. It has SP's with cursors calling themselves inside the cursor loops with different parameters. It has nested cursors and even cursors and nested cursors in triggers . Needless to say, the whole thing looks like a bad joke and is slow as hell.

And to add to that, the .NET app works with huge datasets which are populated by these "cursored" SP's.

And to add even more, most of it (if not all of it) runs inside transactions. Yes.......in case you're wondering, we do face problems of transactions running for minutes and locking up the entire tables and kicking people out. Some of our users do wonder why they are chosen as victims of something so often . I guess that gives a good idea of what can be going on in the transaction logs.

I think that pretty much covers our environment so back to virtualization now, here's what's been proposed to me by our tech department. They want to convert this powerful machine into a Hyper-V server. I'll spare you the details on how they wish to proceed but down the lines it comes to converting the current physical machine into a virtual machine on a Hyper-V server hosted on Windows 2008 R2 and then upgrade the Windows 2003 OS of the virtual machine to Windows 2008 R2.

Here are the things that I'm concerned about.

Memory speed: A 30 GB database running cursors and generating datasets all day long probably uses memory a lot. Will a virtual SQL Server experience slow memory or is it gonna be as fast as when it runs on the physical machine ?

Transaction logs: That bothers me a lot actually. Is it conceivable to run a 30 GB database with a 20 GB transaction log on virtual disk when the amount of I/O that all these transactions and cursors are generating ?

Is it a really bad idea to virtualize this ? Are there any other things I should consider ?

And we do virtualize it, is converting the physical machine to virtual and upgrading the OS a good idea ? Are we gonna run into all kinds of problems ? Should we create a virtual machine from scratch and re-install all the SQL services and databases ?

I'm one of those people who ended in charge because there was nobody else and although I think I'm doing a good job with our server, this kind of stuff really is beyond me level of knowledge and I really don't have the expertise to make that kind of decision......but I have to make it anyway so I will be very grateful for any help I can get on this.
Post #1037690
Posted Wednesday, December 22, 2010 2:19 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:07 PM
Points: 102, Visits: 347
Should I take the lack of answers as an indication that virtualizing SQL Server isn't popular and may not be a good idea ?
Post #1038501
Posted Wednesday, December 22, 2010 2:39 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:07 PM
Points: 2,049, Visits: 3,592
It's not that virtualizing is a bad idea or that people on the forum don't support it. :) The post is indeed long and the questions are probably something that should be discussed in greater detail, maybe as a project with a consultant.

Ultimately the determination to move to virtual is something that should be tested. I'm sure that you can find plenty of success stories as well as failures. The key is understanding what your environment requirements are and can you meet them with a virtual server.

A couple of questions I would have are;

1. Are you looking to do a like for like as far as the resources on the Virtual server?
2. How is your IO performance presently? What are your read and write performance counters showing?
3. How is your memory utilization presently? Is you Page Life Expectancy solid?
4. Do you have people experienced using virtualization so that if you run into performance issues you are going to have support for troubleshooting, tuning, etc?
5. Is is possible to set up a side-by-side install to production and run a test copy of the application against it to see how it performs?

Really, based on what you are describing it doesn't sound like your environment should be a challenge to virtualization. My main concern would be at the disk / IO layer. However, the move should not be taken lightly and the cost savings are not always as dramatic as people make them out to be.

I'm not sure if that helps or not. I will say in closing that I have kicked against it on a couple of occasions as the performance was causing problems but I have also now seen those issues worked through and a very solid SQL Server environment put up that runs very well.

If you have specific questions I can try to answer with experience that I have but it is not deep for sure.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1038515
Posted Wednesday, December 22, 2010 3:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
I, personally, find Brent Ozar's blog very informative ,especially including the comments and the follow-up article. Those two articles combined give a neutral (if possible at all) point of view why you should or shouldn't go for virtualization.

Regarding your specific situation:
If you can get some figures in terms of I/O utilization as well as tempDB and CPU usage you might be able to support the argument that you need such a heavy machine as a dedicated SQL Server. That's step 1.
Step 2 would be to describe the reason why you need a Server of such a size (the c.u.r.s.o.r. *cough* nightmare with some figures could help).
Step 3 would be to get the users involved with a statement how much of a slow-down they're willing to take.
A recommendation could be to get the vendor to speed up the app by replacing the messy code and based on that open the server for virtualization.

With transactions running for minutes, locking up the entire tables and kicking people out we'd consider immediate action as well. But the word "virtualization" is nowhere close to what we'd do... ("performance improvement" or "app replacement" would be much closer)...

If you virtualize under the current scenario you're facing the risk of even more users being kicked out and even lower application response time.




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 #1038530
Posted Wednesday, December 22, 2010 3:46 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:07 PM
Points: 102, Visits: 347
David, Lutz,

Thank you both for sharing your thoughts on this. I agree that checking out I/O performance is the first thing I should do, if that is as "bad" as I think it will be I guess I'm gonna have my answer immediately.

LutzM (12/22/2010)

If you virtualize under the current scenario you're facing the risk of even more users being kicked out and even lower application response time.


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 ?

Unfortunately, having the app recoded (the correct word should be fixed or repaired :)) or replacing it is not an option. We just need to make the best we can with it at least for now.

There's a process that we need to run mid-morning every day, it runs for about 35 minutes. This is 35 minutes every day where nobody can do anything with the system because they'll just get timed out on transactions.

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
Post #1038537
Posted Wednesday, December 22, 2010 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
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).




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 #1038549
Posted Wednesday, December 22, 2010 5:22 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:07 PM
Points: 102, Visits: 347
LutzM (12/22/2010)
[quote]Gagne (12/22/2010)
...
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).


I changed that a while ago. If I recall it was after I had 50 users ringing my phone or walking into my office to complain that nothing was working............which turned to be caused by the expansion of the transaction log. It's backed up every 2 hours now.

But back to the point and the first part of your latest post. The CPU on is actually pretty much idle all the time, processor is really not a concern but we are conscious that we'll have to be careful on how much load we put on that machine if we turn it into a hyper-v host.

Disk I/O however can be an issue. I was reading a white paper yesterday on virtualizing SQL Server and there was something about "physical I/O path". I haven't had the time yet to take a close look at that but if I understood well, it's a way of allowing access to the physical disks to a virtual machine which would obviously improve I/O performance. This is definitely something I need to consider.
Post #1038561
Posted Thursday, December 23, 2010 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:15 PM
Points: 6,842, Visits: 13,370
I was reading a white paper yesterday on virtualizing SQL Server and there was something about "physical I/O path".

Care to share the link? Others might benefit from it.




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 #1038695
Posted Thursday, December 23, 2010 6:28 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:07 PM
Points: 102, Visits: 347
Yes of course, I should have posted the link. It's about SQL 2008 but I'm sure it's good for 2005 as well.

http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx
Post #1038735
Posted Thursday, December 23, 2010 9:01 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:31 AM
Points: 717, Visits: 3,037
We run our SQL servers on VMWare and have been very happy with the performance. BUT, we are a small organization with a lightly loaded primary SQL server with a few hundred users, most of whom are either reading small amounts of data or updating selected rows.

We subcontracted most of the VMWare setup to a consultant who is very experienced in setting up SANs w/ VMWare servers, which has been essential for our success.

If your primary app. is so poorly written, is it a resource hog? Something I didn't see you mention: if you virtualize and SQL server is sharing CPU/RAM/IO with other servers, then the other servers may suffer if you aren't careful.

I like the previous suggestion to create a parallel test environment. I know you said upgrading your application is not a possibility, but it honestly sounds like you'd likely get more performance bang for your buck by doing that than you will by virtualizing. Maybe if you ran the numbers, you and management would have something meaningful to compare?

Good luck,
Rich
Post #1038832
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse