We have a production SQL Server 2008r2 x64. 16GB RAM and this server is used by the application (hosted) and SQL Server.
- The max memory setting = 6GB
- The min memory setting = 128MB.
There are ~25k users access this database server through the application. As the memory usage reaches to the max memory setting value, we will start facing timeout issues and users will not be able to login. Also, we see CPU usage by SQL Server is 100% when this happens.
Is there any way to control this buffer pool memory usage by SQL Server as SQL Server will not release this memory until the OS is in badly need of it. Currently, whenever such a situation occurs, we just restart SQL Server and let it release the memory to the OS. After restarting it works smoothly for few days and again it reaches to the max memory limit and start causing issues.
So how do all huge production environments using SQL Server manage this issue? As memory usage will rapidly be growing.