Is SQL Server Showing Memory Pressure
Published: Jul 31, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron