15 January 2013

Low virtual memory

by Grant Fritchey

12,226 0 Memory

Determining if your SQL Server instance is experiencing memory issues within the SQL Server Virtual Address Space (VAS) can be a challenge. One way around it is to use the sys.dm_os_ring_buffers.

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 SQL Server instance is experiencing memory issues within the SQL Server Virtual Address Space (VAS) 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 virtual memory (also known as internal 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 physical/external memory is low. See Low physical/external memory.

Metric definition

Name

Low Virtual Memory

Description

This metric uses the sys.dm_os_ring_buffers to identify low virtual 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_MEMVIRTUAL_LOW.

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

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_MEMVIRTUAL_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_MEMVIRTUAL_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
18
19
20
21
				
						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_MEMVIRTUAL_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_MEMVIRTUAL_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 virtual memory

Description

This alert is raised if an alert of type RESOURCE_MEMVIRTUAL_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

12,226 Rate

Click to rate this post!

[Total: 2 Average: 5]

Low virtual 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