Question :
We have started using SQL Server 2014 Enterprise edition on our new server having 32 gigs of RAM, and in less than 3 weeks the server process is accumulated 13 GB and plus of RAM.
Including IIS worker processes the total amount of RAM being used is accumulated somewhere 75%-80% so we are just curious if we need to clear the buffer every weekend of at the month end. But, before that we want to make sure if it is a wise idea to do so! SO my questions:
-
Is it a wise Idea to limit the max server memory for SQL Server? (I guess NOT)
-
Is it a good idea to clear the buffer pool at a certain frequency (weekend / month-end)? Will it impact the performance?
Thanks in advance!
Answer :
It’s almost always a good idea to set max server memory setting to some value lower than the total amount of memory available, especially if there are other applications running on the same server (IIS in your case). The actual number depends on total amount of memory and on memory requirements of those other applications.
This setting is important to prevent the various applications/services competing for memory (causing memory starvation), which in turn can cause performance problems.
With 32 GB available, I’d subtract from it 3 GB for the OS, then subtract whatever number is used by IIS and other applications, if any, and set the max server memory to the remainder.
There is no need to periodically clear the SQL Server buffer pools, just the opposite.