How to control the excessive use of ram by SQL Server?
-
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?
-
Answer:
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.
Shreejan sharma at Database Administrators Visit the source
Other answers
Put a limit on the maximum server memory setting on your instance. This will restrict the size of your buffer pool (SQL 2012 is different). There are other components that are not included in this setting like CLRs, etc... Doing this may cause your instance to perform poorly as it looks like it needs more memory to perform. If you come to a point where you have to restart try issuing a DBCC dropcleanbufffers and DBCC FREESYSTEMCACHE first, and see if that makes a difference.
StanleyJohns
Related Q & A:
- How to connect Sql Server Database from android app?Best solution by Stack Overflow
- How to upgrade sql server express 2005 to 2008R2?Best solution by Server Fault
- Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008?Best solution by Database Administrators
- How to output the results of an procedure in SQL server?Best solution by Stack Overflow
- How to make a computer use more RAM?Best solution by Super User
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.