EdVassie (3/5/2014)
I have Buffer Pool Extension enabled and want to get the object name for all pages shown in sys.dm_os_buffer_descriptors.When a page is in the BPE and no longer in the Bufferpool, the normal way of working this out via allocation_unit_id cannot be used as that column is null as shown below.
Does anyone know a SQL query that can get to the object_id using just the database_id, file_id and page_id WITHOUT using DBCC PAGE. I need to get a result set that includes the object_id for every row in sys.dm_os_buffer_descriptors.
Check this one ..
SELECT *, OBJ.name AS Object_Name
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT 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_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
--WHERE database_id = db_id()
--
SQLBuddy