04 July 2012

Out of memory errors

by Grant Fritchey

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.

The number of out of memory errors that have occurred within a rolling five minute window. If you just want to keep an eye out for any memory errors, you can watch the ring buffers for the Out of memory errors alert when it gets registered there. This won’t provide you with information about specifically what errors might be occurring, but it will help you keep an eye on the system to just be aware of the issue. Use this on a system that is basically configured and operating well as an indicator that you may have an issue. Should you begin to get these on a regular basis you’ll need to add additional memory monitoring.

Metric definition

Name

Out of memory errors

Description

This looks for the ring buffer alert type of Out Of Memory, RING_BUFFER_OOM by querying the DMO sys.dm_os_ring_buffers

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			--Ring Buffer OOM errors.
DECLARE @tmp INT;

SELECT  @tmp = 1
FROM    sys.dm_os_ring_buffers AS dorb
        CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE   dorb.ring_buffer_type = 'RING_BUFFER_OOM'
        AND DATEADD(ss,
                    (-1 * ((dosi.cpu_ticks / CONVERT (FLOAT, (dosi.cpu_ticks
                                                              / dosi.ms_ticks)))
                           - dorb.timestamp) / 1000), GETDATE()) > DATEADD(mi,
                                                              -5, GETDATE());

SELECT ISNULL(@tmp, 0);
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
				
						--Ring Buffer OOM errors.
DECLARE @tmp INT;
 
SELECT  @tmp = 1
FROM    sys.dm_os_ring_buffers AS dorb
        CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE   dorb.ring_buffer_type = 'RING_BUFFER_OOM'
        AND DATEADD(ss,
                    (-1 * ((dosi.cpu_ticks / CONVERT (FLOAT, (dosi.cpu_ticks
                                                              / dosi.ms_ticks)))
                           - dorb.timestamp) / 1000), GETDATE()) > DATEADD(mi,
                                                              -5, GETDATE());
 
SELECT ISNULL(@tmp, 0);

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

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

Out of memory errors

Description

This alert fires if an Out Of Memory alert has been registered during the preceding five minutes.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	0
Medium:	Turn off check box
Low:	Turn off check box

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

13,935 Rate

Click to rate this post!

[Total: 2 Average: 5]

Out of memory errors

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