Low physical/external memory

| 0 comments | 4,099 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)

Determining if your operating system is causing memory issues within your SQL Server instance can be a challenge. One way around it is to use the sys.dm_os_ring_buffers to capture alerts registered there that show when server memory (also known as physical memory) is low. This is especially useful in situations where you’re running SQL Server on a shared environment with some other service or application.

For more information, see Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server.

You may also be interested in installing a metric that shows when virtual memory is low. See Low virtual memory.

Low physical/external memory, 5.0 out of 5 based on 1 rating

Metric definition

Metric Name:
Low physical/external memory
Description:
This metric uses the sys.dm_os_ring_buffers to identify low physical/external memory on the SQL Server system. It uses the RING_BUFFER_RESOURCE_MONITOR to identify when the latest alert has a Notification value of RESOURCE_MEMPHYSICAL_LOW.
Enter the T-SQL query that will collect data:
WITH    RingBuffer
          AS (SELECT    CAST(record AS XML) AS xRecord,
                        DATEADD(ss, (-1 * ((dosi.cpu_ticks / CONVERT (float,(dosi.cpu_ticks / dosi.ms_ticks))) - dorb.timestamp)/1000), GETDATE()) AS DateOccurred
              FROM      sys.dm_os_ring_buffers AS dorb
                        CROSS JOIN sys.dm_os_sys_info AS dosi
              WHERE     ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
             )
    SELECT TOP 1
            CASE (xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)'))
              WHEN 'RESOURCE_MEMPHYSICAL_LOW' THEN 1 
              ELSE 0
            END AS LowMemAlert
    FROM    RingBuffer AS rb
            CROSS APPLY rb.xRecord.nodes('Record') record (xr)
    WHERE   xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') = 'RESOURCE_MEMPHYSICAL_LOW' 
            AND rb.DateOccurred > DATEADD(mi, -5, GETDATE())
    ORDER BY rb.DateOccurred DESC;
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
master
Set collection frequency:
Collect data every 5 minutes
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Low physical/external memory
Description:
This alert is raised if an alert of type RESOURCE_MEMPHYSICAL_LOW has been sent to the ring buffers within the last five minutes.
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:0
Medium:Turn off
Low:Turn off
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)