none
sql memory related question RRS feed

  • Question

  • 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




    Sunday, September 22, 2019 9:51 AM

Answers

  • 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.
    • Marked as answer by Samantha v Monday, September 23, 2019 11:39 AM
    Monday, September 23, 2019 11:39 AM

All replies