• State Not Answered
  • Locked Locked
  • Replies 3 replies
  • Subscribers 6 subscribers
  • Views 2691 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Server Memory Exhaustion - but still have memory

MichaelRogers

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?

Parents
  • 0

    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
       SERVERPROPERTY('SERVERNAME') AS 'Instance',
       (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.  
    Michael 
Reply
  • 0

    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
       SERVERPROPERTY('SERVERNAME') AS 'Instance',
       (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.  
    Michael 
Children
  • 0 in reply to TheSurfingDBA

    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).

  • 0 in reply to MichaelRogers

    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.  

    erikdarlingdata.com/sp_pressuredetector/

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.