The database server that I am using is running 6 different SQL Server instances. It has 48 GB RAM. And one of them is consuming more than 10 GB of RAM, total consumption is 20 GB for now. The RAM consumption is growing continuously. Couple of days before it was using more than 40 GB of RAM and the server was responding very slow. The application shows the crashing problems when saving data.

So I restarted the SQL Server services.

As soon as the services were restarted, the usage came down to 4 GB, but now it is growing. And I am worried that it will grow up to 40 GB in 4 or 5 days and make the server slow.

Service restarting is not the good option I guess.

I also found from different sources that we can set the maximum memory usage size for SQL Server. And am not quite sure if this will help or not. I can't test this because the server is using the production database and it would be a risk if the service stops while modifying the setting in SQL Server.

Can any one help for this problem?

  • 1
    By default SQL will use as much RAM as it possibly can to maximise its performance, this is good - it means it's caching common queries and results to make your application faster. It will only use RAM up to the limit at which the OS reports paging may happen. You can override this behavior. See this MS documentation: How to adjust memory usage by using configuration options in SQL Server This may also be useful: How to determine proper SQL Server configuration settings
    – greg84
    Apr 17 '12 at 10:34
  • 4
    It's NOT a problem - it's the designed and default behavior for SQL Server !! And yes - restarting the SQL Server process is a horrible idea..... SQL Server caches as much data and index pages in memory as it can - it's optimizing overall system performance that way. That's the reason a SQL Server machine should always be a dedicated server - doing nothing else but SQL Server.
    – marc_s
    Apr 17 '12 at 10:40
  • Yes I got it. It's designed that way. But when sql server uses much of the memory, it is making the server slow and causing the reports to time out. So I want to prevent sql server to use limited memory.
    – Shreejan sharma
    Apr 17 '12 at 11:47
  • 4
    Actually if SQL Server is using as much memory as it can, I'd say that would make it less likely to have difficulty delivering reports. It is when you artificially constrain SQL Server (or put too many instances on one box that are competing for resources) where you will see problems due to memory grants and other issues. Apr 17 '12 at 12:05
  • But I wonder why it suddenly started showing the reports well after restarting the sql server service. Can it be the case that too many customers were using the application due to which the reports were not displayed?
    – Shreejan sharma
    Apr 17 '12 at 12:14

That is by design. SQL Server is supposed to use all the available memory, as it is storing more and more data in memory so that it doesn't need to go back to the disk to get the same memory over and over.

If you need to limit the amount of memory that a single SQL Server instance is using you can do this in SQL Server Management Studio by right clicking on the instance name within the Object Explorer and selecting properties. Then select the memory tab and set the maximum amount of memory that the SQL Server will be allowed to use. Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the execution plan cache. Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting. However these other things typically don't need all that much memory, it's the buffer pool and the execution plan cache which need the bulk of the memory.

If you set this setting on one instance you'll want to set it on all the instances so that they don't step on each other.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy