none
Memory allocation RRS feed

  • Question

  • Dear All,

    Today one of my app team person had come up with a concern. His application and SQL server both are running on same server. And they have assigned 75% percent of memory to SQL server and remaing is for OS. Now a days he is receivng some windows alerts regarding to memory 'Memory usage reached to critical'. So they are looking to reduce the assigned memory size of SQL server(75%). So if they reduced same, the OS/App can use the same. We suggested them that SQL need 75% of physical memory but still they want to reduce the same. I have collected permon counters output and found that everthying was looking good. Please clarify me on below questions.

    How can we check whether assigned 'Max server memory' for SQL server is using properly or not?

    Assigning 75% of Physical memory to SQL server is correct?

    Is that the best practice to maintain application and Database on same server?

    What is the SQL Server: Buffer Manager:Page Life Expectancy counters recommended value.?(In our server the value is almost all times above than 109429) so is this a good sign?

    Need your help to get rid from concerns and also to increase my skillset.

    Thanks in advance,

    Nagesh


    Nagesh


    • Edited by Nagesh_969 Tuesday, June 3, 2014 2:30 PM
    Tuesday, June 3, 2014 2:28 PM

Answers

  • 1.How can we check whether assigned 'Max server memory' for SQL server is using properly or not?

    2. Assigning 75% of Physical memory to SQL server is correct?

    3. Is that the best practice to maintain application and Database on same server?

    4. What is the SQL Server: Buffer Manager:Page Life Expectancy counters recommended value.?(In our server the value is almost all times above than 109429) so is this a good sign?


    1. Your question is little incorrect SQL server will use all the buffer pool memory assigned dynamically and as per requirement. Memory managent is dynammic and SQL server uses and trims buffer pool as per requirement. Even though memory is not required SQL Server may hold buffer pool completly so to a person novice in SQL server it seems SQL server is leaking memory ( its not realeasing memory) below query can help you see SQL server memory utilization

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    2. Its difficult to answer this question but at first go assigning 75 % seems ok. YOu should not rely on this but should use perfmon counters to actually know your SQL server memory utilization.

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

    3 Simply NO

    4. Refer to section 'Does SQL server has low memory' in below article to know more about these counters

    http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx#Does_my_system_have_low_memory

    From what you posted its a very good sign. Page life expactancy should be as high as possible . It is amount of time page remains in memory if page remain in memory for long it does not signify a memory pressure as SQL server would not have to allocate I/O( which is costly) to get page from disk to memory

    Revert if any issue.Please read Wiki article I have mentioned almost all points related to SQL server memory in it.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    Tuesday, June 3, 2014 2:45 PM
    Moderator

All replies

  • 1.How can we check whether assigned 'Max server memory' for SQL server is using properly or not?

    2. Assigning 75% of Physical memory to SQL server is correct?

    3. Is that the best practice to maintain application and Database on same server?

    4. What is the SQL Server: Buffer Manager:Page Life Expectancy counters recommended value.?(In our server the value is almost all times above than 109429) so is this a good sign?


    1. Your question is little incorrect SQL server will use all the buffer pool memory assigned dynamically and as per requirement. Memory managent is dynammic and SQL server uses and trims buffer pool as per requirement. Even though memory is not required SQL Server may hold buffer pool completly so to a person novice in SQL server it seems SQL server is leaking memory ( its not realeasing memory) below query can help you see SQL server memory utilization

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory

    2. Its difficult to answer this question but at first go assigning 75 % seems ok. YOu should not rely on this but should use perfmon counters to actually know your SQL server memory utilization.

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

    3 Simply NO

    4. Refer to section 'Does SQL server has low memory' in below article to know more about these counters

    http://social.technet.microsoft.com/wiki/contents/articles/22316.sql-server-memory-and-troubleshooting.aspx#Does_my_system_have_low_memory

    From what you posted its a very good sign. Page life expactancy should be as high as possible . It is amount of time page remains in memory if page remain in memory for long it does not signify a memory pressure as SQL server would not have to allocate I/O( which is costly) to get page from disk to memory

    Revert if any issue.Please read Wiki article I have mentioned almost all points related to SQL server memory in it.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    Tuesday, June 3, 2014 2:45 PM
    Moderator
  • HI

    How can we check whether assigned 'Max server memory' for SQL server is using properly or not?

    Easy way is to open task manager and see SQL process memory

    Assigning 75% of Physical memory to SQL server is correct?

    Yes, this is best followed but depends on application size

    Is that the best practice to maintain application and Database on same server?

    Smaller Application is fine however in production or larger application lisk SAP etc will be on different servers

    What is the SQL Server: Buffer Manager:Page Life Expectancy counters recommended value.?(In our server the value is almost all times above than 109429) so is this a good sign?

    Indicates the number of seconds a page will stay in the buffer pool without references. cannot commet unless i know the app configuration and memory other paramaters but seems to be okay

    Tuesday, June 3, 2014 2:59 PM
  • HI

    How can we check whether assigned 'Max server memory' for SQL server is using properly or not?

    Easy way is to open task manager and see SQL process memory

    No Task manager should never be used to SQL server memory allocation. it will show incorrect value if SQL server service account has locked pages in memory privilege . Task Manager do not account for memory correctly if Address Windowing Extensions (AWE) support is enabled for 64 Bit system. Actually what task manager shows you is memory consumed by working set( private byte) not total memory. Total memory consumed can be obtained by Query I gave

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Tuesday, June 3, 2014 3:15 PM
    Moderator
  • Dear Shanky,

    Thanks for you ontime reply/support with valid suggesions, which is helped me alot.

    Regards,

    Nagesh


    Nagesh

    Wednesday, June 4, 2014 10:27 AM
  • Dear Guna,

    Thanks for you reply and valid suggestions.


    Nagesh

    Wednesday, June 4, 2014 10:30 AM