Please Throw this Hardware at the Problem
We’re being told over and over that “throwing hardware at the problem” is not the correct solution for performance problems and a 2x faster server will not make our application twice as fast. Quite true, but there’s one thing that we can do very easily without emptying the piggy bank and won’t hurt for sure: buying more RAM.
The price for server-class RAM has dropped so dramatically that today you can buy a 16 GB module for around € 200. Depending on the architecture of your server, you could max out the memory supported by a standard edition (128 GB) with just € 1,600.
Isn’t it a reasonable price for better performance?
Let’s put it in perspective: you decide that throwing hardware at the problem is not right, so you hire a consultant to tune performance. How many days of his work can you buy with € 1,600? Two? One? How much tuning work will he do in one or two days? Do you want him to work on the core issues or fight with memory pressure? RAM is cheap, consultants are not: make them work on what really matters for you.
Does adding RAM always make sense? No: if your server has 4 GB of data and 16 GB of RAM, there’s no need to add more RAM and if you do you won’t see any improvement. On the other hand, if you have 100 GB of data and 32 GB of RAM, you should be upgrading right away. Isn’t your business worth € 1.600? You’re probably paying much more for SQL Server licensing, so why waste your money on a license that has to run on an underpowered machine? RAM is cheap, SQL Server licenses are not: let your license work on a good machine.
Will it make a huge difference? It depends: if the server is already under heavy memory pressure, you will see a big improvement. Even if memory pressure is not the worst issue on your server, you will see a big difference for those queries that access data not used frequently: if you don’t have enough RAM, that data will probably live outside the buffer cache and will have to be read from disk. RAM is cheap, faster disks are incredibly expensive: let the RAM work and the disks sit idle.
That said, what I see in the wild is lots and lots of production servers with 8 GB of RAM or less. Many of those servers have been installed a few years ago, when RAM was pricier than today, but there’s no reason why you shouldn’t upgrade them now. Upgrading the CPU has an impact on the SQL Server licensing, while adding more RAM doesn’t require additional licensing fees. See? RAM is cheap.
On the other hand, we have many servers running on virtual machines at hosting/cloud facilities, where you pay for machine specs. Something has to change here as well: hosting companies are offering too few RAM in their machines and the configurations with more memory are crazily expensive. Dear hosting/cloud companies, open your eyes: RAM is ridiculously cheap!
Call to action:
Did I mention that RAM is cheap? Check your servers: are they running on less than € 200 of RAM? It’s time to upgrade NOW!
Posted on February 12, 2015, in SQL Server and tagged Performance Tuning, RAM, Tuning. Bookmark the permalink. 7 Comments.
spaghettidba 
This article is ridiculous, I looked after over 600 machines If I apply the throw hardware (RAM) at the problem to all my servers I’ve just cost my organisation $1 million dollars for no real benefit. How about get your application and db tuning right from the outset and save your company thousands.
Yeah, that’s exactly what I said: shut down your brain and upgrade them all!! Did I mention that I work for a RAM manufacturer? 🙂
Perhaps a good indicator to look at is Page Life Expectancy. If it’s below 180 seconds on a busy day – more memory will help. I recently added more memory to a server that had Page Life Expectancy of about 15 seconds – now it’s about 20000 seconds on a busy day.
PLE is a good indicator of memory pressure, however the threshold value is highly dependant on how much memory you already have onboard. A fixed value of 180 is way too low, even for the outdated recommendations by Microsoft (300s). A more realistic threshold could be 300s every 4 GB of RAM for nowadays hardware. Again, YMMV.
Worth mentioning (or making clear)to check your OS will support it – got caught out recently with W2K8R2 Standard only supporting 32Gb, been so used to installing W2K12 I’d forgot and didn’t check ^^
Excellent point! Thanks for bringing it up.
Sadly one place where it’s almost required is when you’re running an application server and the code in the database has no SPs and views to optimize. At a previous employer they had a canned CRM system with such a configuration. The database was over 100 gig and we were getting progressively slower performance with monitoring showing no excessive CPU use or memory exhaustion. The solution was a whole new faster server, not just more memory. And they’re locked in to that upgrade cycle every 2-3 years for as long as they use that CRM POS.
As a result, that vendor’s name does not appear on my resume, nor will I willingly work at a shop based on their “solutions”.