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. <\/p>\r\n
For more information, see Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server<\/a>.<\/p>","tsql":"WITH RingBuffer\r\n AS (SELECT CAST(record AS XML) AS xRecord,\r\n DATEADD(ss,\r\n (-1 * ((dosi.cpu_ticks\r\n \/ CONVERT (FLOAT, (dosi.cpu_ticks\r\n \/ dosi.ms_ticks)))\r\n - dorb.timestamp) \/ 1000), GETDATE()) AS DateOccurred\r\n FROM sys.dm_os_ring_buffers AS dorb\r\n CROSS JOIN sys.dm_os_sys_info AS dosi\r\n WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'\r\n )\r\n SELECT TOP 1\r\n CASE (xr.value('(ResourceMonitor\/Notification)[1]', 'varchar(75)'))\r\n WHEN 'RESOURCE_MEMVIRTUAL_LOW' THEN 1 \r\n ELSE 0\r\n END AS LowMemAlert\r\n FROM RingBuffer AS rb\r\n CROSS APPLY rb.xRecord.nodes('Record') record (xr)\r\n WHERE xr.value('(ResourceMonitor\/Notification)[1]', 'varchar(75)') = 'RESOURCE_MEMVIRTUAL_LOW' \r\n AND rb.DateOccurred > DATEADD(mi, -5, GETDATE())\r\n ORDER BY rb.DateOccurred DESC;","instances":true,"frequency":300,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Low virtual memory","alertdescription":"This alert is raised if an alert of type RESOURCE_MEMVIRTUAL_LOW has been sent to the ring buffers within the last five minutes.","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":0},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"targetentitytype":0}