July 6, 2015 at 4:56 am
select
count(*)as cached_pages_count,
obj.name as objectname,
ind.name as indexname,
obj.index_id as indexid
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
order by cached_pages_count desc
Above query is returning very high values in cached_pages_count as below. Could you please explain the reason for value cached_pages_count is very high and is it risky. if it is risky, could you please share a solution for it.
cached_pages_countobjectname indexname indexid
1511157 audit_activity PK__audit_ac__321418600268428D1
1467836 tran_master tran_mst_pk 1
457599 agent_settlement agent_settlement_pk 1
There many indexes which has cached_pages_count is higher.
July 6, 2015 at 9:35 am
That query is just counting the number of pages SQL Server has cached in memory for each object's indexes. Having frequently accessed pages in memory is a good thing, since reading from memory is much faster than reading from disk.
All that output is showing you is that some indexes have several GB of data cached in memory, which isn't a problem. If those pages weren't being accessed, and other pages were, then those pages would get pushed out of the cache anyway.
All in all, there's nothing to fix there; it's just SQL Server working as intended.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply