Low virtual memory

January 15, 2013 by Grant Fritchey | 1 Comment | 1,101 views
Categories: Memory

Rating: 0.0/5 (0 votes cast)

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

Metric 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.

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_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(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 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 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