Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Large Object in buffer cache Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 7:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:11 AM
Points: 221, Visits: 860
Hello
We are using Redgates SQL Monitor tool and I am getting 'Large object in buffer cache' alert message - I have read that I need to find out what the object is and that this object may be suitable for compression or a schema revision (data type changes or sparse columns).
I have ran the following query:

USE [tempdb]
GO
SELECT COUNT(*) AS cached_pages_count ,
( COUNT(*) * 8.0) / 1024 AS MB ,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
database_id
ORDER BY cached_pages_count DESC;

and the results are thus :

Cached_pages_count MB DataBase_Name
344695 2692.929687 a
336681 2630.320312 b
320527 2504.117187 c
154540 1207.343750 d

Can anybody advise on how i deal with the alert
Post #1443274
Posted Wednesday, April 17, 2013 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 8:28 AM
Points: 2, Visits: 61
I would suggest that you modify the t-sql that you are using for the metric to resemble this. It should include the object name.

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 *,
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;
Post #1443480
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse