RayManAaa
asked on
How much memory does my SQL server really need?
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?
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?
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.
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.
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.
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.
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.
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)
>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.
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.
ASKER
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.
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.
Why buy 2008 ent when you can buy server 2012r2 datacenter and virtualize ?
So much for server sprawl.
So much for server sprawl.
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.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.