Top buffer cache object

| 0 comments | 9,149 views
Categories: ,

VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)

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

Metric 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.

Enter the T-SQL query that will collect data:
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;
Select instances to collect from:
Select all
Choose databases to collect from:
All user databases
Set collection frequency:
Collect data every day
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:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)