A classic question that I get asked very often during my various SQL Server consulting engagements is how much RAM do I need for SQL Server? This question has again a typical “It depends” answer. In today’s blog post let’s have a more detailed look into the various aspects on which “it depends”.
New SQL Server Installations
First I want to discuss how you can estimate how much RAM you need for a new SQL Server installation. Afterwards I will also talk a little bit in more detail about how you can verify if your existing SQL Server installation has enough RAM available.
Estimating how much RAM you need for a completely new SQL Server installation is a quite challenging job, because:
- You have to know how much data you hold in your RAM (your working set)
- You have to know what your indexing strategy looks like
- You have to know whether you are using the Standard or the Enterprise Edition of SQL Server
As you can see from this list, the answer to the question depends mainly on you. Let’s talk a little bit about each item in that list.
The more data you are querying, the more RAM you need. Imagine that most of your data is just archive data that users are not querying very often. In that case the data stays on your storage subsystem and is never ever loaded into RAM. Just think here about your logging and auditing tables: how much data you are really querying for those particular tables?
The better your indexing strategy is, the less RAM you need! If you have a really bad indexing strategy (or even NO indexing strategy!), you will need a huge amount of RAM to support your workload. Imagine you have a table of 100 GB of data, and you query for one specific row in that table. If you don’t have a supporting index on the search predicate, SQL Server will have to scan your complete Heap or Clustered Table. This means that SQL Server has to load your *complete* table into the RAM to return just 1 record! With a supporting index, SQL Server can perform an efficient Seek operation and return the requested record with just a few page reads. This makes a huge difference – trust me!
Even the edition of SQL Server influences how much RAM you can have for your SQL Server installation. With the Standard Edition you are limited to only 128 GB RAM. This sounds like a lot, but in reality it is almost nothing! Imagine your database consists of a few TB of data, and you are limited to only Standard Edition (for budget reasons). In that case you have to be very careful about each individual query that you submit to SQL Server, and you have to make sure that you have a perfectly aligned indexing strategy. With the Enterprise Edition it doesn’t matter that much, because SQL Server supports the maximum RAM of the OS (12 TB with Windows Server 2016).
Existing SQL Server Installations
With an existing SQL Server installation it is quite easy to find out whether you have enough RAM for your workload, or not. As you might know, SQL Server stores all retrieved pages from the storage subsystem in the Buffer Pool. And the Buffer Pool provides you with a performance counter called Page Life Expectancy. That performance counter tells you how long SQL Server stores on average a page of 8K in the Buffer Pool.
Nowadays you should have at least a Page Life Expectancy of a few thousand seconds. The more RAM you have the higher it should be. If your Page Life Expectancy drops below that threshold, you have a serious problem, because SQL Server can’t hold your data for very long in the Buffer Pool. As a side effect you introduce more physical I/O and the whole SQL Server performance just goes down. Again, you can work here on your indexing strategy and make sure that you only read that data physically from your storage subsystem that you have logically requested with your queries.
Summary
How much RAM you should have for your SQL Server installation is a really hard „It depends“ question. If you have to use the Standard Edition of SQL Server, you should have at least the maximum RAM that that edition gives you. The more the better. And with the Enterprise Edition of SQL Server, you should also have as much RAM as possible. RAM is nowadays really, really cheap compared to the licensing costs of the Enterprise Edition. Therefore don’t save money in the wrong place.
Thanks for your time,
-Klaus
2 thoughts on “How much RAM do I need for SQL Server?”
Hi Klaus,
I agree with your reasoning and conclusions for large databases, on the order of 1-2 TB. However, some of us do work with small databases from time to time, on the order of 30 GB for the data file. I’ve thought that there is probably no need for more memory than the size of the DB itself plus 4 GB for the OS, assuming a dedicated machine. Normally, as you say, memory is so cheap that one should max out the Standard Edition. But there is one case where it is not, namely on the cloud using AWS. In that case it is worth knowing how low can you go.
BTW, thank you for your articles!
Hello Jeffrey,
Thanks for your comment.
Yeah, the cloud is a game changer here.
Here I would take as much RAM as the database is large + a few GB for other stuff (like the OS).
Thanks,
-Klaus