Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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
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
) AS tab

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:

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

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

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

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.