x

Cost-Benefit Analysis for SQL Server Storage upgrade

I'm currently working in an environment that has issues with its storage provision, but lacks the management desire to actually upgrade / replace the storage infrastructure.

Part of the problem is that there generally isn't a perceived performance issue with the storage (but I know it should be quicker), it's that the storage provision itself is becoming increasingly fragmented and thinly provisioned.

Can anyone point me in the direction of a cost-benefit analysis for this sort of thing? Or just give me some potential benefits (besides freeing up an amount of time from the DBAs & storage guys for trouble-shooting) and other considerations so I can write my own?

I know we should be looking at upgrading to SQL2008, but for various reasons that's not an option right now.
more ▼

asked Aug 31, 2010 at 03:11 AM in Default

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

What have I done? I have summoned Mr Ozar himself! Brace yerselves, lads!
Aug 31, 2010 at 05:23 AM ThomasRushton ♦
Oh thats a good thing Thomas. +1 for the question, simply for getting Brent over here. :D
Aug 31, 2010 at 05:36 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

I'm going to be blunt here - at first glance, it sounds like there's not really a business problem. You have to define business problems that need to be solved with spending money on a new SAN. New SANs are staggeringly expensive, and when you walk into your boss's office, he's going to be looking at that SAN in relation to your salary.

Here's the problems you mentioned:

"There generally isn't a perceived performance issue with the storage (but I know it should be quicker)" - Why should it be quicker? How quick is quick enough? You can always buy faster storage, so why is the new one going to be better? Are you trying to meet some imaginary metrics set by best practices? The real right answer is, "When the business users are satisfied with the speed, then it's fast enough."

"the storage provision itself is becoming increasingly fragmented" - How are you measuring that? What's the impact to you? Today's SANs are constantly fragmented with the way their provisioning, snapshots, and expansion work.

"and thinly provisioned." - If you're in danger of running out of space in the next X months, that's a business metric to take to your management. Show them the percent that you're using today, show your rate of growth, and show when you project to run out of space. You can get these numbers from your backup software by trending the amount of backed up data averaged by day.

The guys holding the purse strings get requests for money every day. Would you like a raise? Would you like a better desktop computer? Would the vendors like to get paid faster? Would management like to acquire a competitor? There's all kinds of things they need money for, and you need to start explaining real business problems that the additional SAN space would solve. Otherwise, this question sounds like, "Waaaah, I want a new toy!" (I'd rather you hear this from me than your manager.)
more ▼

answered Aug 31, 2010 at 05:29 AM

Brent Ozar gravatar image

Brent Ozar
86

Brent, very valid points (+1) especially with the viewpoint of management. Thomas really needs to have compelling reasons for an upgrade other than "it could be better". The capacity problems will need to be backed up with solid data like you say, something that thomas will need to look into.
Aug 31, 2010 at 05:38 AM WilliamD

Hi Brent

Thanks for swinging by and for the comments. Not as ugly as I was expecting!

Fragmented / thinly-provisioned. The point I was trying to get across (but lacked the appropriate vocabulary) was that we are running out of space.

It's definitely a matter of getting the right data together in order to display the problem, the scale of the problem, and the likely costs. Fortunately, a long & detailed chat with The Storage Guy here has answered a few of my questions. He's also fairly new here.

I'm sure I'll be spending several more chunks of time with him to go through in more detail and try to put together the appropriate business case.

I'll let you all know how we get on.
Aug 31, 2010 at 06:06 AM ThomasRushton ♦
@Thomas. Get the backup details for as far back as you can and then crack open Excel to forecast what the storage requirements will be in 3m, 6m, 1y ... Also highlight when that figure is bigger than your current capacity. Start recording you db sizes, every Sunday night say, and use that for forecasting once you have a good amount of data points.
Aug 31, 2010 at 06:48 AM Fatherjack ♦♦

@Fatherjack

I'm logging what I can. Unfortunately, the old maintenance plans were set up to delete historical information after 2 days. Grr. I'm gradually replacing the MPs and have (I think) managed to remove all the "delete backup history" jobs. I like backup history! The Storage Guy has some more useful stats that I'm building into my recommendations.
Aug 31, 2010 at 06:53 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

You need to sort out what the problem is and then fix it, if its the storage then so be it. Brent Ozar has some great content on his site for benchmarking systems and showing where the bottlenecks might be.

Other than that is a load of questions for you to consider and hopefully the answers will form your submission:
What makes you think the storage is the problem?
Is it a capacity issue rather than sheer IOPS performance?
What is the poor performance costing - honestly - in actual £££'s per day/week/year?
What would a change cost and what benefit would you see? This is a tricky one, stating what the benefits will be ahead of time, especially if you cant demonstrate the cost caused by not changing.

What are the problems you are currently having to handle?
more ▼

answered Aug 31, 2010 at 03:35 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

It really is a capacity issue... The main problem is storage filling up, resulting in SQL Server not responding, and service degradation.

The basic performance is fine - it's just we need to invest heavily in additional capacity, and there's much resistance to that. The storage guy is screaming for it, we (the DBAs) are after it, and our team lead is also on board. I just need to persuade the guys holding the purse-strings!
Aug 31, 2010 at 04:03 AM ThomasRushton ♦
Just saw your answer to Fatherjack after posting. Work out the £ per GB and then show them (purse-string holders) SQL Server 2008 data compression, not only a £ per GB saving, but a HUGE performance improvement too. Addendum - make that 2008 R2 (it is shinier!)
Aug 31, 2010 at 04:16 AM WilliamD
OK. A Capacity issue. Why the reticence in expansion then? I would look to highlight the risks of not doing it as well as the benefits of implementing an expansion project. Investing in the long-term, buying a bigger SAN that will cope with 3 - 5 years of growth (or more may be?) will mean the budget holders can rest assured there wont be any surprises from that direction. What systems does it support? Get the buy-in from a variety of system managers that their data needs a more stable core too.
Aug 31, 2010 at 05:04 AM Fatherjack ♦♦

To add to Fatherjack's suggestions RE Expansion projects. Don't forget you can offer up the possibility of half-stocked shelving to take advantage of disk capacity increases over a 3-5 year lifetime. So you have the option of sticking in a half shelf of new disks in 18 months which are now 60% larger than the current models.

A sensible solution is also to consider your SAN mapping strategy. How much of the SAN is taken up by dorment data or data that doesn't need to be accessed fast/frequently? Is it stored on tier 1 15k disks in the SAN, or in a tier 2/3 disks. Would it be possible to consolidate this data onto a shelf of slow spinning fat SATA disks, freeing up the current faster tier?

A method that seems to work well is putting together three options for this type of project: cheap, moderate and gold-plated. You make your proposal so that the moderate cost one is the sensible choice (also the one you want!). The normal management type will see the cost/benefit and make a simple choice on that.
Aug 31, 2010 at 05:23 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

You say you have storage admins, which is a good thing! They should have some sort of cost breakdown for the SAN storage - £ per GB per year or something similar. These sort of figures should be a good starting point. From the £ per GB, you should be able to get to £ per IOP too, so you can have metrics for raw storage costs and usage costs of the SAN.

You also think the storage can go faster, you will need to prove this. SAN XXX from vendor YYY will have published figures for performance, if that is not available, you can work it out through the performance figures for the hard-drives (incl. RAID performance boost or lag) and also the bottlenecks for the SAN fabric (hard drive interface, HBA speed etc.).

You then run SLQIO on your LUNS with the behaviour you expect to have on the different LUNs (Log file sequential writes/reads or Data file random). This will quickly (inside 30 minutes) give you some clear idea of your storage capabilities.

It may be difficult getting reliable readings if the SAN guys have done thin-provisioning on your SQL LUNs (they love doing this awful, awful thing!).

Be careful when hitting the SAN with SQLIO though, it can knock some of them for six - they will have a good burst at the start because they will most likely use the cache. If you let SQLIO run long enough to overrun cache, you can see your SAN drop off a cliff performance wise.

Take a look at the Brent Ozar article on [SAN Best Practices][1], it is brilliant (especially the video tutorial for SQLIO).

Getting any changes made in a SAN environment will be difficult, I had to kick and scream at my last job to get any sort of head-way. Good luck to you! [1]: http://www.brentozar.com/sql/sql-server-san-best-practices/
more ▼

answered Aug 31, 2010 at 04:14 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

Based on what you all advised, but taking particular heed of @Brent Ozar's advice, I slightly adjusted the focus of my monitoring and reporting, and wrapped everything up in a handy report for the manager's manager's manager a month later.

The report included:

  • information on the capacity of the storage environment,
  • the rates of growth of the databases
  • information from the fn_virtualfilestats function traced over time

as well as more general information about the server infrastructure (numbers of SQL2000 & 2005 servers, for example) and lists of databases that appeared to be no longer required.

I presented this as the final product of my contract, and made recommendations for the storage environment to be beefed up, as well as various consolidation / migration options. Boss^3 seemed to take it all on board, and didn't react badly.

I left, and went off to my next contract.

I had a phone call about four months later asking if I would be able to go back and do some work "now that we've got the new budget in place and the storage issues largely addressed", which is nice. Unfortunately, I wasn't able to take them up on the offer.

Anyway, just thought you might like to know how it all went...
more ▼

answered Nov 22, 2011 at 04:30 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Given that I know your expertise, you've probably thought of this, but remember to make sure storage is really your bottleneck before you spend money for faster storage. Faster drives won't help much if the problem is that your network is too clogged up or that other VMs/applications running on the server are eating all the RAM. And, to paraphrase Jeff Moden, when you have performance issues, check the code first.
more ▼

answered Nov 22, 2011 at 10:07 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

Converted to answer, and +1 because it's a good point.
Nov 22, 2011 at 10:15 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x69
x30
x12
x1

asked: Aug 31, 2010 at 03:11 AM

Seen: 2775 times

Last Updated: Aug 31, 2010 at 03:11 AM