Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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.

share|improve this question

3 Answers 3

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”

share|improve this answer
    
yes, I agree that it limits the buffer pool only. Thank you for the bookmarks I will look into them. –  Martin Sjöberg Nov 24 '11 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? –  Martin Sjöberg Nov 29 '11 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
  • SQLCLR
  • Extended Stored Procedures
  • The binaries them selves
  • SQL Mail
  • SSIS
  • SSAS
  • SSRS
share|improve this answer
    
Of the above mentioned we use the binaries ofc and ssis on this server. –  Martin Sjöberg Nov 29 '11 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 Nov 29 '11 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... –  Martin Sjöberg Dec 7 '11 at 8:41
1  
The FusionIO driver needs a lot of memory to work with. That's probably taking a lot of it. –  mrdenny Dec 7 '11 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. –  Martin Sjöberg Dec 7 '11 at 21:19

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

share|improve this answer
    
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? –  Nick Chammas Nov 24 '11 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. –  Nick Chammas Nov 24 '11 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 Nov 27 '11 at 21:07
    
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 May 27 '12 at 21:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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