none
SQL2008 Memory Utilization

    Question

  • Hello,

    I am running SQL 2008 server with Windows2K8 ent edition, I have assign 33 GB total memory to OS and on SQL i have configure an MAX limit is of 26GB.

    I have gradually increase this memory from 12GB to 26GB now, my question is that whenever i increase the max memory on SQL configuration, SQL server immediately occupied the complete assigned memory, i am really not sure what is the extract requirement or usage of the memory by SQL server.

    I have tried to run multiple queries to determine the actual usage /requirement of memory but unable to get the satisfied answer.

    below is the output of select * from sys.dm_os_sys_memory, 

    need help

    total_physical_memory_kb available_physical_memory_kb total_page_file_kb available_page_file_kb system_cache_kb kernel_paged_pool_kb kernel_nonpaged_pool_kb system_high_memory_signal_state system_low_memory_signal_state system_memory_state_desc
    34602552 3864064 69203260 37793084 4040156 180220 53868 1 0 Available physical memory is high

    Thanks in advance

    Manish

    Thursday, October 17, 2013 8:21 AM

All replies

  • Hi,

    By design, SQL Server will pretty much use all the memory you assign it.

    To start, in perfmon, look at total Server memory and target Server memory counters for SQL Server.  Target server memory is what SQL Server wants, and total is what it has.  If target is greater than total, that's one sign of memory pressure.

    Check Page Life Expectancy.  If it's low, i.e a few hundred seconds, it means that pages aren't staying in the buffer pool that long.

    Beyond that, look at other counters like lazy writes/sec, which if high, is another sign of memory pressure.

    Is it a dedicated SQL Server?  Do you run things like SSRS, etc?  If not, then you probably only need 4 or 5GB for the OS.  I tend to keep raising the SQL Server max server memory setting until there's only about 1GB free, after the OS has had its slice.


    Thanks, Andrew
    My blog...

    • Proposed as answer by vr.babu Thursday, October 17, 2013 8:59 AM
    Thursday, October 17, 2013 8:46 AM
  • Hello,

    Whats you are seeing is how SQL server memory is designed to behave ,if you give memory to SQL having many databases or large databases it immediately acquires memeory to cache as much as data page as possible to avoid hard page faults,this can also happen on SQL server having DB of less size this is something which i have observed  .If you run any query which requests data page, this would be satisfied from page in memory not from picking data page from disk (if possible) which is a costly operation so SQL does it for your own benefit.

    Please paste output of following query

    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 

    I assume you have configured correct value for Max memory setting but please refer to below article

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


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




    • Proposed as answer by vr.babu Thursday, October 17, 2013 8:59 AM
    • Edited by Shanky_621 Thursday, October 17, 2013 9:14 AM
    Thursday, October 17, 2013 8:56 AM
  • Hi Shanky

    Thanks for your reply,

    Please find the below output of given command.

    sql_physmem_inuse_mb awe_memory_mb max_vas_mb sql_committed_mb working_set_percentage vas_available_mb is_there_external_pressure is_there_vas_pressure
    27774 0 8388607 28133 100 8353720 0 0

    I have configured the max value keeping 5-6 GB memory available to OS. 

    Thursday, October 17, 2013 9:59 AM
  • Hi Andrew,

    Thanks for your reply,

    Yes, its a dedicated SQL server no SSRS and anyother service is running. I have checked both Server memory and target Server memory counters , its giving the same result and showing the total allocated memory.

    does this means that the server doesn;t require more memory ? if that is the case then can i reduce my sql server memory ? will this affect the performance of server in any manner ? 

    Regards

    Manish


     

    Thursday, October 17, 2013 10:03 AM
  • Hi Shanky

    Thanks for your reply,

    Please find the below output of given command.

    sql_physmem_inuse_mb awe_memory_mb max_vas_mb sql_committed_mb working_set_percentage vas_available_mb is_there_external_pressure is_there_vas_pressure
    27774 0 8388607 28133 100 8353720 0 0

    I have configured the max value keeping 5-6 GB memory available to OS. 

    Thanks for the output.

    From above I would advise you to first fix max server memory(which you already did) leaving 7 G for OS.Also as prev said you can recalculate it using link also if you are planning to use SSIS/SSRS/SSAS make sure you leave enough memory for it.

    Now awe_memory_mb=0 which means account running SQL server has no Locked pages in memory privilege.I would suggest you to give SQL server service account LPIM privilege, why , please read below article by Jonathan
    https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

    You can use below link to provide LPIM privilege to Account

    http://msdn.microsoft.com/en-us/library/ms190730.aspx

    PS: Make sure you are done with MAX memory setting before providing LPIM privilege


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


    • Edited by Shanky_621 Saturday, October 19, 2013 8:22 AM
    Thursday, October 17, 2013 10:08 AM