SQL Server 2008 R2 – Memory Caching and Release Issues

sql-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 R2 memory issue

Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses. The running programs and needs within Windows take some additional space.

If you want to closely tune the memory use, I recommend reading this, which works well for my servers. See Jonathan Kehayias at: http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

It basically says "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM." This suggests that your Maximum Memory should be tuned to 25 GB.

You can dynamically alter the min/max by setting a new value for those measures. The OS will adjust in a relatively short time without a reboot.

EDIT: Of course, as both Jonathan and Shanky mention, you still need to monitor your performance counters, since any general answer will likely need tuning.

Sql-server – SQL Server 2012 Very Slow to Consume Memory

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege. just use below query to see memory consumed by SQL Server

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 memory issue

Sql-server – SQL Server 2012 Very Slow to Consume Memory

Related Question