Low physical/external memory

February 13, 2013 by Grant Fritchey | 0 comments | 1,884 views
Categories: Memory

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.

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(mm, -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