memory not getting released to OS

  • vsamantha35

    SSChampion

    Points: 11042

    Hi All,

    Need help here.

    Why is sql server is not releasing memory to OS even after lowering the max server memory setting from 61GB to 55GB ? Total RAM 64gb on server. This was onboarded 2 days back. max server memory was not set initially and so it utilized all memory and so we got an alert that 97% mem usage on the server. Lock pages in memory is disabled. There were no active sessions on the server.

    Using Microsoft SQL Server 2016 (SP2) Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

    Second question is, what does available_commit_limit_kb from [sys].[dm_os_process_memory] DMV mean in layman terms ???

    PFA attached screenshot on task mgr and DMV output.

    123-dmv

    Queries used :

    -- Get configuration values for instance

    SELECT @@servername as ServerName,name, cast(value_in_use as int)/1024 as value_in_gb, [description] FROM sys.configurations

    where name in ('max server memory (MB)','min server memory (MB)')

    ORDER BY name ;

    -- OS memory

    SELECT cast(ROUND([total_physical_memory_kb]/1024./1024.,2) as numeric(36,2)) as total_mem_gb,

    cast(ROUND([available_physical_memory_kb]/1024./1024.,2) as numeric(36,2)) as Avl_mem_gb,

    cast(ROUND([total_page_file_kb]/1024./1024.,2) as numeric(36,2)) as total_page_mem_gb,

    cast(ROUND([available_page_file_kb]/1024./1024.,2) as numeric(36,2)) as Avl_page_mem_gb,

    [system_memory_state_desc]

    FROM [sys].[dm_os_sys_memory] WITH (NOLOCK)

    OPTION (RECOMPILE);

    GO

    -- SQL Server Process Address space info

    SELECT cast(ROUND([physical_memory_in_use_kb]/1024./1024.,2) as numeric(36,2)) as total_sql_phy_mem_in_use_gb,

    cast(ROUND([locked_page_allocations_kb]/1024./1024.,2) as numeric(36,2)) as Locked_pages_gb,

    [page_fault_count],

    [memory_utilization_percentage],

    cast(ROUND([available_commit_limit_kb]/1024./1024.,2) as numeric(36,2)) as avl_commit_gb,

    [process_physical_memory_low],

    [process_virtual_memory_low]

    FROM [sys].[dm_os_process_memory] WITH (NOLOCK)

    OPTION (RECOMPILE);

    GO

    --active sessions

    EXEC MASTER..sp_WhoIsActive

    @show_sleeping_spids= 0,

    @OUTPUT_COLUMN_LIST =

    '[session_id],[blocking_session_id],[dd hh:mm:ss.mss],[start_time],[database_name],[status],[open_tran_count],[login_name],[host_name],[program_name],[sql_command],[sql_text]';

    go

    Thanks,

    Sam

  • Site Owners

    SSC Guru

    Points: 80380

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • vsamantha35

    SSChampion

    Points: 11042

    The issue is resolved. It took some time but it lowered the memory usage.Not sure if CHECKPOINT is writing to disk at that time.

  • DinoRS

    SSCrazy

    Points: 2518

    hmm according to my numbers

    59392 / 1024 = 58

    which your own output confirms that SQL Server does stay within that Limit (57.15 GB in Use), so if you do like to lower the Memory Limit for SQL Server to 55 GB, you should first revisit your calculation of 55 GB -> MB and then use that as Max Server Memory Option.

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

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