Low virtual memory

| 2 Comments | 3,108 views
Categories:

VN:F [1.9.17_1161]
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(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 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
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
  • Adama

    The DateAdd function should be changed to DATEADD(mi, -5, GETDATE())

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • SQL Monitor Team

    Hi Adama,
    Thanks for pointing this out – we’ve updated the T-SQL accordingly.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)