Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i am working on SQL server 2008 R2. It has been allowed to take a maximum memory which is around 45 GB. In couple of days it caches everything and the limit is reached. We have other processing as well on the server. Why doesn't it release all the memory after it has finished the processing?

How can i know the reason of the issue which process is doing this caching? I could find out some of the query plans cached but 40 GB of caching after that other processes from the SQL server it takes too much around 47/48 GB which has been an headache.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

For any RDBMS, the bottleneck for performance is (usually) disk performance. Compared to all other parts of the computer, disk I/O is the most expensive in terms of time required. To this end, RDBMS engines focus on operating on as much data as possible in memory, as RAM is much faster to read and write to than physical disk. It also provides a safety mechanism for ACID compliance, so that only work that is completed successfully will be persisted to disk. If anything is rolled back, either due to failure or manual intervention, it can easily be discarded from memory without affecting the physical storage.

This means that relational engines will try and keep as much data in memory for as long as possible. To continuously push and pull objects from disk to memory is expensive and will hurt application performance. There are times items need to be cycled out of memory, but this will happen based on a LRU algorithm that is designed to keep active data in memory.

It is possible to view information about what is in your cache. Note that the memory pool for SQL Server will contain different objects. Primarily it will be the buffer pool, which stores data objects, and the plan cache, which are the query execution plans. You can utilize specific dynamic management views for this observation, which are used in Glenn Berry's DMV script. Look at queries 39 and 55.

If you wish to restrict the amount of memory SQL Server can use, the only option you really have is to reduce your Max Memory configuration for your instance. Be careful with adjusting this, though, as a reduced memory pool can hamper your performance based on the factors described above.

share|improve this answer
    
I do understand your point and all of them are universally accepted. Can't we clear out the plan which are not being used often automatically? Wanted to know if there is anything in our hand? –  Linu1988 Apr 9 at 20:36
1  
Your best bet is to enable optimize for ad hoc plans on your SQL Server, which will only store minimal info for single use queries. In reality, your plan cache segment is only a fraction of the size of your buffer pool segment and cleaning it out automatically won't save you much. If memory pressure is really an issue, I'd just reduce your max memory setting. –  Mike Fal Apr 9 at 20:40
    
Thank you for your help. Memory pressure is there and which is unavoidable as we need performance as well. Any reference to know which are the other processes other than this cache taking memory from SQL server? –  Linu1988 Apr 9 at 20:53
1  
@Linu1988 Memory pressure is there, from what? How have you proven this? Do you have other things on the server that need that memory besides SQL Server? Why? If you can't move these things to another server, then set Max Memory. Now SQL Server will never be able to use as much memory as it clearly may need at some point, but at least your precious other processes won't suffer for it. (Hint: the other idea is to not put other processes on your SQL Server.) –  Aaron Bertrand Apr 10 at 17:06
    
SSIS is running on the same server. So they fall victim. Another server will be needed soon no other way. –  Linu1988 Apr 10 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.