Top buffer cache object

This metric measures the amount of memory used in the buffer cache by the largest object (based on the number of pages). It checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used.

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.

This metric measures the amount of memory used in the buffer cache by the largest object (based on the number of pages). It checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used. You should use this metric if you want to monitor what is in the buffer area, or if you are having performance-related disk read problems.

Memory is one of the most important resources for SQL Server, so it’s important to make sure SQL Server is using it efficiently. For example, if 90% of the buffer pool (memory area) is being used to store data from one table, it is important to try to optimize the size of this table to save space for other tables in memory. It is very common for one or two objects to be responsible for using a large amount of the buffer cache. To increase the efficiency of the buffer cache area, these objects may benefit from a schema revision (datatype changes or sparse columns), and are great candidates for compression.

For more information, see http://blogs.msdn.com/b/chadboyd/archive/2007/02/02/sys-dm-os-buffer-descriptors-aggregations.aspx and http://www.simple-talk.com/community/blogs/jonathanallen/archive/2012/06/12/110696.aspx.

Note: As with all metrics, you must test this T-SQL script before running it in a production environment. This metric will scan the buffer cache, so if you have a server with a lot of memory, this may take some time to run.

Metric definition

Name

Top buffer cache object

Description

This metric measures the amount of memory used in the buffer cache by the largest object (based on the number of pages). It checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used. You should use this metric if you want to monitor what is in the buffer area, or if you are having performance-related disk read problems.

Memory is one of the most important resources for SQL Server, so it's important to make sure SQL Server is using it efficiently. For example, if 90% of the buffer pool (memory area) is being used to store data from one table, it is important to try to optimize the size of this table to save space for other tables in memory. It is very common for one or two objects to be responsible for using a large amount of the buffer cache. To increase the efficiency of the buffer cache area, these objects may benefit from a schema revision (datatype changes or sparse columns), and are great candidates for compression.

For more information, see http://blogs.msdn.com/b/chadboyd/archive/2007/02/02/sys-dm-os-buffer-descriptors-aggregations.aspx and http://www.simple-talk.com/community/blogs/jonathanallen/archive/2012/06/12/110696.aspx.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

1

Collection frequency

86400

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

Large object in buffer cache

Description

This alert is raised when the amount of memory used in the buffer cache by the largest object (based on the number of pages) goes above a specified threshold. It can indicate what is happening in the buffer area, or whether you are having performance-related disk read problems. The metric on which this alert is based checks the sys.dm_os_buffer_descriptors to identify the object, and returns the relative percentage used.

Memory is one of the most important resources for SQL Server, so it's important to make sure SQL Server is using it efficiently. For example, if 90% of the buffer pool (memory area) is being used to store data from one table, it is important to try to optimize the size of this table to save space for other tables in memory. It is very common for one or two objects to be responsible for using a large amount of the buffer cache. To increase the efficiency of the buffer cache area, these objects may benefit from a schema revision (datatype changes or sparse columns), and are great candidates for compression.

For more information, see http://blogs.msdn.com/b/chadboyd/archive/2007/02/02/sys-dm-os-buffer-descriptors-aggregations.aspx and http://www.simple-talk.com/community/blogs/jonathanallen/archive/2012/06/12/110696.aspx.

It is very common that one or two objects being responsible to use a large area of the buffer cache. To increase the efficiency of the buffer cache area, these objects may benefit from a schema revision (datatype changes, or sparse columns), and are great candidates to be compressed. This metric shows the percent usage for the TOP object in the buffer cache based on the number of pages.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:90
Medium:70
Low:30

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