I was reading Brent’s look at “normal memory” for his SQL ConstantCare® clients. It’s a look at some stats from quite a few servers that customers have set up and he is monitoring. Since most of us only have a limited set of instances to examine, it’s nice to see what a wide variety of installs are using. Even though this doesn’t necessarily mean we should change anything in our environment, it does help me to understand some general trends, and perhaps think about how to recommend settings for new installations.
When we build a server, we expect it to work a certain way. Over time, we are almost always adding data to the system, but often we don’t add more RAM. This would be like buying a vehicle and loading it up with supplies you need for work. If the amount of supplies kept growing, would you keep piling them on or buy/rent/borrow a bigger vehicle? Some would, some wouldn’t. I’m always amazed by how far people will push a situation, especially when fairly small and inexpensive changes might make a big difference.
Data size isn’t always a good indicator of how much memory you should allocate, but it can be. Certainly the more data we query in a workload, the more memory makes a difference. Bad code is more likely to surface issues, but memory is often cheaper than paying developers, or usually, finding and training developers to write better code. For a DBA, this might be one of the relatively few differences we can make in the short term that has a far reaching effect. Adding memory might cover up some poor coding and speed up the experience for clients in the short term. In the long term, things will likely tip over again.
If you manage an instance, you can’t usually change code, and it can be hard to get developers to prioritize changing existing code. Most developers are pressured to move forward with new work, not refactor old work. One of the reasons I like moving clients to a database DevOps flow is that DBAs with query tuning skills can spend more time tuning some queries and giving code to developers. It’s much easier to get developers to fix bad code if you give them better code than ask them to find time to rewrite things.
Getting the best performance out of your database server often a balancing act among various choices that each only solve a portion of the problem. Don’t be hesitant about asking for more memory over time, but also be careful that you don’t just depend on this one technique to reduce the number of customer complaints. Learn to write better code and teach others to do the same.