asked
Hardware upgrade for better performance
We have an active/active failover cluster and plan to add more databases on these clusters but the current hardware resource will possibly need to be upgraded. How can we provide performance metrics to upgrade the hardware subsystem if the current resource is okay but adding additional databases will be a problem? How do I justify this? Please help. Thank you.
If you don't already have a baseline on performance for the current systems, that's the place to start. I'd focus on memory and disk, especially understanding how tempdb works. CPU, on most systems, is never really stressed, but memory and disk almost always will be. If you're planning on adding other systems, are those systems already in production elsewhere? Then measure them as well, again, focusing on disk and memory with a special emphasis on tempdb. Or, are they under development? Then, measure them in your dev or QA area (I usually prefer getting those measures in QA). You should be able to do this from a position of knowledge and not have to make any guesses at all.
@Grant Fritchey - wont benchmarking QA only be an indication to how it might be in live? That's why I used the phrase 'best guess' if QA and Prod are different h/w config then stress from system may be different? I'm guessing there isnt a QA cluster ...
@Fatherjack - I agree that the QA to PROD numbers are probably an extrapolated guess but I don't think the absence of a QA cluster would skew results to any degree.
@fatherjack, the cluster shouldn't matter a whit. But yes, you're still, to a degree, extrapolating, assuming that prod has 16 cpu and qa is probably a 2 cpu virtual (plucking numbers from the air). But it's still a question of performing measurements and then using those. Because you can also run production in that same environment and then simply draw straight lines between the two. It's all, at least for me, working from knowledge rather than making guesses. It's mainly that word that bothers me.
@Sqladventure. Only if you put it on another machine. But you don't really need to stress test the system, you need to measure the system you have and the apps you're bringing online. None of this is perfect, flawless, absolutely 100% correct 100% of the time. You never know when a particular set of apps might have a unique conflict on a particular resource. But you can know how much of any given resource an app uses by measuring it. That's your best bet. But, if you want to stress systems, other than prod, I'd suggest taking a look at the RML utilities. I've found the playback capabilities there to be pretty slick.
@Grant Fritchey - cool, so long as I was understanding it right. My poor choice of grammar. Have edited my answer to remove offending term. :)
@Fatherjack - Ha! Why edit because of me? Others would agree with you. An argument could be made that it's at best an educated guess. I just like to focus on the education and deemphasize the guess. You can go the other way and still be perfectly in agreement with the broad strokes.
@Grant Fritchey - because ... I respect your opinion and actually prefer the way that you suggest avoiding a term that has a big association with luck. It's an educated estimate, based on a system with known differences that had xyz hardware. So long as all those involved understand the way the calculation is arrived at then it isnt a guess, its a best estimate without an exact duplicate, or actual test on production.
@Grant Fritchey - and you are the Scary DBA and I am spending a whole load of time with you next month ;)
@fatherjack - I'm getting jazzed about it. Only about 3 weeks remaining. You'll find out I'm the happy fluffy dba and shatter all the illusions.
You need to make a "best
guess
estimate". Why do you think it will be necessary? You must have some experience or evidence that this might be required so document that as soon as you can and work from there. You can, using a test environment, have the current workload and measure/benchmark the hardware performance and then add the new systems and measure/benchmark again. You can then apply the delta changes to the live system and suggest you might see a similar change in its performance. Which part of the hardware are you most concerned about?
do you have benchmark figures for the CPU + RAM usage at the moment? If not then your first steps should be to get a record of how they are being used currently
