Post reply

SQL Server 2008 R2 Using Most of Memory on Server

  • March 20, 2012 at 9:22 am

    #258927

    Hi..

    I was just given a SQL Server 2008 R2 system which has 24GB of memory and is using over the 22 GB memory max configuration setting. I thought if the system was configured with a Max memory setting of 22 GB this would be all it would use and would not go over. The minimum is set at 20 GB and max is 22 GB. It has used 23 GB or more at certain times. Any ideas?

    Thanks...

    NS

  • March 20, 2012 at 9:42 am

    #1461724

    This setting is for the buffer pool only. It does not include all memory usage for additional activities such as CLR and backup buffers. These can add up to several hundreds of MB's depending on whats in use.

    Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by an instance of Microsoft® SQL Server™.

    See this link for the settings definition.

    http://msdn.microsoft.com/en-us/library/aa196734(v=sql.80).aspx

    Another google search and I'm sure you can find the remaining information.

  • March 20, 2012 at 9:46 am

    #1461727

    The max. memory allocated using the memory settings is for SQL buffer pool which includes data cache and procedure cache. There are a number of SQL Server objects that use memory from outside of the buffer pool—that is, the memory area defined by the maximum memory setting. Memory for objects such as linked servers, extended stored procedures, and object linking and embedding (OLE) automation objects is allocated from an area commonly called the MemToLeave area.

    The basic rule of thumb when allocating maximum memory is that the total of each instance’s maximum memory values should be at least 2GB less than the total physical memory installed in the server; however, for some systems, leaving 2GB of memory may not be enough as happend to your system.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • March 20, 2012 at 9:53 am

    #1461732

    Ryan007 (3/20/2012)

    The basic rule of thumb when allocating maximum memory is that the total of each instance’s maximum memory values should be at least 2GB less than the total physical memory installed in the server; however, for some systems, leaving 2GB of memory may not be enough as happend to your system.

    I would (do) follow the suggestions made by SQL CAT on in this article. Its a more reliable calculation than putting your finger in the air and guessing.

    http://sqlcat.com/sqlcat/b/technicalnotes/archive/2011/04/26/lessons-learned-from-benchmarking-a-tier-1-core-banking-isv-solution-temenos-t24.aspx

    Configure the SQL Server “max server memory (MB)” setting by taking the amount of memory allocated to the database system and subtracting one GB for every four cores (round up). This leaves the operating system with enough memory to work efficiently without having to “grab” memory back from SQL Server. For example, if the server has 64 GB of RAM and 24 cores, set the maximum memory to 58 GB (64 GB minus 6 [24 cores divided by 4]).

  • March 20, 2012 at 10:03 am

    #1461741

    Thanks MysteryJimbo

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • March 20, 2012 at 12:17 pm

    #1461815

    Thanks so very much for your assistance! Great advice!

    NS

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply