none
Memory Optimisation

    Question

  • Hi All,

    I have SQL server 2008 R2 Enterprise Edition SP2 which runs on 18 Gigs RAM with 2 CPU's.

    I have set the max memory parameter of the SQL server to 13 gigs and min memory parameter of the SQL server to 8 gigs.

    The memory consumption of the server consistently stays at 16 gigs.

    There are no other application which are running on the server.

    How can I tune the server to better utilise the memory?

    Regards,
    kccrga

    Thursday, September 26, 2013 10:26 PM

Answers

  • Hello,

    I disagree with Saeid's post. Manual memory settings offer the best performance and stability for servers. The problem with Dynamic memory allocations is that SQL Server responds to the low memory flag that windows broadcasts when it's low on memory (I can't quite remember but I think it's 75MB on Windows Server 2008R2). Once this happens you're going to have a massive performance hit as SLQ Server trims its memory... sometimes this manifests itself as disk IO issues as the memory is paged out, sometimes SQL Server can't trim fast enough and the server can crash.

    SQL Server 2008R2 doesn't count certain types of memory as part of the 'max memory' setting. An example of these memory allocations are for the executable itself, worker threads, CLR memory, linked servers, OLE automation, etc. Also not covered is the backup and restore buffers. Once you take into account the amount of memory windows needs, how much SQL Server needs, and how much outside of SQL Server is needed, I would guess you'd want to set your Max Server Memory around 11 GB and SLOWLY bump it up as you see the memory being consistently free.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, September 27, 2013 1:09 AM
    Answerer
  • Hi Sean,

    I agree with you only by setting manual memory settings offer the best performance and stability.

    But I am after what is the good max server memory configuration for the server. Given that I have 18 gigs of physical RAM doesn't mean I can utilise everything. I am also checking the "perfmon" counters for SQL buffer manager to check whether there are any page life expectancy for the server.

    Any thoughts on what can be best suitable max server memory settings for this server? How to figure out the limit?

    Regards,

    Kccrga

    Hello,

    First As said by Sean try to set correct value for SQL server MAX MEMORY SETTING.Below links might help you for further reading about SQL server memory

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

    http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

    >>The memory consumption of the server consistently stays at 16 gigs

    When AWE is in picture with locked pages in memory neither task manager nor perfmon will show you correct value.I rely on DMV below query will help you to give exact value used by SQL server

    select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb, 
    locked_page_allocations_kb/(1024) as awe_memory_mb, 
    total_virtual_address_space_kb/(1024) as max_vas_mb,
    virtual_address_space_committed_kb/(1024) as sql_committed_mb,
    memory_utilization_percentage as working_set_percentage, 
    virtual_address_space_available_kb/(1024) as vas_available_mb,
    process_physical_memory_low as is_there_external_pressure, 
    process_virtual_memory_low as is_there_vas_pressure 
    from sys.dm_os_process_memory 
    go 
    You can keep Min server memory to 4G.But use above links to reach optimum value.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, September 27, 2013 5:31 AM

All replies

  • Dynamic memory allocation gives you the best performance. Please see these links:

    Optimizing Server Performance Using Memory Configuration Options

    Server Memory Options


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Thursday, September 26, 2013 10:44 PM
  • Hello,

    I disagree with Saeid's post. Manual memory settings offer the best performance and stability for servers. The problem with Dynamic memory allocations is that SQL Server responds to the low memory flag that windows broadcasts when it's low on memory (I can't quite remember but I think it's 75MB on Windows Server 2008R2). Once this happens you're going to have a massive performance hit as SLQ Server trims its memory... sometimes this manifests itself as disk IO issues as the memory is paged out, sometimes SQL Server can't trim fast enough and the server can crash.

    SQL Server 2008R2 doesn't count certain types of memory as part of the 'max memory' setting. An example of these memory allocations are for the executable itself, worker threads, CLR memory, linked servers, OLE automation, etc. Also not covered is the backup and restore buffers. Once you take into account the amount of memory windows needs, how much SQL Server needs, and how much outside of SQL Server is needed, I would guess you'd want to set your Max Server Memory around 11 GB and SLOWLY bump it up as you see the memory being consistently free.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, September 27, 2013 1:09 AM
    Answerer
  • Hi Sean,

    I agree with you only by setting manual memory settings offer the best performance and stability.

    But I am after what is the good max server memory configuration for the server. Given that I have 18 gigs of physical RAM doesn't mean I can utilise everything. I am also checking the "perfmon" counters for SQL buffer manager to check whether there are any page life expectancy for the server.

    Any thoughts on what can be best suitable max server memory settings for this server? How to figure out the limit?

    Regards,

    Kccrga

    Friday, September 27, 2013 1:30 AM
  • Hi Sean,

    I agree with you only by setting manual memory settings offer the best performance and stability.

    But I am after what is the good max server memory configuration for the server. Given that I have 18 gigs of physical RAM doesn't mean I can utilise everything. I am also checking the "perfmon" counters for SQL buffer manager to check whether there are any page life expectancy for the server.

    Any thoughts on what can be best suitable max server memory settings for this server? How to figure out the limit?

    Regards,

    Kccrga

    Hello,

    First As said by Sean try to set correct value for SQL server MAX MEMORY SETTING.Below links might help you for further reading about SQL server memory

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

    http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

    >>The memory consumption of the server consistently stays at 16 gigs

    When AWE is in picture with locked pages in memory neither task manager nor perfmon will show you correct value.I rely on DMV below query will help you to give exact value used by SQL server

    select physical_memory_in_use_kb/(1024) as sql_physmem_inuse_mb, 
    locked_page_allocations_kb/(1024) as awe_memory_mb, 
    total_virtual_address_space_kb/(1024) as max_vas_mb,
    virtual_address_space_committed_kb/(1024) as sql_committed_mb,
    memory_utilization_percentage as working_set_percentage, 
    virtual_address_space_available_kb/(1024) as vas_available_mb,
    process_physical_memory_low as is_there_external_pressure, 
    process_virtual_memory_low as is_there_vas_pressure 
    from sys.dm_os_process_memory 
    go 
    You can keep Min server memory to 4G.But use above links to reach optimum value.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, September 27, 2013 5:31 AM