04 July 2012

Top buffer cache object

by Fabiano Amorim

14,079 0 Memory, Performance

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

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.

The T-SQL query that will collect data


		
			
			
Transact-SQL
			
			WITH  CTE_1
        AS (SELECT DB_NAME() AS dbName,
                obj.name AS objectname,
                ind.name AS indexname,
                COUNT(*) AS cached_pages_count
              FROM sys.dm_os_buffer_descriptors AS bd
              INNER JOIN (SELECT object_id AS objectid,
                              OBJECT_NAME(object_id) AS name,
                              index_id,
                              allocation_unit_id
                            FROM sys.allocation_units AS au 
                            INNER JOIN sys.partitions AS p
                            ON
                              au.container_id = p.hobt_id
                              AND (au.type = 1
                              OR au.type = 3)
                          UNION ALL
                          SELECT object_id AS objectid,
                              OBJECT_NAME(object_id) AS name,
                              index_id,
                              allocation_unit_id
                            FROM sys.allocation_units AS au 
                            INNER JOIN sys.partitions AS p
                            ON
                              au.container_id = p.partition_id
                              AND au.type = 2
                         ) AS obj
              ON
                bd.allocation_unit_id = obj.allocation_unit_id 
              LEFT OUTER JOIN sys.indexes ind
              ON
                obj.objectid = ind.object_id
                AND obj.index_id = ind.index_id
              WHERE bd.database_id = DB_ID()
                AND bd.page_type IN ('data_page', 'index_page')
              GROUP BY obj.name,
                ind.name,
                obj.index_id
           )
  SELECT TOP 1 --*, -- Uncomment to return the object name
      ObjPercent = CONVERT(NUMERIC(18, 2),
	    (CONVERT(NUMERIC(18, 2), cached_pages_count)
        / SUM(cached_pages_count) OVER ()) * 100)
    FROM CTE_1
    ORDER BY cached_pages_count DESC;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
				
						WITH  CTE_1
        AS (SELECT DB_NAME() AS dbName,
                obj.name AS objectname,
                ind.name AS indexname,
                COUNT(*) AS cached_pages_count
              FROM sys.dm_os_buffer_descriptors AS bd
              INNER JOIN (SELECT object_id AS objectid,
                              OBJECT_NAME(object_id) AS name,
                              index_id,
                              allocation_unit_id
                            FROM sys.allocation_units AS au 
                            INNER JOIN sys.partitions AS p
                            ON
                              au.container_id = p.hobt_id
                              AND (au.type = 1
                              OR au.type = 3)
                          UNION ALL
                          SELECT object_id AS objectid,
                              OBJECT_NAME(object_id) AS name,
                              index_id,
                              allocation_unit_id
                            FROM sys.allocation_units AS au 
                            INNER JOIN sys.partitions AS p
                            ON
                              au.container_id = p.partition_id
                              AND au.type = 2
                         ) AS obj
              ON
                bd.allocation_unit_id = obj.allocation_unit_id 
              LEFT OUTER JOIN sys.indexes ind
              ON
                obj.objectid = ind.object_id
                AND obj.index_id = ind.index_id
              WHERE bd.database_id = DB_ID()
                AND bd.page_type IN ('data_page', 'index_page')
              GROUP BY obj.name,
                ind.name,
                obj.index_id
           )
  SELECT TOP 1 --*, -- Uncomment to return the object name
      ObjPercent = CONVERT(NUMERIC(18, 2),
	    (CONVERT(NUMERIC(18, 2), cached_pages_count)
        / SUM(cached_pages_count) OVER ()) * 100)
    FROM CTE_1
    ORDER BY cached_pages_count DESC;

Instances to collect from

Select all

Databases to collect from

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.

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

14,079 Rate

Click to rate this post!

[Total: 0 Average: 0]

Top buffer cache object

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