SQL Server 2008 – Max Memory Not Limiting RAM Usage

memorysql-server-2008

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.

Update 1:

Inspired by another Q/A related to RAM on this page https://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.

Best Answer

SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit.

Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start.

Also related: SQL Server 2008 R2 “Ghost Memory”

Related Question