SQL Server 2008 R2 memory issue

Question

I have read many posts regarding the memory but still its not clear.

I have dedicated server SQL Server 2008 R2 with Windows 2008 R2, 32 GB RAM, 2 6 core processor and a database of around 60 GB on it. Min and Max amount is set to 16 GB and 28 GB.

After few days if I check in task manager, it shows the load of 31 GB Memory but actually maximum is allotted to SQL Server is 28 GB and nothing is running on he server.

Why its increasing to the maximum limit? And where this 31 GB used? If its used in cache then how to clear the cache without restarting the SQL Server?

There are different questions in my mind if someone can help and discuss on this.

Answer 1

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.

Answer 2

I would try to answer the questions point wise

After few days if i check in task manager, it shows the load of 31 GB Memory but actually maximum is alloted to SQL is 28 GB and nothing is running on he server

First please note that task manager is not correct place to look for SQL Server memory utilization especially when SQL Server service account has locked pages in memory privilege. It will not show correct value and will only show process private bytes not the*lockecD* memory. Since you have SQL server 2008 R2 please use below query to find memory utilized 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

Now as to why it is taking memory more than 28G because 28 G is restriction on BUFFER POOL not on total memory that can be consumed by SQL Server. Buffer pool only caters to request for memory which requires <= 8 KB pages. For any memory requirement which requires continuous memory greater than 8 KB windows API directly allocates them NOT FROM THE BUFFER POOL. So this outside allocation causes SQL Server memory consumption to increase.

Following features are given memory outside from buffer pool IN SQL SERVER 2008 R2 the list has been changed in SQL Server 2012

SQLCLR,

BACKUP threads,

XML,

Extended stored procs

Linked Server memory

Thread Stack memory

Memory for Database mail

I am sure you must be using some of these features

Why its increasing to the maximum limit? And where this 31 GB used? If its used in cache then how to clear the cache without restarting the SQL?

Its increasing to maximum limit because this is how SQL Server is designed to use memory. SQ Server will take as much memory as possible to cache data pages and other objects as well so that when request comes for reading a page it wont have to generate a request for physical I/O and then get the page from disk to memory the whole process is costly and depending on the load on Server can take time. On the contrary if page is avaialble in memory your request would be satisfied immediately. But this is not a drawback it would trim down its memory consumption when SQLOS asks it to do so because it was requested by windows OS that it would require more memory so this whole process is dynamic. So NO NEED to worry whem SQL Server is utilizing memory.

For more details about memory please read below article

http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx

PS: Do revert if you still need clarification