SQLTeam.com | Weblogs | Forums

If the memory utilization of the server is going high, then how to handle?


If the memory utilization of the server is going high, then how to handle?


Does your instance have a Memory Maximum set?
One way to get to it is in SSMS, Object Explorer, right click the instance and select Properties. Under Select a page choose memory.

At a minimum this will require a restart of the service to take effect. I am not sure if a reboot is needed.


My understanding is that if you change the maximum memory, it may not take effect immediately, but eventually it will come down.

On that dialog, at the very bottom, there is a radio button that allows you to choose between Configured Values and Running Values. The documentation says this about those buttons: "Displays the configured values for the options on this pane. If you change these values, click Running Values to see whether the changes have taken effect. If they have not, the instance of SQL Server must be restarted first." But that is a generic message for all the tabs in that dialog. So that does not necessarily mean you have to restart.

Don't forget that the values you set in there are in MEGABYTES, not in gigabytes.


Why to handle is a better question. SQL Server normally takes all the storage available up to max mem. Doesn't mean its using it all all the time. Nothing to worry about unless you're getting errors.


The only way to handle it is to reduce the "max memory" configuration for the server. Also, be aware that before SQL 2012, that value is for buffer space only, and that SQL will use additional RAM for other things when running, and it can significant amounts of RAM.

You can lower max RAM while SQL is running and, after after you apply the new max memory setting (by issuing the RECONFIGURE command), SQL will eventually release RAM down to the specified amount, without you having to reboot or even to stop and restart the instance.