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.<\/p>\r\n\r\n
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.<\/p>\r\n\r\n
For more information, see http:\/\/blogs.msdn.com\/b\/chadboyd\/archive\/2007\/02\/02\/sys-dm-os-buffer-descriptors-aggregations.aspx<\/a> and http:\/\/www.simple-talk.com\/community\/blogs\/jonathanallen\/archive\/2012\/06\/12\/110696.aspx<\/a>. ","tsql":"WITH CTE_1\r\n AS (SELECT DB_NAME() AS dbName,\r\n obj.name AS objectname,\r\n ind.name AS indexname,\r\n COUNT(*) AS cached_pages_count\r\n FROM sys.dm_os_buffer_descriptors AS bd\r\n INNER JOIN (SELECT object_id AS objectid,\r\n OBJECT_NAME(object_id) AS name,\r\n index_id,\r\n allocation_unit_id\r\n FROM sys.allocation_units AS au \r\n INNER JOIN sys.partitions AS p\r\n ON\r\n au.container_id = p.hobt_id\r\n AND (au.type = 1\r\n OR au.type = 3)\r\n UNION ALL\r\n SELECT object_id AS objectid,\r\n OBJECT_NAME(object_id) AS name,\r\n index_id,\r\n allocation_unit_id\r\n FROM sys.allocation_units AS au \r\n INNER JOIN sys.partitions AS p\r\n ON\r\n au.container_id = p.partition_id\r\n AND au.type = 2\r\n ) AS obj\r\n ON\r\n bd.allocation_unit_id = obj.allocation_unit_id \r\n LEFT OUTER JOIN sys.indexes ind\r\n ON\r\n obj.objectid = ind.object_id\r\n AND obj.index_id = ind.index_id\r\n WHERE bd.database_id = DB_ID()\r\n AND bd.page_type IN ('data_page', 'index_page')\r\n GROUP BY obj.name,\r\n ind.name,\r\n obj.index_id\r\n )\r\n SELECT TOP 1 --*, -- Uncomment to return the object name\r\n ObjPercent = CONVERT(NUMERIC(18, 2),\r\n\t (CONVERT(NUMERIC(18, 2), cached_pages_count)\r\n \/ SUM(cached_pages_count) OVER ()) * 100)\r\n FROM CTE_1\r\n ORDER BY cached_pages_count DESC;\r\n","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Large object in buffer cache","alertdescription":" 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.<\/p>\r\n\r\n 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.<\/p>\r\n\r\n For more information, see http:\/\/blogs.msdn.com\/b\/chadboyd\/archive\/2007\/02\/02\/sys-dm-os-buffer-descriptors-aggregations.aspx<\/a> and http:\/\/www.simple-talk.com\/community\/blogs\/jonathanallen\/archive\/2012\/06\/12\/110696.aspx<\/a>. \r\n\r\nIt 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.\r\n\r\nThis metric shows the percent usage for the TOP object in the buffer cache based on the number of pages.\r\n","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":90},"_thresholds_medium":{"selected":true,"value":70},"_thresholds_low":{"selected":true,"value":30},"thresholds_comment":"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.","targetentitytype":0}