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.

AnandPhadke
  • 170
  • 1
  • 5
  • 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
  • @Mitch Wheat -- Total of 25k Users...there could be ~1000+ users might connect at a time...and it all again depends..like based on user login – AnandPhadke Jan 18 '14 at 10:43
  • @marc_s - Lets say if we have proper indexes and DB design, will not lead to this situation? Bcos as I am wondering irrespective of the good design/indexes the sql server uses this buffer ppol to cache the data/plans etc...as sql server is used for long, cant this buffer pool size grow to the max mem size and that time if other process needs memory then what to do? – AnandPhadke Jan 18 '14 at 10:45
  • Actually I need to know in what situations sql server will release this memory to OS? Bcos in my case after reaching to the max size, the front end web application is not able to login for users – AnandPhadke Jan 18 '14 at 10:47
  • what version? (R2?) what service pack? What cumulative update? – Mitch Wheat Jan 18 '14 at 10:51
  • Sql server 2008 R2 SP1 – AnandPhadke Jan 18 '14 at 10:58
  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) – AnandPhadke Jan 18 '14 at 10:59
  • 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
  • Did you managed to solve the issue? If yes, how? – Krunal Feb 22 '14 at 05:10
  • Krunal -- The solution is to fine tune your heavily resource intensive queries. – AnandPhadke Feb 24 '14 at 07:36

0 Answers0