Get Object Name from database_id, file_id, Page_id
-
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.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
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_NameFROM 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
-
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.
SELECT *FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
sqlbuddy123 (3/5/2014)
Check this one ..SELECT *, OBJ.name AS Object_NameFROM 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()
How would that work when the allocation_unit_id is null, as shown in the image and as Ed explicitly stated?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.SELECT *FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID
Should work. I might add database_id to the predicate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (3/5/2014)
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.SELECT *FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID
Should work. I might add database_id to the predicate.
No need, because the first parameter to the function limits the returned resultset to just the DB of interest. It's like index_physical_stats.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/5/2014)
SQLRNNR (3/5/2014)
GilaMonster (3/5/2014)
Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.SELECT *FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa
WHERE allocated_page_file_id = @FileID AND allocated_page_page_id = @PageID
Should work. I might add database_id to the predicate.
No need, because the first parameter to the function limits the returned resultset to just the DB of interest. It's like index_physical_stats.
Fair point. I ran it and passed the DBName to the parameter. So, dunno what I was thinking.:Whistling:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Gail's code looks like it might work 🙂
I am out of office until Monday 10 and will check it out then. Thanks for the suggestion!
BTW, Buffer Pool Extension is a really good addition to SQL Server. We put a 400GB BPE file on to our 60GB BI server and when I last checked we were getting about 70% hit rate on the BPE, and a lot more throughput on user reporting. IMHO anyone considering SQL2014 backed by spinning disks should aim to get SSD space and exploit the BPE.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply