13 February 2013

Low physical/external memory

by Grant Fritchey

14,625 0 Memory

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 show when server memory is low.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

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

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.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			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;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
				
						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;

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

300

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

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 threshholds

Default threshold values

High:	0
Medium:	Turn off
Low:	Turn off

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

14,625 Rate

Click to rate this post!

[Total: 3 Average: 4.7]

Low physical/external memory

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics