Solved

How much memory does my SQL server really need?

Posted on 2014-01-27
10
1,035 Views
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
2012:
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.

http://msdn.microsoft.com/en-us/library/ms143506.aspx

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?
0
Comment
Question by:RayManAaa
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:alexgreen312
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:pgm554
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:alexgreen312
Comment Utility
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.
0
 
LVL 22

Expert Comment

by:Nick Rhode
Comment Utility
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)
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 30

Expert Comment

by:pgm554
Comment Utility
>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.
0
 

Author Comment

by:RayManAaa
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:pgm554
Comment Utility
Why buy 2008 ent when you can buy server 2012r2 datacenter and virtualize ?

So much for server sprawl.
0
 
LVL 22

Expert Comment

by:Nick Rhode
Comment Utility
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.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Preface Having the need * to contact many different companies with different infrastructures * do remote maintenance in their network required us to implement a more flexible routing solution. As RAS, PPTP, L2TP and VPN Client connections are no…
Many times while working on a computer regardless of any Operating System, lag and crashes seem to creep in, hindering your working speed. Sometimes, it can also cause your work to be lost unexpectedly and as a result, you are unable to meet your de…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now