April 17, 2013 at 7:59 am
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
April 17, 2013 at 1:53 pm
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;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply