SQL 2014 Memory Limit

[AceDaz]

Hi all

 

We have just upgraded to SQL 2014 in preparation for the Summer 2015 Release, but the server is running at over 90% used memory.

 

The server has 12Gb RAM and is running SIMS/FMS, Bromcom Webfolder, DataSec, and a few other Sims-Sync software which are run as scheduled tasks.

 

 

I remember vaguely that I applied a limit on the amount of memory that SQL 2008 R2 used, does anyone else limit their SQL memory usage? If so, what do they limit it to / size of SIMS/FMS database?

 

Many thanks

[Steve21]

Ours isn't limited as all it runs is SIMS and syncs which happen out of hours anyway. Not like anything else is really running on it to need the RAM

 

Steve

[AceDaz]

Cheers Steve, we are planning to move to FMS MultiView and Bromcom users have complained about performance issues.

[Sagima]

Our SQL box runs all of our various databases (except sccm) so we have memory limits (min server memory and max server memory) set for each of them so the server doesn't have to waste time sorting out who gets what - leaving some for the host. works on for us although we are still back on 2008R2 at the moment - 2014 in the summer!

[AceDaz]

Hi Sagima

 

What are your Min and Max values?

 

Any idea what size your SIMS and FMS databases are?

[Sagima]

Hi Sagima

 

What are your Min and Max values?

 

Any idea what size your SIMS and FMS databases are?

 

Sorry - should have made it clear we don't use sims but our mis instance is set to 10GB and our other sql instance are set to other values so that the total is 4GB less than the installed RAM for the OS to use itself. The database itself is 6GB but our database manager says there isn't a necessarily a direct correlation between the two - it should be based on transactions unless you are having the database live in RAM in which case you need to allow for both - he thinks is how 2014 works like that if you let it so we are allowing for 18GB to start with on our new sql box just for the MIS. (SCCM is set to 8GB min and max.)

[Ghostgame]

You can limit the amount of RAM SQL can use in SQL Management Studio.

I have limited SQL especially when the school was on Express.

SQL seems to have a habit of trying to grab all the RAM it can, even if it does not need it.

[SHimmer45]

unless you limit the ram usage SQL server will use all it can get from the system, its left like this as default.

our SIMS host which has 16GB ram uses 85-90% of the installed ram for SQL.

[Scott_L]

I should just introduce myself and set disclaimer before being helpful (sorry, it’s necessary); I don’t work for Capita and haven't worked with SIMS for quite come time. I work for Advanced Learning and do have SQL knowledge and experience, so if I can help I feel I should.

 

As I don’t work for Capita, I’m unaware of their requirements and best practices, please check anything I do suggest (and things suggested by others) with Capita before deviating from their documentation; I nor Advanced Learning can be responsible for any unwanted side effects from following advice given here.

 

Best practise is to set limits within SQL. This will reserve memory for other services as well as the OS. Normally these should be set for best performance, tips can be found from the links below:

 

Microsoft - Good little bit of information

SQL Server Central - Nice site to get information and help from

Brent Ozar - Information and scripts to help monitor and work out memory usage

 

I hope some of these links are of some help. It is also worth implementing SQL maintenance tasks to keep your database healthy and performing well.

 

Scott