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.