Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How much memory does my SQL server really need?

Posted on 2014-01-27
10
Medium Priority
?
1,324 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 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.
0
 
LVL 14

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.
0
 
LVL 30

Expert Comment

by:pgm554
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 14

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.
0
 
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)
0
 
LVL 30

Expert Comment

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

Author Comment

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

Expert Comment

by:pgm554
ID: 39812827
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
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.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction: I have always been a big fan of Windows but my liking towards it is slowly being eroded by the variety of other Applications that I encounter, when I browse the Web. Most of the software available is free and maybe Open Source too. …
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…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

877 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