SQL Server Instance consuming 95% memory

  • Hi

    I have a  production DB Instance consuming 95% memory,  JUST one database consuming all memory

    All articles say - set max server memory on SQL   as the only setting and enable AWE to allocate memory.

    Out of 32 GB- 28 GB is allocated to SQL Server and its at 95%  and I'm getting alerted.

    These are top waits:

    BROKER_RECEIVE_WAITFOR    14728997213
    BROKER_TRANSMITTER    491847414
    BROKER_EVENTHANDLER    286013973
    How to fix this before the box crashes. This is production,so we cannot reboot whenever we want!

    Thank you

  • This was removed by the editor as SPAM

  • sqlguy80 - Thursday, March 9, 2017 9:26 PM

    Hi

    I have a  production DB Instance consuming 95% memory,  JUST one database consuming all memory

    All articles say - set max server memory on SQL   as the only setting and enable AWE to allocate memory.

    Normal, expected, documented behaviour. The articles are correct, set max server memory (AWE is not needed)

    These are top waits:

    BROKER_RECEIVE_WAITFOR    14728997213
    BROKER_TRANSMITTER    491847414
    BROKER_EVENTHANDLER    286013973

    Those are benign waits, they're normal to see on any server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • it is normal for SQL Server to use as much memory as it can, since it wants to cache things like your data, indexes, query execution plans, etc in memory so it's not so dependent on disk I/O.  You can get a simple overview like:

    SELECT sm.total_physical_memory_kb / 1024 AS physical_mem,
        pm.physical_memory_in_use_kb / 1024 AS sql_physical_used,
        pm.locked_page_allocations_kb / 1024 as locked_in_mem,
        c.value_in_use AS config_max,
        sm.available_physical_memory_kb / 1024 AS avail_physical,
        sm.system_cache_kb / 1024 AS system_cache,
        pm.page_fault_count AS sql_page_faults, pm.memory_utilization_percentage AS mem_utilized_pct,
        sm.system_memory_state_desc
      FROM sys.dm_os_sys_memory sm
        CROSS JOIN sys.dm_os_process_memory pm
        CROSS JOIN sys.configurations c
      WHERE c.name = 'max server memory (MB)'

    There are some good scripts available to look at your wait stats and ignore the ones that don't really matter such as at:
    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    https://www.brentozar.com/archive/2014/03/t-sql-tuesday-stop-tuning-wait-stats-percentages/

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

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