2

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.

Improve this question
  • 3
    SQL Server will try to use as much memory as it can get to speed up queries. That's by design - that's not a bug. If your buffer pool gets polluted, then maybe the application and database design are not as good as they could be - tables are too large, queries are inefficient..... – marc_s Jan 18 '14 at 10:21
  • 1
    Out of all apps, SQL Server does an amazing job of memory management. Are you sure you have a memory problem, and not poor indexes/bad TSQL? And what Marc_s said... Also, can you explain "25k users " a bit more. Is that 25K+ connections at the same time? – Mitch Wheat Jan 18 '14 at 10:21
  • 1
    The symptom of SQL server using increasing memory is usually a red herring (that's how it is designed, and it will give it back if configured appropriately). More likely when you restart server some bad plans get flushed from plan cache.... – Mitch Wheat Jan 18 '14 at 10:24
  • 1
    RTM!?! Why? Just Why? RTM is not service pack 1. Which is it???? I doubt you will get any serious help on that old a version. Also I note that you are on a VM. what's the ballon manager settings? Memory reservation? In short, hire a local consultant! – Mitch Wheat Jan 18 '14 at 11:02
  • 1
    If your database design is optimal, and your queries are optimized - then the apps will use less memory so SQL Server will have to cache less pages in the buffer pool and possibly less execution plans in the plan cache – marc_s Jan 18 '14 at 11:16