How much memory does my SQL server really need?

Posted on 2014-01-27
Last Modified: 2014-02-10
How much memory does my SQL server really need? Sounds like an easy question right? When you look at the minimum recommendations from Microsoft regarding SQL 2012 it states the following.  

Processor, Memory, and Operating System Requirements
The following memory and processor requirements apply to all editions of SQL Server
Memory [1]  
Minimum:  Express Editions: 512 MB, All other editions: 1 GB
Recommended: Express Edition: 1 GB All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.

Microsoft is recommending at least 4 Gigs of RAM for SQL 2012.  In addition, that we should add more memory as the database size increases.  However it is not clear how much memory we should add as the database grows.

For example, lets say you have a database on a default SQL instance that is 100GB in size and the server has 2 x CPU at 3.30 GHz Quad core Win 2008 R2 Standard edition.  This is a dedicated physical SQL server which will be supporting 1000 clients.  However, lets also take into consideration that not all 1000 clients will be using this SQL server all at the same time everyday.  

Lets also say that the database in question is in pristine condition and the DB growth rate is expected to be 1GB a month.  In addition that the the TempDB is setup with additional secondary data files to reflect the CPU core count.

Here is my question, would you configure your SQL server with only the min memory recommended by Microsoft?  If no, how much memory would you give this SQL database and why?
Question by:RayManAaa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39812449
the easy answer is: it really depends.

it depends on how much users you have on the db, working on how much data.

* for example, you may have a small database, but plenty of users, you won't need "that" much for the data, but a bit more for the session (each session is using some space in the sql server memory space)
* you may have a HUGE database, but only couple of users, and only querying (normally) a couple of small tables, and some backend job that would be appending to the LARGE tables, and doing pre-aggregation. you would not need that much memory, still, assigned to the sql instance.

the more data you (your users) will be activately querying (and updating), the more of this data would be "best" to be kept in RAM, hence the more memory your sql instance would need. you CAN live with less ram, but your users will complain more and more about a "sluggish" sql server box, and tell you names ;-)

so, you may need to run your sql server with for example 8GB of ram, and see if it works fine (including doing fine-tuning on the queries !!!). if still your server is sluggish, ensure your max memory setting is configured so your sql server instance is NOT using up ALL the RAM of the box, because then you are going into the "so-called" PAGING issue, which will make that your RAM data goes to the disk and back all the time. SQL box will be slow as a dog, and worse.
LVL 12

Expert Comment

by:Alex Green
ID: 39812450
It really depends on what you're doing with it, if people are going to be doing queries and such then the memory load will increase.

If you have it set up currently on another server I'd run perfmon for a few days to get a baseline, then configure accordingly, unfortunately you can't really guess how much memory/CPU it's going to need.

I have a database where I work which is 50GB in size, it sits on a server with 128GB and 4 CPU each with 4 cores and it uses up around 100Gb of memory, why? Don't know.
LVL 30

Expert Comment

ID: 39812458
Buy as much memory as you can afford.

This is a case of never being too rich or too good looking .

If you don't want to spend on memory ,get a SSD and set up the DB to reside on it.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 12

Expert Comment

by:Alex Green
ID: 39812464
That's completely pointless throwing a database on an SSD, not only will the SSD work out more expensive per GB it won't help with large queries.
LVL 22

Expert Comment

by:Nick Rhode
ID: 39812542
As state above it all depends on amount of concurrent users, database size, and transactions.  Just keep in mind whatever RAM you give SQL it takes.  (SQL chews up as much RAM as you give it)
LVL 30

Expert Comment

ID: 39812610
>the SSD work out more expensive per GB

An Intel SSD DC S3500 80 gb is about $120  bucks retail.

Where are you going to get 80 gigs of ecc ram for that?

And if you use an Intel MOBO,you can use it as cache.

More than one way to skin a cat.

Author Comment

ID: 39812798
Thanks everyone for all your replies,

I agree that the more RAM the better, however I need to explain why.

What I am looking for is an easy way to explain to upper management why we need to configure the proposed SQL server with more RAM.  You see, what I am trying to do is prevent a SQL sprawl.  We have 85 SQL instances (~5TB of data) spread out throughout our organization.

What I am proposing to do is to consolidate some of these SQL instances (approx 20 SQL instances for now) into a new SQL cluster.  The information that I provided about the database size and the number of users was an attempt to create a template to help management understand.  Basically, I am looking to create a formula to apply to determine the initial size of RAM that I should allocate per SQL instance.

My plan so far, is to configure the new SQL servers with Windows 2008 R2 (64 bit) Enterprise edition with SQL 2012 (64 bit) Enterprise.  The reason why I want to use the Windows 2008 Enterprise edition is because it does not have the memory limit that Windows 2008 R2 Standard edition has.

Windows 2008 Standard edition can only use 32GB of RAM while Windows 2008 Enterprise edition can use 2TB.  In my humble opinion, it would make more sense to configure the server with Windows 2008 enterprise edition.  Simply because we will never run into a memory limitation due to the OS.

However, I am running into opposition mainly from the Windows administrators.  Their point is that it would cost much more money to get the Enterprise edition and that my SQL database would never use that much RAM.  

Interesting how my Windows administrators came to that conclusion with no facts to back them up.

Yours truly
Just a DBA.
LVL 30

Expert Comment

ID: 39812827
Why buy 2008 ent when you can buy server 2012r2 datacenter and virtualize ?

So much for server sprawl.
LVL 22

Expert Comment

by:Nick Rhode
ID: 39812941
One thing is to always meet what is needed and then some.  I always buy more than whats necessary so there is room to grow.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 39813047
when you consolidate "machines", in terms of RAM assigned to the sql instances you should keep " as is ", so if a sql instance today is using 20GB of RAM, so it should be assigned 20GB of RAM tomorrow, unless performance tuning / analysis is telling something else.

the "consolidation" will still save RAM, as the OS overhead will be less.

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had a hard drive that you can't boot into, but need to change the registry? Here is the solution! This article guides you through accessing and editing a registry of a non-primary drive. To read registry information on a non-prim…
INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question