Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
My Database server is consuming 71% - 75% percent of RAM memory, I can see from perfMonitor logs that SQL_Service is eating up 65% - 69% of RAM memory.
We carried out different operations to lower the memory consumption like:
Server RAM memory is 65 GB in total.
Looking forward for an expert opinion. How to Lower the memory consumption ?
It seems like you are running SQL Server together with an application on one server.
SQL Server will consume all the memory it can by design. (The default MAX memory setting in SQL Server being 2147483647 MB.)
If you changed the default MAX memory setting to something else, then SQL Server will start consuming memory after a reboot for data cache, query plan cache, and for other SQL Server relevant components.
At some point in time the SQL Server will pass the MIN memory setting. SQL Server will not release memory back to the OS past this setting.
Later on the SQL Server instance will possibly reach the MAX memory setting. SQL Server will not pass this line for data cache and plan cache. However, the SQL Server process can use more than the MAX memory server setting, because some objects run outside of the MAX memory setting (depending on version) and SQL Server can overcommit memory. This is discussed in the following article
Memory configuration and sizing considerations in SQL Server 2012 and later versions
Your SQL Server will now consume memory between the MIN and MAX memory setting until you reboot the SQL Server instance.
CAUTION: Reducing the MAX memory setting of a running SQL Server instance will purge the data cache in memory and result in a reduction in performance.
If your SQL Server instance is running on an application server and not on its own, then you might have to reduce the MAX memory setting for SQL Server to allow the application to have some share of the memory.
If your SQL Server instance is running on its own, then try configuring the MAX memory setting according to the following recommendation:
...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM...
Reference: How much memory does my SQL Server actually need? (SQLSkills.com)
There are some other good articles out their that explain how SQL Server uses RAM.
Adjust your thinking.
SQL Server caches as much as you allow it - because RAM is faster than discs. Unless SQL Server uses TOO MUCH memory (and you can give an upper limit) - why would you ever complain about the use of memory as cache? Would you prefer the memory not to be used at all? Then why buy it in the first place. You could have saved money by buying only 32GB memory - and then SQL Server would not use more than that. Would you be happier? And setting a limit basically only makes sense on shared servers for - shared with other services, multiple instances, stuff like that. On a dedicated server you want SQL Server to use as much memory as possible.
So, the issue is: you have no problem to start with. You imagine one, while in reality SQL Server works as intended.
Database is a vital part of any application and is active all the time.This is why databases are often hosted in dedicated servers where nothing else can hamper the resource utilization for the database.
Now coming to your question, there is no problem at all and SQL Server is working as expected. SQL Server is smart enough and caches as much as possible to make operations faster.
There is a setting called MAX MEMORY SETTING that can be set to a value at which you want SQL Server NOT to use any further RAM. This is done after keeping aside the memory usage for the OS processes.
For Example:
In a 16 GB Server, we estimated the SQL Server Buffer Pool and set the MAX Memory to 10 GB. Now SQL Server memory usage never goes beyond that and OS uses the remaining 6 GB.
