memory not getting released to OS

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  September 22, 2019 at 9:52 am

  #3682325

  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.

  

  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: 80330

  More actions

  September 23, 2019 at 10:10 am

  #3682432

  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: 11551

  More actions

  September 23, 2019 at 11:39 am

  #3682451

  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: 2683

  More actions

  September 23, 2019 at 11:42 am

  #3682453

  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.