This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

SQL Server Memory Exhaustion - but still have memory

We have been getting an advisory condition for  SQL Server Memory Exhaustion but we have plenty of memory available.  Can someone explain what drives this condition?  There's 110GB allocated to SQL Server and it appears we are using around 80GB and yet that alert in the upper left  is for memory exhaustion.     Where should I be looking?

  • I look at this and see that the number of memory grants requested is possibly causing this alert to fire.  PLE took a hit but recovered.  These are relating to SQL Server Memory pressure    If you are able to run this query ...

       (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory',
       (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)',
       (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)',
       (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)',
       (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
       (SELECT ((total_physical_memory_kb - available_physical_memory_kb)*1.0/total_physical_memory_kb*1.00 )*100.00  FROM sys.dm_os_sys_memory) as Mem_Consumed_Percentage,
       (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
       GETDATE() AS 'Data Sample Timestamp'
    This will tell you the [system_memory_state_desc] and if you are experiencing  memory pressure at the OS layer and other metrics.
     [system_memory_state_desc]  has only 3 possible values.
    • Available physical memory is high
    • Available physical memory is low
    • Physical memory usage is steady
    If you see anything but [Available physical memory is high] then you are under Memory pressure at the OS Layer.  
    Specifically to this advisory condition, if the queries requesting Memory grants are addressed this will no longer fire.   
    If you find that you can not modify the code, you can tweak this condition to be less sensitive by adjusting the trigger threshold.   
    Here is a great post from Allen White on this exact topic.  
  • Thanks for the response.  I will run this tomorrow when the load is high and see what comes up.

    I meant to note that my system memory at the time showed 80+GB available so I'm expecting the query results to be "Available physical memory is high" (as it is now).

  • yeah, just wanted to be sure there was no OS memory pressure, which can lead to server instability.  next step will be tackling queries with high memory grants.  Have you tried Erik Darlings sp_pressuredector.  It can help as well diagnose some of this stuff.