SQL Server Max Memory not limiting use of RAM

Question

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.

Answer 1

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”

Answer 2

As said Buffer pool and procedure cache are about the only things which are controlled by max server memory. There are lots of other things within SQL Server that can eat up memory beyond that limit. They include (but are not limited to):

• Database Mail
• SQLCLR
• Extended Stored Procedures
• The binaries them selves
• SQL Mail
• SSIS
• SSAS
• SSRS

Answer 3

From SQL 2012 onwards, single_pages_kb got replaced by pages_kb in that DMV. https://msdn.microsoft.com/en-us/library/ms175019.aspx?f=255&MSPPError=-2147217396

So if you want to run the query included in the question, on a 2012+ server, remove the single_ string.

Answer 4

http://msdn.microsoft.com/en-us/library/ms178067.aspx

To reduce the max server memory you may need to restart SQL Server to release the memory.

My understanding is that if a page in the buffer pool hasn't been written to disk, it will not be released until it is.

Does lowering the max memory setting cause SQL Server to flush out dirty pages?

He could monitor the buffer manager in perfmon to verify that. Perfmon -> SQLServer:Buffer Manager : Database Pages