I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014.
The max ram is set to 60000MB. Amount of free ram is 0 according to task manager after a few days online.
If I change the max ram to below 53GB it will after a few days to stabilize and have some free ram.
It is the sql process that does allocate the ram according to task manager. How do i come to terms with what the problem really is? It goes without saying that i did alot of testing already but havn't solved this to my liking yet. and ohh we do not get the typical memory starvation lagging when the available ram is down to 0 free.
Inspired by another Q/A related to RAM on this page http://dba.stackexchange.com/a/7062/2744 . I used these two to see what the RAM is being used for.
SELECT TOP ( 10 ) [type] AS [Memory Clerk Type] , SUM(single_pages_kb) AS [SPA Mem, Kb] FROM sys.dm_os_memory_clerks GROUP BY [type] ORDER BY SUM(single_pages_kb) DESC OPTION ( RECOMPILE ) ; SELECT DB_NAME(database_id) AS [Database Name] , COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors --WHERE database_id > 4 -- system databases -- AND database_id <> 32767 -- ResourceDB GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC OPTION ( RECOMPILE ) ;
The amount used shown by these are first select 7948432 Kb second one 44030,57812 MB that is a total of about 52GB used by sql server... so where did the rest of my RAM go? :-) Task manager show right now cached 363, available 401, free 40 and sqlservr.exe has Memory private set 64 459 656. Max Ram set to 60000MB as before.