Is SQL Server Showing Memory Pressure
Published: 20th November 2014
This is something I’ve never really been able to prove but have now found, courtesy of 2 VERY good posts (Grant Fritchey and Jonathan Kehayais respectively - links to follow), that it’s actually quite simple to track and monitor in order to ascertain whether or not your SQL Server is experiencing memory pressure.

This will be a very basic description, mostly because I’m basically just logging the code on my website so that I can access it more easily (as I would otherwise have to hunt Grant and Jonathan’s scripts down each time), but should suffice if you don’t want to read their more intensive technical explanations.

If you want to read the original articles then just click the respective name: (Grant | Jonathan).

So what are we trying to show?

Basically we’re going to let SQL Server tell us when it does or doesn’t get squeezed for memory by Windows.

The code we’ll run is as follows (it’s actually a merge of the code found in the 2 blogs):

SELECT  
    
EventTime,
    
record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
      
record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
      
record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem
FROM
(
      
SELECT
        
DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,
        
CONVERT (xml, record) AS record
    
FROM sys.dm_os_ring_buffers
    
CROSS JOIN sys.dm_os_sys_info
    
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
) AS tab
ORDER BY EventTime DESC


Which in my test system (in which I forced a poor memory situation) returns the following:

The RmNotification column is to be read as follows:

  • RESOURCE_MEM_STEADY
    • No memory issues (the order above is inconsistent due to the timestamps, but these usually follow a memory low condition to say that Windows is happy with the new memory level achieved)

  • RESOURCE_MEMPHYSICAL_LOW
    • Windows is running low on memory and SQL Server must return some

  • RESOURCE_MEMPHYSICAL_HIGH
    • Windows has spare memory and SQL Server can take some more if required

If the IndicatorsSystem value is greater than zero then this means that the memory situation was server-wide.

The IndicatorsProcess value means that it was a specific process which ran into the memory condition and can be one of 3 values:

  • 1 = High Physical Memory
  • 2 = Low Physical Memory
  • 3 = Low Virtual Memory

Sadly we can’t find out which process without hooking up an Extended Events session and correlating the results, but at least we do know that SQL Server is being affected by memory conditions.

Therefore the next time you suspect there is memory pressure affecting your SQL Server you will now be able to run a quick query to validate your assumption and see when these issues are happening.
Comments:
sunil
08/06/2017 11:09:00
i can see in my environment IndicatorsSystem value is greater than zero.

in your post you said it is server wide. what it means?

can you please explain me in detail.
Kevin Urquhart
08/06/2017 13:53:00
Hi Sunil,

It basically means that the memory condition was at the server level. Ie. Windows was reporting low memory and requesting that SQL Server give some back.

This likely means that you're max memory settings are too high or that you have other memory intensive processes kicking off in Windows which are competing for memory.

Thanks,
Kevin
NB: Comments will only appear once they have been moderated.