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.

        [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

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

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.

4 Answers 4


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”

  • yes, I agree that it limits the buffer pool only. Thank you for the bookmarks I will look into them. Commented Nov 24, 2011 at 15:41
  • I am reading up on those links and there are some good stuff there def. I will update my question with what I find out. I also feel relifed that i have the number to Max Pizzeria readily available... I wonder if they do homedelivery? Commented Nov 29, 2011 at 14:38

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
  • Extended Stored Procedures
  • The binaries them selves
  • SQL Mail
  • SSIS
  • SSAS
  • SSRS
  • Of the above mentioned we use the binaries ofc and ssis on this server. Commented Nov 29, 2011 at 13:06
  • 1
    What other software is installed on the server? And I mean anything. MPIO drivers, Flash drive drivers, backup software, anti-virus, sys-internals, etc.
    – mrdenny
    Commented Nov 29, 2011 at 16:06
  • The server is pretty clean and newly installed but ofc we do have some there. I will try to get a complete list put together at the end of this week. Short and from my memory... we do have an iodrive(dell), mcafee, processexplorer on desktop, iometer, treesize... Commented Dec 7, 2011 at 8:41
  • 1
    The FusionIO driver needs a lot of memory to work with. That's probably taking a lot of it.
    – mrdenny
    Commented Dec 7, 2011 at 19:05
  • Is it possible to prove this? Or to configure it to use less RAM? Sofar it seems to release RAM when it has to and i havn't noticed any downsides but just incase we have to increase amount of ssis packages I am concerned at what might happen with RAM usage. Commented Dec 7, 2011 at 21:19

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.

  • Thanks. I converted it to page_size_in_bytes, but that wasn't the same.
    – Elias
    Commented Feb 28, 2018 at 14:25


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

  • You "may" need to restart SQL Server. It's not always required. Do you know the conditions under which a restart is or isn't required to release memory? Commented Nov 24, 2011 at 16:29
  • I would edit these details into your existing answer and delete this one. Perhaps mrdenny can answer your question about flushing dirty pages. Commented Nov 24, 2011 at 16:58
  • 1
    Correct, SQL can't release a memory page while it is dirty (has been written to). Every time the system checkpoints the dirty pages are written to disk. I don't believe that changing max server memory causes a checkpoint to happen.
    – mrdenny
    Commented Nov 27, 2011 at 21:07
  • 1
    Restarting the instance to change memory settings could be a big mistake. While changing the memory settings doesn't cause a CHECKPOINT operation on the databases, it does flush out the procedure cache. If you restart the instance just to change memory settings, not only will the procedure cache be cold, but the data cache will be cold, too. If the max memory cannot be reduced because of dirty pages in memory, run the CHECKPOINT command on the databases to flush the dirty pages to disk, then change the memory setting at off-peak time without restarting the instance.
    – Jon Seigel
    Commented May 27, 2012 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.